MySQL BOOLEAN 或 TINYINT 混淆
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11167793/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
BOOLEAN or TINYINT confusion
提问by Bipin Chandra Tripathi
I was designing a database for a site where I need to use a boolean datetype to store only 2 states, true or false. I am using MySQL.
While designing the database using phpMyAdmin, I found that I have both the BOOLEAN datatype and the TINYINT datatype.
I went through different articles, some said TINYINT is the same as BOOLEAN, no difference. Some say BOOLEAN is converted into TINYINT in MySQL.
我正在为一个站点设计一个数据库，在该站点中我需要使用布尔日期类型来仅存储 2 个状态，true 或 false。我正在使用 MySQL。
在使用 phpMyAdmin 设计数据库时，我发现我同时拥有 BOOLEAN 数据类型和 TINYINT 数据类型。
我看了不同的文章，有人说TINYINT和BOOLEAN是一样的，没有区别。有人说 BOOLEAN 在 MySQL 中转换为 TINYINT。
MY question is, if they both are same why do there exist two? There should be only one of them.
我的问题是，如果它们都相同，为什么会存在两个？应该只有其中之一。
Here is the reference to the articles I read:
http://www.careerride.com/MySQL-BOOL-TINYINT-BIT.aspx
http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html
这是我阅读的文章的参考：
http: //www.careerride.com/MySQL-BOOL-TINYINT-BIT.aspx
http://dev.mysql.com/doc/refman/5.5/en/numeric-type -overview.html
回答by Devart
MySQL does not have internal boolean data type. It uses the smallest integer data type - TINYINT.
MySQL 没有内部布尔数据类型。它使用最小的整数数据类型 - TINYINT。
The BOOLEAN and BOOL are equivalents of TINYINT(1), because they are synonyms.
BOOLEAN 和 BOOL 相当于 TINYINT(1)，因为它们是同义词。
Try to create this table -
尝试创建此表 -
CREATE TABLE table1 (
column1 BOOLEAN DEFAULT NULL
);
Then run SHOW CREATE TABLE, you will get this output -
然后运行 SHOW CREATE TABLE，你会得到这个输出 -
CREATE TABLE `table1` (
`column1` tinyint(1) DEFAULT NULL
)
回答by Tom Stambaugh
Just a note for php developers (I lack the necessary stackoverflow points to post this as a comment) ... the automagic (and silent) conversion to TINYINT means that php retrieves a value from a "BOOLEAN" column as a "0" or "1", not the expected (by me) true/false.
只是给 php 开发人员的一个说明（我缺乏必要的 stackoverflow 点将其作为评论发布）......自动（和无声）转换为 TINYINT 意味着 php 从“BOOLEAN”列中检索一个值作为“0”或“1”，不是预期的（由我）真/假。
A developer who is looking at the SQL used to create a table and sees something like: "some_boolean BOOLEAN NOT NULL DEFAULT FALSE," might reasonably expect to see true/false results when a row containing that column is retrieved. Instead (at least in my version of PHP), the result will be "0" or "1" (yes, a string "0" or string "1", not an int 0/1, thank you php).
正在查看用于创建表的 SQL 并看到类似“some_boolean BOOLEAN NOT NULL DEFAULT FALSE”的开发人员可能会合理地期望在检索包含该列的行时看到真/假结果。相反（至少在我的 PHP 版本中），结果将是“0”或“1”（是的，字符串“0”或字符串“1”，而不是 int 0/1，谢谢 php）。
It's a nit, but enough to cause unit tests to fail.
这是一个 nit，但足以导致单元测试失败。
回答by Please_Dont_Bully_Me_SO_Lords
The Newest MySQL Versions have the new BITdata type in which you can specify the number of bits in the field, for example BIT(1)to use as Booleantype, because it can be only 0or 1.
最新的 MySQL 版本具有新的BIT数据类型，您可以在其中指定字段中的位数，例如BIT(1)用作Boolean类型，因为它只能是0或1。
回答by fortune
As of MySql 5.1 version reference
从 MySql 5.1 版本参考开始
BIT(M) = approximately (M+7)/8 bytes,
BIT(1) = (1+7)/8 = 1 bytes (8 bits)
=========================================================================
================================================== ========================
TINYINT(1) take 8 bits.
https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html#data-types-storage-reqs-numeric
https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html#data-types-storage-reqs-numeric
回答by Patrik Rikama-Hinnenberg
The numeric type overview for MySQL states: BOOL, BOOLEAN: These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true.
MySQL 的数字类型概述指出： BOOL、BOOLEAN：这些类型是 TINYINT(1) 的同义词。零值被认为是错误的。非零值被认为是真的。
See here: https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html
请参阅此处：https: //dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html
