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

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,
tinyint, varchar字段包含值0/1，
varchar fields containing the strings '0'/'1' or 'true'/'false'
包含字符串'0'/'1'或'true'/'false'的varchar字段
and finally enum Fields containing the two options 'true'/'false'.
最后，enum字段包含两个选项“true”/“false”。

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?

那么你使用哪种数据类型呢?是否有一个为布尔值设计的类型，我忽略了?你使用一种或另一种方式看有什么优点/缺点吗?

10 个解决方案

#1

1048

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

对于MySQL 5.0.3或更高版本，可以使用位。手册说:

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中，位数据类型用于存储位字段值。一种类型的位(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.

布尔:这些类型是TINYINT(1)的同义词。0的值被认为是假的。非零值被认为是正确的。

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

BTW: this is just a matter of https://google.com/search?q=mysql+boolean+datatype.

顺便说一句:这只是https://google.com/search?q=mysql+boolean+数据类型的问题。

Funny isn't it, this link, posted a few years back, has become recursive.

有趣的是，这个链接，几年前发布的，已经变成了递归的。

#2

197

BOOL and BOOLEAN are synonyms of TINYINT(1). Zero is false, anything else is true. More information here.

BOOL和BOOLEAN是TINYINT(1)的同义词。0是假的，其他的都是真的。更多的信息在这里。

#3

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.

然后，要测试true，请检查some_flag是否为NULL，要测试false，请检查some_flag是否为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:优化、备份、复制等等》中描述了这种方法。)

#4

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)，其中“== true”和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) NULL occupies only one bit and can take only the values NULL and '' (the empty string).

当您需要一个只能取两个值的列时，CHAR(0)也很不错:定义为CHAR(0) NULL的列只占用一个位，并且只能取值NULL和"(空字符串)。

#5

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').

如果使用布尔类型，则将其别名为TINYINT(1)。如果您希望使用标准化的SQL，并且不介意字段可能包含超出范围的值(基本上任何不为0的值都是“true”)，那么这是最好的方法。

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基于枚举指定的顺序。

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+中，可以使用位(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.

所有这些都将使用大约相同数量的存储，所以最好选择最容易使用的存储。

#6

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

#7

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.

如果您有很多布尔字段，那么位只比各种字节选项(tinyint、enum、char(1))更有利。一个位字段仍然占用一个完整的字节。两个位字段适合这个字节。三，四，五，六，七，八。然后它们开始填充下一个字节。最终，节省的钱是如此之少，您应该关注数以千计的其他优化。除非你要处理大量的数据，否则这几个字节加起来不会太多。如果在PHP中使用bit，则需要对进出值进行排版。

#8

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_flags for 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_flags field 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)。掩码(隐藏)最右边的7位(使用位运算符&)，将第8位7位移到右边，最后得到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 must mask. 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_flags field.

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

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

你会发现这些网站在实施这个方法时很有用:

MySQL — Bit Functions and Operators
MySQL -位函数和操作符
Decimal/Binary Conversion Tool
十进制和二进制转换工具

#9

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'.

我受够了试图得到0、NULLS和“准确地循环PHP、MySql和POST值，所以我只使用‘是’和‘不是’。”

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

这是完美的，不需要特别的处理，不明显和容易做。

#10

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中的这个链接布尔数据类型，如果只需要存储0或1，位(1)是更好的选择。

#1