咨询：MySQL 10插入含0xFF特殊字符失败，MySQL 5却正常的原因

场景复现 #

我使用MySQL客户端C库向数据库插入数据，将特殊字符0xFF作为字符串分隔符，代码如下：

char buffer[256];
char name[64] = "my_name";
char path[64] = "path/path";

path[4] = 255;      // 把/替换成0xFF

sprintf(buffer, "INSERT TestsNameTable (testName, testPath) VALUES (\"%s\", \"%s\")", name, path);
mysql_query(&mSQLConnection, buffer);

这段代码在Linux环境的MariaDB 5.5.68（兼容MySQL 5）中运行正常，但在Linux环境的MariaDB 10.5.16（兼容MySQL 10）中报错：

error code 1366: Incorrect string value: '\xFFpath...' for column UPSE_Reporting.TestsNameTable.testPath at row 1

对比现象 #

在两个版本的数据库客户端中执行以下SQL命令，均能正常插入：

MariaDB [UPSE_Reporting]> INSERT INTO TestsNameTable (testName, testPath) VALUES ("cedric", CONCAT("path",CHAR(255),"path"));
Query OK, 1 row affected (0.02 sec)

字符集配置 #

两个版本的数据库均使用默认字符集latin1，表字段的字符集查询结果如下：

MariaDB [UPSE_Reporting]> SELECT table_schema, table_name, column_name, character_set_name, collation_name FROM information_schema.columns WHERE table_schema="UPSE_Reporting" AND table_name="TestsNameTable" ORDER BY table_schema, table_name,ordinal_position; 

+----------------+----------------+-------------+--------------------+-------------------+
| table_schema   | table_name     | column_name | character_set_name | collation_name    |
+----------------+----------------+-------------+--------------------+-------------------+
| UPSE_Reporting | TestsNameTable | _rowid      | NULL               | NULL              |
| UPSE_Reporting | TestsNameTable | testName    | latin1             | latin1_swedish_ci |
| UPSE_Reporting | TestsNameTable | testPath    | latin1             | latin1_swedish_ci |
+----------------+----------------+-------------+--------------------+-------------------+
3 rows in set (0.00 sec)

请问为何该操作在MariaDB 10.5中无法正常执行？

解答 #

核心原因：MariaDB 10.5对字符串有效性的校验更严格 #

• SQL_MODE默认配置变化
  MariaDB 10.5默认的sql_mode包含STRICT_TRANS_TABLES和NO_INVALID_CHARACTERS这类严格校验规则，而5.5版本默认的严格模式较弱。当直接发送包含0xFF字节的字符串时，服务器会校验字节是否符合当前字符集的合法范围——虽然latin1理论上支持0x00-0xFF的所有字节，但严格模式下会进一步检查字符是否属于可打印或合法编码范围，0xFF会被判定为无效字符。

• 客户端连接字符集的影响
  你的C代码用sprintf直接拼接SQL并发送，此时客户端连接的字符集设置会影响服务器对字节的解析。如果客户端连接时的字符集不是latin1（比如默认是utf8），服务器会把收到的0xFF字节当作utf8编码处理，而utf8中0xFF是无效编码，因此触发1366错误。而用客户端执行CONCAT("path",CHAR(255),"path")时，CHAR(255)会被服务器直接解析为latin1编码的合法字符，绕过了客户端字符集的转换问题。

• 版本间校验逻辑更新
  MariaDB 10.2及以后版本对字符串有效性的校验逻辑进行了优化，处理非标准ASCII字符时会更严格地校验字节是否符合当前字符集定义。5.5版本的校验逻辑相对宽松，允许插入latin1范围内的所有字节，哪怕是不可打印字符。

解决方法 #

• 使用预处理语句（推荐）：不要用sprintf拼接SQL，改用MySQL的预处理语句（mysql_stmt_prepare+mysql_stmt_bind_param），直接传递二进制数据，既避免字符集转换问题，又能防止SQL注入。
• 显式设置客户端连接字符集：在调用mysql_query前，执行mysql_set_character_set(&mSQLConnection, "latin1")，确保客户端和服务器字符集一致，让服务器正确解析0xFF字节为latin1字符。
• 调整SQL_MODE（不推荐）：临时或永久去掉NO_INVALID_CHARACTERS和STRICT_TRANS_TABLES，但会降低数据校验的安全性。

内容的提问来源于stack exchange，提问作者Cedric