MySQL 枚举类型如何定义比较好 tinyint?enum?varchar?

7 篇文章 0 订阅
4 篇文章 0 订阅

目录

前言

enum介绍

实践

最后的建议:


前言

 

当项目中遇到比较多的枚举字段时怎么选择MySQL的类型呢?tinyint,varchar还是enum?据我观察大家还是用tinyint的比较多,少数也会直接用varchar。

 

说到枚举,这个类型真的是有点坑,写的时候又不想校验(尤其是枚举值比较多的时候),又想直接在表里可以显示出原字符(说到底还是懒)。tinyint可以校验插入的值为自己定义的数值且索引友好但是显示不友好,varvhar可以直接在表中显示字段值但对索引又不友好。

 

两种都不是最优选择,后来MySQL出现了enum类型,可以直接显示值且内部为数字索引。本来以为这个enum可以解放我们的双手了,但我调研了一下发现还是有一些大坑的,难怪很少有人用。

 

enum介绍

 

先来介绍一下enum类型吧。

 

ENUM 是一个字符串对象,其值通常选自一个允许值列表中,该列表在表创建时的列规格说明中被明确地列举。(建表的时候写到建表语句里)

虽然表面是字符串值,但其内部是数字索引,其索引值从1开始。

注意:下标并不是从 0 开始,而 0 则具有其它的意义(空)

 

ENUM数据类型提供以下优点:

  • 节省存储空间,MySQL ENUM使用数字索引(1,2,3,…)来表示字符串值。
  • 可读查询和输出,数字将转换回查询结果中的相应字符

实践

下面我们来建个表试一下这个enum类型。

CREATE TABLE `test_enum` (

    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '主键ID',

    `color` ENUM('red','yellow','blue')  COMMENT '颜色',

    PRIMARY KEY ( `id` )

) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT = '颜色表';


这是一个有red,blue,yellow三种颜色的颜色表。如果我们插入列表中的数据,是完全没问题的。

INSERT INTO test_enum(color) VALUES ('red'), ('yellow')

这个时候看一下确实使用的是数字索引:

 

SELECT color+0 FROM test_enum

 

但如果我们插入了一个不存在enum列表中值white,会怎么样呢?

INSERT INTO test_enum(color) VALUES ('red'), ('white')

这个时候MySQL会有个报错:

1265 - Data truncated for column 'color' at row 2, Time: 0.017000s

这是因为我们的MySQL开启了严格模式。如果关掉严格模式,或者使用比较古老的版本,仅仅是发出了一个警告而已。

但你以为关闭了严格模式就真的可以插入成功了吗?

Naive!

你只是插入了一个空字符串,对此MySQL 官方还是作出了一点的解释:

「如果在 ENUM 列中插入无效值(即,允许值列表中不存在的字符串),则会插入空字符串 ( '' ) 作为特殊错误值,这个空字符串可以通过此字符串具有数字值 0 来区分 正常 的空字符串 」

翻译一下:

就是说如果往 enum 列中插入了无效的值,可以被插入,但插入的是一个特殊的空字符串,而该空字符串的数值是 0。所以正常的enum索引从1开始,0就是留给这个空字符串的。

但是0和‘0’还是不一样的

INSERT INTO test_enum(color) VALUES  (0)

这个时候会报错:
 

1265 - Data truncated for column 'color' at row 1, Time: 0.001000s

如果这样,就可以:
INSERT INTO test_enum(color) VALUES  ('0')

所以enum类型对于php等弱语言类型的支持很差,弱语言类型打引号和不打引号的值可能是同一类型,但是对于mysql中enum类型的字段来说,那就不一定是一回事了。

这里可以看出来enum类型确实对枚举值进行了一个校验,但是如果我们这个字段需要新增一种颜色而表结构忘记改了,那么我们以为插入数据成功了,其实只是插入一个空,这点真是一个值得注意的坑。

如果使用数字作为ENUM枚举常量,这种双重性很容易导致混乱,例如ENUM(‘1’,’2’,’3’)。建议尽量避免这么做。

所以超级不推荐在mysql中设置某一字段类型为enum,但是存的值为数字,比如‘0’,‘1’,‘2’;

 

另外,对于enum的排序也是个问题,其不是按枚举值字母顺序排序的,而是按数字索引排序的,也就是按你定义的顺序排序的。这点也是需要特别注意的。

SELECT color FROM test_enum order by color

 

总结一下enum主要优点:

1、数据更紧凑,节省存储空间。因为 ENUM 列一般都是有限的值,一般不多余 5 个这样,这就比保存 true 或 false 节省空间多了。因为 MySQL 会在创建或者修改表结构时将 enum 允许的值自动编码为数字,而这个数字一般的分配空间为 1~2 字节 ( byte ) ,具体取决于实现。

例如,将值为 yellow 的100万行插入表将需要 100 万字节的存储空间,而如果将实际字符串 yellow存储在 VARCHAR 列中则需要 600 万字节。


2、更好的可读性,虽然在存储的是数字,但在输入和输出时使用的都是对应的字符值。

3、如果启用了严格的SQL模式,错误值会导致警告或错误,可在一定程度上过滤掉脏数据。

但是,他的缺点却更多:

1,在MySQL语句中可使用ENUM的索引值,也可以使用字符串,容易误用,尤其对于数字型枚举值。

2,更改enum枚举成员需要使用ALTER TABLE语句重建整个表,大部分情况下会进行全表扫描;对于一些列未来可能会改变的字符串,使用枚举不是一个好主意,除非能接受只在列表末尾添加元素。

3,如果启用了严格的 SQL 模式 ( sql_mode ) ,尝试插入无效的 ENUM 值会导致错误。如果关闭严格模式插入的数据仍然是错误的。

4,字符枚举值排序是按定义顺序排的而不是按字母顺序排的,如果想按字母顺序需要 ORDER BY CONCAT(col)。

5,ENUM类型不是SQL标准,属于MySQL,而其他DBMS不一定有原生的支持。

6,枚举值不能是表达式,即使是计算字符串值的表达式也是如此。

 

看了这么多缺点,我还是决定放弃使用enum了。

那么情况下可以使用enum呢?

1,你的enum值是固定不变的,比如扑克牌的花色等。

2,enum的值数量大于2个并少于10个。(我觉得超过10个就很难管理与使用了)

3,这个表不需要存储额外的关联信息。比如扑克牌花色想关联黑色和红色那么就很难了。

 

最后的建议:

  1. 非常不建议使用ENUM存数字,如果搭配弱类型语言,那简直就是给自己找麻烦。
  2. 尽量不要用这个类型,除非你非常确定你的枚举成员不会改变,或者新增成员也只是在队尾新增,还有你没有转换数据库的需求;
  3. 如果字段是字符串,并且长度固定,建议用char类型;如果不固定且没有搜索要求用varchar也没啥问题。
  4. 如果是数值型,建议使用tinyint,只占1个字节,比较稳妥。就是这个字段备注要写清楚了,不然别人看表全是数字一脸懵逼。

 

 

 

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值