這insert語(yǔ)句有點(diǎn)詭異
問(wèn)題背景
有同事反饋在mysql上面執(zhí)行一條普通的insert語(yǔ)句，結(jié)果報(bào)錯(cuò)，
execute failed due to >>> Incorrect string value: '\xA1;offl...' for column 'biz_info' at row 1
經(jīng)過(guò)半天的折騰，終于搞清楚了來(lái)龍去脈，這里簡(jiǎn)單給大家分享下。為了方便說(shuō)明，我將測(cè)試?yán)又械谋砗驼Z(yǔ)句簡(jiǎn)化，但不影響問(wèn)題重現(xiàn)。
問(wè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:鏍″洯緗
問(wèn)題分析
從報(bào)錯(cuò)的結(jié)果來(lái)看，感覺(jué)是字符集轉(zhuǎn)換引起的問(wèn)題，而且由于連接串的字符集是UTF8，表的字符集是GBK，更容易引起懷疑。但是，即使是字符集轉(zhuǎn)換，也不應(yīng)該導(dǎo)致插入報(bào)錯(cuò)，因?yàn)檎Z(yǔ)句中的中文字符“校園網(wǎng)"都是普通漢字，UTF8->GBK不應(yīng)該存在問(wèn)題。
那我們?cè)诨剡^(guò)頭來(lái)看看insert語(yǔ)句，唯一特殊的是使用了concat和char兩個(gè)函數(shù)。會(huì)不會(huì)跟這兩個(gè)函數(shù)有關(guān)系？char(59)實(shí)際是字符“;”，為了驗(yàn)證想法，做了兩個(gè)實(shí)驗(yàn)：
將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ù)返回的是一個(gè)binary類型字符串，在進(jìn)行concat時(shí)，會(huì)導(dǎo)致'cardName:校園網(wǎng)'字符串到binary的轉(zhuǎn)換。轉(zhuǎn)換前，mysql將字符串‘cardName:校園網(wǎng)’看作是9個(gè)英文字符和3個(gè)漢字字符；轉(zhuǎn)換后，mysql將其看作是18個(gè)字節(jié)的二進(jìn)制串，其中，UTF8字符集的三個(gè)漢字“校園網(wǎng)”占了9個(gè)字節(jié)。由于目標(biāo)表字符集是GBK，因此在入庫(kù)時(shí)，還會(huì)發(fā)生一次binary到GBK的轉(zhuǎn)碼,“校園網(wǎng)”的二級(jí)制編碼是E6A0A1 E59BAD E58DA1，在轉(zhuǎn)碼過(guò)程中，由于GBK字符集只包含一個(gè)字節(jié)(編碼值<128)和二個(gè)字節(jié)的字符(漢字和特殊字符)，“校園網(wǎng)”的二進(jìn)制串會(huì)按照兩個(gè)字節(jié)拆分E6A0 A1E5 9BAD E58D A1，前面四個(gè)變?yōu)椤版牎鍥|”，解析到A1時(shí)，由于A1既不是單字節(jié)字符，又不能與后面的字節(jié)組成一個(gè)合法的GBK字符，導(dǎo)致轉(zhuǎn)換出錯(cuò)。
現(xiàn)在就很好解釋為啥改變語(yǔ)句后，兩種情況都OK了。第一種情況，將char(59)直接替換成‘;’，由于不涉及UF8到binary的轉(zhuǎn)換，只有utf8到gbk轉(zhuǎn)碼的過(guò)程，這個(gè)轉(zhuǎn)換是OK的，不會(huì)出現(xiàn)亂碼；第二種情況，將連接串的字符集設(shè)置為GBK，那么會(huì)涉及GBK到binary的轉(zhuǎn)換，然后再?gòu)腷inary轉(zhuǎn)換到GBK，由于整個(gè)轉(zhuǎn)換過(guò)程并沒(méi)有二進(jìn)制數(shù)據(jù)丟失，所以也是OK的。
問(wèn)題產(chǎn)生的兩個(gè)關(guān)鍵點(diǎn)
連接字符集與表字符集不匹配 使用了char函數(shù)
解決辦法
1.char函數(shù)提供了using語(yǔ)法來(lái)實(shí)現(xiàn)返回特定字符集的字符串，比如：char(59 using utf8)
2.保證連接字符集與表字符集一致。
作者 |?天士夢(mèng)
歡迎關(guān)注“Java引導(dǎo)者”，我們分享最有價(jià)值的Java的干貨文章，助力您成為有思想的Java開發(fā)工程師！