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~255num 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)
TimeStampyyyymmddhhmmss
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下打印出的日志存在类似


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