The BLOB and TEXT Types

最新推荐文章于 2021-05-10 15:43:04 发布

Bcc的笔记

最新推荐文章于 2021-05-10 15:43:04 发布

阅读量88

点赞数

分类专栏： MySQL笔记文章标签：字符串 java mysql 数据库 python

本文链接：https://blog.csdn.net/weixin_41431016/article/details/109666491

版权

MySQL笔记专栏收录该内容

43 篇文章 0 订阅

订阅专栏

官网参考：https://dev.mysql.com/doc/refman/5.7/en/blob.html

字符串类型对应的存储需求

Data Type	Storage Required
`CHAR(M)`	`M` × `w` bytes, 0 `<= M <=` 255, where `w` is the number of bytes required for the maximum-length character in the character set. See Section 14.8.1.2, “The Physical Row Structure of an InnoDB Table” for information about `CHAR` data type storage requirements for `InnoDB` tables.
`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⁸
`BLOB`, `TEXT`	`L` + 2 bytes, where `L` < 2¹⁶
`MEDIUMBLOB`, `MEDIUMTEXT`	`L` + 3 bytes, where `L` < 2²⁴
`LONGBLOB`, `LONGTEXT`	`L` + 4 bytes, where `L` < 2³²
`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)

关于row size 参考官方文档

varchar允许的最大字符长度是65,535，超过这个限制就需要考虑text和blob
varchar(N)，N 代表允许的中文字的个数(字符集为utf8的一个中文字占用三个字符)也表示英文字符的个数，

mysql> desc varch
    -> ;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(3) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into varch values('hell');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into varch values('hel');
Query OK, 1 row affected (0.00 sec)

mysql> select * from varch;
+-----------+
| name      |
+-----------+
| 新中国    |
| hel       |
+-----------+
2 rows in set (0.00 sec)

N>255就会占用2个额外的字符存储长度，所以N最大为(65535-2)/3

mysql> create table varch(name varchar(21845));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table varch(name varchar(21844));
Query OK, 0 rows affected (0.11 sec)

对于innodb,row size 的限制是innodb_page_size的设置的一半(如果设置不超过32KB，默认是16KB)，对于text 和blob 没有限制，只会占用row size 的9-12字节(byte)

mysql>  create table varch(col1 varchar(10920),col2 varchar(10920),col3 text);          
Query OK, 0 rows affected (0.12 sec)
# 10920 计算公式
mysql> select (65535-(2+2+9))/3/2; 
+---------------------+
| (65535-(2+2+9))/3/2 |
+---------------------+
|      10920.33333333 |
+---------------------+
# 括号里的2、2、9是字段的长度存储所需大小