MySQL数据类型解惑

476 阅读5分钟

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个字节

序号字符编码占用字节数
1latin11
2ucs22
3utf83
4utf8mb44

那么在字符编码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. A VARCHAR 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 the latin1 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 the ucs2 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占用字节数与字符编码声明长度有关