Postgresql 枚举有什么优点和缺点？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2318123/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql enum what are the advantages and disadvantages?
提问by Jasper Floor
Where I work we use a postgres database (8.3 soon to migrate to 8.4). There is a small debate currently on the use of enums in the database. Personally I do not like the db enum type. Among other things it puts application logic in the database and creates a posibility for a mismatch between code and data.
在我工作的地方，我们使用 postgres 数据库（8.3 很快就会迁移到 8.4）。目前有一个关于在数据库中使用枚举的小辩论。我个人不喜欢 db 枚举类型。除其他外，它将应用程序逻辑放在数据库中，并为代码和数据之间的不匹配创造了可能性。
I was wondering what exactly are the advantages of postgres enums (besides readablity) and what are the disadvatages?
我想知道 postgres 枚举的优点到底是什么（除了可读性）以及缺点是什么？
回答by Mark
The advantages of enums are:
枚举的优点是：
- Performance is better. You can just display what you get out of the core table instead of either having a separate lookup table that translates a code to a value or having app logic that translates a code to a value. This can be especially useful in datawarehouse applications.
- Ad hoc SQL is easier to write
- 性能更好。您可以只显示从核心表中得到的内容，而不是使用将代码转换为值的单独查找表或将代码转换为值的应用程序逻辑。这在数据仓库应用程序中特别有用。
- Ad hoc SQL 更容易编写
The disadvantages are:
缺点是：
- Encoding display values into your database ddl is bad form. If you translate the enum value into a different display value in your app code, then you lost a lot of the advantages of using enums.
- Adding values requires DDL changes
- Makes language localization difficult
- Database portability is decreased
- 将显示值编码到您的数据库 ddl 中是错误的形式。如果您在应用程序代码中将枚举值转换为不同的显示值，那么您就失去了使用枚举的许多优势。
- 添加值需要更改 DDL
- 使语言本地化变得困难
- 数据库可移植性降低
回答by Denise
Enums combine the advantages of ints with the advantages of strings: they are small and fast like ints, readable like strings, and have the additional advantage of being safe (you can't mis-spell an enum).
枚举结合了整数的优点和字符串的优点：它们像整数一样小而快速，像字符串一样可读，并且具有安全的额外优势（你不能拼错枚举）。
However, if you don't care about readability, an int is as good as an enum.
但是，如果您不关心可读性，则 int 与枚举一样好。
回答by Grigory Kislin
As advantage you have also DB checking, that nothing else enum value couldn't be recorded in column. The big disadvantage for me was, that enum could be modified only by adding value to the end, but since Postgres 9.1 it is in the past: https://stackoverflow.com/a/7834949/548473
作为优势，您还进行了数据库检查，列中无法记录任何其他枚举值。对我来说最大的缺点是，只能通过在最后添加值来修改枚举，但从 Postgres 9.1 开始，它已经过去了：https: //stackoverflow.com/a/7834949/548473
回答by Simon Clinch
Point is, if applications are allowed to do DDL, they are more likely to cause blocking or conflict. DDL is best done offline i.e. in single-user mode.
重点是，如果允许应用程序做 DDL，它们更容易造成阻塞或冲突。DDL 最好离线完成，即在单用户模式下。
