一文詳解MySQL?text能存多少個字符
前言
今天測試給提了個bug, 排查原因是插入數(shù)據(jù)時字段長度不夠?qū)е?。我使用的是MySQL8的數(shù)據(jù)庫,在給某個表新增一條數(shù)據(jù),其中一個字段submit_info使用的是text的類型預設用來存儲大的json字符串。之前對text到底能存多長的字符串沒概念,恰好這次預到問題決定調(diào)查一下。這里記錄一下
一、字符集字符長度和字節(jié)長度
首先需要知道字符長度和字節(jié)長度、和字符集,不同的字符集存儲的中文字符時占的字節(jié)長度不一樣。
MySQL在當前庫中通過 show variables like '%char%';
可查看當前數(shù)據(jù)庫的字符集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個字節(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);
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的長度是 L < 2^16
= 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.
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) |
二、驗證案例
(一)表結(jié)構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
。
參考資料
總結(jié)
到此這篇關于MySQL text能存多少個字符的文章就介紹到這了,更多相關MySQL text存多少字符內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
mysql數(shù)據(jù)庫mysql: [ERROR] unknown option ''--skip-grant-tables'
這篇文章主要介紹了mysql數(shù)據(jù)庫mysql: [ERROR] unknown option '--skip-grant-tables',需要的朋友可以參考下2020-03-03MYSQL(電話號碼,身份證)數(shù)據(jù)脫敏的實現(xiàn)
在日常開發(fā)需求中會經(jīng)常遇到數(shù)據(jù)脫敏處理,比如身份證號、手機號,需要使用*進行部分替換顯示。這樣能使敏感隱私信息在一定程度上得到保護。本文就來介紹一下2021-05-05Windows Server 2003 下配置 MySQL 集群(Cluster)教程
這篇文章主要介紹了Windows Server 2003 下配置 MySQL 集群(Cluster)教程,本文先是講解了原理知識,然后給出詳細配置步驟和操作方法,需要的朋友可以參考下2015-06-06MySQL數(shù)據(jù)庫中null的知識點總結(jié)
在本篇文章里小編給大家整理的是關于MySQL數(shù)據(jù)庫null的知識點以及相關實例,需要的朋友們可以學習下。2019-10-10MySQL創(chuàng)建數(shù)據(jù)表時設定引擎MyISAM/InnoDB操作
這篇文章主要介紹了MySQL創(chuàng)建數(shù)據(jù)表時設定引擎MyISAM/InnoDB操作,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2020-08-08MySQL數(shù)據(jù)庫case?when?then?end的詳細使用方法
在SQL語法中我們首先使用CASE關鍵字開頭,然后根據(jù)不同的條件使用WHEN關鍵字,并在每個條件后面指定結(jié)果,這篇文章主要給大家介紹了關于MySQL數(shù)據(jù)庫case?when?then?end的詳細使用方法,需要的朋友可以參考下2023-12-12