MySQL入门教程：ENUM和SET类型

MySQL入门教程：ENUM和SET类型，ENUM和SET类型是两种特殊的字符串类型，它们有很多相似之处，使用方法也是分类系，通常都从一个在表创建时明确列举的允许值的一张表中选择，其主要的区别是ENUM列必须是值集合中的一个成员，而SET列可以包括其中的任意成员。

例如，创建如下两个串列：

color ENUM(“red”,”black”,”green”,”yellow”)

property SET(“car”,”house”,”stock”) NOT NULL

那么color和property可能的值分别为：

color：NULL、”red”、”black”、”green”和”yellow”

而property可能的值就复杂的多：

“”

“car”

“house”

“car,house”

“stock”

“car,stock”

“house,stock”

“car,house,stock”

由于空串可以表示不具备值的集合的任何一个值，所以这也是一个合法的SET值。

ENUM类型可以有65536个成员，而SET类型最多可以有64个成员。

ENUM和SET类型是如何存储的

ENUM和SET类型在数据库内部并不是用字符的方式存储的，而是使用一系列的数字，因此更为高效。

ENUM和SET类型的合法值列表的原则为：

此列表决定了列的可能合法值。

可按任意的大小写字符插入 ENUM 或 SET 值，但是列定义中指定的串的大小写字符决定了以后检索它们时的大小写。

在 ENUM 定义中的值顺序就是排序顺序。SET 定义中的值顺序也决定了排序顺序，但是这个关系更为复杂，因为列值可能包括多个集合成员。

SET 定义中的值顺序决定了在显示由多个集合成员组成的 SET 列值时，子串出现的顺序。

对于ENUM列类型，成员是从 1 开始顺序编号的。(0 被 MySQL 用作错误成员，如果以串的形式表示就是空串。)枚举值的数目决定了 ENUM 列的存储大小。一个字节可表示 256 个值，两个字节可表示 65 536 个值。因此，枚举成员的最大数目为 65 536(包括错误成员)，并且存储大小依赖于成员数目是否多于 256 个。在 ENUM 定义中，可以最多指定 65 535(而不是 65 536)个成员，因为 MySQL 保留了一个错误成员，它是每个枚举的隐含成员。在将一个非法值赋给 ENUM 列时，MySQL 自动将其换成错误成员。

对于SET类型，SET 列的集合成员不是顺序编号的，而是每个成员对应 SET 值中的一个二进制位。第一个集合成员对应于 0 位，第二个成员对应于 1 位，如此等等。数值 SET 值 0 对应于空串。SET 成员以位值保存。每个字节的 8 个集合值可按此方式存放，因此 SET 列的存储大小是由集合成员的数目决定的，最多 64 个成员。对于大小为 1 到 8、9 到 16、17 到 24、25 到 32、33 到 64 个成员的集合，其 SET 值分别占用 1、2、3、4 或 8 个字节。

例如，还是上面的例子，我们从一个表中检索出ENUM和SET列的值，及其对应的数值：

对于ENUM类型的color列

SELECT color,color+0 from my_table

其结果为：

+--------+---------+

| color  | color+0 |

+--------+---------+

| NULL   | NULL   |

| black  |    2 |

| green  |    3 |

| yellow  |    4 |

| red   |    1 |

| red   |    1 |

| green  |     3 |

| green  |    3 |

| yellow |     4 |

+--------+---------+

对于SET类型的property列，同样的

SELECT property,property+0 FROM my_table;

其结果为

+-----------------+------------+

| property     | property+0 |

+-----------------+------------+

|                  0 |

| house,stock   |      6 |

| car,stock    |      5 |

| stock      |      4 |

| car,house,stock |      7 |

| car,house    |      3 |

| house      |      2 |

| car,stock      |      5 |

| house,stock   |      6 |

+-----------------+------------+

你可以仔细了解它们之间的对应关系。

因此，在给列赋值、检索时，你不仅可以使用值表中的字符串，也可以使用数值来表示一个值，例如下列语句是等价的：

INSERT my_table SET property=’car,house,stock’

INSERT my_table SET property=7

对于ENUM列也同样如此：

INSERT my_table SET color=’red’;

INSERT my_table SET color=1