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

2021-11-20 00:00:00 boolean mysql sqldatatypes

既然 MySQL 似乎没有任何布尔"数据类型,那么您在 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?

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

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

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

Over time I have used and seen several approaches:

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

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

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.

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

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?

推荐答案

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

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

从 MySQL 5.0.3 开始,BIT 数据类型用于存储位域值.一种类型的 BIT(M) 能够存储 M 位值.M可以范围从 1 到 64.

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 手册,您可以使用 BOOLBOOLEAN,它们目前是 tinyint(1):

Otherwise, according to the MySQL manual you can use BOOL or BOOLEAN, which are at the moment aliases of tinyint(1):

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.

MySQL 还声明:

我们打算实现完整的布尔值类型处理,按照标准 SQL,在未来的 MySQL 中发布.

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

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

相关文章