mysql 字段被截断_msyql存储数据时字段被截断

msyql存储数据时字段被截断

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

背景：

字段类型 MEDIUMTEXT

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

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

有几率出现截断概率，

日志看入库前参数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 一方面可能是历史包袱，另一方面估计也是为了省空间。

有意者可以进行测试

先向在数据库中建表以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;

插入表情符号emoji

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