一條詭異的insert語句
作者 | 天士夢
問題背景
有同事反饋在mysql上面執(zhí)行一條普通的insert語句，結(jié)果報錯，
execute failed due to >>> Incorrect string value: '\xA1;offl...' for column 'biz_info' at row 1
經(jīng)過半天的折騰，終于搞清楚了來龍去脈，這里簡單給大家分享下。為了方便說明，我將測試例子中的表和語句簡化，但不影響問題重現(xiàn)。
問題復(fù)現(xiàn)
連接字符集：UTF8
表結(jié)構(gòu)：
CREATE TABLE `ggg` (
`id` int(11) DEFAULT NULL,
`c` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
root@test 06:13:48>insert into ggg values(1,concat('cardName:校園網(wǎng)',char(59),'offlineCardType:campus'));
Query OK, 1 row affected, 1 warning (2.51 sec)
root@test 06:14:36>show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1366
Message: Incorrect string value: '\x91;offl...' for column 'c' at row 1
查看結(jié)果
root@test 06:16:06>select * from ggg where id=1;
*************************** 1. row ***************************
id: 1
c: cardName:鏍″洯緗
從報錯的結(jié)果來看，感覺是字符集轉(zhuǎn)換引起的問題，而且由于連接串的字符集是UTF8，表的字符集是GBK，更容易引起懷疑。但是，即使是字符集轉(zhuǎn)換，也不應(yīng)該導(dǎo)致插入報錯，因為語句中的中文字符“校園網(wǎng)"都是普通漢字，UTF8->GBK不應(yīng)該存在問題。
那我們在回過頭來看看insert語句，唯一特殊的是使用了concat和char兩個函數(shù)。會不會跟這兩個函數(shù)有關(guān)系？char(59)實際是字符“;”，為了驗證想法，做了兩個實驗：
將char(59)替換成';'
insert into ggg values(1,concat('cardName:校園網(wǎng)',';','offlineCardType:campus'));
設(shè)置連接串字符集為GBK
insert into ggg values(1,concat('cardName:校園網(wǎng)',char(59),'offlineCardType:campus'));
果然，兩種情況執(zhí)行結(jié)果都是OK的，查詢結(jié)果如下：
root@test 09:22:32>select * from ggg\G
*************************** 1. row ***************************
id: 1
c: cardName:鏍″洯緗
*************************** 2. row ***************************
id: 1
c: cardName:校園網(wǎng);offlineCardType:campus
*************************** 3. row ***************************
id: 1
c: cardName:校園網(wǎng);offlineCardType:campus
跟蹤了下源代碼，找到了原因。char()函數(shù)返回的是一個binary類型字符串，在進行concat時，會導(dǎo)致'cardName:校園網(wǎng)'字符串到binary的轉(zhuǎn)換。轉(zhuǎn)換前，mysql將字符串‘cardName:校園網(wǎng)’看作是9個英文字符和3個漢字字符；轉(zhuǎn)換后，mysql將其看作是18個字節(jié)的二進制串，其中，UTF8字符集的三個漢字“校園網(wǎng)”占了9個字節(jié)。由于目標(biāo)表字符集是GBK，因此在入庫時，還會發(fā)生一次binary到GBK的轉(zhuǎn)碼,“校園網(wǎng)”的二級制編碼是E6A0A1 E59BAD E58DA1，在轉(zhuǎn)碼過程中，由于GBK字符集只包含一個字節(jié)(編碼值<128)和二個字節(jié)的字符(漢字和特殊字符)，“校園網(wǎng)”的二進制串會按照兩個字節(jié)拆分E6A0 A1E5 9BAD E58D A1，前面四個變?yōu)椤版牎鍥|”，解析到A1時，由于A1既不是單字節(jié)字符，又不能與后面的字節(jié)組成一個合法的GBK字符，導(dǎo)致轉(zhuǎn)換出錯。
現(xiàn)在就很好解釋為啥改變語句后，兩種情況都OK了。第一種情況，將char(59)直接替換成‘;’，由于不涉及UF8到binary的轉(zhuǎn)換，只有utf8到gbk轉(zhuǎn)碼的過程，這個轉(zhuǎn)換是OK的，不會出現(xiàn)亂碼；第二種情況，將連接串的字符集設(shè)置為GBK，那么會涉及GBK到binary的轉(zhuǎn)換，然后再從binary轉(zhuǎn)換到GBK，由于整個轉(zhuǎn)換過程并沒有二進制數(shù)據(jù)丟失，所以也是OK的。
連接字符集與表字符集不匹配 使用了char函數(shù)
解決辦法
1.char函數(shù)提供了using語法來實現(xiàn)返回特定字符集的字符串，比如：char(59 using utf8)
2.保證連接字符集與表字符集一致。
往期推薦
技術(shù)交流群最近有很多人問，有沒有讀者交流群，想知道怎么加入。加入方式很簡單，有興趣的同學(xué)，只需要點擊下方卡片，回復(fù)“加群“，即可免費加入我們的高質(zhì)量技術(shù)交流群！
點擊閱讀原文，送你免費Spring Boot教程！