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，它會在字符串的右邊補(bǔ)充空格，直到總字符數(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，它不會在字符串的右邊補(bǔ)充空格，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用戶創(chuàng)建與授權(quán)的簡單實例
MySQL等主流數(shù)據(jù)庫的最高權(quán)限一般是root用戶,有時我們需要提供數(shù)據(jù)庫的賬號和密碼以使用某些服務(wù),下面這篇文章主要給大家介紹了關(guān)于mysql用戶創(chuàng)建與授權(quán)的相關(guān)資料,需要的朋友可以參考下2022-03-03
mysql官網(wǎng)開始發(fā)布相關(guān)的5.6系列的各個版本，對于mysql5.6系列的版本對一起的版本進(jìn)行了全局性的細(xì)節(jié)性加強(qiáng)2013-06-06
這篇文章主要介紹了Mysql查詢時如何使用for update行鎖還是表鎖問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08
mysql odbc字符集設(shè)置(中文顯示亂碼),碰到這個問題的朋友可以參考下。2011-08-08
mysql怎么關(guān)閉sql_mode=ONLY_FULL_GROUP_BY模式
前段時間在項目開發(fā)過程中發(fā)現(xiàn)了系統(tǒng)異常,打開日志查看發(fā)現(xiàn)了如下的這個報錯,查找相關(guān)資料終于解決了,這篇文章主要給大家介紹了關(guān)于mysql怎么關(guān)閉sql_mode=ONLY_FULL_GROUP_BY模式的相關(guān)資料,需要的朋友可以參考下2024-01-01
MySQL誤刪數(shù)據(jù)庫造成了數(shù)據(jù)的丟失,這是非常尷尬的,下面這篇文章主要給大家介紹了關(guān)于MySQL恢復(fù)誤刪數(shù)據(jù)的相關(guān)資料,文中通過實例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-06-06
這篇文章主要給大家介紹了關(guān)于MLSQL Stack如何讓流調(diào)試更加簡單的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用MLSQL具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-06-06
這篇文章主要介紹了MySQL 游標(biāo)的定義與使用方式，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下2021-01-01
這篇文章主要介紹了Mysql中的select ...for update用法,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08
這篇文章主要介紹了mysql游標(biāo)的原理與用法,結(jié)合實例形式分析了mysql游標(biāo)的概念、原理、用法及操作注意事項,需要的朋友可以參考下2020-04-04