MySQL字段超长截断

寒三石
2,219 阅读3分钟

最近开发一个项目的时候，接到测试的反馈，某个字段如果输入很长的中文时，那么查询出来的结果会有乱码。

首先在MySQL数据库中查了下个这个字段，发现前边的中文是正常显示的，而最后一个字符是乱码，另外丢失了后边一段的中文字符。

我查了表定义，该列的定义是VARCHAR(2048)，即最多2048个字符，参考之前开发其他系统的经验（事后发现这里绕了弯路，这个系统的数据库配置跟之前不一样），一个中文只算一个字符，测试只输入了1000个中文，应该是够用，即便超长了，数据库也会抛出Data too long的异常。

于是开始调试这个项目的代码，怀疑是哪里有截断的代码逻辑，但一路debug下来，都没有发现截断的逻辑。

最后将目光又转回数据库，试着插入几条超长字段的sql，发现插入成功，但是被截断了。

由于敏感信息不能直接展示，这里我模拟复现下问题，复现的步骤如下

# 新建一个mysql容器并登录
docker run --name mysql-5.6 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:5.6.28
docker exec -it mysql-5.6 env LANG=C.UTF-8 /bin/bash
mysql -uroot -p

# 建库建表，并进行一些设置（字段设置为  VARCHAR(10) ），以保证跟生产数据库配置基本一致
CREATE DATABASE test;
USE test;
CREATE TABLE `tbl_test` (
	`content` VARCHAR(10)
);

SET character_set_client=latin1;
SET character_set_results=latin1;
SET @@session.sql_mode='NO_ENGINE_SUBSTITUTION';

随后插入了3条数据：

INSERT INTO tbl_test (content) VALUES ('中');
INSERT INTO tbl_test (content) VALUES ('中中中中');
INSERT INTO tbl_test (content) VALUES ('中中中中中中');

查询这张表的数据：

image-20220217114535015.png

可以看到第2条和第3条出现了乱码，且被截断了。

通过查询文档和检索类似问题，确定了问题根源。

首先关于长度约束问题，VARCHAR的长度限制确实是按字符算的，但是由于该表用的字符集是latin1，虽然也支持中文，但它本身是个单字节字符集，所以一个汉字会算作3个字符（3个字节存的）。相比较utf8这种变长字符集，虽然一个汉字还是存储成多个字节，但还是按一个字符来算的。

我们可以用下面的sql来测试不同字符集下单个汉字的字符数

SELECT CHAR_LENGTH(_utf8"中") AS character_length;
SELECT CHAR_LENGTH(_latin1"中") AS character_length;

image-20220217120152810.png

可以看到在latin1字符集下，一个汉字是算3个字符，而utf8则是按一个字符算。而我们这张表的字符集是latin1，超过3个汉字便会超过上限了。至于最后一个字符乱码，则是因为最后一个汉字被截到只剩一个字节。

对于超长截断的问题，这跟数据库的sql_mode配置相关

参见官方文档dev.mysql.com/doc/refman/…

Strict SQL Mode

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.

也就是说如果你开启了严格模式，那么插入超长的字段就会报Data too long for column的错误；如果没有，就会替你截断。

SELECT @@session.sql_mode;

image-20220217120826102.png

如果需要开启严格模式（mysql 5.7是默认开启的），需要在sql_mode配置STRICT_ALL_TABLES或STRICT_TRANS_TABLES

Strict SQL mode is in effect if either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled, although the effects of these modes differ somewhat:

avatar
文章
阅读
粉丝