MySQL的常用数据类型包括：Number/Date/String，而String类型中又包含了Char/Varchar/Binary/blob/text等长度不同的简单数据类型，有时我们需要对数据做更细致的管理，比如枚举和集合，就需要复合类型ENUM和SET了。
ENUM 枚举类型
ENUM适合于只能在一组固定值中选一个的场景，比如性别只能为男或者女。
ENUM的优势在于：
只能在固定值中选择，可以在数据库层面限制非法值。
数据的存储用数字来存储，占用空间少。
但是它的使用有很多需要我们注意的地方，一不小心你就会得到错误的结果。
使用ENUM枚举类型
mysql> create table test (name varchar(40), sex enum('male', 'female') );
mysql> insert into test (name, sex) values('a', 'male'), ('b', 'female'), ('c', 'male');
mysql> select * from test;
+------+--------+
| name | sex    |
+------+--------+
| a    | male   |
| b    | female |
| c    | male   |
+------+--------+
3 rows in set (0.00 sec)
创建枚举类型时，我们使用关键字enum，同时跟着一组可枚举值列表，这些可枚举值必须使用字符串的格式，否则会报错。如果插入值的大小写不匹配，会自动转换成枚举值。
ENUM类型数据存储的实际值是索引值
我们所有枚举值都是按照枚举值列表中的索引值进行存储的，如上面的ENUM('male', 'female')的sex字段所有值为：
字面值
存储值
NULL
NULL
”
0
‘male’
1
‘female’
2
因此如果有1000条记录都存储为male，我们可能认为数据库存储了4000个字符，其实只存储了1000个1字符。而在查询的时候又会将这个编码过的数字转为实际的值。
我们可以用两个例子测试下：
mysql> select * from test where sex=1;
+------+------+
| name | sex  |
+------+------+
| a    | male |
| c    | male |
+------+------+
2 rows in set (0.00 sec)
mysql> select name, sex+0 from test;
+------+-------+
| name | sex+0 |
+------+-------+
| a    |     1 |
| b    |     2 |
| c    |     1 |
+------+-------+
3 rows in set (0.00 sec)
这种存储和查询的方式会导致一些处理数字的函数，也会使用存储的值来进行计算，如SUM()和AVG()：
mysql> select name, avg(sex) from test;
+------+--------------------+
| name | avg(sex)           |
+------+--------------------+
| a    | 1.3333333333333333 |
+------+--------------------+
1 row in set (0.00 sec)
读写时不要使用数字
由于上面介绍的用索引值存储的特性，我们不要用枚举类型来存储数字格式的列，否则会引起很大的混淆，如：
mysql> create table test2 (numbers enum('0', '1', '2'));
Query OK, 0 rows affected (0.04 sec)
# 此时2被当做索引值，因此是'1'；'2'就是'2'；'3'因为不是合法值，会用索引值尝试，因此是'2'
mysql> insert into test2 (numbers) values (2), ('2'), ('3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from test2;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+
3 rows in set (0.00 sec)
枚举类型的默认值
即便一列被设定为枚举类型，但依然有额外两种值为合法值：NULL和''。
当我们插入一个非法值时，在宽松模式下，会插入一个普通的空字符''，其值为0。而在严格模式下会报错。
当该字段设定为允许为空时，NULL字段可以被正常插入。当不允许为空时，如果你不填值，会使用默认值：枚举值的第一个，如上面的male。
除了设置为严格模式，否则没有合适的办法让一列数据必须插入合法枚举值。使用默认值很多情况下不能满足需求。
枚举类型的排序
常规使用order by进行排序时，会按照字母的文本顺序。但枚举类型由于存储为索引值，因此会按照索引值进行排序：NULL < '' = 0 < 1 < 2。
如果希望按照文本类型进行排序，可以使用：
order by cast(col as char)
或者
order by concat(col)
枚举值声明的限制
创建数据类型时，枚举值不允许为表达式，如：
mysql> create table test (name varchar(40), sex enum('male', concat('fem', 'ale') );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'concat('fem', 'ale') )' at line 1
枚举值数量的限制
枚举值用1-2个字节来存储，因此上限值为2^16-1=65535。
SET集合类型
SET和ENUM类型非常相似，它适合于只能在一组固定值中选零个或多个的场景，比如一个人喜欢的颜色可以为红、黄、蓝等颜色中的一个或多个，也可以都不喜欢。
SET的优势和ENUM也相似，在于：
只能在固定值中选择，可以在数据库层面限制非法值。
数据的存储用数字来存储，占用空间少。但在枚举值数量很多，而枚举值字符数少时这一可能不成立。
使用SET枚举类型
mysql> create table test2 (name varchar(40), color set('red', 'green', 'blue', 'yellow'));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test2(name,color) values ('a', 'red'), ('b', 'red,green'), ('c', 'green,blue,yellow');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from test2;
+------+-------------------+
| name | color             |
+------+-------------------+
| a    | red               |
| b    | red,green         |
| c    | green,blue,yellow |
+------+-------------------+
3 rows in set (0.00 sec)
创建时，我们使用关键字set，同时跟着一组可枚举值列表，这些可枚举值必须使用字符串的格式，否则会报错。
SET类型数据存储的实际值是索引值的和
我们所有枚举值都是按照列表中的索引值进行存储的，不同的是通过设置二进制数为1的位置，即2的幂次方。如上面的SET('red', 'blue', 'green', 'yellow')的color字段所有值为：
枚举值
二进制值
十进制数字
red
0001
1
green
0010
2
blue
0100
4
yellow
1000
8
而当有多个值时，通过所有值的求和得到存储的值。因此存储的数据量变少了，当取出的时候编码过的数字又会被转义成实际的字符串。
我们可以用两个例子测试下：
mysql> select name,color+0 from test2;
+------+---------+
| name | color+0 |
+------+---------+
| a    |       1 |
| b    |       3 |
| c    |      14 |
+------+---------+
3 rows in set (0.00 sec)
mysql> select name,color from test2 where color=14;
+------+-------------------+
| name | color             |
+------+-------------------+
| c    | green,blue,yellow |
+------+-------------------+
1 row in set (0.00 sec)
这种存储和查询的方式会导致一些处理数字的函数，也会使用存储的值来进行计算，如SUM()和AVG()：
mysql> select avg(color) from test2;
+------------+
| avg(color) |
+------------+
|          6 |
+------------+
1 row in set (0.00 sec)
插入时的顺序和次数
当插入值时，set类型不关注你插入的顺序和一个枚举值的插入次数，它会自动去重并进行求和得到值，等到取出时，会按照声明的顺序返回：
mysql> insert into test2(name,color) values ('d', 'yellow,green,red,yellow');
Query OK, 1 row affected (0.00 sec)
mysql> select name,color from test2;
+------+-------------------+
| name | color             |
+------+-------------------+
| d    | red,green,yellow  |
+------+-------------------+
4 rows in set (0.00 sec)
查找集合值
由于set类型的特殊性，因此有专用的查找函数：
mysql> select * from test2 where find_in_set('red', color);
+------+------------------+
| name | color            |
+------+------------------+
| a    | red              |
| b    | red,green        |
| d    | red,green,yellow |
+------+------------------+
3 rows in set (0.00 sec)
# 这一种方法当出现lightred颜色的时候就无法正确工作了
mysql> select * from test2 where color like '%red%';
+------+------------------+
| name | color            |
+------+------------------+
| a    | red              |
| b    | red,green        |
| d    | red,green,yellow |
+------+------------------+
3 rows in set (0.00 sec)
集合值的计算方式是位运算
前面说是对枚举值去重并自动求和只是为了方便理解，实际上是进行位运算，得到最终的值，如0001 + 0100 = 0101。
因此我们也可以用类似的方法来查找值：
mysql> select name,color from test2 where color & 10;
+------+-------------------+
| name | color             |
+------+-------------------+
| b    | red,green         |
| c    | green,blue,yellow |
| d    | red,green,yellow  |
+------+-------------------+
3 rows in set (0.00 sec)
mysql> select name,color from test2 where color & 12;
+------+-------------------+
| name | color             |
+------+-------------------+
| c    | green,blue,yellow |
| d    | red,green,yellow  |
+------+-------------------+
2 rows in set (0.00 sec)
上面的这个&符号什么含义我没查到，但我猜测这个&符号的含义就是位运算，当两个数在一个位置都为1时返回true，如果没有一个位置两者都为1则为false。
具体的可以计算下：a,b,c,d分别为0001,0011,1110,1011，此时10为1100，12为1010，可以计算的到上面的结果。其他数字的结果也都符合，所以应该符合我的猜测。
枚举类型的排序
常规使用order by进行排序时，会按照字母的文本顺序。但枚举类型由于存储为索引值，因此会按照索引值进行排序：NULL < 0 < 1 < 2。
如果希望按照文本类型进行排序，可以使用：
order by cast(col as char)
或者
order by concat(col)
枚举值数量的限制
枚举值用1-8个字节来存储，因此上限值为8*8=64个。
参考资料
作者：赵帅强

MySQL的常用数据类型包括：Number/Date/String，而String类型中又包含了Char/Varchar/Binary/blob/text等长度不同的简单数据类型，有时我们需要对数据做更细致的管理，比如枚举和集合，就需要复合类型ENUM和SET了。
ENUM 枚举类型
ENUM适合于只能在一组固定值中选一个的场景，比如性别只能为男或者女。
ENUM的优势在于：只能在固定值中选择，可以在数据库层面限制非法值。
数据的存储用数字来存储，占用空间少。
但是它的使用有很多需要我们注意的地方，一不小心你就会得到错误的结果。
使用ENUM枚举类型mysql> create table test (name varchar(40), sex enum('male', 'female') );
mysql> insert into test (name, sex) values('a', 'male'), ('b', 'female'), ('c', 'male');
mysql> select * from test;
+------+--------+
| name | sex    |
+------+--------+
| a    | male   |
| b    | female |
| c    | male   |
+------+--------+
3 rows in set (0.00 sec)
创建枚举类型时，我们使用关键字enum，同时跟着一组可枚举值列表，这些可枚举值必须使用字符串的格式，否则会报错。如果插入值的大小写不匹配，会自动转换成枚举值。
ENUM类型数据存储的实际值是索引值
我们所有枚举值都是按照枚举值列表中的索引值进行存储的，如上面的ENUM('male', 'female')的sex字段所有值为：字面值存储值
NULLNULL
''0
'male'1
'female'2
因此如果有1000条记录都存储为male，我们可能认为数据库存储了4000个字符，其实只存储了1000个1字符。而在查询的时候又会将这个编码过的数字转为实际的值。
我们可以用两个例子测试下：mysql> select * from test where sex=1;
+------+------+
| name | sex  |
+------+------+
| a    | male |
| c    | male |
+------+------+
2 rows in set (0.00 sec)
mysql> select name, sex+0 from test;
+------+-------+
| name | sex+0 |
+------+-------+
| a    |     1 |
| b    |     2 |
| c    |     1 |
+------+-------+
3 rows in set (0.00 sec)
这种存储和查询的方式会导致一些处理数字的函数，也会使用存储的值来进行计算，如SUM()和AVG()：mysql> select name, avg(sex) from test;
+------+--------------------+
| name | avg(sex)           |
+------+--------------------+
| a    | 1.3333333333333333 |
+------+--------------------+
1 row in set (0.00 sec)
读写时不要使用数字
由于上面介绍的用索引值存储的特性，我们不要用枚举类型来存储数字格式的列，否则会引起很大的混淆，如：mysql> create table test2 (numbers enum('0', '1', '2'));
Query OK, 0 rows affected (0.04 sec)
# 此时2被当做索引值，因此是'1'；'2'就是'2'；'3'因为不是合法值，会用索引值尝试，因此是'2'
mysql> insert into test2 (numbers) values (2), ('2'), ('3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from test2;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+
3 rows in set (0.00 sec)
枚举类型的默认值
即便一列被设定为枚举类型，但依然有额外两种值为合法值：NULL和''。
当我们插入一个非法值时，在宽松模式下，会插入一个普通的空字符''，其值为0。而在严格模式下会报错。
当该字段设定为允许为空时，NULL字段可以被正常插入。当不允许为空时，如果你不填值，会使用默认值：枚举值的第一个，如上面的male。除了设置为严格模式，否则没有合适的办法让一列数据必须插入合法枚举值。使用默认值很多情况下不能满足需求。
枚举类型的排序
常规使用order by进行排序时，会按照字母的文本顺序。但枚举类型由于存储为索引值，因此会按照索引值进行排序：NULL < '' = 0 < 1 < 2。
如果希望按照文本类型进行排序，可以使用：order by cast(col as char)
或者
order by concat(col)
枚举值声明的限制
创建数据类型时，枚举值不允许为表达式，如：mysql> create table test (name varchar(40), sex enum('male', concat('fem', 'ale') );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'concat('fem', 'ale') )' at line 1
枚举值数量的限制
枚举值用1-2个字节来存储，因此上限值为2^16-1=65535。
SET集合类型
SET和ENUM类型非常相似，它适合于只能在一组固定值中选零个或多个的场景，比如一个人喜欢的颜色可以为红、黄、蓝等颜色中的一个或多个，也可以都不喜欢。
SET的优势和ENUM也相似，在于：只能在固定值中选择，可以在数据库层面限制非法值。
数据的存储用数字来存储，占用空间少。但在枚举值数量很多，而枚举值字符数少时这一可能不成立。
使用SET枚举类型mysql> create table test2 (name varchar(40), color set('red', 'green', 'blue', 'yellow'));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test2(name,color) values ('a', 'red'), ('b', 'red,green'), ('c', 'green,blue,yellow');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from test2;
+------+-------------------+
| name | color             |
+------+-------------------+
| a    | red               |
| b    | red,green         |
| c    | green,blue,yellow |
+------+-------------------+
3 rows in set (0.00 sec)
创建时，我们使用关键字set，同时跟着一组可枚举值列表，这些可枚举值必须使用字符串的格式，否则会报错。
SET类型数据存储的实际值是索引值的和
我们所有枚举值都是按照列表中的索引值进行存储的，不同的是通过设置二进制数为1的位置，即2的幂次方。如上面的SET('red', 'blue', 'green', 'yellow')的color字段所有值为：枚举值二进制值十进制数字
red00011
green00102
blue01004
yellow10008
而当有多个值时，通过所有值的求和得到存储的值。因此存储的数据量变少了，当取出的时候编码过的数字又会被转义成实际的字符串。
我们可以用两个例子测试下：mysql> select name,color+0 from test2;
+------+---------+
| name | color+0 |
+------+---------+
| a    |       1 |
| b    |       3 |
| c    |      14 |
+------+---------+
3 rows in set (0.00 sec)
mysql> select name,color from test2 where color=14;
+------+-------------------+
| name | color             |
+------+-------------------+
| c    | green,blue,yellow |
+------+-------------------+
1 row in set (0.00 sec)
这种存储和查询的方式会导致一些处理数字的函数，也会使用存储的值来进行计算，如SUM()和AVG()：mysql> select avg(color) from test2;
+------------+
| avg(color) |
+------------+
|          6 |
+------------+
1 row in set (0.00 sec)
插入时的顺序和次数
当插入值时，set类型不关注你插入的顺序和一个枚举值的插入次数，它会自动去重并进行求和得到值，等到取出时，会按照声明的顺序返回：mysql> insert into test2(name,color) values ('d', 'yellow,green,red,yellow');
Query OK, 1 row affected (0.00 sec)
mysql> select name,color from test2;
+------+-------------------+
| name | color             |
+------+-------------------+
| d    | red,green,yellow  |
+------+-------------------+
4 rows in set (0.00 sec)
查找集合值
由于set类型的特殊性，因此有专用的查找函数：mysql> select * from test2 where find_in_set('red', color);
+------+------------------+
| name | color            |
+------+------------------+
| a    | red              |
| b    | red,green        |
| d    | red,green,yellow |
+------+------------------+
3 rows in set (0.00 sec)
# 这一种方法当出现lightred颜色的时候就无法正确工作了
mysql> select * from test2 where color like '%red%';
+------+------------------+
| name | color            |
+------+------------------+
| a    | red              |
| b    | red,green        |
| d    | red,green,yellow |
+------+------------------+
3 rows in set (0.00 sec)
集合值的计算方式是位运算
前面说是对枚举值去重并自动求和只是为了方便理解，实际上是进行位运算，得到最终的值，如0001 + 0100 = 0101。
因此我们也可以用类似的方法来查找值：mysql> select name,color from test2 where color & 10;
+------+-------------------+
| name | color             |
+------+-------------------+
| b    | red,green         |
| c    | green,blue,yellow |
| d    | red,green,yellow  |
+------+-------------------+
3 rows in set (0.00 sec)
mysql> select name,color from test2 where color & 12;
+------+-------------------+
| name | color             |
+------+-------------------+
| c    | green,blue,yellow |
| d    | red,green,yellow  |
+------+-------------------+
2 rows in set (0.00 sec)
上面的这个&符号什么含义我没查到，但我猜测这个&符号的含义就是位运算，当两个数在一个位置都为1时返回true，如果没有一个位置两者都为1则为false。
具体的可以计算下：a,b,c,d分别为0001,0011,1110,1011，此时10为1010，12为1100，当& 10时，也就是只需要第1位或第3位存在1时(为真)就算匹配成功，从第1位到第4位(高位到低位)分别是yellow,blue,green,red，所以这个语句类似于select name,color from test2 where find_in_set('yellow', color) or find_in_set('green', color);
可以计算的到上面的结果，其他数字的结果也都符合。
枚举类型的排序
常规使用order by进行排序时，会按照字母的文本顺序。但枚举类型由于存储为索引值，因此会按照索引值进行排序：NULL < 0 < 1 < 2。
如果希望按照文本类型进行排序，可以使用：order by cast(col as char)
或者
order by concat(col)
枚举值数量的限制
枚举值用1-8个字节来存储，因此上限值为8*8=64个。
参考资料

Number/Date/String
，而String
类型中又包含了Char/Varchar/Binary/blob/text
等长度不同的简单数据类型，有时我们需要对数据做更细致的管理，比如枚举和集合，就需要复合类型ENUM
和SET
了。
ENUM
枚举类型
ENUM
适合于只能在一组固定值中选一个的场景，比如性别只能为男或者女。
ENUM
的优势在于：
只能在固定值中选择，可以在数据库层面限制非法值。
数据的存储用数字来存储，占用空间少。
但是它的使用有很多需要我们注意的地方，一不小心你就会得到错误的结果。
使用ENUM
枚举类型
mysql> create table test (name varchar(40), sex enum('male', 'female') );
mysql> insert into test (name, sex) values('a', 'male'), ('b', 'female'), ('c', 'male');
mysql> select * from test;
+------+--------+
| name | sex    |
+------+--------+
| a    | male   |
| b    | female |
| c    | male   |
+------+--------+
3 rows in set (0.00 sec)
创建枚举类型时，我们使用关键字enum
，同时跟着一组可枚举值列表，这些可枚举值必须使用字符串的格式，否则会报错。如果插入值的大小写不匹配，会自动转换成枚举值。
ENUM
类型数据存储的实际值是索引值
我们所有枚举值都是按照枚举值列表中的索引值进行存储的，如上面的ENUM('male', 'female')
的sex
字段所有值为：
字面值
存储值
NULL
NULL
”
0
‘male’
1
‘female’
2
因此如果有1000
条记录都存储为male
，我们可能认为数据库存储了4000
个字符，其实只存储了1000
个1
字符。而在查询的时候又会将这个编码过的数字转为实际的值。
我们可以用两个例子测试下：
mysql> select * from test where sex=1;
+------+------+
| name | sex  |
+------+------+
| a    | male |
| c    | male |
+------+------+
2 rows in set (0.00 sec)
mysql> select name, sex+0 from test;
+------+-------+
| name | sex+0 |
+------+-------+
| a    |     1 |
| b    |     2 |
| c    |     1 |
+------+-------+
3 rows in set (0.00 sec)
这种存储和查询的方式会导致一些处理数字的函数，也会使用存储的值来进行计算，如SUM()
和AVG()
：
mysql> select name, avg(sex) from test;
+------+--------------------+
| name | avg(sex)           |
+------+--------------------+
| a    | 1.3333333333333333 |
+------+--------------------+
1 row in set (0.00 sec)
读写时不要使用数字
由于上面介绍的用索引值存储的特性，我们不要用枚举类型来存储数字格式的列，否则会引起很大的混淆，如：
mysql> create table test2 (numbers enum('0', '1', '2'));
Query OK, 0 rows affected (0.04 sec)
# 此时2被当做索引值，因此是'1'；'2'就是'2'；'3'因为不是合法值，会用索引值尝试，因此是'2'
mysql> insert into test2 (numbers) values (2), ('2'), ('3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from test2;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+
3 rows in set (0.00 sec)
枚举类型的默认值
即便一列被设定为枚举类型，但依然有额外两种值为合法值：NULL
和''
。
当我们插入一个非法值时，在宽松模式下，会插入一个普通的空字符''
，其值为0
。而在严格模式下会报错。
当该字段设定为允许为空时，NULL
字段可以被正常插入。当不允许为空时，如果你不填值，会使用默认值：枚举值的第一个，如上面的male
。
除了设置为严格模式，否则没有合适的办法让一列数据必须插入合法枚举值。使用默认值很多情况下不能满足需求。
枚举类型的排序
常规使用order by
进行排序时，会按照字母的文本顺序。但枚举类型由于存储为索引值，因此会按照索引值进行排序：NULL < '' = 0 < 1 < 2
。
如果希望按照文本类型进行排序，可以使用：
order by cast(col as char)
或者
order by concat(col)
枚举值声明的限制
创建数据类型时，枚举值不允许为表达式，如：
mysql> create table test (name varchar(40), sex enum('male', concat('fem', 'ale') );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'concat('fem', 'ale') )' at line 1
枚举值数量的限制
枚举值用1-2
个字节来存储，因此上限值为2^16-1=65535
。
SET
集合类型
SET
和ENUM
类型非常相似，它适合于只能在一组固定值中选零个或多个的场景，比如一个人喜欢的颜色可以为红、黄、蓝等颜色中的一个或多个，也可以都不喜欢。
SET
的优势和ENUM
也相似，在于：
只能在固定值中选择，可以在数据库层面限制非法值。
数据的存储用数字来存储，占用空间少。但在枚举值数量很多，而枚举值字符数少时这一可能不成立。
使用SET
枚举类型
mysql> create table test2 (name varchar(40), color set('red', 'green', 'blue', 'yellow'));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test2(name,color) values ('a', 'red'), ('b', 'red,green'), ('c', 'green,blue,yellow');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from test2;
+------+-------------------+
| name | color             |
+------+-------------------+
| a    | red               |
| b    | red,green         |
| c    | green,blue,yellow |
+------+-------------------+
3 rows in set (0.00 sec)
创建时，我们使用关键字set
，同时跟着一组可枚举值列表，这些可枚举值必须使用字符串的格式，否则会报错。
SET
类型数据存储的实际值是索引值的和
我们所有枚举值都是按照列表中的索引值进行存储的，不同的是通过设置二进制数为1
的位置，即2
的幂次方。如上面的SET('red', 'blue', 'green', 'yellow')
的color
字段所有值为：
枚举值
二进制值
十进制数字
red
0001
1
blue
0010
2
green
0100
4
yellow
1000
8
而当有多个值时，通过所有值的求和得到存储的值。因此存储的数据量变少了，当取出的时候编码过的数字又会被转义成实际的字符串。
我们可以用两个例子测试下：
mysql> select name,color+0 from test2;
+------+---------+
| name | color+0 |
+------+---------+
| a    |       1 |
| b    |       3 |
| c    |      14 |
+------+---------+
3 rows in set (0.00 sec)
mysql> select name,color from test2 where color=14;
+------+-------------------+
| name | color             |
+------+-------------------+
| c    | green,blue,yellow |
+------+-------------------+
1 row in set (0.00 sec)
这种存储和查询的方式会导致一些处理数字的函数，也会使用存储的值来进行计算，如SUM()
和AVG()
：
mysql> select avg(color) from test2;
+------------+
| avg(color) |
+------------+
|          6 |
+------------+
1 row in set (0.00 sec)
插入时的顺序和次数
当插入值时，set
类型不关注你插入的顺序和一个枚举值的插入次数，它会自动去重并进行求和得到值，等到取出时，会按照声明的顺序返回：
mysql> insert into test2(name,color) values ('d', 'yellow,green,red,yellow');
Query OK, 1 row affected (0.00 sec)
mysql> select name,color from test2;
+------+-------------------+
| name | color             |
+------+-------------------+
| d    | red,green,yellow  |
+------+-------------------+
4 rows in set (0.00 sec)
查找集合值
由于set
类型的特殊性，因此有专用的查找函数：
mysql> select * from test2 where find_in_set('red', color);
+------+------------------+
| name | color            |
+------+------------------+
| a    | red              |
| b    | red,green        |
| d    | red,green,yellow |
+------+------------------+
3 rows in set (0.00 sec)
# 这一种方法当出现lightred颜色的时候就无法正确工作了
mysql> select * from test2 where color like '%red%';
+------+------------------+
| name | color            |
+------+------------------+
| a    | red              |
| b    | red,green        |
| d    | red,green,yellow |
+------+------------------+
3 rows in set (0.00 sec)
集合值的计算方式是位运算
前面说是对枚举值去重并自动求和只是为了方便理解，实际上是进行位运算，得到最终的值，如0001 + 0100 = 0101
。
因此我们也可以用类似的方法来查找值：
mysql> select name,color from test2 where color & 10;
+------+-------------------+
| name | color             |
+------+-------------------+
| b    | red,green         |
| c    | green,blue,yellow |
| d    | red,green,yellow  |
+------+-------------------+
3 rows in set (0.00 sec)
mysql> select name,color from test2 where color & 12;
+------+-------------------+
| name | color             |
+------+-------------------+
| c    | green,blue,yellow |
| d    | red,green,yellow  |
+------+-------------------+
2 rows in set (0.00 sec)
上面的这个&
符号什么含义我没查到，但我猜测这个&
符号的含义就是位运算，当两个数在一个位置都为1
时返回true
，如果没有一个位置两者都为1
则为false
。
具体的可以计算下：a,b,c,d
分别为0001,0011,1110,1011
，此时10
为1100
，12
为1010
，可以计算的到上面的结果。其他数字的结果也都符合，所以应该符合我的猜测。
枚举类型的排序
常规使用order by
进行排序时，会按照字母的文本顺序。但枚举类型由于存储为索引值，因此会按照索引值进行排序：NULL < 0 < 1 < 2
。
如果希望按照文本类型进行排序，可以使用：
order by cast(col as char)
或者
order by concat(col)
枚举值数量的限制
枚举值用1-8
个字节来存储，因此上限值为8*8=64
个。
参考资料
注意：本文来自SegmentFault博客。本站无法对本文内容的真实性、完整性、及时性、原创性提供任何保证，请您自行验证核实并承担相关的风险与后果！
CoLaBug.com遵循[CC BY-SA 4.0]分享并保持客观立场，本站不承担此类作品侵权行为的直接责任及连带责任。您有版权、意见、投诉等问题，请通过[eMail]联系我们处理，如需商业授权请联系原作者/原网站。

MySQL的常用数据类型包括：Number/Date/String，而String类型中又包含了Char/Varchar/Binary/blob/text等长度不同的简单数据类型，有时我们需要对数据做更细致的管理，比如枚举和集合，就需要复合类型ENUM和SET了。
ENUM 枚举类型
ENUM适合于只能在一组固定值中选一个的场景，比如性别只能为男或者女。
ENUM的优势在于：只能在固定值中选择，可以在数据库层面限制非法值。
数据的存储用数字来存储，占用空间少。
但是它的使用有很多需要我们注意的地方，一不小心你就会得到错误的结果。
使用ENUM枚举类型mysql> create table test (name varchar(40), sex enum('male', 'female') );
mysql> insert into test (name, sex) values('a', 'male'), ('b', 'female'), ('c', 'male');
mysql> select * from test;
+------+--------+
| name | sex    |
+------+--------+
| a    | male   |
| b    | female |
| c    | male   |
+------+--------+
3 rows in set (0.00 sec)
创建枚举类型时，我们使用关键字enum，同时跟着一组可枚举值列表，这些可枚举值必须使用字符串的格式，否则会报错。如果插入值的大小写不匹配，会自动转换成枚举值。
ENUM类型数据存储的实际值是索引值
我们所有枚举值都是按照枚举值列表中的索引值进行存储的，如上面的ENUM('male', 'female')的sex字段所有值为：字面值存储值
NULLNULL
''0
'male'1
'female'2
因此如果有1000条记录都存储为male，我们可能认为数据库存储了4000个字符，其实只存储了1000个1字符。而在查询的时候又会将这个编码过的数字转为实际的值。
我们可以用两个例子测试下：mysql> select * from test where sex=1;
+------+------+
| name | sex  |
+------+------+
| a    | male |
| c    | male |
+------+------+
2 rows in set (0.00 sec)
mysql> select name, sex+0 from test;
+------+-------+
| name | sex+0 |
+------+-------+
| a    |     1 |
| b    |     2 |
| c    |     1 |
+------+-------+
3 rows in set (0.00 sec)
这种存储和查询的方式会导致一些处理数字的函数，也会使用存储的值来进行计算，如SUM()和AVG()：mysql> select name, avg(sex) from test;
+------+--------------------+
| name | avg(sex)           |
+------+--------------------+
| a    | 1.3333333333333333 |
+------+--------------------+
1 row in set (0.00 sec)
读写时不要使用数字
由于上面介绍的用索引值存储的特性，我们不要用枚举类型来存储数字格式的列，否则会引起很大的混淆，如：mysql> create table test2 (numbers enum('0', '1', '2'));
Query OK, 0 rows affected (0.04 sec)
# 此时2被当做索引值，因此是'1'；'2'就是'2'；'3'因为不是合法值，会用索引值尝试，因此是'2'
mysql> insert into test2 (numbers) values (2), ('2'), ('3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from test2;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+
3 rows in set (0.00 sec)
枚举类型的默认值
即便一列被设定为枚举类型，但依然有额外两种值为合法值：NULL和''。
当我们插入一个非法值时，在宽松模式下，会插入一个普通的空字符''，其值为0。而在严格模式下会报错。
当该字段设定为允许为空时，NULL字段可以被正常插入。当不允许为空时，如果你不填值，会使用默认值：枚举值的第一个，如上面的male。除了设置为严格模式，否则没有合适的办法让一列数据必须插入合法枚举值。使用默认值很多情况下不能满足需求。
枚举类型的排序
常规使用order by进行排序时，会按照字母的文本顺序。但枚举类型由于存储为索引值，因此会按照索引值进行排序：NULL < '' = 0 < 1 < 2。
如果希望按照文本类型进行排序，可以使用：order by cast(col as char)
或者
order by concat(col)
枚举值声明的限制
创建数据类型时，枚举值不允许为表达式，如：mysql> create table test (name varchar(40), sex enum('male', concat('fem', 'ale') );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'concat('fem', 'ale') )' at line 1
枚举值数量的限制
枚举值用1-2个字节来存储，因此上限值为2^16-1=65535。
SET集合类型
SET和ENUM类型非常相似，它适合于只能在一组固定值中选零个或多个的场景，比如一个人喜欢的颜色可以为红、黄、蓝等颜色中的一个或多个，也可以都不喜欢。
SET的优势和ENUM也相似，在于：只能在固定值中选择，可以在数据库层面限制非法值。
数据的存储用数字来存储，占用空间少。但在枚举值数量很多，而枚举值字符数少时这一可能不成立。
使用SET枚举类型mysql> create table test2 (name varchar(40), color set('red', 'green', 'blue', 'yellow'));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test2(name,color) values ('a', 'red'), ('b', 'red,green'), ('c', 'green,blue,yellow');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from test2;
+------+-------------------+
| name | color             |
+------+-------------------+
| a    | red               |
| b    | red,green         |
| c    | green,blue,yellow |
+------+-------------------+
3 rows in set (0.00 sec)
创建时，我们使用关键字set，同时跟着一组可枚举值列表，这些可枚举值必须使用字符串的格式，否则会报错。
SET类型数据存储的实际值是索引值的和
我们所有枚举值都是按照列表中的索引值进行存储的，不同的是通过设置二进制数为1的位置，即2的幂次方。如上面的SET('red', 'blue', 'green', 'yellow')的color字段所有值为：枚举值二进制值十进制数字
red00011
green00102
blue01004
yellow10008
而当有多个值时，通过所有值的求和得到存储的值。因此存储的数据量变少了，当取出的时候编码过的数字又会被转义成实际的字符串。
我们可以用两个例子测试下：mysql> select name,color+0 from test2;
+------+---------+
| name | color+0 |
+------+---------+
| a    |       1 |
| b    |       3 |
| c    |      14 |
+------+---------+
3 rows in set (0.00 sec)
mysql> select name,color from test2 where color=14;
+------+-------------------+
| name | color             |
+------+-------------------+
| c    | green,blue,yellow |
+------+-------------------+
1 row in set (0.00 sec)
这种存储和查询的方式会导致一些处理数字的函数，也会使用存储的值来进行计算，如SUM()和AVG()：mysql> select avg(color) from test2;
+------------+
| avg(color) |
+------------+
|          6 |
+------------+
1 row in set (0.00 sec)
插入时的顺序和次数
当插入值时，set类型不关注你插入的顺序和一个枚举值的插入次数，它会自动去重并进行求和得到值，等到取出时，会按照声明的顺序返回：mysql> insert into test2(name,color) values ('d', 'yellow,green,red,yellow');
Query OK, 1 row affected (0.00 sec)
mysql> select name,color from test2;
+------+-------------------+
| name | color             |
+------+-------------------+
| d    | red,green,yellow  |
+------+-------------------+
4 rows in set (0.00 sec)
查找集合值
由于set类型的特殊性，因此有专用的查找函数：mysql> select * from test2 where find_in_set('red', color);
+------+------------------+
| name | color            |
+------+------------------+
| a    | red              |
| b    | red,green        |
| d    | red,green,yellow |
+------+------------------+
3 rows in set (0.00 sec)
# 这一种方法当出现lightred颜色的时候就无法正确工作了
mysql> select * from test2 where color like '%red%';
+------+------------------+
| name | color            |
+------+------------------+
| a    | red              |
| b    | red,green        |
| d    | red,green,yellow |
+------+------------------+
3 rows in set (0.00 sec)
集合值的计算方式是位运算
前面说是对枚举值去重并自动求和只是为了方便理解，实际上是进行位运算，得到最终的值，如0001 + 0100 = 0101。
因此我们也可以用类似的方法来查找值：mysql> select name,color from test2 where color & 10;
+------+-------------------+
| name | color             |
+------+-------------------+
| b    | red,green         |
| c    | green,blue,yellow |
| d    | red,green,yellow  |
+------+-------------------+
3 rows in set (0.00 sec)
mysql> select name,color from test2 where color & 12;
+------+-------------------+
| name | color             |
+------+-------------------+
| c    | green,blue,yellow |
| d    | red,green,yellow  |
+------+-------------------+
2 rows in set (0.00 sec)
上面的这个&符号什么含义我没查到，但我猜测这个&符号的含义就是位运算，当两个数在一个位置都为1时返回true，如果没有一个位置两者都为1则为false。
具体的可以计算下：a,b,c,d分别为0001,0011,1110,1011，此时10为1010，12为1100，当& 10时，也就是只需要第1位或第3位存在1时(为真)就算匹配成功，从第1位到第4位(高位到低位)分别是yellow,blue,green,red，所以这个语句类似于select name,color from test2 where find_in_set('yellow', color) or find_in_set('green', color);
可以计算的到上面的结果，其他数字的结果也都符合。
枚举类型的排序
常规使用order by进行排序时，会按照字母的文本顺序。但枚举类型由于存储为索引值，因此会按照索引值进行排序：NULL < 0 < 1 < 2。
如果希望按照文本类型进行排序，可以使用：order by cast(col as char)
或者
order by concat(col)
枚举值数量的限制
枚举值用1-8个字节来存储，因此上限值为8*8=64个。
参考资料