問題背景

　　一天，小伙伴找到我，他說他碰到一個(gè)很奇怪的問題

　　他說：明明表名的入?yún)⑹?nbsp;test  ，為什么展示到界面的記錄包括 test 這條記錄？

　　他補(bǔ)充道：會(huì)不會(huì)是 MyBatis-Plus 做了什么騷操作，把 test  末尾的空格給拿掉了

　　我：你直接把 SQL 語句到 MySQL 執(zhí)行下試試

　　結(jié)果如下：

　　這看起來不夠直觀，我移動(dòng)下光標(biāo)

　　然后我和小伙伴面面相覷

環(huán)境準(zhǔn)備

　　 MySQL5 、 MySQL8 各準(zhǔn)備一個(gè)

　　我們來看下默認(rèn)情況下，末尾空白的判斷情況

　　 MySQL 5.7.36 如下

　　1 表示 TRUE ，也就是相等

　　 MySQL 8.0.27 如下

　　0 表示 FALSE ，表示不相等

　　這是什么原因，我們繼續(xù)往下看

字符集與字符序

　　比較肯定就需要比較規(guī)則， SQL 的比較規(guī)則就離不開字符序，字符序又與字符集相關(guān)，所以我們一個(gè)一個(gè)來捋

　　字符集

　　關(guān)于字符集，不是只言片語可以說清楚的，但是大家也不用擔(dān)心，網(wǎng)上相關(guān)資料已經(jīng)非常多，大家擦亮慧眼去查閱即可

　　簡(jiǎn)單點(diǎn)來說：字符集定義了字符和字符的編碼

　　有人又問了：字符、字符的編碼又是什么？

　　為了方便大家理解，舉個(gè)簡(jiǎn)單栗子

　　　　有四個(gè)字符：A、B、C、D，這四個(gè)字符的編碼分別是 A = 0, B = 1, C = 2, D = 3

　　　　這里的字符（A、B、C、D） + 編碼（0、1、2、3）就構(gòu)成了字符集（character set）

　　 MySQL 支持的字符集有很多，可以通過 SHOW CHARACTER SET; 查看

　　 Charset ：字符集名

　　 Description ：描述

　　 Default collation ：默認(rèn)字符序

　　 Maxlen ：每個(gè)字符最多字節(jié)數(shù)

　　字符序

　　定義了字符的比較規(guī)則；字符間的比較按何種規(guī)則進(jìn)行

　　一個(gè)字符集對(duì)應(yīng)多個(gè)字符序，通過 SHOW COLLATION; 可以查看全部的字符序；也可以帶條件查具體某個(gè)字符集的字符序

　　 Default 等于 Yes 表示是默認(rèn)字符序

　　每個(gè)字符集都有默認(rèn)的字符序

　　server的字符集與字符序

　　當(dāng)我們創(chuàng)建數(shù)據(jù)庫時(shí)，沒有指定字符集、字符序，那么server字符集、server字符序就會(huì)作為該數(shù)據(jù)庫的字符集、字符序

　　database的字符集與字符序

　　指定數(shù)據(jù)庫級(jí)別的字符集、字符序

　　同一個(gè)MySQL服務(wù)下的數(shù)據(jù)庫，可以分別指定不同的字符集、字符序

　　創(chuàng)建、修改數(shù)據(jù)庫的時(shí)候，可以通過 CHARACTER SET 、  COLLATE  指定數(shù)據(jù)庫的字符集、字符序

　　可以通過

　　查看數(shù)據(jù)庫的字符集和字符序

　　table的字符集與字符序

　　創(chuàng)建、修改表的時(shí)候，可以通過 CHARACTER SET 、 COLLATE 指定表的字符集、字符序

　　可以通過

　　查看表的字符序

　　column的字符集與字符序

　　類型為 CHAR 、 VARCHAR 、 TEXT 的列，可以指定字符集、字符序

　　可以通過

　　查看字段的字符集和字符序

　　多個(gè)維度指定字符集、字符序的話，粒度越細(xì)的優(yōu)先級(jí)越高（ column > table > database > server ）

　　如果細(xì)粒度未指定字符集、字符序，那么會(huì)繼承上一級(jí)的字符集，字符序則是上一級(jí)字符集的默認(rèn)字符序

