MySQL 拾遗
首页 死磕 Java 优质系列 Java 技术总览 Java 面试宝典 关于我

臭名昭著的 MySQL ENUM 类型 （ 下 ）

转
 2023-03-21  阅读(3)
©版权
原文作者：简单教程 原文地址：https://www.twle.cn/c/yufei/mysqlfav/mysqlfav-basic-distinct.html

使用 MySQL ENUM 作为列的数据类型有两个主要的好处，一是使得数据更紧凑进而节省空间，二是更好的可阅读性。但是，享受这些好处的同时，也要承担 ENUM 所带来的各种负面影响。

我们罗列几个主要的负面在此，并在接下来的内容中对它们做一一解释

  1. 千万不要使用数字作为枚举值，因为这样容易混淆它们的字面值和内部索引值。
  2. 在 ORDER BY 语句中使用 ENUM 更要注意
  3. 创建和使用 ENUM 数据类型的一些问题
  4. 枚举值字面量和内部索引的问题
  5. 处理枚举值字面量的一些问题
  6. ENUM 类型中的 NULL 或空值问题
  7. ENUM 类型的排序问题
  8. ENUM 类型的一些限制

创建和使用 ENUM 数据类型

如果要将某一列指定为 ENUM 类型，可以使用 ENUM 关键字，且每个枚举值都需要使用单引号 ( ' ) 引起来，例如下面的建表语句

    CREATE TABLE shirts (
        name VARCHAR(40),
        size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
    );

创建了表之后，我们就可以往表中插入值了，对于 ENUM 类型，值原则上来说必须是定义表结构所指定的枚举值之一

    INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
      ('polo shirt','small');

数据插入成功后，我们就可以使用 SQL SELECT 语句来查询数据

    SELECT name, size FROM shirts WHERE size = 'medium';

结果如下

    +---------+--------+
    | name    | size   |
    +---------+--------+
    | t-shirt | medium |
    +---------+--------+

如果要更新 ENUM 列，所传递的值也必须是定义时的枚举值之一

    UPDATE shirts SET size = 'small' WHERE size = 'large';

如果在插入和更新值时所传递的值并不在枚举值列表中，那么就会抛出上一章节中开始那部分的问题。

ENUM 枚举值字面量和内部索引

ENUM 类型中的任何一个枚举值都有一个内部的数字索引：

  1. 所以在创建表结构时指定的枚举值都会分配一个内部索引，索引的下标从 1 开始

    注意：下标并不是从 0 开始，而 0 则具有其它的意义

  2. 空字符串错误值的索引为 0，这样，我们可以直接使用 0 值来查询那些插入的或更新的无效的枚举值

        SELECT * FROM tbl_name WHERE enum_col=0;
  1. NULL 值的索引为 NULL
  2. ENUM 最多只能包含 65,535 个不同的枚举值

当然了，这里的术语 「 索引 」 指的是枚举值列表中的位置。它与表索引无关。

我们使用一个范例来解释下上面的几条规则，例如，假设某一列的类型为 ENUM('Mercury', 'Venus', 'Earth') ，那么该列存储的实际值则为

枚举值 索引
NULL NULL
''空字符串 0
'Mercury' 1
'Venus' 2
'Earth' 3

如果在数字上下文中检索 ENUM 值，则返回列值的索引。例如，我们可以像这样从 ENUM 列中检索数值

    SELECT enum_col+0 FROM tbl_name;

当在 ENUM 列上使用 SUM() 或 AVG() 等聚合函数时，因为这些函数的参数必须是一个数字，所以 MySQL 会自动使用它们的索引值作为参数。也就是说，对于需要计算的场景，都会使用内部索引。其实，真实的枚举值，只有在插入或者显示或者查询时才会用到。

ENUM 字面量的处理

在创建表结构时，MySQL 会自动删除 ENUM 枚举值的尾随空格，例如会把 'medium ' 转换成 'medium'。

检索时，MySQL 会自动将存储的内部索引转换为定义时指定的相应的 enum 枚举值字面量。

因此，需要注意的是，可以为 ENUM 列分配字符集和排序规则。对于二进制或区分大小写的排序规则，在为列分配值时会考虑使用字母顺序。

如果将数字存储到 ENUM 列中，则将该数字视为可能值的索引，并且存储的值是具有该索引的枚举成员 (当然了，这条规则对 LOAD DATA 无效，因为 LOAD DATA 会把所有的值都视为字符串 )。

如果引用了数值，即使枚举值列表中没有匹配的字符串，但它仍会被解释为索引。

因为这个原因，所以，不建议使用看起来像数字的枚举值来定义 ENUM 列，因为这很容易让人感到困惑，分不清传递(引用) 的到底是枚举值字面量还是内部索引。

例如，以下列的枚举成员的字符串值为 '0'、'1' 和 '2'，而数字索引值为 1 、2 和 3

    numbers ENUM('0','1','2')
  1. 如果我们在插入数据或者更新数据时指定存储 2 ，因为会被解释为索引值，所以实际存储的枚举值为 '1' ( 索引为 2 的值 ）。
  2. 而如果我们存储 '2' ，因为枚举值字面量 '2' 存在，所以存储的值也为 2 。
  3. 但如果我们存储 '3' ，因为枚举值字面量 '3' 并不存在，那么它就会被视为是内部索引 3 ，进而存储的实际值其实是 '2'
    mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');
    mysql> SELECT * FROM t;
    +---------+
    | numbers |
    +---------+
    | 1       |
    | 2       |
    | 2       |
    +---------+

如果要确定 ENUM 列的所有可能值，SHOW COLUMNS FROM tbl_name LIKE 'enum_col' 语句可以解析出 enum_col 列中的所有 enum 定义

ENUM 中的 NULL 或空 '' 值问题

在某些情况下，ENUM 数据类型的枚举值也可以是空字符串（ '' ）或 NULL

  1. 如果在 ENUM 列中插入无效值（即，允许值列表中不存在的字符串），则会插入空字符串 ( '' ) 作为特殊错误值，这个特殊错误值空字符串的索引为 0 ，从而与实际的 正常 的空字符串 ( 索引大于 1 ) 区分开来

    当然了，如果启用了严格的 SQL 模式 ( sql_mode ) ，尝试插入无效的 ENUM 值会导致错误

  2. 如果一个 ENUM 列添加了 NULL 约束，那么这个 ENUM 列就允许 NULL 值，且默认的值就是 NULL

  3. 如果一个 ENUM 列添加了 NOT NULL 约束，那么它的默认值就是第一个枚举值。

ENUM 枚举值的排序问题

因为 ENUM 类型存储的是枚举值的内部索引，所以 ENUM 值根据其索引号进行排序，具体显示出来，则取决于定义列是的枚举成员顺序。

例如，如果在定义列时，指定了 'b' 在 'a' 前面 ('b'，'a')，那么 'b' 的顺序就会在 'a' 之前，且空字符串在非空字符串之前排序，NULL 值在所有其他枚举值之前排序

也就是排序的顺序默认是 NULL '' 'b' 'a'

这是一个大坑啊，为了避免这个坑，为了在 ENUM 列上使用 ORDER BY 子句时防止出现意外结果，则需要做如下选择

  1. 指定 ENUM 列的排序顺序使用字母顺序表
  2. 或者使用 ORDER BY CAST (col AS CHAR) 或 ORDER BY CONCAT(col) 确保 enum 列按词法排序而不是索引编号排序

ENUM 数据类型的一些限制

  1. 枚举值不能是表达式，即使该表达式用于计算字符串值。

    例如，下面的建表语句是无效的，会执行失败，因为 CONCAT()函数不能用于构造枚举值

        CREATE TABLE sizes (
            size ENUM('small', CONCAT('med','ium'), 'large')
        );
  1. 不能使用用户变量作为枚举值。例如下面的语句也是无效的
        SET @mysize = 'medium';
        
        CREATE TABLE sizes (
            size ENUM('small', @mysize, 'large')
        );
  1. 我们强烈建议不要使用数字用作枚举值，因为它不会通过适当的 TINYINT 或 SMALLINT 类型保存在存储上。而且，如果你错误地引用 ENUM 值，很容易混淆枚举字面量和底层索引值 ( 可能不相同 )
  2. ENUM 列定义中的重复值会导致警告，如果启用了严格的 SQL 模式，则会出错

Java 面试宝典是大明哥全力打造的 Java 精品面试题，它是一份靠谱、强大、详细、经典的 Java 后端面试宝典。它不仅仅只是一道道面试题，而是一套完整的 Java 知识体系，一套你 Java 知识点的扫盲贴。

它的内容包括：

  • 大厂真题：Java 面试宝典里面的题目都是最近几年的高频的大厂面试真题。
  • 原创内容：Java 面试宝典内容全部都是大明哥原创，内容全面且通俗易懂，回答部分可以直接作为面试回答内容。
  • 持续更新：一次购买，永久有效。大明哥会持续更新 3+ 年，累计更新 1000+，宝典会不断迭代更新，保证最新、最全面。
  • 覆盖全面：本宝典累计更新 1000+，从 Java 入门到 Java 架构的高频面试题，实现 360° 全覆盖。
  • 不止面试：内容包含面试题解析、内容详解、知识扩展，它不仅仅只是一份面试题，更是一套完整的 Java 知识体系。
  • 宝典详情：https://www.yuque.com/chenssy/sike-java/xvlo920axlp7sf4k
  • 宝典总览：https://www.yuque.com/chenssy/sike-java/yogsehzntzgp4ly1
  • 宝典进展：https://www.yuque.com/chenssy/sike-java/en9ned7loo47z5aw

目前 Java 面试宝典累计更新 400+ 道，总字数 42w+。大明哥还在持续更新中，下图是大明哥在 2024-12 月份的更新情况：

想了解详情的小伙伴，扫描下面二维码加大明哥微信【daming091】咨询

同时，大明哥也整理一套目前市面最常见的热点面试题。微信搜[大明哥聊 Java]或扫描下方二维码关注大明哥的原创公众号[大明哥聊 Java] ，回复【面试题】 即可免费领取。

阅读全文
文章标签： MySQL