The BLOB and TEXT Types

时间 2019-11-17 标签 blob text types

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

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

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、9是字段的长度存储所需大小

text至关于longvarchar，BLOB值被视为二进制字符串（字节串）。它们具备二进制字符集和排序规则，比较和排序基于列值中字节的数值。 TEXT值被视为非二进制字符串（字符串）。它们具备除二进制以外的字符集，而且基于字符集的排序来对值进行排序和比较。blob至关于binaryvarchar

注意点

建立索引时必须制定前缀
非strict sql mode模式下，若是长度超过限制，则会自动进行截断
不支持default值

text 和 blob 类型的字段可能会很是的长，因此就可能遇到如下限制

进行分组或排序时只会对max_sort_length 指定的长度进行，次变量默认值为1024，能够进行session级别的调整
因为memory 存储引擎不支持这两种类型，因此包含此字段的查询若是用到临时表，则表会在磁盘上建立，从而会形成性能的消耗，因此避免使用 SELECT *
server和client传输此类型的数据时可能会超过通讯包的长度限制，经过max_allowed_packet进行设置，客户端和服务器端都须要设置