关于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;
  1. 插入表情符号emoji
insert into ts_test ('id','content') values (1,'aaaaaaaa?aaaaaaaa')
  1. 修改字段类型为普通为类型，就会发现内容从表情符开始被截断
ALTER TABLE `test` CHANGE COLUMN `content` `content` text not null;
  1. 然后再查询该条数据，就会发现表情符后面的内容不见了