(MariaDB)MySQL数据类型和存储机制全面讲解-木庄网络博客

本文整理自网络，侵删。

1.1 数据类型概览

数据类型算是一种字段约束，它限制每个字段能存储什么样的数据、能存储多少数据、能存储的格式等。MySQL/MariaDB大致有5类数据类型，分别是：整形、浮点型、字符串类型、日期时间型以及特殊的ENUM和SET类型。

这5种数据类型的意义、限制和相关说明如下图所示：

各数据类型占用字节数，参见mariadb官方手册。

1.2 存储机制和操作方式

数据类型之所以能限定字段的数据存储长度，是因为在创建表时在内存中严格划定了地址空间，地址空间的长度是多少就能存储多少字节的数据。当然，这是一个很粗犷的概念，更具体的存储方式见下面的描述。

数据类型限定范围的方式有两种：一是严格限定空间，划分了多少空间就只能存储多少数据，超出的数据将被切断；二是使用额外的字节的bit位来标记某个地址空间的字节是否存储了数据，存储了就进行标记，不存储就不标记。

1.2.1 整型的存储方式

此处主要说明整型的存储方式，至于浮点型数据类型的存储方式要考虑的东西太多。

对于整型数据类型来说，它严格限定空间，但它和字符不同，因为每个已划分的字节上的bit位上的0和1直接可以计算出数值，所以它的范围是根据bit位的数量值来计算的。一个字节有8个Bit位，这8个bit位可以构成2^8=256个数值，同理2字节的共2^16=65536个数值，4字节的int占用32bit，可以表示的范围为0-2^32。也就是说，在0-255之间的数字都只占用一个字节，256-65535之间的数字需要占用两个字节。

需要注意，在MySQL/mariadb中的整型数据类型可以使用参数M，M是一个正整数，例如INT(M)，tinyint(M)。这个M表示的是显示长度，如int(4)表示在输出时将显示4位整数，如果实际值的位数小于显示值宽度，则默认使用空格填充在左边。而结果位数超出时将不影响显示结果。一般该功能都会配合zerofill属性用0代替空格填充，但是使用了zerofill后，该列就会自动变成无符号字段。例如：

CREATE TABLE test3(id INT(2) ZEROFILL NOT NULL);
INSERT INTO test3 VALUES(1),(2),(11),(111);
SELECT id FROM test3;
+-----+
| id |
+-----+
| 01 |
| 02 |
| 11 |
| 111 |
+-----+
4 rows in set (0.00 sec)

唯一需要注意的是，显示宽度仅仅影响显示效果，不影响存储、比较、长度计算等等任何操作。

1.2.2 字符类型的存储方式

此处主要说明char和varchar的存储方式以及区别。

char类型是常被称为"定长字符串类型"，它严格限定空间长度，但它限定的是字符数，而非字节数，但以前老版本中限定的是字节数。因此char(M)严格存储M个字符，不足部分使用空格补齐，超出M个字符的部分直接截断。

由于char类型有"短了就使用空格补足"的能力，因此为了体现数据的真实性，在从地址空间中检索数据时将自动删除尾随的空格部分。这正是char的一个特殊性，即使是我们手动存储的尾随空格也会被认为是自动补足的，于是在检索时被删除。也就是说在where语句中name='gaoxiaofang '和name='gaoxiaofang'的结果是一样的。

例如：

create table test2(a char(4) charset utf8mb4);
insert into test2 values('恭喜你'),('恭喜你成功晋级'),('hello'),('he ');
select concat(a,'x') from test2;
+---------------+
| concat(a,'x') |
+---------------+
| 恭喜你x |
| 恭喜你成x |
| hellx  |
| hex  |
+---------------+
4 rows in set

从上面的结果可以看到，char(4)只能存储4个字符，并删除尾随空格。

varchar常被称为"变长字符串类型"，它存储数据时使用额外的字节的bit位来标记某个字节是否存储了数据。每存储一个字节(不是字符)占用一个bit位进行记录，因此一个额外的字节可以标记共256个字节，2个额外的字节可以标记65536个字节。但MySQL/mariadb限制了最大能存储65536个字节。这表示，如果是单字节的字符，它最多能存储65536个字符，如果是多字节字符，如UTF8的每个字符占用3个字节，它最多能存储65536/3=21845个utf8字符。

