MySQL尾部空格處理方法詳解
在之前的這篇文章Oracle/MySQL/PG/SQL Server關(guān)系數(shù)據(jù)庫中NULL與空字符串的區(qū)別中，簡單對比、介紹了一下MySQL的NULL/空格/尾部空格處理方式，主要對比了NULL與長度為空的字符串，其實很多地方?jīng)]有分析到位就一筆帶過了。這篇文章重新來細(xì)說一下MySQL的尾部空格處理方式。在MySQL中，有幾個因素會影響MySQL如何處理空格。這里簡單淺析一下.
MySQL的排序規(guī)則有一個屬性Pad Attributes屬性，這個屬性的設(shè)置會影響數(shù)據(jù)庫如何處理尾部空格（是否忽略尾部空格），如下官方文檔描述
Collation?Pad?Attributes Collations?based?on?UCA?9.0.0?and?higher?are?faster?than?collations?based?on?UCA?versions?prior?to?9.0.0.?They?also?have?a?pad?attribute?of?NO?PAD,?in?contrast?to?PAD?SPACE?as?used?in?collations?based?on?UCA?versions?prior?to?9.0.0.?For?comparison?of?nonbinary?strings,?NO?PAD?collations?treat?spaces?at?the?end?of?strings?like?any?other?character?(see?Trailing?Space?Handling?in?Comparisons). Comparison?of?nonbinary?string?values?(CHAR,?VARCHAR,?and?TEXT)?that?have?a?NO?PAD?collation?differ?from?other?collations?with?respect?to?trailing?spaces.?For?example,?'a'?and?'a?'?compare?as?different?strings,?not?the?same?string.?This?can?be?seen?using?the?binary?collations?for?utf8mb4.?The?pad?attribute?for?utf8mb4_bin?is?PAD?SPACE,?whereas?for?utf8mb4_0900_bin?it?is?NO?PAD.?Consequently,?operations?involving?utf8mb4_0900_bin?do?not?add?trailing?spaces,?and?comparisons?involving?strings?with?trailing?spaces?may?differ?for?the?two?collations
官方文檔，關(guān)于比較中尾部空格處理介紹如下：
Trailing?Space?Handling?in?Comparisons MySQL?collations?have?a?pad?attribute,?which?has?a?value?of?PAD?SPACE?or?NO?PAD: ??Most?MySQL?collations?have?a?pad?attribute?of?PAD?SPACE. ??The?Unicode?collations?based?on?UCA?9.0.0?and?higher?have?a?pad?attribute?of?NO?PAD;?see?Section?10.10.1,?“Unicode?Character?Sets”. For?nonbinary?strings?(CHAR,?VARCHAR,?and?TEXT?values),?the?string?collation?pad?attribute?determines?treatment?in?comparisons?of?trailing?spaces?at?the?end?of?strings: ??For?PAD?SPACE?collations,?trailing?spaces?are?insignificant?in?comparisons;?strings?are?compared?without?regard?to?trailing?spaces. ??NO?PAD?collations?treat?trailing?spaces?as?significant?in?comparisons,?like?any?other?character. The?differing?behaviors?can?be?demonstrated?using?the?two?utf8mb4?binary?collations,?one?of?which?is?PAD?SPACE,?the?other?of?which?is?NO?PAD.
我們先來看看PAD SPACE與NO PAD的具體意義：
PAD SPACE：在排序和比較運算中，忽略字符串尾部空格。
NO PAD：在排序和比較運算中，字符串尾部空格當(dāng)成普通字符，不能忽略。
SELECT?*?
FROM?INFORMATION_SCHEMA.COLLATIONS
???????WHERE?CHARACTER_SET_NAME?=?'utf8mb4';
mysql>?select?@@version;
+-----------+
|?@@version?|
+-----------+
|?8.0.33????|
+-----------+
1?row?in?set?(0.00?sec)
mysql>?SELECT?*?
????->?FROM?INFORMATION_SCHEMA.COLLATIONS
????->????????WHERE?CHARACTER_SET_NAME?=?'utf8mb4'
????->???AND?COLLATION_NAME?IN('utf8mb4_general_ci','utf8mb4_0900_ai_ci');
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
|?COLLATION_NAME?????|?CHARACTER_SET_NAME?|?ID??|?IS_DEFAULT?|?IS_COMPILED?|?SORTLEN?|?PAD_ATTRIBUTE?|
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
|?utf8mb4_0900_ai_ci?|?utf8mb4????????????|?255?|?Yes????????|?Yes?????????|???????0?|?NO?PAD????????|
|?utf8mb4_general_ci?|?utf8mb4????????????|??45?|????????????|?Yes?????????|???????1?|?PAD?SPACE?????|
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
2?rows?in?set?(0.00?sec)
mysql>下面我們來測試演練一下，首先新建一個測試數(shù)據(jù)庫gsp
CREATE?DATABASE?IF?NOT?EXISTS?gsp??? DEFAULT?CHARACTER?SET?utf8mb4 DEFAULT?COLLATE?utf8mb4_general_ci; mysql>?create?table?test(id?int?not?null,?name?varchar(16)); Query?OK,?0?rows?affected?(0.01?sec) mysql>?insert?into?test(id?,?name)?values(1,?null); Query?OK,?1?row?affected?(0.00?sec) mysql>?insert?into?test(id?,?name)?values(2,?''); Query?OK,?1?row?affected?(0.01?sec) mysql>?insert?into?test(id?,?name)?values(3,?'?');?--?包含一個空格 Query?OK,?1?row?affected?(0.00?sec) mysql>?insert?into?test(id?,?name)?values(4,?'??');--?包含兩個空格 Query?OK,?1?row?affected?(0.00?sec) mysql>? #length獲取字符串字節(jié)長度的內(nèi)置函數(shù)， #char_length獲取字符串長度的內(nèi)置函數(shù) #hex將字符或數(shù)字轉(zhuǎn)換為十六進(jìn)制格式 mysql>?select?id,?length(name),?char_length(name),?hex(name)?from?test; +----+--------------+-------------------+-----------+ |?id?|?length(name)?|?char_length(name)?|?hex(name)?| +----+--------------+-------------------+-----------+ |??1?|?????????NULL?|??????????????NULL?|?NULL??????| |??2?|????????????0?|?????????????????0?|???????????| |??3?|????????????1?|?????????????????1?|?20????????| |??4?|????????????2?|?????????????????2?|?2020??????| +----+--------------+-------------------+-----------+ 4?rows?in?set?(0.00?sec) mysql>?select?*?from?test?where?name=''; +----+------+ |?id?|?name?| +----+------+ |??2?|??????| |??3?|??????| |??4?|??????| +----+------+ 3?rows?in?set?(0.00?sec) mysql> mysql>?select?*?from?test?where?name='?';?--?包含一個空格 +----+------+ |?id?|?name?| +----+------+ |??2?|??????| |??3?|??????| |??4?|??????| +----+------+ 3?rows?in?set?(0.00?sec) mysql>?select?*?from?test?where?name='??';?--?包含兩個空格 +----+------+ |?id?|?name?| +----+------+ |??2?|??????| |??3?|??????| |??4?|??????| +----+------+ 3?rows?in?set?(0.00?sec) mysql>
官方文檔中也要一個例子簡單說明，兩者比較時，如何處理尾部空格。如下所示，相當(dāng)直觀、明了：
mysql>?SET?NAMES?utf8mb4?COLLATE?utf8mb4_bin; mysql>?SELECT?'a?'?=?'a'; +------------+ |?'a?'?=?'a'?| +------------+ |??????????1?| +------------+ mysql>?SET?NAMES?utf8mb4?COLLATE?utf8mb4_0900_bin; mysql>?SELECT?'a?'?=?'a'; +------------+ |?'a?'?=?'a'?| +------------+ |??????????0?| +------------+
我們上面的例子中，gsp數(shù)據(jù)庫的排序規(guī)則為utf8mb4_general_ci，它的Pad Attributes屬性為PAD SPACE，在排序和比較運算中，數(shù)據(jù)庫將忽略字符串尾部空格，所以上面幾個SQL的查詢結(jié)果是一樣的。如果你數(shù)據(jù)庫的排序規(guī)則選擇utf8mb4_0900_ai_ci，那么Pad Attributes屬性為NO PAD，此時,在排序和比較運算中，字符串尾部空格當(dāng)成普通字符處理，不能忽略.我們知道MySQL的排序規(guī)則分為服務(wù)器級別，數(shù)據(jù)庫級別、表級別、列級別、SQL語句級別。優(yōu)先級順序為：SQL語句級別>列級別>表級別>數(shù)據(jù)庫級別>服務(wù)器級別。你可以重新建一個數(shù)據(jù)庫，設(shè)置數(shù)據(jù)庫排序規(guī)則為utf8mb4_0900_ai_ci，也可以修改其他級別的排序規(guī)則...。這里我們就直接修改表的列級別排序規(guī)則。
腳本如下所示：
drop?table?test; create?table?test(id?int?not?null,?name?varchar(16)?collate?utf8mb4_0900_ai_ci?); insert?into?test(id?,?name)?values(1,?null); insert?into?test(id?,?name)?values(2,?''); insert?into?test(id?,?name)?values(3,?'?');?--?包含一個空格 insert?into?test(id?,?name)?values(4,?'??');--?包含兩個空格
然后，我們測試一下，如下所示，此時由于數(shù)據(jù)庫將字符串尾部空格當(dāng)成普通字符，所以此時的查詢結(jié)果就不同了。如下所示
mysql>?select?*?from?test?where?name='';?--?長度為零的空字符串 +----+------+ |?id?|?name?| +----+------+ |??2?|??????| +----+------+ 1?row?in?set?(0.00?sec) mysql>?select?*?from?test?where?name='?';?--?含一個空格 +----+------+ |?id?|?name?| +----+------+ |??3?|??????| +----+------+ 1?row?in?set?(0.00?sec) mysql>?select?*?from?test?where?name='??';--?包含兩個空格 +----+------+ |?id?|?name?| +----+------+ |??4?|??????| +----+------+ 1?row?in?set?(0.00?sec) mysql>
另外，我們這里測試的是VARCHAR類型，如果字段類型為CHAR呢？
其實呢，對于CHAR類型和VARCHA類型，它們的存儲略有區(qū)別：
CHAR(N)：當(dāng)插入的字符數(shù)小于N，它會在字符串的右邊補充空格，直到總字符數(shù)達(dá)到N再進(jìn)行存儲；當(dāng)查詢返回數(shù)據(jù)時默認(rèn)會將字符串尾部的空格去掉，除非SQL_MODE設(shè)置PAD_CHAR_TO_FULL_LENGTH。
VARCHAR(N)：當(dāng)插入的字符數(shù)小于N，它不會在字符串的右邊補充空格，insert內(nèi)容原封不動的進(jìn)行存儲；如果原本字符串右邊有空格，在存儲和查詢返回時都會保留空格
The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.
PAD_CHAR_TO_FULL_LENGTH
By default, trailing spaces are trimmed from CHAR column values on retrieval. If PAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrieved CHAR values are padded to their full length. This mode does not apply to VARCHAR columns, for which trailing spaces are retained on retrieval.
Note: As of MySQL 8.0.13, PAD_CHAR_TO_FULL_LENGTH is deprecated. Expect it to be removed in a future version of MySQL.
注意事項：
PAD_CHAR_TO_FULL_LENGTH只影響CHAR類型，不影響VARCHAR類型。
MySQL 8.0.13后，PAD_CHAR_TO_FULL_LENGTH參數(shù)過時/廢棄了。這個參數(shù)可能在后續(xù)的MySQL版本中被移除。
當(dāng)前版本（MySQL 8.0.33）中，暫時還可以在SQL_MODE中設(shè)置這個參數(shù)，不過默認(rèn)不會設(shè)置此參數(shù)。那么我們來測試驗證一下：
drop?table?test; create?table?test(id?int?not?null,?name?char(16)??); insert?into?test(id?,?name)?values(1,?null); insert?into?test(id?,?name)?values(2,?''); insert?into?test(id?,?name)?values(3,?'?');?--?包含一個空格 insert?into?test(id?,?name)?values(4,?'??');--?包含兩個空格 mysql>?show?variables?like?'sql_mode'\G ***************************?1.?row?*************************** Variable_name:?sql_mode ????????Value:?ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 1?row?in?set?(0.00?sec) mysql>?--如下所示，SQL_MODE沒有設(shè)置PAD_CHAR_TO_FULL_LENGTH時，查詢返回數(shù)據(jù)時默認(rèn)會將字符串尾部的空格去掉，所以你看到長度為零 mysql>?select?id,?length(name),?char_length(name),hex(name)?from?test; +----+--------------+-------------------+-----------+ |?id?|?length(name)?|?char_length(name)?|?hex(name)?| +----+--------------+-------------------+-----------+ |??1?|?????????NULL?|??????????????NULL?|?NULL??????| |??2?|????????????0?|?????????????????0?|???????????| |??3?|????????????0?|?????????????????0?|???????????| |??4?|????????????0?|?????????????????0?|???????????| +----+--------------+-------------------+-----------+ 4?rows?in?set?(0.00?sec) mysql>
那么我們手工設(shè)置一下當(dāng)前會話的SQL_MODE,然后對比測試一下：
mysql>?set?sql_mode='PAD_CHAR_TO_FULL_LENGTH'; Query?OK,?0?rows?affected,?1?warning?(0.01?sec) mysql>?show?variables?like?'sql_mode'; +---------------+-------------------------+ |?Variable_name?|?Value???????????????????| +---------------+-------------------------+ |?sql_mode??????|?PAD_CHAR_TO_FULL_LENGTH?| +---------------+-------------------------+ 1?row?in?set?(0.01?sec) mysql>?select?id,?length(name),?char_length(name),hex(name)?from?test; +----+--------------+-------------------+----------------------------------+ |?id?|?length(name)?|?char_length(name)?|?hex(name)????????????????????????| +----+--------------+-------------------+----------------------------------+ |??1?|?????????NULL?|??????????????NULL?|?NULL?????????????????????????????| |??2?|???????????16?|????????????????16?|?20202020202020202020202020202020?| |??3?|???????????16?|????????????????16?|?20202020202020202020202020202020?| |??4?|???????????16?|????????????????16?|?20202020202020202020202020202020?| +----+--------------+-------------------+----------------------------------+ 4?rows?in?set?(0.00?sec) mysql>
通過上面的分析講述，我們知道當(dāng)數(shù)據(jù)庫的排序規(guī)則的Pad Attributes屬性為NO PAD時，此時SQL_MODE的PAD_CHAR_TO_FULL_LENGTH設(shè)置與否將會影響查詢結(jié)果。我們新建一個gsp2數(shù)據(jù)庫，數(shù)據(jù)庫排序規(guī)則為utf8mb4_0900_ai_ci，下面我們通過實驗對比一下就知道了：
mysql>?use?gsp2; Database?changed mysql>?show?variables?like?'sql_mode'\G ***************************?1.?row?*************************** Variable_name:?sql_mode ????????Value:?ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 1?row?in?set?(0.00?sec) --?對于CHAR類型，查詢返回數(shù)據(jù)時默認(rèn)會將字符串尾部的空格去掉，所以name=''會得到三條記錄 mysql>?select?*?from?test?where?name=''; +----+------+ |?id?|?name?| +----+------+ |??2?|??????| |??3?|??????| |??4?|??????| +----+------+ 3?rows?in?set?(0.01?sec) mysql>?select?*?from?test?where?name='?'; Empty?set?(0.00?sec) mysql>?select?*?from?test?where?name='??'; Empty?set?(0.00?sec) mysql>?set?sql_mode='PAD_CHAR_TO_FULL_LENGTH'; Query?OK,?0?rows?affected,?1?warning?(0.00?sec) mysql>?show?variables?like?'sql_mode'\G ***************************?1.?row?*************************** Variable_name:?sql_mode ????????Value:?PAD_CHAR_TO_FULL_LENGTH 1?row?in?set?(0.01?sec) --?SQL_MODE設(shè)置PAD_CHAR_TO_FULL_LENGTH。查詢返回數(shù)據(jù)時，字符串尾部的空格不會去掉，此時，name字段時16個空格，故而下面查詢條件查不到數(shù)據(jù)。 mysql>??select?*?from?test?where?name=''; Empty?set?(0.01?sec) mysql>?select?*?from?test?where?name='?'; Empty?set?(0.00?sec) mysql>?select?*?from?test?where?name='??'; Empty?set?(0.00?sec) mysql>
總結(jié)
關(guān)于MySQL的尾部空格是否忽略，以及對查詢結(jié)果的影響，既跟數(shù)據(jù)庫的排序規(guī)則有關(guān)（確切來說，是跟數(shù)據(jù)庫排序規(guī)則的Pad Attributes有關(guān)），其實還跟字符類型和SQL_MODE是否設(shè)置PAD_CHAR_TO_FULL_LENGTH有關(guān)。
到此這篇關(guān)于MySQL尾部空格處理方法詳解的文章就介紹到這了,更多相關(guān)MySQL尾部空格處理內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL系統(tǒng)及自定義變量方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-04-04
這篇文章主要介紹了MySQL中查詢的有關(guān)英文字母大小寫問題的分析,作者從多個角度進(jìn)行了實驗,需要的朋友可以參考下2015-05-05
使用MySQL的LAST_INSERT_ID來確定各分表的唯一ID值
MySQL數(shù)據(jù)表結(jié)構(gòu)中，一般情況下，都會定義一個具有‘AUTO_INCREMENT’擴展屬性的‘ID’字段，以確保數(shù)據(jù)表的每一條記錄都可以用這個ID唯一確定2011-08-08
在使用MySQL數(shù)據(jù)庫管理系統(tǒng)時,有時候會碰到忘記了root用戶的密碼的情況,這時候就需要找到一種解決方案來重置或者恢復(fù)root密碼,本文將介紹兩種常用的方法來解決這個問題,需要的朋友可以參考下2024-09-09
本文主要介紹了MySQL?衍生表(Derived?Tables)的使用,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧方法2025-06-06
本文給大家分享的是如何在linux下安裝mysql 圖解教程，步奏非常詳細(xì)，也很實用，這里推薦給大家2016-04-04
MySQL?FIND_IN_SET字符串函數(shù)深度解析
FIND_IN_SET是MySQL中處理分隔字符串的重要函數(shù),適合處理標(biāo)簽、分類等多值場景,這篇文章主要介紹了MySQL?FIND_IN_SET字符串函數(shù)的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2025-12-12
MySQL中JSON_CONTAINS函數(shù)詳細(xì)用法示例?及場景
這篇文章主要介紹了MySQL中JSON_CONTAINS函數(shù)詳細(xì)用法示例?及場景的相關(guān)資料,MySQL JSON_CONTAINS檢查目標(biāo)JSON是否包含搜索值,文中將用法介紹的非常詳細(xì),需要的朋友可以參考下2025-06-06
今天小編就為大家分享一篇關(guān)于Mysql通過存儲過程分割字符串為數(shù)組，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-03-03
MySQL 啟動失敗(code=exited, status=1/FAILURE)異常解決方案
在MySQL服務(wù)啟動時遇到啟動失敗問題,通過查看和分析錯誤日志文件,找到并解決了配置文件中的錯誤項,成功重啟了MySQL服務(wù).文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-11-11