MySQL数据库字段超长问题

蔚落 2023-10-07 19:23 31阅读 0赞

## 1. 存在的问题 ##

在向MySQL数据库表中插入或更新记录时，有时会出现字段超长的问题，包括但不限于以下场景：

*  处理上游系统发送的交易信息，将多个字段拼成一个JSON或其他格式的字符串；在增加字段，或数据较长时，写入或更新数据库时可能超长；
 *  调用下游系统的服务，返回的部分字段（如错误信息等）较长时，导致更新数据库记录失败。

## 2. 问题解决与优化建议 ##

### 2.1. JSON等格式的字段 ###

有业务含义的重要字段，不建议通过JSON字符串格式保存在一个数据库字段中。

假如需要将字段以JSON字符串格式保存在一个数据库字段中，建议只保存相对不重要，且不需要作为唯一的查询条件的字段，在进行保存时也需要考虑字段超长问题，及新旧数据与新旧代码相互之间的兼容问题。

#### 2.1.1. MySQL字符串字段长度 ####

半角英文字母、数字、符号等常见字符，1个字符占用1个字节；1个汉字字符占用3个字节。

在utf8字符集下，1个字符最多占用3个字节，不支持占用4个字节的字符。

在utf8mb4字符集下，1个字符最多占用4个字节，可以保存emoji表情等占用4个字节的字符。

MySQL字符串字段的最大长度如下所示：

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>最大长度</th> 
   <th>单位</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CHAR(n)</td> 
   <td>255</td> 
   <td>字符数</td> 
  </tr> 
  <tr> 
   <td>VARCHAR(n)</td> 
   <td>65535</td> 
   <td>字符数</td> 
  </tr> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>255</td> 
   <td>字节数</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>65535</td> 
   <td>字节数</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT</td> 
   <td>16777215</td> 
   <td>字节数</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT</td> 
   <td>4294967295</td> 
   <td>字节数</td> 
  </tr> 
 </tbody> 
</table>

需要注意，`CHAR、VARCHAR类型字段的最大长度的单位为字符数，能够保存的汉字数量等于最大支持字符数`；

`TEXT等类型字段的最大长度的单位为字节数，能够保存的汉字数量不超过最大支持字节数的1/3`。

#### 2.1.2. MySQL使用较长的字符串类型字段影响 ####

MySQL、MariaDB较新版本支持JSON类型字段，其他版本需要使用字符串类型字段保存。

MySQL中长度超过768字节的固定长度字段被编码为变长字段，例如VARCHAR（超过768字节）、TEXT等，变长字段被称为页外列（off-page），不是保存在InnoDB的B+树索引中，而是保存在溢出页（overflow page）中。在溢出页中，变长字段的值以单链表形式存储。

对于保存JSON形式的字符串类型字段，由于需要保存较多内容，很可能属于变长字段。保存JSON形式的字符串类型字段不适合在查询时作为唯一的查询条件，原因如下：

*  保存JSON形式的字符串类型字段不适合创建索引：一是JSON字符串中的字段顺序不固定，通过like进行最左匹配查询，很难从保存JSON形式的字段中查询到需要的数据；二是因为InnoDB索引支持的长度有限（在MySQL InnoDB默认配置下，索引支持的最大长度为768字节）；
 *  仅通过变长字段进行查询时，无法通过B+树结构的索引进行查询，而是需要在单链表形式的溢出页中逐条进行查询，查询效率会非常低。

查询变长字段，与不查询变长字段相比，开销会更大，耗时会更长。因为查询变长字段时，会增加从溢出页中查询数据的步骤；且需要返回的数据量可能较大，数据返回耗时会增加。

在查询包含变长字段的数据库表时，假如不需要获取变长字段，则不应该在SQL语句中指定查询变长字段。

### 2.2. 可以截断的字段 ###

对于截断后不影响使用的字段，在写入或更新数据库时，可对存在超长风险的字段按照数据库字段长度进行截断；

JDK中的String.substring()方法，commons-lang3中的StringUtils.substring()、StringUtils.truncate()方法，参数中的数字单位都是字符数，不是字节数。

在Java中对字符串进行截取时，建议使用StringUtils.truncate()方法。

MySQL中的CHAR、VARCHAR类型的最大长度，也是字符数，不是字节数。

因此在Java中对字符串根据MySQL的字符串类型字段长度进行截取时，两者的长度是一致的。

例如MySQL中的字段为VARCHAR(200)，则可使用以下方式进行截取，将截取结果写入数据库。

StringUtils.truncate("xxx", 200);