数据库记录属性的整数与字符
假设我有一张包含房地产列表的表格。每个列表都可以是“出售”或“出租”。因此，我可以将“出售”映射到 0，将“出租”映射到 1，并将其作为 INT 存储在数据库中。但是，如果我将其存储为 CHAR 类型的字段中的“销售”/“租赁”，则会更具描述性。或者我可以将 0 和 1 映射到程序中的两个常量 FOR_SALE 和 FOR_RENT。或者使用字符“S”和“R”。在一个此类属性的选项总数非常小的情况下，将此类属性存储在数据库中的最佳实践是什么？
Say I have a table with real estate listings. Every listing can be either 'For sale' or 'For rent'. Therefore, I can map 'For sale' to 0, 'For rent' to 1 and store it as an INT in the database. However, it would be much more descriptive if I store it as 'sale' / 'rent' in a field of type CHAR. Or I can map 0 and 1 to two constants FOR_SALE and FOR_RENT in my program. Or use chars 'S' and 'R'. What are the best practices for storing such properties in a database with a condition that the total number of options for one such property is very small.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我会将列表中的属性存储为 int 并将其作为查找表的外键，您可以在其中添加描述。
I'd store the attribute in the listing as an int and make it a foreign key to a lookup table where you can add your descriptions.
您应该使用 char(1) 或 int （取决于选项的数量）并将值映射到常量字符串，这样您将节省空间，并且将来可以轻松配置字符串:)
You should use a char(1) or an int (depending upon the number of options) and map the values to constant strings, this way you'll save space and the strings will be easily configurable in the future :)
PostgreSQL 和 MySQL 支持 枚举类型，这就是您听起来正在寻找的类型。枚举类型的唯一问题是数据库可移植性。例如，Oracle 没有枚举类型，因此您必须使用 CHAR。因此，如果您选择使用 PostgreSQL，那么没有理由不利用它的功能。如果需要数据库可移植性，使用 CHAR(1) 或 NUMBER(1) 是最有效的。
更新：您可以使用带有外键的查找表，正如其他响应提到的那样，但对于不会更改的布尔值，这会引入不必要的复杂性。特别是当您考虑必须在 ORM 中为它们创建额外的类时。但是，如果您预计该列/变量的值范围会发生变化，那么使用查找表是最好的方法。
PostgreSQL and MySQL support enumerated types, which is what you sound like you're looking for. Only problem with enumerated types is database portability. Oracle for example, does not have enumerated types so you have to use CHAR instead. So if you're set on PostgreSQL for example, there is no reason to not tap into its features. If you need database portability, using CHAR(1) or NUMBER(1) is most efficient.
Update: You can use a lookup table with a foreign key as other responses have mentioned but with boolean values that will not change, this would be introducing unnecessary complexity. Especially when you consider having to create additional classes for them in your ORM. If however, you are anticipating range of values for that column / variable to change, using lookup table is the best way to go.
我只是使用
char(1)来完成如此简单的事情；我还会对其施加CHECK约束（如果可用），以提供某种健全性检查措施。即使它是正确的，为只有两个值的东西添加一个额外的表也是毫无意义的。此外，当您手动调试或修改数据库时，该列中的S或R将为您提供帮助，1或6几乎毫无意义。当然，如果您有值并且无法想出合理的助记符来表示它们，那么“int 和 FK”方法更有意义。
如果有必要，您可以很容易地从
char(1)更改为“int 和 FK”。I'd just use a
char(1)for such a simple thing; I'd also slap aCHECKconstraint on it (if available) to give some measure of sanity checking. Adding an extra table for something with only two values is a bit pointless even if it is pendantically correct. Also, anSorRin the column will help you when you're debugging or mucking around in the database by hand, a1or6will be pretty much meaningless.Of course, if you have values and can't come up with sensible mnemonic characters to represent them, then the "int and an FK" approach makes more sense.
You can change from
char(1)to "int and an FK" quite easily if it ever becomes necessary.