一文詳解MySQL?text能存多少個(gè)字符
前言
今天測(cè)試給提了個(gè)bug, 排查原因是插入數(shù)據(jù)時(shí)字段長(zhǎng)度不夠?qū)е?。我使用的是MySQL8的數(shù)據(jù)庫(kù)，在給某個(gè)表新增一條數(shù)據(jù)，其中一個(gè)字段submit_info使用的是text的類型預(yù)設(shè)用來(lái)存儲(chǔ)大的json字符串。之前對(duì)text到底能存多長(zhǎng)的字符串沒(méi)概念，恰好這次預(yù)到問(wèn)題決定調(diào)查一下。這里記錄一下
一、字符集字符長(zhǎng)度和字節(jié)長(zhǎng)度
首先需要知道字符長(zhǎng)度和字節(jié)長(zhǎng)度、和字符集，不同的字符集存儲(chǔ)的中文字符時(shí)占的字節(jié)長(zhǎng)度不一樣。
MySQL在當(dāng)前庫(kù)中通過(guò) show variables like '%char%';可查看當(dāng)前數(shù)據(jù)庫(kù)的字符集character_set_database->utf8mb4 。
show variables like ‘%char%’;
| Variable_name | Value |
|---|---|
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
在 utf8mb4字符集下英文占用1個(gè)字節(jié)長(zhǎng)度，一般漢字占3-4個(gè)字節(jié)長(zhǎng)度?？捎?length(字段)，char_length(字段)進(jìn)行區(qū)分
select version(), submit_info,length(submit_info),char_length(submit_info) from appro_flow_main where id in(35,36);
| version() | submit_info | length(submit_info) | char_length(submit_info) |
|---|---|---|---|
| 8.0.18 | 呂呂呂 | 9 | 3 |
| 8.0.18 | aaa | 3 | 3 |
根據(jù)MySQL官網(wǎng)的資料顯示，text的長(zhǎng)度是 L < ２＾１６ = 65536個(gè)字節(jié)長(zhǎng)度，注意這里是字節(jié)長(zhǎng)度，而不是字符長(zhǎng)度（varchar(n)這里的n是字符長(zhǎng)度），所以說(shuō) text 在 character_set_database->utf8mb4 字符集下，大約能存 65535 / 3 = 21845個(gè)漢字 并不多，如果存json串實(shí)際上并存儲(chǔ)不了多大的json對(duì)象尤其是包含中文比較多的情況下。
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.
| Data Type | Storage Required |
|---|---|
CHAR(*M*) | The compact family of InnoDB row formats optimize storage for variable-length character sets. See COMPACT Row Format Storage Characteristics. Otherwise, M × w bytes, <= *M* <= 255, where w is the number of bytes required for the maximum-length character in the character set. |
BINARY(*M*) | M bytes, 0 <= *M* <= 255 |
VARCHAR(*M*), VARBINARY(*M*) | L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytes |
| TINYBLOB, TINYTEXT | L + 1 bytes, where L < 2^8 |
| BLOB, TEXT | L + 2 bytes, where L < 2^16 |
| MEDIUMBLOB, MEDIUMTEXT | L + 3 bytes, where L < 2^24 |
| LONGBLOB, LONGTEXT | L + 4 bytes, where L < 2^32 |
ENUM('*value1*','*value2*',...) | 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) |
SET('*value1*','*value2*',...) | 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) |
二、驗(yàn)證案例
（一）表結(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 '流程實(shí)例號(hào)', `flow_instance_name` varchar(128) COLLATE utf8mb4_general_ci NOT NULL COMMENT '流程實(shí)例名稱', `flow_no` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '流程號(hào)', `appro_status` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '流程審核狀態(tài)(進(jìn)行中: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)建時(shí)間', `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新時(shí)間', `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就會(huì)直接拋異常 text的最大長(zhǎng)度是65535個(gè)字節(jié)長(zhǎng)度，2^16 = 65536
@Test
void createWorld() {
double num = Math.pow(2, 16);
System.out.println("長(zhǎng)度：" + 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());
}控制臺(tái)打印如下：
長(zhǎng)度：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é)論
實(shí)際上根據(jù) 以上的分析
- text 可以存儲(chǔ) 65535個(gè)字節(jié)=> 65535 （byte）/1024 ≈ 64KB 大小
- 在utf8mb4的字符集下 text 最多可以存儲(chǔ) 65535 / 3(一個(gè)漢字的占用字節(jié)長(zhǎng)度) = 21845， 2萬(wàn)個(gè)左右的中文字符
由上可知，比較大的JSON串尤其是含中文較多的不適合使用text進(jìn)行存儲(chǔ)。根據(jù)需求使用 更長(zhǎng)的 MEDIUMTEXT、LONGTEXT。
參考資料
總結(jié)
到此這篇關(guān)于MySQL text能存多少個(gè)字符的文章就介紹到這了,更多相關(guān)MySQL text存多少字符內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Mysql實(shí)現(xiàn)水平分庫(kù)的示例代碼
本文主要介紹了Mysql實(shí)現(xiàn)水平分庫(kù)的示例代碼，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-06-06![mysql數(shù)據(jù)庫(kù)mysql: [ERROR] unknown option ''--skip-grant-tables''](http://img.jbzj.com/images/xgimg/bcimg1.png)
mysql數(shù)據(jù)庫(kù)mysql: [ERROR] unknown option ''--skip-grant-tables'
這篇文章主要介紹了mysql數(shù)據(jù)庫(kù)mysql: [ERROR] unknown option '--skip-grant-tables',需要的朋友可以參考下2020-03-03
本文主要講解了Mysql主從同步原理，主從同步可以擴(kuò)展數(shù)據(jù)庫(kù)的負(fù)載能力、容錯(cuò)還可以數(shù)據(jù)備份等。想要了解相關(guān)內(nèi)容的朋友可以閱讀這篇文章2021-08-08
MYSQL(電話號(hào)碼,身份證)數(shù)據(jù)脫敏的實(shí)現(xiàn)
在日常開發(fā)需求中會(huì)經(jīng)常遇到數(shù)據(jù)脫敏處理，比如身份證號(hào)、手機(jī)號(hào)，需要使用*進(jìn)行部分替換顯示。這樣能使敏感隱私信息在一定程度上得到保護(hù)。本文就來(lái)介紹一下2021-05-05
Windows Server 2003 下配置 MySQL 集群(Cluster)教程
這篇文章主要介紹了Windows Server 2003 下配置 MySQL 集群(Cluster)教程,本文先是講解了原理知識(shí),然后給出詳細(xì)配置步驟和操作方法,需要的朋友可以參考下2015-06-06
快速學(xué)習(xí)MySQL索引的入門超級(jí)教程
這篇文章主要介紹了快速學(xué)習(xí)MySQL索引的入門教程,包括索引的創(chuàng)建和刪除等基礎(chǔ)知識(shí),需要的朋友可以參考下2015-11-11
MySQL數(shù)據(jù)庫(kù)中null的知識(shí)點(diǎn)總結(jié)
在本篇文章里小編給大家整理的是關(guān)于MySQL數(shù)據(jù)庫(kù)null的知識(shí)點(diǎn)以及相關(guān)實(shí)例，需要的朋友們可以學(xué)習(xí)下。2019-10-10
MySQL創(chuàng)建數(shù)據(jù)表時(shí)設(shè)定引擎MyISAM/InnoDB操作
這篇文章主要介紹了MySQL創(chuàng)建數(shù)據(jù)表時(shí)設(shè)定引擎MyISAM/InnoDB操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-08-08
MySQL數(shù)據(jù)庫(kù)case?when?then?end的詳細(xì)使用方法
在SQL語(yǔ)法中我們首先使用CASE關(guān)鍵字開頭,然后根據(jù)不同的條件使用WHEN關(guān)鍵字,并在每個(gè)條件后面指定結(jié)果,這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫(kù)case?when?then?end的詳細(xì)使用方法,需要的朋友可以參考下2023-12-12
這篇文章主要介紹了Mysql表連接的執(zhí)行流程詳解，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下，希望對(duì)你的學(xué)習(xí)有所幫助2022-08-08