　　通常情況下我們一般不會(huì)指定 table 、 column 粒度的字符集、字符序

　　也就是說，通常情況下 column 的字符集會(huì)與 database 的字符集一致，而 column 的字符序則是 database 字符集的默認(rèn)字符序

空白丟失

　　上面講了那么多，跟空白丟失有什么關(guān)系？

　　大家先莫急，繼續(xù)往下看

　　 MySQL5.7 The CHAR and VARCHAR Types中有這么一段

　　翻譯過來就是：

　　　　1、類型是 CHAR 、 VARCHAR 、 TEXT 列的值，會(huì)根據(jù)列的字符序來比較和排序

　　　　2、所有 MySQL 排序規(guī)則的類型都是 PAD SPACE 。這就意味著， CHAR 、 VARCHAR 、 TEXT 類型的值進(jìn)行比較時(shí)，不用考慮任何末尾空格，LIKE 除外

　　　　3、不受 SQL mode 影響，也就是說不管是嚴(yán)格模式，還是非嚴(yán)格模式，都不影響 2 所說的規(guī)則

　　劃重點(diǎn)，記筆記：在 MySQL5.7 及以下（ <=5.7 ）版本中，排序規(guī)則都是 PAD SPACE ，末尾的空格會(huì)忽略不考慮

　　那如何讓末尾空格參與比較了，有三種處理方式

　　1、 BINARY ，類似 SELECT 'test' = BINARY 'test '; 

　　2、 LIKE ，類似 SELECT 'test' LIKE 'test '; 

　　3、 LENGTH 函數(shù)，類似

　　 MySQL8 做了調(diào)整，The CHAR and VARCHAR Types 有如下說明

　　翻譯過來就是：

　　　　1、類型是 CHAR 、 VARCHAR 、 TEXT 列的值，會(huì)根據(jù)列的字符序來比較和排序

　　　　2、 MySQL 字符序的 pad 參數(shù)的可選值，除了 PAD SPACE ，還增加了 NO PAD 

　　　　3、對(duì)于非二進(jìn)制字符串（ CHAR 、 VARCHAR 、 TEXT ），字符序 pad 參數(shù)決定如何去處理字符串末尾的空格

　　　　　　 NO PAD 不會(huì)忽略末尾空格，會(huì)將其當(dāng)做其他字符一樣對(duì)待

　　　　　　 PAD SPACE 會(huì)忽略末尾空格， LIKE 除外

　　　　　　 SQL mode 不參與字符串末尾空格的處理

　　 MySQL8 server 維度的字符集是 utf8mb4 ，對(duì)應(yīng)的默認(rèn)字符序是： utf8mb4_0900_ai_ci 

　　 Pad_attribute 的值是 NO PAD ，也就是不會(huì)忽略字符串末尾的空格

　　所以在 MySQL8 中， SELECT 'test' = 'test '; 默認(rèn)情況下得到的結(jié)果是 0

總結(jié)

　　1、非二進(jìn)制字符串（ CHAR 、 VARCHAR 、 TEXT ）比較時(shí)，末尾空格的處理跟列的字符序有直接關(guān)系

　　2、 MySQL5.7 及之前的版本，排序規(guī)則的類型都是 PAD SPACE ，會(huì)忽略字符串末尾的空格， LIKE 除外

　　3、 MySQL8 開始，字符序增加了一個(gè)參數(shù) Pad_attribute ，該參數(shù)的值不同，對(duì)字符串末尾空格的處理方式不同

　　　　 NO PAD ：字符串末尾的空格會(huì)和其他字符一樣，不會(huì)被忽略

　　　　 PAD SPACE ：字符串末尾的空格會(huì)被忽略， LIKE 除外

　　4、如上針對(duì)的都是非二進(jìn)制字符串的排序和比較，而不是儲(chǔ)存

參考

　　The CHAR and VARCHAR Types

　　The CHAR and VARCHAR Types

　　再見亂碼：5分鐘讀懂MySQL字符集設(shè)置

到此這篇關(guān)于記一次mysql字符串末尾空白丟失的排查的文章就介紹到這了,更多相關(guān)mysql字符串末尾空白丟失的排查內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論