是否有理由不在 SQL 中将布尔值存储为位数据类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/777269/
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
Are there reasons for not storing boolean values in SQL as bit data types?
提问by anopres
Are there reasons for not storing boolean values in SQL as bit data types without NULL? I see them often stored as integers without constraints to limit values to 0 and 1, and as strings with things like T/F, True/False, yes/no, etc., again without constraints. Isn't it better to store them as bits and not have to worry about additional constraints? What am I missing here?
是否有理由不在 SQL 中将布尔值存储为没有 NULL 的位数据类型？我看到它们通常存储为没有约束的整数，以将值限制为 0 和 1，并作为字符串存储，如 T/F、真/假、是/否等，同样没有约束。将它们存储为位而不用担心额外的约束不是更好吗？我在这里缺少什么？
回答by Powerlord
I'd always stick with the smallest data type I can to store this.
我总是坚持使用最小的数据类型来存储它。
- SQLServer: BIT
- Oracle: NUMBER(1) (or BOOLEAN in PL/SQL)
- MySQL: TINYINT (iirc BOOLEAN maps to this automatically)
- SQLServer：位
- Oracle：NUMBER(1)（或 PL/SQL 中的 BOOLEAN）
- MySQL：TINYINT（iirc BOOLEAN 自动映射到此）
Edit: Oracle's BOOLEAN is PL/SQL only, not table definition. Updated answer to reflect this.
编辑：Oracle 的 BOOLEAN 只是 PL/SQL，不是表定义。更新答案以反映这一点。
回答by SQLMenace
what typically happens down the road is that someone wants to add also a maybe to yes and no, if you have a bit then now you have to change all your code to tinyint
通常发生的事情是有人想在 yes 和 no 中添加一个maybe，如果你有一点那么现在你必须将所有代码更改为tinyint
if you had tinyint to begin with then you don't.....believe me this happens more than you think
如果你有 tinyint 开始，那么你不会......相信我这种情况发生的比你想象的要多
回答by TheTXI
I see them often stored as integers without constraints to limit values to 0 and 1, and as strings with things like T/F, True/False, yes/no, etc., again without constraints. Isn't it better to store them as bits and not have to worry about additional constraints?
我看到它们通常存储为没有约束的整数，以将值限制为 0 和 1，并作为字符串存储，如 T/F、真/假、是/否等，同样没有约束。将它们存储为位而不用担心额外的约束不是更好吗？
Yes!
是的！
What am I missing here?
我在这里缺少什么？
Actually it should be "what am I NOT missing here?" and the answer would be: common sense.
实际上应该是“我在这里没有遗漏什么？” 答案是：常识。
回答by HLGEM
Some reasons not to do so include:
不这样做的一些原因包括：
Not all databases have a bit datatype so you use int instead to be able to use differnt backends
并非所有数据库都有 bit 数据类型，因此您可以使用 int 来代替不同的后端
In some databases you cannot index a bit field.
在某些数据库中，您不能索引位字段。
And often what you have is not truly a true/false, yes/no with no other possibilities. For instance you might have a bit field for status meaning something like open or closed. But later you realize you need cancelled as a status as well.
通常你所拥有的并不是真正的真/假，是/否，没有其他可能性。例如，您可能有一个状态位字段，表示打开或关闭之类的内容。但后来你意识到你也需要取消作为一种状态。
回答by TStamper
BIT is the datatype normally used to store BOOLEAN values. Simply because if the BIT is 1 then its true and 0 then its false. It is that simple.
BIT 是通常用于存储 BOOLEAN 值的数据类型。仅仅是因为如果 BIT 是 1，那么它是真，0 那么它是假的。就是这么简单。
回答by rguerreiro
When I want booleans in the database I always use the bit data type. In SQL they can be NULL. But when running your program you'll have to consider that a bool (e.g. in C#) is a value type which in this case can't be NULL. You'll have to compare with the System.DBNull value.
当我想要数据库中的布尔值时，我总是使用位数据类型。在 SQL 中，它们可以为 NULL。但是在运行你的程序时，你必须考虑 bool（例如在 C# 中）是一种值类型，在这种情况下不能为 NULL。您必须与 System.DBNull 值进行比较。
回答by mattruma
We always store the data as a bit, it's small, and more importantly this is the case it is designed for.
我们总是将数据存储为一点，它很小，更重要的是，这是它的设计目的。
We have had times where the end user was going to be working with the data directly, and to them, Yes/No or Y/N was more readable. In this case, we just created a view that reflected the friendlier data display.
我们曾经有过最终用户将直接处理数据的情况，对他们来说，是/否或是/否更具可读性。在这种情况下，我们刚刚创建了一个反映更友好数据显示的视图。
回答by sarvesh
Use Enum if you have more than two statuses.
如果您有两个以上的状态，请使用 Enum。
回答by Mladen Prajdic
one reason is that people don't know about bit or think that y/n is simpler for formatting. other reason is that sometimes you think: hmm maybe over time this will be more than a bool field. and you make it int just in case.
一个原因是人们不了解 bit 或认为 y/n 更简单地格式化。另一个原因是有时你会想：嗯，也许随着时间的推移，这将不仅仅是一个布尔字段。以防万一，您将其设为 int 。
you're not missing anything :)
你没有错过任何东西:)
回答by Darren Kopp
I think third normalization form would state that you should have a table that stores the values True and False, and reference that. Make sure you do that with your dates as well!
我认为第三种规范化形式会说明您应该有一个表来存储值 True 和 False，并引用它。确保你对你的约会也这样做！
But who completely adheres to 3NF anyway? ;)
但究竟谁完全遵守 3NF？;)
