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

当前第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只是修饰显示结果，不会影响存储的数据值。

以上这篇(MariaDB)MySQL数据类型和存储机制全面讲解就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持。

更多相关Mysql内容来自木庄网络博客

标签：Mysql

返回前面的内容