用于存储布尔值的 MySQL 数据类型

Which MySQL data type to use for storing boolean values

提问by markus

Since MySQL doesn't seem to have any 'boolean' data type, which data type do you 'abuse' for storing true/false information in MySQL?

由于 MySQL 似乎没有任何“布尔”数据类型，那么您“滥用”哪种数据类型来在 MySQL 中存储真/假信息？

Especially in the context of writing and reading from/to a PHP script.

特别是在编写和读取 PHP 脚本的上下文中。

Over time I have used and seen several approaches:

随着时间的推移，我使用并看到了几种方法：

• tinyint, varchar fields containing the values 0/1,
• varchar fields containing the strings '0'/'1' or 'true'/'false'
• and finally enum Fields containing the two options 'true'/'false'.

• tinyint，包含值 0/1 的 varchar 字段，
• 包含字符串 '0'/'1' 或 'true'/'false' 的 varchar 字段
• 最后枚举包含两个选项“真”/“假”的字段。

None of the above seems optimal. I tend to prefer the tinyint 0/1 variant, since automatic type conversion in PHP gives me boolean values rather simply.

以上似乎都不是最佳选择。我倾向于更喜欢 tinyint 0/1 变体，因为 PHP 中的自动类型转换为我提供了相当简单的布尔值。

So which data type do you use? Is there a type designed for boolean values which I have overlooked? Do you see any advantages/disadvantages by using one type or another?

那么您使用哪种数据类型？是否有一种为我忽略的布尔值设计的类型？您是否看到使用一种或另一种类型的优点/缺点？

回答by markus

For MySQL 5.0.3 and higher, you can use BIT. The manual says:

对于 MySQL 5.0.3 及更高版本，您可以使用BIT. 手册上说：

As of MySQL 5.0.3, the BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.

从 MySQL 5.0.3 开始，BIT 数据类型用于存储位字段值。一种类型的 BIT(M) 能够存储 M 位值。M 的范围是 1 到 64。

Otherwise, according to the MySQL manual you can use bool and boolean which are at the moment aliases of tinyint(1):

否则，根据 MySQL 手册，您可以使用 bool 和 boolean，它们目前是tinyint(1) 的别名：

Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

Bool、Boolean：这些类型是TINYINT(1) 的同义词。零值被认为是错误的。非零值被认为是真的。

MySQL also states that:

MySQL 还指出：

We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.

我们打算在未来的 MySQL 版本中根据标准 SQL 实现完整的布尔类型处理。

References: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

参考资料：http: //dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

回答by Philip Morton

BOOLand BOOLEANare synonyms of TINYINT(1). Zero is false, anything else is true. More information here.

BOOL和BOOLEAN的同义词TINYINT(1)。零是false，其他任何东西都是true。更多信息在这里。

回答by R. S.

This is an elegant solution that I quite appreciate because it uses zero data bytes:

这是一个优雅的解决方案，我非常欣赏，因为它使用零数据字节：

some_flag CHAR(0) DEFAULT NULL

To set it to true, set some_flag = ''and to set it to false, set some_flag = NULL.

要将其设置为 true，请设置some_flag = ''并将其设置为 false，请设置some_flag = NULL。

Then to test for true, check if some_flag IS NOT NULL, and to test for false, check if some_flag IS NULL.

然后测试是否为真，检查是否 some_flag IS NOT NULL，并测试是否为假，检查是否 some_flag IS NULL。

(This method is described in "High Performance MySQL: Optimization, Backups, Replication, and More" by Jon Warren Lentz, Baron Schwartz and Arjen Lentz.)

（此方法在 Jon Warren Lentz、Baron Schwartz 和 Arjen Lentz 的“高性能 MySQL：优化、备份、复制等”中有所描述。）

回答by Ciaran McNulty

If you use the BOOLEAN type, this is aliased to TINYINT(1). This is best if you want to use standardised SQL and don't mind that the field could contain an out of range value (basically anything that isn't 0 will be 'true').

如果您使用 BOOLEAN 类型，则它是 TINYINT(1) 的别名。如果您想使用标准化 SQL 并且不介意该字段可能包含超出范围的值（基本上任何不为 0 的值都是“真”），这是最好的。

ENUM('False', 'True') will let you use the strings in your SQL, and MySQL will store the field internally as an integer where 'False'=0 and 'True'=1 based on the order the Enum is specified.

ENUM('False', 'True') 将让您在 SQL 中使用字符串，并且 MySQL 将在内部将该字段存储为一个整数，其中 'False'=0 和 'True'=1 基于指定 Enum 的顺序.

In MySQL 5+ you can use a BIT(1) field to indicate a 1-bit numeric type. I don't believe this actually uses any less space in the storage but again allows you to constrain the possible values to 1 or 0.

在 MySQL 5+ 中，您可以使用 BIT(1) 字段来指示 1 位数字类型。我不相信这实际上在存储中使用了更少的空间，但再次允许您将可能的值限制为 1 或 0。

All of the above will use approximately the same amount of storage, so it's best to pick the one you find easiest to work with.

上述所有内容将使用大致相同的存储量，因此最好选择您认为最容易使用的存储量。

回答by Josh

This question has been answered but I figured I'd throw in my $0.02. I often use a CHAR(0), where '' == true and NULL == false.

这个问题已得到解答，但我想我会投入 0.02 美元。我经常使用CHAR(0), where '' == true and NULL == false。

