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，True / False，yes / no等等，再次没有约束。将它们存储为位并且不必担心额外的约束不是更好吗？我在这里想念的是什么？
11 个解决方案
#1
44
I'd always stick with the smallest data type I can to store this.
我总是坚持使用最小的数据类型来存储它。
- SQLServer: BIT
- SQLServer：BIT
- Oracle: NUMBER(1) (or BOOLEAN in PL/SQL)
- Oracle：NUMBER（1）（或PL / SQL中的BOOLEAN）
- MySQL: TINYINT (iirc BOOLEAN maps to this automatically)
- MySQL：TINYINT（iirc BOOLEAN自动映射到此）
Edit: Oracle's BOOLEAN is PL/SQL only, not table definition. Updated answer to reflect this.
编辑：Oracle的BOOLEAN只是PL / SQL，而不是表定义。更新了答案以反映这一点。
#2
12
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
通常发生的事情是有人想要添加一个也许是和否，如果你有一点，那么现在你必须将你的所有代码更改为tinyint
if you had tinyint to begin with then you don't.....believe me this happens more than you think
如果你开始使用tinyint那么你就不要......相信我，这比你想象的更多
#3
8
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，True / False，yes / no等等，再次没有约束。将它们存储为位并且不必担心额外的约束不是更好吗？
Yes!
是!
What am I missing here?
我在这里想念的是什么？
Actually it should be "what am I NOT missing here?" and the answer would be: common sense.
实际上它应该是“我在这里不缺少什么？”答案是：常识。
#4
4
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值进行比较。
#5
4
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.
我们曾经有过最终用户直接使用数据的时间，对于他们来说，是/否或Y / N更具可读性。在这种情况下，我们刚刚创建了一个反映友好数据显示的视图。
#6
4
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则为假。就这么简单。
#7
4
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
并非所有数据库都有一个数据类型，因此您可以使用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.
而且你经常拥有的并不是真正的错误，是/否，没有其他可能性。例如，您可能有一个状态的位字段意味着打开或关闭。但是后来你意识到你也需要取消作为一个状态。
#8
1
Use Enum if you have more than two statuses.
如果您有两种以上的状态，请使用Enum。
#9
0
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.
一个原因是人们不知道有点或认为y / n更容易格式化。另一个原因是，有时你会想：嗯，也许随着时间的推移，这将不仅仅是一个布尔场。为了以防万一，你把它变成int。
you're not missing anything :)
你没有遗漏任何东西:)
#10
0
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呢？ ;）
#11
-1
I use bit a lot. But sometimes I want to be able to have the ability to return false - or many values of true (like error messaging). So if I use an int instead of boolean I can doe something like:
我用了很多东西。但有时我希望能够返回false - 或许多true值（如错误消息）。所以，如果我使用int而不是boolean，我可以这样做：
0 = False 1 = Password incorrect 2 = Username does not exist. 3 = Account locked out - to many failed attempts. 4 = Account disabled.
0 =假1 =密码不正确2 =用户名不存在。 3 =帐户被锁定 - 许多失败的尝试。 4 =帐户被禁用。
And so on.
等等。
#1
44
I'd always stick with the smallest data type I can to store this.
我总是坚持使用最小的数据类型来存储它。
- SQLServer: BIT
- SQLServer：BIT
- Oracle: NUMBER(1) (or BOOLEAN in PL/SQL)
- Oracle：NUMBER（1）（或PL / SQL中的BOOLEAN）
- MySQL: TINYINT (iirc BOOLEAN maps to this automatically)
- MySQL：TINYINT（iirc BOOLEAN自动映射到此）
Edit: Oracle's BOOLEAN is PL/SQL only, not table definition. Updated answer to reflect this.
编辑：Oracle的BOOLEAN只是PL / SQL，而不是表定义。更新了答案以反映这一点。
#2
12
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
通常发生的事情是有人想要添加一个也许是和否，如果你有一点，那么现在你必须将你的所有代码更改为tinyint
if you had tinyint to begin with then you don't.....believe me this happens more than you think
如果你开始使用tinyint那么你就不要......相信我，这比你想象的更多
#3
8
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，True / False，yes / no等等，再次没有约束。将它们存储为位并且不必担心额外的约束不是更好吗？
Yes!
是!
What am I missing here?
我在这里想念的是什么？
Actually it should be "what am I NOT missing here?" and the answer would be: common sense.
实际上它应该是“我在这里不缺少什么？”答案是：常识。
#4
4
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值进行比较。
#5
4
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.
我们曾经有过最终用户直接使用数据的时间，对于他们来说，是/否或Y / N更具可读性。在这种情况下，我们刚刚创建了一个反映友好数据显示的视图。
#6
4
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则为假。就这么简单。
#7
4
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
并非所有数据库都有一个数据类型，因此您可以使用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.
而且你经常拥有的并不是真正的错误，是/否，没有其他可能性。例如，您可能有一个状态的位字段意味着打开或关闭。但是后来你意识到你也需要取消作为一个状态。
#8
1
Use Enum if you have more than two statuses.
如果您有两种以上的状态，请使用Enum。
#9
0
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.
一个原因是人们不知道有点或认为y / n更容易格式化。另一个原因是，有时你会想：嗯，也许随着时间的推移，这将不仅仅是一个布尔场。为了以防万一，你把它变成int。
you're not missing anything :)
你没有遗漏任何东西:)
#10
0
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呢？ ;）
#11
-1
I use bit a lot. But sometimes I want to be able to have the ability to return false - or many values of true (like error messaging). So if I use an int instead of boolean I can doe something like:
我用了很多东西。但有时我希望能够返回false - 或许多true值（如错误消息）。所以，如果我使用int而不是boolean，我可以这样做：
0 = False 1 = Password incorrect 2 = Username does not exist. 3 = Account locked out - to many failed attempts. 4 = Account disabled.
0 =假1 =密码不正确2 =用户名不存在。 3 =帐户被锁定 - 许多失败的尝试。 4 =帐户被禁用。
And so on.
等等。