(MariaDB)MySQL数据类型详解和存储机制

共计 7272 个字符，预计需要花费 19 分钟才能阅读完成。

本文目录：
1.1 数据类型概览
1.2 存储机制和操作方式
1.2.1 整型的存储方式
1.2.2 字符类型的存储方式
1.2.3 日期时间型的存储方式
1.2.4 ENUM 数据类型
1.2.5 SET 数据类型
1.3. 数据类型属性：unsigned
1.3. 数据类型属性：zerofill

 

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

ENUM 的每个 value 都通过 index 号码进行编号，无论是检索还是操作该字段时都会通过 index 的值来操作。value1 的 index=1，value2 的 index=2，依次类推。但需要注意有两个特殊的 index 值：NULL 值的 index=NULL，空数据的 index=0。

例如 ENUM('a','b','c')，向该字段依次插入'','b','a','c',NULL,'xxx' 时，由于第一个和最后一个都会截断为空数据，所以它们的 index 为 0，插入的 NULL 的 index 为 NULL，插入的 ’b’,’a’,’c’ 的 index 值分别为 2,1,3。所以 index 号码和值的对应关系为：

index	value
NULL	NULL
0	”
0	”
1	‘a’
2	‘b’
3	‘c’

使用 ENUM 的 index 进行数据检索：

mysql> select * from test6 where gender=2;
+----+-------------+--------+
| id | name        | gender |
+----+-------------+--------+
|  2 | gaoxiaofang | f      |
+----+-------------+--------+
1 row in set

特别建议，不要使用 ENUM 存储数值，因为无论是排序还是检索或其他操作，都是根据 index 值作为条件的，这很容易产生误解。例如，下面是用 ENUM 存储两个数值，然后进行检索和排序操作。

mysql> create table test7(id enum('3','1','2'));
mysql> insert into test7 values('1'),('2'),('3');

# 检索时 id=2，但结果查出来却为 1，因为 id= 2 的 2 是 enum 的 index 值，在 enum 中 index= 2 的值为 1 
mysql> select * from test7 where id=2;
+----+
| id |
+----+
| 1  |
+----+
1 row in set

# 按照 id 进行排序时，也是通过 index 大小进行排序的
mysql> select * from test7 order by id asc;
+----+
| id |
+----+
| 3  |
| 1  |
| 2  |
+----+
3 rows in set

因此，强烈建议不要在 ENUM 中存放数值，即使是浮点型数值也很容易出现歧义。

 

1.2.5 SET 数据类型

对于 SET 类型，和 enum 类似，不区分大小写，存储时删除尾随空格，null 也是有效值。但不同的是可以组合多个给出的值。如 set('a','b','c','d') 可以存储 'a，b','d，b' 等，多个成员之间使用逗号隔开。所以，使用多个成员的时候，成员本身的值中不能出现逗号。如果要存储的内容不在 set 列表中，则截断为空值。

SET 数据类型占用的空间大小和 SET 成员数量 M 有关，计算方式为(M+7)/ 8 取整。所以：1- 8 个成员占用 1 个字节；
9-16 个成员占用 2 个字节；
17-24 个成员占用 3 字节；
25-32 个成员占用 4 个字节；
33-64 个成员占用 8 字节。

MySQL/MariaDB 限制最多只能有 64 个成员。

存储 SET 数据类型的数据时忽略重复成员并按照枚举时的顺序存储 。如set('b','b','a')，存储'a,b,a','b,a,b' 的结果都是 ’b,a’。

mysql> create table test8(a set('d','b','a'));
mysql> insert into test8 values('b,b,a'),('b,a,b'),('bab');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 1

mysql> select * from test8;
+-----+
| a   |
+-----+
| b,a |
| b,a |
|     |
+-----+
3 rows in set

使用 find_in_set(set_value,set_column_name) 可以检索出包含指定 set 值 set_value 的行。例如检索 a 字段中包含成员 b 的行：

mysql> select * from test8 where find_in_set('b',a);
+-----+
| a   |
+-----+
| b,a |
| b,a |
+-----+
2 rows in set

 

1.3 数据类型属性：unsigned

unsigned 属性就是让数值类型的数据变得无符号化。使用 unsigned 属性将会改变数值数据类型的范围，例如 tinyint 类型带符号的范围是 -128 到 127，而使用 unsigned 时范围将变成 0 到 255。同时 unsigned 也会限制该列不能插入负数值。

create table t(a int unsigned,b int unsigned);
insert into t select 1,2;
insert into t select -1,-2;

上面的语句中，在执行第二条语句准备插入负数时将会报错，提示超出范围。

使用 unsigned 在某些情况下确有其作用，例如一般的 ID 主键列不会允许使用负数，它相当于实现了一个 check 约束。但是使用 unsigned 有时候也会出现些不可预料的问题：在进行数值运算时如果得到负数将会报错。例如上面的表 t 中，字段 a 和 b 都是无符号的列，且有一行 a =1,b=2。

mysql> select * from t;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set

此时如果计算 a-b 将会出错，不仅如此，只要是 unsigned 列参与计算并将得到负数都会出错。

mysql> select a-b from t;
1690 - BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - `test`.`t`.`b`)'
mysql> select a-2 from t;
1690 - BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - 2)'

如果计算结果不是负数时将没有影响。

mysql> select 2-a,a*3 from t;
+-----+-----+
| 2-a | a*3 |
+-----+-----+
|   1 |   3 |
+-----+-----+
1 row in set

这并不是 MySQL/MariaDB 中的 bug，在 C 语言中的 unsigned 也一样有类似的问题。这个问题在 MySQL/MariaDB 中设置 set sql_mode='no_unsigned_subtraction' 即可解决。

所以个人建议不要使用 unsigned 属性修饰字段。

 

1.4 数据类型属性：zerofill

zerofill 修饰字段后，不足字段显示部分将使用 0 来代替空格填充，启用 zerofill 后将自动设置 unsigned。zerofill 一般只在设置了列的显示宽度后一起使用。关于列的显示宽度在上文已经介绍过了。

mysql> create table t1(id int(4) zerofill);
mysql> select * from t1;
+-------+
| id    |
+-------+
|  0001 |
|  0002 |
|  0011 |
| 83838 |
+-------+
4 rows in set (0.00 sec)

zerofill 只是修饰显示结果，不会影响存储的数据值。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-10/148004.htm