MySQL 枚举类型的选择

MySQL中的枚举字段, 该用什么类型定义呢?

问题简述

在项目数据库表设计阶段, 我们常常会用到枚举字段

例如 性别, 状态等数量有限在业务有特殊含义的字段

在建表时常用的字段有如下三种:

  • tinyint
  • varchar
  • enum

当然也有创建Reference table利用外键来管理枚举类型, 本节暂不讨论

其中 tinyint用的最多, varchar其次

enum由于一些比较严重的缺陷所以用的最少

下面我们将简述三种类型各自的特点以及应用场景

类型特点分析

TINYINT

特点

  1. 容量小：tinyint 只占用 1 字节的存储空间，可以存储范围为 -128 到 127 的整数值。因此，在数量有限的枚举字段中，使用 tinyint 可以有效地节省存储空间。
  2. 整数表示：tinyint 存储的是整数值，而不是直接存储含义明确的字符或字符串。这样可以在查询和索引时更高效地处理，因为比较整数值的速度通常比比较字符或字符串值的速度更快

优点：

  1. 节省存储空间：相比于字符串类型的枚举，使用 tinyint 类型可以节省存储空间，特别是在包含大量枚举字段的表中，可以显著减少数据占用。
  2. 查询和索引性能较好：由于 tinyint 存储的是整数值，所以在查询和索引操作时相对高效，特别是在大量数据情境下，可以提高查询性能。
  3. 易于扩展和维护：如果需要添加新的枚举值，只需要在 tinyint 数据类型的范围内增加合适的值即可，无需对表结构进行更改。

缺点：

  1. 不直观：tinyint 类型存储的是整数值，而不是直接存储有意义的字符或字符串，所以在直接展示给终端用户或报表时，可能需要进行额外的处理来转换为易于理解的含义。
  2. 不支持自助描述：相比于字符串类型的枚举，tinyint 类型无法自己描述枚举值的含义，需要在应用层或文档中加以说明。

应用场景：

  1. 状态字段：当业务需求包含有限且离散的状态集合时，如订单状态、用户状态等，可以使用 tinyint 类型作为状态枚举字段。
  2. 类型字段：当业务需要分类不同类型的实体对象时，比如商品类型、车辆类型等，可以使用 tinyint 类型作为类型枚举字段。
  3. 标志字段：当需要表示开关状态或异或逻辑的选项时，如布尔值字段 (0/1) 或开启/关闭等，可以使用 tinyint 类型作为标志枚举字段。

综上所述，将 tinyint 作为枚举类型在节省存储空间、提高查询性能方面具有优势，适用于数量有限、离散且不需要复杂描述的枚举值情况。但需要注意，在展示给用户时可能需要进行转换和解释，以保证易于理解和使用。

VARCHAR

特点

  1. 灵活性：VARCHAR 类型可以存储可变长度的字符数据，因此可以灵活地存储不同长度的枚举值，适用于枚举值长度不固定的情况。
  2. 直观性：VARCHAR 类型直接存储有意义的字符或字符串枚举值，便于直接展示给用户或在报表中使用。

优点：

  1. 易读易懂：直接存储有意义的字符或字符串枚举值，便于人类直接理解，无需进行额外转换。
  2. 支持自助描述：在数据库层面就包含了枚举值的描述，无需特殊处理，减少了在应用层对枚举值进行解释的成本。

缺点：

  1. 存储消耗：相比较于使用整数类型的枚举，使用 VARCHAR 类型会消耗更多的存储空间，特别在包含大量枚举字段的表中，可能对存储资源造成压力。

应用场景：

  1. 描述性字段：适用于需要直接存储描述性枚举值的场景，如文本分类、产品类型等。
  2. 数据稀疏场景：当枚举值数量较多、但实际使用频率不高时，使用 VARCHAR 类型可以避免预分配大量整数值，节省存储空间。

总的来说，将 VARCHAR 类型用作枚举类型适用于需要直接存储有意义的描述性枚举值，同时要考虑存储消耗的问题。通常适用于枚举值数量有限、较为离散且需要直接展示给用户的情况。

TINYINT 和 VARCHAR 性能比较:

Mysql使用varchar或者tinyint表示枚举性能差异 - Narcissu5 - 博客园 (cnblogs.com)

ENUM

特点

  1. 有限取值：ENUM 类型允许您为列指定一组可能的值。这意味着您可以限制该列的值只能是 ENUM 列表中定义的值的其中之一。
  2. 内部存储：在数据库内部，ENUM 类型的数据存储为整数，并且会分别对应枚举列表中定义的值，这样可以有效地节省存储空间。

优点：

  1. 数据完整性：使用 ENUM 类型可以确保存储在列中的值只会是事先定义好的枚举列表中的其中一个值，确保了数据的完整性和一致性。
  2. 节省存储空间：由于 ENUM 类型内部以整数形式存储枚举值，可以节省存储空间，特别在包含大量枚举字段的表中，能够有效减少存储空间的占用。
  3. 直观性：相比于整数类型的枚举，ENUM 类型直接在数据库中存储有意义的描述性枚举值，便于直接展示和理解。

缺点：

  1. 扩展困难：如果需要添加新的枚举值，修改 ENUM 类型需要对表结构进行更改，这可能涉及到对数据库结构的修改和数据的迁移，相比于整数类型的枚举，扩展性较差。
  2. 跨数据库兼容性：ENUM 类型在不同数据库系统之间的兼容性可能有所不同，因此在不同数据库系统之间迁移时需要格外留意。

其中在MySQL中更是不建议, 除上述几点外, 还有如下几个MySql自身实现的原因:

​ MySQL, ENUM使用数字索引(1，2，3，…)来表示字符串值(注意：下标并不是从 0 开始，而 0 则具有其它的意义（空）), 如果使用数字作为ENUM枚举常量，这种双重性很容易导致混乱，例如ENUM(‘1’，’2’，’3’), 尽量避免这么做。

​ MySQL中非严格模式下, ENUM 列中插入无效的值，是允许的，但插入的是一个特殊的空字符串，而该空字符串的索引是 0。所以正常的enum索引从1开始，0就是留给这个空字符串的。

​

应用场景：

  1. 固定取值：ENUM 类型适用于具有固定的、不经常改变的取值范围的字段，如性别、婚姻状况等。
  2. 状态字段：适用于表示固定状态的字段，如订单状态、用户状态等。

总结

综上, 关于MySql中枚举值, 建表时类型选择我们给出以下建议:

  1. 避免使用ENUM类型存储数字：在 MySQL 中，尽量避免使用 ENUM 类型存储数字类型的枚举值，特别是在与弱类型语言搭配使用时，可能会引起问题。如果需要存储数字类型的枚举值，建议考虑使用整数类型，如 tinyint，以提高稳定性和易读性。
  2. 考虑枚举成员的稳定性和维护性：在选择枚举类型时，需要慎重思考枚举成员是否稳定以及变更的可能性。如果枚举成员会经常变动，应该避免使用 ENUM 类型，以免频繁的数据库结构变更带来麻烦。
  3. 对于固定的字符串枚举成员：如果枚举成员是固定的字符串，可以考虑使用 char 类型，特别是当长度固定时，char 类型能有效节省存储空间。
  4. 长度可变的字符串枚举成员：如果枚举成员长度不固定，可以考虑使用 varchar 类型。这种类型能够灵活存储不同长度的枚举值，适用于长度不固定且无需索引的场景。
  5. 数值型的枚举成员：对于数值型的枚举成员，建议使用 tinyint 类型，占用较小的存储空间且具备较高的稳定性。