Why MySQL INSERT Fails with Incorrect String Value and How to Fix It

Problem background: a colleague reported an error when executing a normal INSERT statement in MySQL.

execute failed due to >>> Incorrect string value: '\xA1;offl...' for column 'biz_info' at row 1

After extensive troubleshooting the author reproduced the issue with a simplified table and statements.

Problem Reproduction

Connection character set: UTF8

Table definition:

CREATE TABLE `ggg` (
  `id` int(11) DEFAULT NULL,
  `c` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

Insert statement that triggers the warning:

insert into ggg values(1, concat('cardName:校园网', char(59), 'offlineCardType:campus'));
show warnings\G;

Result of the SELECT shows corrupted characters:

select * from ggg where id=1;
-- output
id: 1
c: cardName:鏍″洯缃

Problem Analysis

The error originates from character set conversion. The connection uses UTF8 while the table uses GBK, but the real issue is the use of char() , which returns a binary string. During concat , the UTF‑8 Chinese text is first converted to binary, then MySQL attempts to convert that binary data to GBK, causing illegal byte sequences (e.g., "鏍″洯缃").

Two experiments proved the hypothesis:

Replacing char(59) with the literal ';' makes the INSERT succeed because no binary conversion occurs.

Setting the connection character set to GBK also succeeds, as the conversion path no longer loses data.

Key Points that Cause the Issue

Mismatch between connection character set (UTF8) and table character set (GBK).

Use of the char() function, which produces binary data.

Solution

1. Use the using clause with char() to specify the desired character set, e.g., char(59 using utf8).

2. Ensure the connection character set matches the table character set.