解決MySQL Varchar 型別尾部空格的問題

背景

近期發現系統中某個輸入框裡如果輸入xxx+空格的時候會出現異常情況，經過排查發現在呼叫後端介面時會有兩步操作，一是從資料庫中查詢到的陣列中將與xxx+空格一致的元素剔除，二是根據xxx+空格從資料庫中查詢對應的明細。

出現異常的原因是在剔除時未能剔除掉對應的元素，也就意味著xxx+空格對應的內容在資料庫中不存在；但是在查詢明細時還是查詢到了，頓時感覺很費解，也就衍生出了這篇文章後續的內容。

原因

• 開發人員在處理前端傳過來的字串時沒有執行 trim()，所以導致與陣列中元素匹配的時候沒有匹配到，也就沒能剔除對應的元素，"a".equals("a ") 的結果肯定是 false 嘛。

• MySQL 在查詢時會忽略掉字串最後的空格，所以導致xxx+空格作為查詢條件時和xxx為同一效果。

詳解

對於第一條原因只能說是開發時疏漏，沒什麼可說的，我們著重瞭解下第二條，為什麼 MySQL 會忽略掉查詢條件最後的空格。本文基於 MySQL 8.0.28，文章中有些內容是 MySQL 8.0 新增的，但主體也適用於 5.x 版本。

在探究之前我們需要準備下使用的資料庫，畢竟實踐出來的結果才是真實的，首先我們準備一個測試使用的資料庫和表，結構如下，字元集和排序規則先選擇比較常用的 utf8mb4 和 utf8mb4_unicode_ci，之後在表裡插入兩條資料：

mysql> desc test;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int         | NO   | PRI | NULL    |       |
| name_char    | char(20)    | YES  |     | NULL    |       |
| name_varchar | varchar(20) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

INSERT INTO `test` VALUES (1, 'char1', 'varchar1');
INSERT INTO `test` VALUES (2, 'char2     ', 'varchar2     ');

char 和 varchar 的區別

首先看一下官方對於 char 型別和 varchar 型別的介紹，以下內容摘自【11.3.2 The CHAR and VARCHAR Types】

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.
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

通過以上我們可以得知以下幾部分內容：

• char 型別長度為 0-255，varchar 型別長度為 0-65535，char 和 varchar 型別的長度其實還會受到內容長度的影響，這裡我們不深究。

• char 型別為定長欄位，儲存時會向右填充空格至宣告的長度；varchar 型別為變長欄位，儲存時宣告的只是可儲存的最長內容，實際長度與內容有關。

• 在 sql mode 中未開啟 PAD_CHAR_TO_FULL_LENGTH 時，char 型別在查詢時會在忽略尾部空格（關於 sql mode 的資料請移步【5.1.11 Server SQL Modes】，這裡我們不深究）

下面的查詢結果中第一行是都沒有空格的結果，第二行是都帶有 5 個空格的結果，可以看到 char 型別無論帶不帶空格都只會返回基本的字元。

mysql> select concat("(",name_char,")") name_char, concat("(",name_varchar,")") name_varchar from test;
+-----------+-----------------+
| name_char | name_varchar    |
+-----------+-----------------+
| (char1)   | (varchar1)      |
| (char2)   | (varchar2     ) |
+-----------+-----------------+
2 rows in set (0.01 sec)

第一行好理解，你存進去的時候沒帶空格，資料庫自己填充上了空格，總不能查出來的結果還變了吧；第二行則是入庫的時候字串最後的字元和資料庫填充的字元是同一種，查詢的時候資料庫怎麼分得清是你自己填的還是它填的呢，直接一刀切。而 varchar 型別因為不會被填充，所以查詢結果中完成的保留下了尾部空格。

varchar 對於尾部空格的處理

上節瞭解過 char 型別查詢時會忽略尾部空格，但是在實際使用中發現 varchar 也有類似的規則，在檢視檔案時發現有以下一段內容，摘自【11.3.2 The CHAR and VARCHAR Types】

Values in CHAR, VARCHAR, and TEXT columns are sorted and compared according to the character set collation assigned to the column.
MySQL collations have a pad attribute of PAD SPACE, other than Unicode collations based on UCA 9.0.0 and higher, which have a pad attribute of NO PAD.