因此，varchar(M)存储时除了真实数据占用空间长度，还要额外计算1或2个字节的Bit位长度，即对于单字节字符实际占用的空间为M+1或M+2个字节，对于多字节字符(如3字节)实际占用的空间为M*3+1或M*3+2个字节。

由于varchar存储时需要采用额外的bit位记录每一个字节，短了的数据不会自动使用补齐，因此显式存储的尾随空格也会被存储并在Bit位上进行标记，也就是说不会删除尾随空格。

和char(M)一样，当指定varchar(2)时，只能存储两个字节的字符，如果超出了，则切断。

关于char、varchar以及text字符串类型，它们在比较时不会考虑尾随空格，但做like匹配或正则匹配时会考虑空格，因为匹配时字符是精确的。例如：

create table test4(a char(4),b varchar(5));
insert into test4 values('ab ','ab ');
select a='ab ',b='ab ',a=b from test4;
+-----------+--------------+-----+
| a='ab ' | b='ab ' | a=b |
+-----------+--------------+-----+
|  1 |  1 | 1 |
+-----------+--------------+-----+
1 row in set
select a like 'ab ' from test4;
+-------------------+
| a like 'ab ' |
+-------------------+
|   0 |
+-------------------+
1 row in set

最后需要说明的是，数值在存储(或调入内存)时，以数值型方式存储比字符型或日期时间类型更节省空间。因为整数值存储时是直接通过bit计算数值的，0-255之间的任意整数都只占一个字节，256-65535之间的任意整数都占2个字节，而占用4个字节时便可以代表几十亿个整数之间的任意一个，这显然比字符型存储时每个字符占用一个字节节省空间的多。例如值"100"存储为字符型时占用三个字节，而存储为数值型将只占用一个字节。因此数据库默认将不使用引号包围的值当作数值型，如果明确要存储为字符型或日期时间型则应该使用引号包围以避免歧义。

1.2.3 日期时间型的存储方式

日期时间性数据存储时需要使用引号包围，避免和数值类型的数据产生歧义。关于日期时间的输入方式是非常宽松的，以下几种方式都是被允许的：任意允许的分隔符，建议使用4位的年份。

20110101
2011-01-01 18:40:20
2011/01/01 18-40-20
20110101184020

1.2.4 ENUM数据类型

ENUM数据类型是枚举型。定义方式为ENUM('value1','value2','value3',...)，在向该类型的字段中插入数据时只能插入value中的某一个或NULL，插入其他值或空(即'')时都将截断为空数据。存储时会忽略大小写(将转换为ENUM中的字符)，且会截断尾随空格。

mysql> create table test6(id int auto_increment primary key,name char(20),gender enum('Mail','f'));
mysql> insert into test6(name,gender) values('malongshuai','Mail'),('gaoxiaofang','F'),('wugui','x'),('tuner',null),('woniu','');
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 2
mysql> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message     |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated for column 'gender' at row 3 |
| Warning | 1265 | Data truncated for column 'gender' at row 5 |
+---------+------+---------------------------------------------+
2 rows in set
mysql> select * from test6;
+----+-------------+--------+
| id | name | gender |
+----+-------------+--------+
| 1 | malongshuai | Mail |
| 2 | gaoxiaofang | f |
| 3 | wugui | |
| 4 | tuner | NULL |
| 5 | woniu | |
+----+-------------+--------+
5 rows in set

ENUM类型的数据存储时是通过index数值进行存储的，相比于字符串类型，它只需要1或2个字节进行存储即可。理论上，当value的数量少于256个时只需一个字节，超出256个但少于65536个时使用2个字节存储。MySQL/MariaDB限制最多只能存储65536个value。当然，这是理论上的限制，实际存储时要考虑的因素有很多，例如NULL也会占用bit位，所以实际存储时可能250个value就需要2个字节。

阅读剩余部分