数据类型

Klustron大约 4 分钟

在做数据库设计时，我们经常需要给很多表增加一个‘类型’ 字段，比如人的性别有‘男’， ‘女’，学校的类型有 ‘幼儿园’，‘小学’，‘中学’，‘大学’，汽车的类型有‘轿车’，‘suv’，‘mpv’ 等，这种字段的本质特点是它们包含 “有限个离散值”。

对于这种字段，使用 enum 数据类型是最合适的，mysql 和 PostgreSQL 都有此数据类型。enum 类型有这么一些特点：

在表中 enum 字段存储的实际是其顺序值（整数），而不是字符串。
按照 enum 值做查找和比较的时候，按照顺序值来查找和比较，而不是按照 enum 项的字符串。不过 mysql 在这个方面有个问题（见下文截图）：与其某个 enum 项相同的字符串做范围比较（>, <, >=, <=)时候，并没有按照顺序值比较，而是按照字面值比较了。但是 mysql 在按照 enum 列做排序的时候，却也能够正确滴按照 enum 项的顺序值来排序。
postgresql 的 enum 类型需要预先定义好，然后在 create table 的时候使用该类型。这样做的好处是多个表可以使用同一个 enum 类型，不必多次定义而导致不一致等问题。
可以对 enum 列定义索引，索引 key 排序也是按照 enum 顺序值排序，并且索引 key 也是 enum 顺序值（整数）。

下面的截图是使用 KunlunBase 做的示例。里面 mysql 的示例是使用 KunlunBase 的存储节点（ mysql 8.0.15 ）做的。

创建使用 enum 数据类型的表，然后插入数据。

对 enum 类型的列做范围查找和等值查找，顺序值决定 enum 项的大小关系。

按照 enum 类型的列排序时候，会按照其顺序值而不是字面字符串做排序。查询元数据表可以看到 enum 类型的元数据。

mysql 的enum用法：可以做等值查找，但是范围查找没有按照顺序值而是按照 enum 项的字面字符串来比较的，这样做其实是错误的。

mysql中 enum 列的定义，按照 enum 项出现的顺序给每个 enum 项赋予顺序值，从 0 开始。

按照 enum 列排序时，是按照 enum 项的顺序值来排序的。从元数据字典可以看到‘职级’这个 enum 类型的 enum 项的顺序值。

插入更多行后，再次排序，仍然是按照 enum 列的顺序值而不是字面字符串来排序的。

mysql 对 enum 列也是按照顺序值排序，但是与 enum 字符串做大小比较时候无法聪明滴按照顺序值来比较。

在举例说明 enum 的用法之后，我列举一下不使用 enum 的数据库设计当中不好的设计方案。这些方案应该被摒弃，切不可模仿。

有人直接使用字符串类型，比如 varchar(N) 类型的列，来存储这样的字段值，这样的问题是，有可能上层应用的错误会输入意外的字段值，比如上例中学校类型字段，如果应用层数据处理不充分，导致插入一行 Rx.type = ‘大学’，那么这样的行 Rx 也完全可以插入到表中。于是当你查找类型=‘大学’ 的行时候，就找不到 Rx 了。还有一个问题是空间利用率问题。在 mysql 和 postgresql 中，enum 字段实际存储的是 enum 值的数值，通常会比存储字符串节省空间，而且在查找和比较时，数值比较也比字符串比较更快。
另有人会在 db 的表中使用数字来代表类型，然后在应用层完成数字与类型字符串的转换。比如上例学校类型中，用1代表‘幼儿园’，2代表‘小学’等，然后在应用层完整这种数字与字符串之间的转换，向最终用户展示字符串，向 db 的对应字段中存入数字。这样做的问题是，增加了应用层开发的工作量和维护开销。设想后期需要增加更多类型值，还要修改应用层代码。而且，没有应用层代码，还完全无法理解字段值的意义（当然，可以增加注释说明），影响数据的可读性。

从这里可以看出 enum 类型的几个优点：

所以，强烈建议数据库设计和应用系统设计的时候，适当的时候使用 enum 类型。

# 数据类型