这是 MySQL 枚举数据类型的适当使用吗？
我最近开始利用业余时间进行 PHP + MySQL 自由开发，以补充我编写 C#/SQL Server 代码的全职工作的收入。我注意到的与数据库相关的重大差异之一是 MySQL 有枚举数据类型，而 SQL Server 没有。
当我注意到枚举数据类型时，我立即决定扁平化我的数据模型，转而使用一个使用枚举的大表，而不是用于离散实体的许多较小的表和一个大的“桥”类表。
我目前正在开发的网站是一家唱片公司的网站。我只有一张表来存储该标签的版本，即“版本”表。我通常在单独的表中使用外键的地方都使用了枚举——艺术家姓名、标签名称等。用户可以通过后端编辑这些枚举列。我认为与使用文本字段相比，枚举的主要优点是艺术家姓名将被重复使用，这应该会提高数据完整性。我还发现数据库中表较少的优势。
顺便说一句，我仍然有一个额外的表和一个桥接表 - 有一个“标签”功能可以将标签添加到特定版本，并且由于这是多对多关系，因此我感觉有一个离散的标签表和一个将标签连接到版本的桥接表是合适的。
以前从未在数据库中遇到过 ENUM 数据类型，我想知道我是否明智地使用了此功能，或者是否存在我没有预见到的问题可能会再次困扰我这种数据架构的结果。有经验的MySQL用户，您怎么看？
I have recently started doing freelance PHP + MySQL development in my free time, to supplement my income from a full-time job where I write C#/SQL Server code. One of the big database-related differences I've noticed is that MySQL has an enum datatype, whereas SQL Server does not.
When I noticed the enum datatype, I immediately decided to flatten my data model in favor of having a big table that makes use of enumerations rather than many smaller tables for discrete entities and one big "bridge" sort of table.
The website I'm currently working on is for a record label. I only have one table to store the releases for the label, the "releases" table. I have used enumerations everywhere I would normally use a foreign key to a separate table--Artist name, Label name, and several others. The user has the ability to edit these enumeration columns through the backend. The major advantage I see for enumerations over using a text field for this is that artist names will be reused, which should improve data integrity. I also see an advantage in having fewer tables in the database.
Incidentally, I do still have one additional table and a bridge table--there is a "Tags" feature to add tags to a particular release, and since this is a many-to-many relationship, I feel a discrete tag table and a bridge table to join tags to releases is appropriate
Having never encountered an ENUM datatype in a database before, I wonder if I am making wise use of this feature, or if there are problems I haven't foreseen that might come back to bite me as a result of this data architecture. Experienced MySQL'ers, what do you think?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
简而言之，这不是一个好的设计。外键有一个目的。
来自 ENUM 类型的文档：
您的设计不允许存储超过 65k 个不同的艺术家姓名。
您是否考虑过添加新艺术家姓名时会发生什么？我假设您正在运行 ALTER TABLE 来添加新的枚举类型？根据 类似的问题，这是一个非常昂贵的操作 。将此与仅向
artist表添加另一行的成本进行对比。如果您有多个表需要引用一位艺术家/艺术家的姓名，会发生什么情况？如何跨表重复使用枚举值？
这种方法还存在许多其他问题。我认为像这样简化数据库设计确实会对您造成伤害（外键或拥有多个表并不是一件坏事！）。
In short, this is not a good design. Foreign keys have a purpose.
From the documentation for the ENUM type:
Your design will not allow you to store more than 65k distinct artist names.
Have you considered what happens when you add a new artist name? I assume you are running an
ALTER TABLEto add new enum types? According to a similar SO question this is a very expensive operation. Contrast this with the cost of simply adding another row to theartisttable.What happens if you have more than one table that needs to refer to an artist/artist's name? How do you re-use enum values across tables?
There are many other problems with this approach as well. I think that simplifying your database design like this does you a real disservice (foreign keys or having multiple tables are not a bad thing!).
说实话——当我读到的时候我就停了下来……
如果我理解正确的话，这意味着有所有艺术家的枚举。但艺术家的列举肯定会成为一个变化点：将会有更多的艺术家。我真诚地怀疑唱片公司从未计划增加或更改艺术家名单；）
因此，在我看来，这是对枚举的错误使用。
我也不认为对于不可避免的相当平凡的用例执行 ALTER TABLE 是不合适的。 （创建/读取/更新/销毁艺术家）我没有数字来支持这个观点。
您必须将其视为什么信息是实体或实体的属性的问题：对于唱片公司来说，艺术家是实体，但媒体类型可能不是。艺术家有很多与其相关的信息（姓名、流派、奖项、网站 URL、资历...），这表明他们是一个实体，而不是另一个实体（例如发行）的属性。此外，艺术家作为系统日常使用的一部分被创建/读取/更新和销毁，进一步表明它们是实体。
实体往往有自己的桌子。现在，当您查看这些版本的媒体类型时，您必须问自己媒体类型是否有任何其他信息...如果它不仅仅是名称，您就有一个新实体。例如，如果您的系统必须跟踪媒体类型是否已过时，那么媒体类型现在有 2 个属性（名称、已过时），并且它应该是一个单独的实体。如果 Medai 类型仅具有您正在构建的范围内的名称，那么它是另一个实体的属性，并且应该只是一列，而不是表。那时我会考虑使用枚举。
I'm going to be honest - I stopped when I read...
If I understand correctly, that means there is an enumeration of all artists. But that enumeration of artists is definitely going to be a point of variation: there will be more artists. I sincerely doubt the record label never plans on increasing or changing the list of artists ;)
As such, in my opinion, that is an incorrect use of an enumeration.
I also don't think it's appropriate to perform an
ALTER TABLEfor what is inevitably a rather mundane use case. (Create/Read/Update/Destroy artist) I have no numbers to back up that opinion.You have to look at it as a question of what information is an entity or an attribute of an entity: for a record label, artists are entities, but media types may not be. Artists have lots of information associated with them (name, genre, awards, web site url, seniority...) which suggests they are an entity, not an attribute of another entity such as Release. Also, Artists are Created/Read/Updated and Destroyed as part of regular everyday use of he system, further suggesting they are entities.
Entities tend to get their own table. Now, when you look at the Media Type of these Releases, you have to ask yourself whether Media Type has any other information... if it's anything more than Name you have a new Entity. For example, if your system has to keep track of whether a media type is obsolete, now there are 2 attributes for Media Type (name, is obsolete) and it should be a separate entity. If the Medai Types only have a Name within the scope of what you're building, then it's an attribute of another entity and should only be a column, not a table. At that point I would consider using an enumeration.
我认为你不能在艺术家等领域使用枚举。这就像你限制你的应用程序增长一样。维护这个专栏真的很困难。使用 ENUM 本身并不是问题。但在以下情况下会出现问题：
I dont think you can use enumerations in fields like artists. Its like you are restricting your application from growing. It will be really hard to maintain the column. Using ENUM is not a problem its own. But will be an issue in the following situations