MySQL 中的 BIT 和 TINYINT 有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/290223/
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
What is the difference between BIT and TINYINT in MySQL?
提问by carrier
In which cases would you use which? Is there much of a difference? Which I typically used by persistence engines to store booleans?
在哪些情况下你会使用哪个？有很大区别吗？持久性引擎通常使用哪个来存储布尔值？
回答by Robert Gamble
A TINYINT is an 8-bit integer value, a BIT field can store between 1 bit, BIT(1), and 64 bits, BIT(64). For a boolean values, BIT(1) is pretty common.
TINYINT 是一个 8 位整数值，一个 BIT 字段可以存储 1 位 BIT(1) 和 64 位 BIT(64)。对于布尔值，BIT(1) 很常见。
回答by Nelson Miranda
From Overview of Numeric Types;
来自数值类型概述；
BIT[(M)]
位[(M)]
A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.
This data type was added in MySQL 5.0.3 for MyISAM, and extended in 5.0.5 to MEMORY, InnoDB, BDB, and NDBCLUSTER. Before 5.0.3, BIT is a synonym for TINYINT(1).
位域类型。M 表示每个值的位数，从 1 到 64。如果省略 M，则默认为 1。
这种数据类型是在 MySQL 5.0.3 中为 MyISAM 添加的，并在 5.0.5 中扩展到 MEMORY、InnoDB、BDB 和 NDBCLUSTER。在 5.0.3 之前，BIT 是 TINYINT(1) 的同义词。
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
TINYINT[(M)] [未签名] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
一个非常小的整数。有符号范围是 -128 到 127。无符号范围是 0 到 255。
Additionally consider this;
另外考虑这一点；
BOOL, BOOLEAN
布尔值，布尔值
These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.
这些类型是 TINYINT(1) 的同义词。零值被认为是错误的。非零值被认为是真的。
回答by Sheldmandu
All these theoretical discussions are great, but in reality, at least if you're using MySQL and really for SQLServer as well, it's best to stick with non-binary data for your booleans for the simple reason that it's easier to work with when you're outputting the data, querying and so on. It is especially important if you're trying to achieve interoperability between MySQL and SQLServer (i.e. you sync data between the two), because the handling of BIT datatype is different in the two of them. SO in practice you will have a lot less hassles if you stick with a numeric datatype. I would recommend for MySQL to stick with BOOL or BOOLEAN which gets stored as TINYINT(1). Even the way MySQL Workbench and MySQL Administrator display the BIT datatype isn't nice (it's a little symbol for binary data). So be practical and save yourself the hassles (and unfortunately I'm speaking from experience).
所有这些理论讨论都很棒，但实际上，至少如果您正在使用 MySQL 并且实际上也用于 SQLServer，那么最好坚持使用非二进制数据作为您的布尔值，原因很简单，当您使用它时更容易使用'正在输出数据，查询等。如果您试图实现 MySQL 和 SQLServer 之间的互操作性（即在两者之间同步数据），这一点尤其重要，因为两者对 BIT 数据类型的处理是不同的。所以在实践中，如果你坚持使用数字数据类型，你会少很多麻烦。我建议 MySQL 坚持使用 BOOL 或 BOOLEAN 存储为 TINYINT(1)。甚至 MySQL Workbench 和 MySQL Administrator 显示 BIT 数据类型的方式也不好（它是二进制数据的一个小符号）。
回答by Michael Madsen
BIT should only allow 0 and 1 (and NULL, if the field is not defined as NOT NULL). TINYINT(1) allows any value that can be stored in a single byte, -128..127 or 0..255 depending on whether or not it's unsigned (the 1 shows that you intend to only use a single digit, but it does not prevent you from storing a larger value).
BIT 应该只允许 0 和 1（和 NULL，如果该字段未定义为 NOT NULL）。TINYINT(1) 允许任何可以存储在单个字节中的值，-128..127 或 0..255 取决于它是否是无符号的（1 表示您打算只使用一个数字，但它确实不会阻止您存储更大的值）。
For versions older than 5.0.3, BIT is interpreted as TINYINT(1), so there's nodifference there.
对于 5.0.3 之前的版本，BIT 被解释为 TINYINT(1)，因此没有区别。
BIT has a "this is a boolean" semantic, and some apps will consider TINYINT(1) the same way (due to the way MySQL used to treat it), so apps may format the column as a check box if they check the type and decide upon a format based on that.
BIT 具有“这是一个布尔值”语义，一些应用程序会以相同的方式考虑 TINYINT(1)（由于 MySQL 过去处理它的方式），因此如果应用程序检查类型，则应用程序可能会将列格式化为复选框并基于此决定格式。
回答by Allen Hardy
Might be wrong but:
可能是错的，但是：
Tinyint is an integer between 0 and 255
Tinyint 是 0 到 255 之间的整数
bit is either 1 or 0
位是 1 或 0
Therefore to me bit is the choice for booleans
因此对我来说 bit 是布尔值的选择
回答by AndreiTiberiu
From my experience I'm telling you that BIT has problems on linux OS types(Ubuntu for ex). I developped my db on windows and after I deployed everything on linux, I had problems with queries that inserted or selected from tables that had BIT DATA TYPE.
根据我的经验，我告诉您 BIT 在 linux 操作系统类型（例如 Ubuntu）上存在问题。我在 Windows 上开发了我的数据库，并且在我在 linux 上部署了所有内容之后，我遇到了从具有 BIT DATA TYPE 的表中插入或选择的查询的问题。
Bit is not safe for now. I changed to tinyint(1) and worked perfectly. I mean that you only need a value to diferentiate if it's 1 or 0 and tinyint(1) it's ok for that
Bit 目前不安全。我改为 tinyint(1) 并且工作得很好。我的意思是你只需要一个值来区分它是 1 还是 0 和 tinyint(1) 就可以了
