一文詳解MySQL?text能存多少個(gè)字符
前言
今天測(cè)試給提了個(gè)bug, 排查原因是插入數(shù)據(jù)時(shí)字段長(zhǎng)度不夠?qū)е隆Ｎ沂褂玫氖荕ySQL8的數(shù)據(jù)庫(kù)，在給某個(gè)表新增一條數(shù)據(jù)，其中一個(gè)字段submit_info使用的是text的類型預(yù)設(shè)用來存儲(chǔ)大的json字符串。之前對(duì)text到底能存多長(zhǎng)的字符串沒概念，恰好這次預(yù)到問題決定調(diào)查一下。這里記錄一下
一、字符集字符長(zhǎng)度和字節(jié)長(zhǎng)度
首先需要知道字符長(zhǎng)度和字節(jié)長(zhǎng)度、和字符集，不同的字符集存儲(chǔ)的中文字符時(shí)占的字節(jié)長(zhǎng)度不一樣。
MySQL在當(dāng)前庫(kù)中通過 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)度），所以說 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萬個(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經(jīng)典高逼格/命令行操作(速成)(推薦)
這篇文章主要介紹了Mysql命令行操作，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04
MySQL千萬級(jí)大數(shù)據(jù)SQL查詢優(yōu)化知識(shí)點(diǎn)總結(jié)
在本篇文章里小編給大家整理的是一篇關(guān)于MySQL千萬級(jí)大數(shù)據(jù)SQL查詢優(yōu)化知識(shí)點(diǎn)總結(jié)內(nèi)容，有需要的朋友們可以學(xué)習(xí)參考下。2019-12-12
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)的多表操作，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，感興趣的小伙伴可以參考一下，希望對(duì)你的學(xué)習(xí)有所幫助2022-12-12
mysql忘記root密碼的解決辦法(針對(duì)不同mysql版本)
這篇文章主要介紹了mysql忘記root密碼的解決辦法(針對(duì)不同mysql版本),文章通過代碼示例和圖文結(jié)合的方式給大家講解的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2024-06-06
詳解MySQL實(shí)時(shí)同步到Oracle解決方案
這篇文章主要介紹了詳解MySQL實(shí)時(shí)同步到Oracle解決方案，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
mysql密碼忘記怎么辦？卸載重裝？這篇文章主要為大家詳細(xì)介紹了不卸載也能重新設(shè)置密碼的方法，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-02-02
mysql community server 8.0.12安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了mysql community Server 8.0.12安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-08-08
Mysql 5.6 "隱式轉(zhuǎn)換"導(dǎo)致的索引失效和數(shù)據(jù)不準(zhǔn)確的問題
這篇文章主要介紹了Mysql 5.6 “隱式轉(zhuǎn)換”導(dǎo)致的索引失效和數(shù)據(jù)不準(zhǔn)確的問題,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-12-12
這篇文章主要介紹了Mac下mysql 8.0.22 找回密碼的方法，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2020-11-11
在我們寫業(yè)務(wù)代碼的時(shí)候,會(huì)經(jīng)常碰見排序方式既不是正序也不是倒序,下面這篇文章主要給大家介紹了關(guān)于mysql按照自定義(指定順序)排序的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-06-06