A weird insert statement.

Issue background

Colleague feedback in MySQL above a normal INSERT statement, the results of an error,
Execute failed due to >>> incorrect string value: ' \xa1;offl ... ' forcolumn ' biz_info ' at row 1

After a half-day toss, finally made clear the ins and outs, here simple to share the next. For the sake of illustration, I will simplify the tables and statements in the test example without affecting the recurrence of the problem.

Problem recurrence

Connection Character Set: UTF8

Table structure:

CREATE TABLE ' GGG ' (
' id ' int (one) DEFAULT NULL,
' C ' varchar (+) DEFAULT NULL
) Engine=innodb DEFAULT CHARSET=GBK;

[email protected] 06:13:48>insert into Gggvalues (1,concat (' Cardname: Campus network ', char (), ' Offlinecardtype:campus '));

Query OK, 1 row affected, 1 warning (2.51sec)

[Email protected] 06:14:36>show Warnings\g

1. row***************************

Level:warning

code:1366

Message:incorrect string value: ' \x91;offl ... ' for column ' C ' at row 1

View Results

[Email protected] 06:16:06>select * from Gggwhere id=1;

1. row***************************

Id:1

C:cardname: æ ″ 洯 network

Problem analysis

Judging from the result of the error, the feeling is the problem caused by character set conversion, and because the character set of the concatenated string is UTF8, the table's character set is GBK, which is more likely to cause suspicion. However, even character set conversions should not result in insert errors, because the Chinese characters in the statement "Campus network" are common Chinese characters, UTF8->GBK should not have problems. So we're looking back at the INSERT statement, and the only special one is the use of concat and char two functions. Will it have anything to do with these two functions? CHAR (59) is actually the character ";", in order to verify the idea, did two experiments:

1. Replace char (59) with '; '
   

INSERT into GGG values (1,concat (' Cardname: Campus network ','; ', ' Offlinecardtype:campus '));

2. Set the connection string character set to GBK

Insert INTO Gggvalues (1,concat (' Cardname: Campus net ', char (), ' Offlinecardtype:campus '));

Sure enough, the results of both cases are OK, the query results are as follows:

[Email protected] 09:22:32>select * fromggg\g

1. row***************************

Id:1
C:cardname: æ ″ 洯 Network

2. row***************************

Id:1
C:cardname: Campus network; Offlinecardtype:campus

3. row***************************

Id:1
C:cardname: Campus network; Offlinecardtype:campus

Traced the source code and found the reason. The char () function returns a binary type string that, when concat, causes the ' Cardname: Campus net ' string to binary conversion . Before the conversion, MySQL treated the string ' Cardname: Campus net ' as 9 English characters and 3 kanji character; After the conversion, MySQL was treated as a 18-byte binary string, where the three Chinese characters "campus net" of the UTF8 character set accounted for 9 bytes. Because the target table character set is GBK, so in the storage, there will also be a binary to GBK transcoding , "Campus Network" two-level encoding is E6A0A1 E59bad e58da1, in the transcoding process, because the GBK character set contains only one byte (encoded value < 128) and two bytes of characters (Chinese characters and special characters), the "Campus network" binary string will be split according to two bytes e6a0 a1e5 9BAD e58d A1, the first four changed to "Æ ″ 洯 Network", when parsing to A1, because A1 is neither a single-byte character, It is not possible to make a valid GBK character with the subsequent bytes, resulting in a conversion error.
Now it's good to explain why the two cases are OK after changing the statement. In the first case, the char (59) is replaced directly with '; ', because it does not involve the conversion of UF8 to binary, only the process of UTF8 to GBK transcoding, this conversion is OK, does not appear garbled; the second case, set the character set of the connection string to GBK, Then it involves GBK to binary conversion, and then from binary to GBK, because the entire conversion process and no binary data loss, so it is OK.

Two key points from the problem

1. The connection character set does not match the table character set
   
2. Using the Char function

Solutions

The 1.char function provides a using syntax to implement a string that returns a specific character set, for example: char (using UTF8)

2. Ensure that the connection character set is consistent with the table character set.

A weird insert statement.