From mysql docs:

来自mysql 文档：

CHAR(0)is also quite nice when you need a column that can take only two values: A column that is defined as CHAR(0)NULLoccupies only one bit and can take only the values NULLand ''(the empty string).

CHAR(0)当您需要一个只能取两个值的列时，它也非常好：定义为的列CHAR(0)NULL仅占用一位并且只能取值NULL和''（空字符串）。

回答by Fred

I use TINYINT(1) in order to store boolean values in Mysql.

我使用 TINYINT(1) 来在 Mysql 中存储布尔值。

I don't know if there is any advantage to use this... But if i'm not wrong, mysql can store boolean (BOOL) and it store it as a tinyint(1)

我不知道使用它是否有任何优势......但如果我没有错，mysql可以存储布尔值（BOOL）并将其存储为tinyint（1）

http://dev.mysql.com/doc/refman/5.0/en/other-vendor-data-types.html

http://dev.mysql.com/doc/refman/5.0/en/other-vendor-data-types.html

回答by Thor

Bit is only advantageous over the various byte options (tinyint, enum, char(1)) if you have a lot of boolean fields. One bit field still takes up a full byte. Two bit fields fit into that same byte. Three, four,five, six, seven, eight. After which they start filling up the next byte. Ultimately the savings are so small, there are thousands of other optimizations you should focus on. Unless you're dealing with an enormous amount of data, those few bytes aren't going to add up to much. If you're using bit with PHP you need to typecast the values going in and out.

如果您有很多布尔字段，Bit 仅优于各种字节选项（tinyint、enum、char(1)）。一个位域仍然占据一个完整的字节。两个位字段适合同一个字节。三、四、五、六、七、八。之后他们开始填充下一个字节。最终节省的成本非常小，您应该关注数以千计的其他优化。除非您处理大量数据，否则这几个字节不会加起来太多。如果您在 PHP 中使用 bit，则需要对传入和传出的值进行类型转换。

回答by Jonathan

Until MySQL implements a bit datatype, if your processing is truly pressed for space and/or time, such as with high volume transactions, create a TINYINT field called bit_flagsfor all your boolean variables, and mask and shift the boolean bit you desire in your SQL query.

在 MySQL 实现位数据类型之前，如果您的处理确实需要空间和/或时间，例如大容量事务，请bit_flags为所有布尔变量创建一个 TINYINT 字段，并在 SQL 中屏蔽和移动您想要的布尔位询问。

For instance, if your left-most bit represents your bool field, and the 7 rightmost bits represent nothing, then your bit_flagsfield will equal 128 (binary 10000000). Mask (hide) the seven rightmost bits (using the bitwise operator &), and shift the 8th bit seven spaces to the right, ending up with 00000001. Now the entire number (which, in this case, is 1) is your value.

例如，如果最左边的位代表 bool 字段，而最右边的 7 位不代表任何内容，那么您的bit_flags字段将等于 128（二进制 10000000）。屏蔽（隐藏）最右边的七个位（使用按位运算符&），并将第 8 位向右移动七个空格，以 00000001 结束。现在整个数字（在本例中为 1）就是您的值。

SELECT (t.bit_flags & 128) >> 7 AS myBool FROM myTable t;

if bit_flags = 128 ==> 1 (true)
if bit_flags = 0 ==> 0 (false)

You can run statements like these as you test

您可以在测试时运行这样的语句

SELECT (128 & 128) >> 7;

SELECT (0 & 128) >> 7;

etc.

等等。

Since you have 8 bits, you have potentially 8 boolean variables from one byte. Some future programmer will invariably use the next seven bits, so you mustmask. Don't just shift, or you will create hell for yourself and others in the future. Make sure you have MySQL do your masking and shifting — this will be significantly faster than having the web-scripting language (PHP, ASP, etc.) do it. Also, make sure that you place a comment in the MySQL comment field for your bit_flagsfield.

由于您有 8 位，因此一个字节可能有 8 个布尔变量。一些未来的程序员总是会使用接下来的七位，所以你必须屏蔽。不要只是转移，否则你会在未来为自己和他人创造地狱。确保你让 MySQL 做你的屏蔽和转换——这将比让 web 脚本语言（PHP、ASP 等）做的快得多。此外，请确保在您的bit_flags字段的 MySQL 注释字段中放置注释。

You'll find these sites useful when implementing this method:

实施此方法时，您会发现这些站点很有用：

• MySQL — Bit Functions and Operators
• Decimal/Binary Conversion Tool

• MySQL — 位函数和运算符
• 十进制/二进制转换工具

回答by Geoff Kendall

I got fed up with trying to get zeroes, NULLS, and '' accurately round a loop of PHP, MySql and POST values, so I just use 'Yes' and 'No'.

我厌倦了尝试获取零、NULLS 和 '' 准确地环绕 PHP、MySql 和 POST 值的循环，所以我只使用了 'Yes' 和 'No'。

This works flawlessly and needs no special treatment that isn't obvious and easy to do.

这可以完美地工作，不需要任何不明显和容易做到的特殊处理。

回答by Vidz

Referring to this link Boolean datatype in Mysql, according to the application usage, if one wants only 0 or 1 to be stored, bit(1) is the better choice.

参考Mysql中的这个link Boolean datatype，根据应用的使用情况，如果只想存储0或1，bit(1)是更好的选择。