I've been reading through some of guides on database optimization and best practices and a lot of them suggest not using boolean flags at all in the DB schema (ex http://forge.mysql.com/wiki/Top10SQLPerformanceTips). However, they never provide any reason as to why this is bad. Is it a peformance issue? is it hard to index or query properly?
我已经阅读了一些关于数据库优化和最佳实践的指南，其中有很多建议根本不使用DB模式中的布尔标志(例如http://forge.mysql.com/wiki/Top10SQLPerformanceTips)。然而，他们从来没有提供任何理由来解释为什么这是坏事。这是一个性能问题吗?是否很难正确地索引或查询?
Furthermore, if boolean flags are bad, what should you use to store boolean values in a database? Is it better to store boolean flags as an integer and use a bitmask? This seems like it would be less readable.
此外，如果布尔标志不好，那么应该使用什么来在数据库中存储布尔值呢?将布尔标志存储为整数并使用位掩码是否更好?这似乎不太容易读懂。
4 个解决方案
#1
6
I don't think it is bad and I've never seen a reason stated for this either. Perhaps some old database engines couldn't store them efficiently, but modern ones do. As you say, it's a lot more readable to use booleans than bitmasks. See this question for a similar discussion: Is adding a bit mask to all tables in a database useful?
我不认为这是一件坏事，我也从未见过这样的理由。也许一些旧的数据库引擎不能有效地存储它们，但是现代的数据库引擎可以。如您所言，使用布尔值比使用位掩码更具可读性。类似的讨论请参见这个问题:向数据库中的所有表添加位掩码是否有用?
#2
5
The only reason I could think of would be cases where you should use ENUM instead. Sure, you only want true and false now, but if you'd want to add something else later than you'd need to do an ALTER TABLE operation, which could be very expensive.
我能想到的唯一原因是您应该使用ENUM的情况。当然，您现在只需要true和false，但是如果您希望在以后添加其他内容，而不需要执行ALTER TABLE操作，这可能会非常昂贵。
#3
1
My guess: portability of your design.
我的猜想:你设计的便携性。
e.g.
如。
-
Microsoft Access treats boolean as -1 as true or 0 as false while other databases may treat boolean differently.
Microsoft Access将布尔值视为true，将0视为false，而其他数据库可能对布尔值有不同的处理。
-
In MySQL (version 4+) on the other hand, value of zero is considered false. Non-zero values are considered true.
另一方面，在MySQL(版本4+)中，0的值被认为是假的。非零值被认为是正确的。
#4
0
Granted database practice has little to do with theory, I'll still attempt theoretical explanation. Tables are finite relations. Each relation is an extension of predicate. A Boolean attribute is a misnomer for a predicate.
假设数据库实践与理论几乎没有关系，我仍将尝试理论解释。表是有限的关系。每个关系都是谓词的扩展。布尔属性是谓词的误用。
#1
6
I don't think it is bad and I've never seen a reason stated for this either. Perhaps some old database engines couldn't store them efficiently, but modern ones do. As you say, it's a lot more readable to use booleans than bitmasks. See this question for a similar discussion: Is adding a bit mask to all tables in a database useful?
我不认为这是一件坏事，我也从未见过这样的理由。也许一些旧的数据库引擎不能有效地存储它们，但是现代的数据库引擎可以。如您所言，使用布尔值比使用位掩码更具可读性。类似的讨论请参见这个问题:向数据库中的所有表添加位掩码是否有用?
#2
5
The only reason I could think of would be cases where you should use ENUM instead. Sure, you only want true and false now, but if you'd want to add something else later than you'd need to do an ALTER TABLE operation, which could be very expensive.
我能想到的唯一原因是您应该使用ENUM的情况。当然，您现在只需要true和false，但是如果您希望在以后添加其他内容，而不需要执行ALTER TABLE操作，这可能会非常昂贵。
#3
1
My guess: portability of your design.
我的猜想:你设计的便携性。
e.g.
如。
-
Microsoft Access treats boolean as -1 as true or 0 as false while other databases may treat boolean differently.
Microsoft Access将布尔值视为true，将0视为false，而其他数据库可能对布尔值有不同的处理。
-
In MySQL (version 4+) on the other hand, value of zero is considered false. Non-zero values are considered true.
另一方面，在MySQL(版本4+)中，0的值被认为是假的。非零值被认为是正确的。
#4
0
Granted database practice has little to do with theory, I'll still attempt theoretical explanation. Tables are finite relations. Each relation is an extension of predicate. A Boolean attribute is a misnomer for a predicate.
假设数据库实践与理论几乎没有关系，我仍将尝试理论解释。表是有限的关系。每个关系都是谓词的扩展。布尔属性是谓词的误用。