前言

今天測試給提了個bug, 排查原因是插入數(shù)據(jù)時字段長度不夠?qū)е?。我使用的是MySQL8的數(shù)據(jù)庫，在給某個表新增一條數(shù)據(jù)，其中一個字段submit_info使用的是text的類型預(yù)設(shè)用來存儲大的json字符串。之前對text到底能存多長的字符串沒概念，恰好這次預(yù)到問題決定調(diào)查一下。這里記錄一下

一、字符集字符長度和字節(jié)長度

首先需要知道字符長度和字節(jié)長度、和字符集，不同的字符集存儲的中文字符時占的字節(jié)長度不一樣。

MySQL在當前庫中通過 show variables like '%char%';可查看當前數(shù)據(jù)庫的字符集character_set_database->utf8mb4 。

show variables like ‘%char%’;

在 utf8mb4字符集下英文占用1個字節(jié)長度，一般漢字占3-4個字節(jié)長度?？捎?length(字段)，char_length(字段)進行區(qū)分

select version(), submit_info,length(submit_info),char_length(submit_info) from appro_flow_main where id in(35,36);

根據(jù)MySQL官網(wǎng)的資料顯示，text的長度是 L <　２＾１６ = 65536個字節(jié)長度，注意這里是字節(jié)長度，而不是字符長度（varchar(n)這里的n是字符長度），所以說 text 在 character_set_database->utf8mb4 字符集下，大約能存 65535 / 3 = 21845個漢字 并不多，如果存json串實際上并存儲不了多大的json對象尤其是包含中文比較多的情況下。

String Type Storage Requirements

In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

二、驗證案例

（一）表結(jié)構(gòu)DDL和Java Entity

CREATE TABLE `appro_flow_main` (
  `id` int(9) NOT NULL AUTO_INCREMENT COMMENT '物理主鍵id',
  `flow_instance_no` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '流程實例號',
  `flow_instance_name` varchar(128) COLLATE utf8mb4_general_ci NOT NULL COMMENT '流程實例名稱',
  `flow_no` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '流程號',
  `appro_status` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '流程審核狀態(tài)(進行中:1、已結(jié)束:0、保存:2、撤銷)',
  `submit_info` text COLLATE utf8mb4_general_ci COMMENT '提交信息json格式【元數(shù)據(jù)格式：name、key、value、type】',
  `sponsor` varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '流程發(fā)起人',
  `initi_date` int(8) DEFAULT NULL COMMENT '流程發(fā)起日期',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新時間',
  `table_id` varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_fin_fn` (`flow_instance_no`,`flow_no`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='審批流程主表'

/**
 * 流程主表
 *
 * @author lvzb
 * @date 2022/08/09  10:40
 **/
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@TableName("appro_flow_main")
public class FlowMain implements Serializable {

    @TableId(type = IdType.AUTO)
    private Integer id;
    private String flowInstanceNo;
    private String flowInstanceName;
    private String flowNo;
    private String approStatus;
    private String submitInfo;
    private String sponsor;
    private Integer initiateDate;
    private Integer branch;
    private Date createTime;
    private Date updateTime;

}

（二）Java代碼

使用的mybatis-plus 這里只給出核心代碼。注意如果for循環(huán)里 i 從 0.0開始的話，第一條insert就會直接拋異常 text的最大長度是65535個字節(jié)長度，2^16 = 65536

    @Test
    void createWorld() {
        double num = Math.pow(2, 16);
        System.out.println("長度：" + num);
        StringBuilder sb = new StringBuilder();
        StringBuilder cb = new StringBuilder();
        for (double i = 1.0; i < num; i++) {
            sb.append("呂");
            cb.append("a");
        }

        flowMainMapper.insert(FlowMain.builder()
                .flowNo("" + System.nanoTime())
                .flowInstanceName("a")
                .flowInstanceNo("aaa")
                .submitInfo(cb.toString())
                .build());

        flowMainMapper.insert(FlowMain.builder()
                .flowNo("" + System.nanoTime())
                .flowInstanceName("a")
                .flowInstanceNo("aaa")
                .submitInfo(sb.toString())
                .build());
    }

控制臺打印如下：

長度：65536.0
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@38f502fc] was not registered for synchronization because synchronization is not active
2022-10-27 15:57:53.448  INFO 58032 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2022-10-27 15:57:53.922  INFO 58032 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@1440325059 wrapping com.mysql.cj.jdbc.ConnectionImpl@521a506c] will not be managed by Spring
==>  Preparing: INSERT INTO appro_flow_main ( flow_instance_no, flow_instance_name, flow_no, submit_info ) VALUES ( ?, ?, ?, ? )
==> Parameters: aaa(String), a(String), 360639398372500(String), aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...........
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@38f502fc]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4fc3529] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@954492773 wrapping com.mysql.cj.jdbc.ConnectionImpl@521a506c] will not be managed by Spring
==>  Preparing: INSERT INTO appro_flow_main ( flow_instance_no, flow_instance_name, flow_no, submit_info ) VALUES ( ?, ?, ?, ? )
==> Parameters: aaa(String), a(String), 360640025716500(String), 呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂呂..............
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4fc3529]

org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'submit_info' at row 1
### The error may exist in demo/mybatis/plus/dao/mapper/FlowMainMapper.java (best guess)
### The error may involve demo.mybatis.plus.dao.mapper.FlowMainMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO appro_flow_main  ( flow_instance_no, flow_instance_name, flow_no,  submit_info )  VALUES  ( ?, ?, ?,  ? )
### Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'submit_info' at row 1
; Data truncation: Data too long for column 'submit_info' at row 1; nested exception is com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'submit_info' at row 1

三、結(jié)論

實際上根據(jù) 以上的分析

• text 可以存儲 65535個字節(jié)=> 65535 （byte）/1024 ≈ 64KB 大小
• 在utf8mb4的字符集下 text 最多可以存儲 65535 / 3(一個漢字的占用字節(jié)長度) = 21845， 2萬個左右的中文字符

由上可知，比較大的JSON串尤其是含中文較多的不適合使用text進行存儲。根據(jù)需求使用 更長的 MEDIUMTEXT、LONGTEXT。

參考資料

• MySQL官網(wǎng)|String Type Storage Requirements
• byte 和 KB 換算 參考

總結(jié)

到此這篇關(guān)于MySQL text能存多少個字符的文章就介紹到這了,更多相關(guān)MySQL text存多少字符內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論