1. 前言
在刚使用MySQL
数据类型甚至很长一段时间都认为int(10)
表示的是只能存储10位数字;varchar(10)
只能存不到10个汉字,因为1个汉字需要好几个字节。事实上,这些认知都是错误的,通过官网和实践你都可以得出正确的论述。
2. 数字类型
为什么说int(M)只能存储M位数字这个论述是错误的?一起来看下下面示例
创建一张表
create table data_type(id int(3));
根据数据类型声明以及错误的论述可以得出id
字段中只能存下3
位数字,我们可以试着往id
中插入一个位数大于3
位数字的值,看看结果会怎么样
mysql> insert into data_type values(1000001);
Query OK, 1 row affected (0.00 sec)
通过实践可以看到一个7
位数的值也是可以正常插入的,为什么会这样呢?来看看官方文档的解释
2.1 官方文档对M的解释
For integer data types,
M
indicates the maximum display width. The maximum display width is 255. Display width is unrelated to the range of values a type can store
翻译过来就是M
只表示最大的显示宽度,显示宽度和存储值的范围没有任何关系
2.2 显示宽度
通过上面的解释,你可能还是无法理解显示宽度到底是什么意思,一起来看下下面示例
创建一张表
create table data_type(id int(5) ZEROFILL);
插入3条数据
insert into data_type values(1);
insert into data_type values(100);
insert into data_type values(10000);
查看结果
到此我想你应该明白M
的含义了,M
只是表示显示宽度并不表示存储值的范围,在声明ZEROFILL
情况下当存储值的位数小于M,则使用0进行填充,当存储值的位数大于等于M,则正常显示
特别注意:以上示例在第三方客户端下可能不能复现,尽可能通过命令行方式进行操作
2.3 占用字节数与存储范围
既然M
只表示显示宽度,那么整数类型占用字节数
与存储范围
分别是多少呢?来看下官方罗列展示
从官方文档的展示可以清楚了解每种类型占用字节数
以及相应存储范围
如你知道的那样,每张表都会有bigint(20)
类型的id
作为主键,主键一般都不会存在负数,将id
声明为bigint(20) unsigned
可以使其存储范围变为原来的2
倍,这也是扩大存储范围的一种方法
3.字符串类型
你觉得varchar(M)
可以存储多少个汉字?需不需要进行M / 4
或者M / 3
计算?一起来看下下面示例
创建表
create table data_type(name varchar(3))engine=innodb charset=utf8mb4;
插入数据
mysql> insert into data_type values('张');
Query OK, 1 row affected (0.01 sec)
mysql> insert into data_type values('张三');
Query OK, 1 row affected (0.00 sec)
mysql> insert into data_type values('张小三');
Query OK, 1 row affected (0.00 sec)
mysql> insert into data_type values('张小小三');
ERROR 1406 (22001): Data too long for column 'name' at row 1
可以看到varchar(3)
是可以插入3个汉字,超过3个汉字就插入失败,这里可以得出一个结论,那就是M
代表的不是字节
3.1 官方文档对M的解释
String Data Type SyntaxA variable-length string.
M
represents the maximum column length in characters.
翻译过来就是M
表示字符长度,也就是说可以存放M
个数字
、英文字母
、汉字
3.2 占用字节数
既然M
表示字符长度,那么varchar(M)
会占用多少个字节呢?
3.2.1 字符编码
字符编码
不同所占用的字节数不同,比如latin1
字符编码1个字符
占1个字节
;ucs2
字符编码1个字符
占2个字节
;utf8
字符编码1个字符
占3个字节
;utf8mb4
字符编码1个字符
占4个字节
;
序号 | 字符编码 | 占用字节数 |
---|---|---|
1 | latin1 | 1 |
2 | ucs2 | 2 |
3 | utf8 | 3 |
4 | utf8mb4 | 4 |
那么在字符编码
为utf8mb4
的情况下,varchar(M)
占用4M
个字节是否正确?
3.2.2 长度占用字节数
String Data Type SyntaxMySQL stores
VARCHAR
values as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. AVARCHAR
column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.
根据官方文档可以了解到还需要1~2个字节来存储字符串长度,再来看一个官方示例说明:
For example, a
VARCHAR(255)
column can hold a string with a maximum length of 255 characters. Assuming that the column uses thelatin1
character set (one byte per character), the actual storage required is the length of the string (L
), plus one byte to record the length of the string. For the string'abcd'
,L
is 4 and the storage requirement is five bytes. If the same column is instead declared to use theucs2
double-byte character set, the storage requirement is 10 bytes: The length of'abcd'
is eight bytes and the column requires two bytes to store lengths because the maximum length is greater than 255 (up to 510 bytes).
varchar(255)
在latin1
编码下存储abcd
需要占用 4 * 1 + 1 = 5
个字节;varchar(255)
在ucs2
编码下存储abcd
需要占用 4 * 2 + 2 = 10
个字节;
ucs2
字符编码下varchar(255)
长度 = 255 * 2
大于255字节,因此需要2个字节
来表示长度
#4.总结
数字类型
中的M
表示显示宽度不表示存储值范围;字符串类型
中的M
表示字符
,varchar
占用字节数与字符编码
和声明长度
有关