msyql存储数据时字段被截断

关于mysql中字段类型为text文本存储json格式数据，字段被截断的问题

背景：

1. 字段类型 MEDIUMTEXT

2. 确定存储内容5548个字符，换算为字节不超过16M

3. 数据库内已经存在更长内容的存储成功，短的内容存储失败

4. 有几率出现截断概率，

5. 日志看入库前参数json都是完整的，入库后内容被截断，导致前端读取数据结构解析失败而报错

发生这种情况的原因有两种可能：

1. 建表时数据库字段设置长度不足

• 当输入内容的字符长度大于当前字段设置长度时，数据库会自动截断内容

• 字段类型以下几种：

• 字符串类型

类型	范围	说明
Char(N) [ binary]	N=1~255 个字节 binary ：分辨大小写	固定长度	std_name cahr(32) not null
VarChar(N) [binary]	N=1~255 个字节 binary ：分辨大小写	可变长度	std_address varchar(256)
TinyBlob	最大长度255个字节(2^8-1) Blob (Binary large objects)	储存二进位资料，且有分大小写	memo text not null
TinyText	最大长度255个字节(2^8-1)
Blob	最大长度65535个字节(2^16-1)
Text	最大长度65535个字节(2^16-1)
MediumBlob	最大长度 16777215 个字节(2^24-1)
MediumText	最大长度 16777215 个字节(2^24-1
LongBlob	最大长度4294967295个字节 (2^32-1)
LongText	最大长度4294967295个字节 (2^32-1)
Enum	集合最大数目为65535	列举(Enumeration)，Enum单选、Set复选	sex enum(1,0) habby set(‘玩电玩’,’睡觉’,’看电影’,’听音乐’)
Set	集合最大数目为64

• 数字/数值类型

类型	范围	说明
TinyInt[M] [UNSIGNED]	-128~127 UNSIGNED ： 0~255	num tinyint unsigned
SmallInt[M] [UNSIGNED]	-32768~32767 UNSIGNED ：0~ 65535
MediumInt[M] [UNSIGNED]	-8388608~8388607 UNSIGNED ：0~16777215
Int[M] [UNSIGNED]	-2^31~2^31-1 UNSIGNED ： 0~2^32
BigInt[M] [UNSIGNED]	-2^63~2^63-1 UNSIGNED ： 0~2^64
Float [(M,D)]	-3.4E+38~3.4E+38( 约 )

注： M 为长度， D 为小数,Float 4 bytes,Double 8 bytes Double [(M,D)] -1.79E+308~1.79E+308( 约 )Decimal [(M,D)] 辨別ZeroFill：当宣告关键字ZeroFill为自动填满0，如 000021

• 日期时间类型

类型	范围
Date	日期(yyyy-mm-dd)
Time	时间(hh:mm:ss)
DateTime	日期与时间組合(yyyy-mm-dd hh:mm:ss)
TimeStamp	yyyymmddhhmmss
Year	年份yyyy

• 根据计算转换字符长度可知，并没有超过长度限制，故此种原因排除

2. 字符编码格式数据库不支持

• 原数据库字符集设置为CHARSET=utf8，有些数据编码格式例如emoji的表情符号mysql 中的utf8并不支持
• 据官方文档的解释：

  mysql 支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面（BMP）。

• 也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情（Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上），和很多不常用的汉字，以及任何新增的 Unicode 字符等等。

• 观察日志数据发现linux下打印出的日志存在类似 <u+1f44d> 样式的字符，属于unicode编码的特殊字符，文本内容也是在此截断的,由此可以判断为字符编码格式问题

3. 解决方案

3.1 升级 mysql 版本，并将utf8字符集升级到utf8mb4

升级你的 mysql 到 5.5.3 之后即可，查看当前环境版本：

select version();

• MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8bp4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。
• 所以好的技术就是，采用对当前而言最好的解决方案，然后再逐步迭代满足新的需求。

3.1.1 直接修改表结构

--修改数据库字符集
ALTER DATABASE test CHARACTER SET = utf8mb4;
--修改表字符集
alter table test convert to character set utf8mb4;
--修改字符字符集
ALTER TABLE `test` CHANGE COLUMN `name` `name` varchar(12) CHARACTER SET utf8mb4;

3.1.2 修改数据库默认配置

[client]
default-character-set = utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
[mysql]
default-character-set = utf8mb4

3.2 修改数据库字段类型

修改字段类型为二进制存储，通过样可以达到目的，进行兼容

--修改字符字符集
ALTER TABLE `test` CHANGE COLUMN `name` `name` MediumBlob ;

3.3 强行过滤掉生僻字符串

• 从业务和技术的角度综合考虑，可以做个折中，将生僻字符串提前过滤掉，因为这类字符串本来就使用的很少，即使存进数据库了，展示、查询的时候也会多少有其它的问题，不如直接过滤掉，mysql 不支持四字节的 utf8 一方面可能是历史包袱，另一方面估计也是为了省空间。

有意者可以进行测试

1. 先向在数据库中建表以utf8字符集，字段设置为二进制文本类型或者

    CREATE TABLE `ts_test` (
    `id` int(10) NOT NULL,
    `content` text NOT NULL CHARACTER SET utf8mb4, --或者 `content` BLOB NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
   

2. 插入表情符号emoji

    insert into ts_test ('id','content') values (1,'aaaaaaaa👍aaaaaaaa')
   

3. 修改字段类型为普通为类型，就会发现内容从表情符开始被截断

    ALTER TABLE `test` CHANGE COLUMN `content` `content` text not null;
   

4. 然后再查询该条数据，就会发现表情符后面的内容不见了

​