如何在MySQL中存储布尔数据类型？

Which datatype is used for storing true/false information in MySQL?

哪个数据类型用于在MySQL中存储true / false信息？

Especially when writing and reading with 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'.

Not one seems optimal, I prefer the tinyint 0/1 variant, since automatic type conversion in PHP gives me boolean values rather simply.

似乎没有一个是最优的，我更喜欢tinyint 0/1变体，因为PHP中的自动类型转换非常简单地给出了布尔值。

So, is there a type designed for boolean values which I have overlooked?

那么，有没有为布尔值设计的类型我忽略了？

Do you see any advantages/disadvantages by using one type or another?

您是否看到使用某种类型的优点/缺点？

3 个解决方案

#1

There are lot's of options.

有很多选择。

For compatibility with a lot of different MySQL clients, client libraries, ORM, etc. we've settled on

为了与许多不同的MySQL客户端，客户端库，ORM等兼容，我们已经确定了

 col  TINYINT(1) UNSIGNED COMMENT 'boolean, ...'

And we don't use the TINYINT(1) UNSIGNED datatype for anything except boolean. (Where we can, we add NOT NULL.) We add a comment, with "boolean" as the first string, followed by the actual description.

并且除了boolean之外，我们不使用TINYINT（1）UNSIGNED数据类型。（我们可以，我们添加NOT NULL。）我们添加一个注释，其中“boolean”作为第一个字符串，后跟实际描述。

Note that the (1) doesn't influence the range of values that can be stored. It can still hold integer values from 0 thru 255. We just include it to help distinguish our "boolean" type from other TINYINT values, which are not booleans.

请注意，（1）不会影响可以存储的值的范围。它仍然可以保存0到255之间的整数值。我们只是包含它以帮助区分我们的“布尔”类型与其他不是布尔值的TINYINT值。

We handle a NULL value as NULL, a value of 0 as "false", and any other value is considered "true"... the same way that MySQL interprets an integer value in a boolean context.

我们将NULL值处理为NULL，将值0设置为“false”，并将任何其他值视为“true”...与MySQL解释布尔上下文中的整数值的方式相同。

We've not encountered any clients that can't handle an integer type.

我们没有遇到任何无法处理整数类型的客户端。

#2

For MySQL 5.0.3 and higher, you can use BIT or TINYINT.

对于MySQL 5.0.3及更高版本，您可以使用BIT或TINYINT。

Here you have a more elaborated answer

在这里你有一个更详细的答案

#3

FOR mysql use the type BOOLEAN NOT NULL type like

FOR mysql使用类型BOOLEAN NOT NULL类型

ALTER TABLE `products` ADD `test` BOOLEAN NOT NULL;

if Boolean not working use tiny int like TINYINT(1) NOT NULL eg. ALTER TABLEproductsADDtestTINYINT(1) NOT NULL;

如果布尔值不工作则使用像TINYINT（1）NOT NULL这样的小int。 ALTER TABLEproductsADDtestTINYINT（1）NOT NULL;

important TINYINT(1) value inside tinyint is necessary.

tinyint中的重要TINYINT（1）值是必要的。

#1