使用短字符串作为枚举而不是整数的缺点？
我的数据库中有许多类似枚举的字段。只是一个整数，它是另一个表的 FK，该表可能包含显示名称或可能包含一些附加数据。问题是，这个数字毫无意义，并且在代码中使用起来不太好。另外，如果您尝试合并来自不同数据库的一些枚举，或者移动数据，或者如果它是自动增量......则可能会遇到一些名称/键冲突......
仅使用短字符串有什么缺点吗？有点像常数？并使用它作为主键？ int 通常是 4 个字节，我想我真的不需要超过 10 个字符，所以 varchar(10) 就可以了......我不怀疑磁盘使用真的是一个问题。
I've got many enum-like fields in my database. Just an integer that's a FK to another table that might contain a display name or maybe a little additional data. Problem is, the number is pretty meaningless and not very nice to work with in the code. Also, you might run into some name/key clashing if you try to merge some of these enums from different databases, or move the data around, or if it's an autoincrement...
Are there any downsides of just using a short string? Kind of like a constant? And use that as the primary key instead? An int is usually 4 bytes, I'm thinking I don't really need more than 10 chars, so a varchar(10) would do... I don't suspect disk usage would really be an issue.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我个人的方法是对需要更改代码才能添加或删除的项目使用文本键。如果用户应该能够添加或删除给定的项目，那么我会坚持使用代理键。这允许您的代码使用更易于阅读的文本值。一个例子可能是“状态”（例如开/关、已获取/待处理/已售出）。总会有一些代码根据状态而有不同的行为，因此，该代码需要查找特定的状态值。这些“状态”不是任何人都可以简单地添加或删除甚至重命名的东西。代码依赖于它们。因此，对于这些，我使用字符串 PK。但是，诸如“类别”之类的内容，用户应该能够随意添加或删除这些类别，在这种情况下，代理键（与适当的业务键相结合）会更好。
My personal approach is to use text keys for items that would require changing code to add or remove. If a given item is something that a user should be able to add or remove, then I would stick with a surrogate key. This allows for your code to use text values which is far easier to read. An example might be "states" (e.g. On/Off, Acquired/Pending/Sold). Invariably there will be code that acts differently depending on the state and thus, that code needs look for specific state values. Those "states" are not something that anyone can simply add or remove or even rename. Code is dependent on them. Thus, for these, I use a string PK. However, something like say "categories" a user should be able to add or remove those at will and in those cases a surrogate key (combined with the appropriate business key) is better.
这取决于密钥的不可变程度。
例如，如果我们谈论使用邮政编码作为键链接到美国各州的表，则没有什么坏处。例如，任何人都不太可能将加利福尼亚州的邮政编码从 CA 更改为其他名称，因此将其存储为密钥是完全合理的。
另一方面，诸如订单状态代码之类的内容可能会随着时间的推移而发生变化。当您第一次构建应用程序时，您可能有一些状态（即“已采取”、“已发货”、“已完成”）。但随着时间的推移，企业可能会决定需要添加新状态并需要修改现有状态 - 例如，他们可能希望采用“已发货”状态，并将其分解为“OnPallet”、“InTransit”和“海关”。如果发生这种情况，并且您依赖“Shipped”作为键，则您需要对
Order表中具有“Shipped”键的所有行进行大量更新，或者您最终会得到一堆具有不太有意义的键的订单。另一方面，如果您使用无意义的数字键，则只需更新Order_Status表即可插入新值并更新一个“已发货”行。It depends on how immutable the key is.
If we're talking about linking to a table of US states using a postal code as a key, for example, there is no harm. It is exceedingly unlikely that anyone would ever change California's postal code from CA to something else, for example, so storing that as a key would be perfectly reasonable.
On the other hand, something like an Order Status Code is likely to change over time. When you first build the application, you may have a handful of statuses (i.e. "Taken", "Shipped", "Complete"). But then over time, the business may decide that new statuses need to be added and existing statuses need to be modified-- they may want to take the "Shipped" status, for example, and decompose that into "OnPallet", "InTransit", and "InCustoms". If that happens and you're relying on "Shipped" being a key, you'll have a bunch of updates to do to all the rows in the
Ordertable that have a "Shipped" key or you would end up with a bunch of orders that have a less meaningful key. If you use a meaningless numeric key, on the other hand, you would merely have to update theOrder_Statustable to insert the new values and to update the one "Shipped" row.