用于存储布尔值的 MySQL 数据类型
既然 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 手册，您可以使用 BOOL 或 BOOLEAN，它们目前是 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
相关文章