根據這一段描述，我們可以得知 char、varchar 和 text 內容的排序和比較過程受排序規則影響，在 UCA 9.0.0 之前 pad 屬性預設為 PAD SPACE，而之後的預設屬性為 NO PAD。

在官方檔案中可以找到以下說明，摘自【Trailing Space Handling in Comparisons】

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.

這一段主要描述 char、varchar 和 text 型別在比較時，如果排序規則的 pad 屬性為 PAD SPACE 則會忽略尾部空格，NO PAD 屬性則不會，而這正解釋了最初的問題。我們通過修改列的排序規則驗證以下，首先看一下當前使用 PAD SPACE 時的查詢結果。

mysql> show full columns from test;
+--------------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field        | Type        | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+--------------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| id           | int         | NULL               | NO   | PRI | NULL    |       | select,insert,update,references |         |
| name_char    | char(20)    | utf8mb4_unicode_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| name_varchar | varchar(20) | utf8mb4_unicode_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+--------------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
3 rows in set (0.01 sec)

mysql> select * from test where name_varchar = 'varchar2';
+----+-----------+---------------+
| id | name_char | name_varchar  |
+----+-----------+---------------+
|  2 | char2     | varchar2      |
+----+-----------+---------------+
1 row in set (0.01 sec)

可以看到在 PAD SPACE 屬性下可以通過varchar2查詢到varchar2，說明比較時忽略的尾部的空格，我們將 name_varchar 的排序規則切換為 UCA 9.0.0 以後版本再來看一下結果。

mysql> ALTER TABLE test CHANGE name_varchar name_varchar VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show full columns from test;
+--------------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field        | Type        | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+--------------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| id           | int         | NULL               | NO   | PRI | NULL    |       | select,insert,update,references |         |
| name_char    | char(20)    | utf8mb4_unicode_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| name_varchar | varchar(20) | utf8mb4_0900_ai_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+--------------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
3 rows in set (0.01 sec)

mysql> select * from test where name_varchar = 'varchar2';
Empty set (0.00 sec)

與預期一樣，切換排序規則後，尾部空格參與比較，已經不能通過varchar2查詢到varchar2了。

確定排序規則的 pad 屬性

那接下來的問題是如何判斷當前的排序規則是基於 UCA 9.0.0 之前還是之後的版本呢？其實在 mysql 8.x 版本中，排序規則儲存在 information_schema 庫的 COLLATIONS 表中，可以通過以下語句查詢對應的 pad 屬性值，例如我們一開始選擇的 utf8mb4_unicode_ci。

mysql> select collation_name, pad_attribute from information_schema.collations where collation_name = 'utf8mb4_unicode_ci';
+--------------------+---------------+
| collation_name     | pad_attribute |
+--------------------+---------------+
| utf8mb4_unicode_ci | PAD SPACE     |
+--------------------+---------------+
1 row in set (0.00 sec)

除了查詢資料庫以外，還可以通過排序規則的名稱進行區別，在官方檔案中有以下一段描述，摘自【Unicode Collation Algorithm (UCA) Versions】

MySQL implements the xxx_unicode_ci collations according to the Unicode Collation Algorithm (UCA) described at http://www.unicode.org/reports/tr10/. The collation uses the version-4.0.0 UCA weight keys: http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt. The xxx_unicode_ci collations have only partial support for the Unicode Collation Algorithm.

Unicode collations based on UCA versions higher than 4.0.0 include the version in the collation name. Examples:

• utf8mb4_unicode_520_ci is based on UCA 5.2.0 weight keys (http://www.unicode.org/Public/UCA/5.2.0/allkeys.txt),

• utf8mb4_0900_ai_ci is based on UCA 9.0.0 weight keys (http://www.unicode.org/Public/UCA/9.0.0/allkeys.txt).

可以看出，名稱類似 xxx_unicode_ci 的排序規則是基於 UCA 4.0.0 的，而 xxx_520_ci 是基於 UCA 5.2.0，xxx_0900_ci 是基於 UCA 9.0.0 的。通過查詢資料庫驗證，排序規則中包含 0900 字樣的 pad 屬性均為 NO PAD，符合以上描述。

需要注意的是 binary 排序規則的 pad 屬性為 NO PAD，這裡其實不是個例外，因為 char、varchar 和 text 型別都歸類為nonbinary。

到此這篇關於MySQLVarchar型別尾部空格的文章就介紹到這了,更多相關MySQLVarchar型別尾部空格內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！