如何使用enum数据类型

2022-01-20 00:00:00 字段 字符串 排序 类型 顺序

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


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


1、在表中enum字段存储的实际是其顺序值(整数),而不是字符串。


2、按照enum值做查找和比较的时候,按照顺序值来查找和比较,而不是按照enum项的字符串。不过mysql在这个方面有个问题(见下文截图):与其某个enum项相同的字符串做范围比较(>, <, >=, <=)时候,并没有按照顺序值比较,而是按照字面值比较了。但是mysql在按照enum列做排序的时候,却也能够正确滴按照enum项的顺序值来排序。


3、postgresql的enum类型需要预先定义好,然后在create table的时候使用该类型。这样做的好处是多个表可以使用同一个enum类型,不必多次定义而导致不一致等问题。


4、可以对enum列定义索引,索引key排序也是按照enum顺序值排序,并且索引key也是enum顺序值(整数)。

 

下面的截图是使用昆仑分布式数据库做的示例。里面mysql的示例是使用昆仑分布式数据库的存储节点(mysql8.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的数据库设计当中不好的设计方案。这些方案应该被摒弃,切不可模仿。


1、有人直接使用字符串类型,比如varchar(N) 类型的列,来存储这样的字段值,这样的问题是,有可能上层应用的错误会输入意外的字段值,比如上例中学校类型字段,如果应用层数据处理不充分,导致插入一行Rx.type = ‘大 学’,那么这样的行Rx也完全可以插入到表中。于是当你查找 类型=‘大学’ 的行时候,就找不到Rx了。还有一个问题是空间利用率问题。在mysql和postgresql中,enum字段实际存储的是enum值的数值,通常会比存储字符串节省空间,而且在查找和比较时,数值比较也比字符串比较更快。


2、另有人会在db的表中使用数字来代表类型,然后在应用层完成数字与类型字符串的转换。比如上例学校类型中,用1代表‘幼儿园’,2代表‘小学’等,然后在应用层完整这种数字与字符串之间的转换,向终用户展示字符串,向db的对应字段中存入数字。这样做的问题是,增加了应用层开发的工作量和维护开销。设想后期需要增加更多类型值,还要修改应用层代码。而且,没有应用层代码,还完全无法理解字段值的意义(当然,可以增加注释说明),影响数据的可读性。


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


1、数据校验,拒绝非法值。


2、高效存储和计算。


3、数据可读性强,不需要依赖应用代码来解释。


4、不需要应用程序做任何数值解释和转换,降低应用软件开发和维护的成本。


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

点击阅读原文

推荐阅读

KunlunBase架构介绍
KunlunBase技术优势介绍
KunlunBase技术特点介绍
PostgreSQL vs MySQL TPC-H 测试
Kunlun-Storage vs PostgreSQL OLTP 测试


-END-

相关文章