MySQL Tinyint vs Bit？

Tinyint vs Bit?

提问by Seibar

I don't want to touch-off a religious war here, but there seem to be two schools of thoughts in how to represent boolean values in a database. Some say bitis the appropriate data type, while others argue tinyintis better.

我不想在这里引发一场宗教War，但似乎有两种关于如何在数据库中表示布尔值的思想流派。有人说bit是合适的数据类型，而另一些人则认为tinyint更好。

The only differences I'm aware of are these:

我所知道的唯一区别是：

• bit: storage size is 1 bit, possible values are 0 or 1
• tinyint: storage size is 1 byte, possible values are 0-255

• bit: 存储大小为 1 位，可能的值为 0 或 1
• tinyint: 存储大小为 1 字节，可能的值为 0-255

Which data type is better when you need to represent boolean values? Is tinyintworth the extra overhead "just in case" you need to values > 1?

当您需要表示布尔值时，哪种数据类型更好？是否tinyint值得“以防万一”您需要值 > 1 的额外开销？

回答by ScottS

When you add a bit column to your table it will occupy a whole byte in each record, not just a single bit. When you add a second bit column it will be stored in the same byte. The ninth bit column will require a second byte of storage. Tables with 1 bit column will not gain any storage benefit.

当您向表中添加一个位列时，它将在每条记录中占用整个字节，而不仅仅是一个位。当您添加第二个位列时，它将存储在同一字节中。第九位列将需要第二个字节的存储空间。具有 1 位列的表不会获得任何存储优势。

Tinyint and bit can both be made to work, I have used both successfully and have no strong preference.

Tinyint 和 bit 都可以工作，我都成功使用过，没有强烈的偏好。

回答by Mike Robinson

Bit...unless you're of the "true / false / file not found" clan

位...除非您属于“真/假/未找到文件”氏族

In case you didn't get the reference...

如果你没有得到参考...

And in the case of Linq2SQL, bit works with true/false which makes it easier to program for. There's advantages to both.

在 Linq2SQL 的情况下，bit 与 true/false 一起使用，这使得编程更容易。两者各有优势。

And there's also programming maintenance to consider. What happens if you (or a junior intern programmer) uses a 2, 3, 25, 41, 167, 200 etc? Where is that documented? Bits are self-documentingand pretty universal.

还有编程维护需要考虑。如果您（或初级实习程序员）使用 2、3、25、41、167、200 等会怎样？这是在哪里记录的？位是自我记录的并且非常通用。

回答by John Rudy

I use bits when appropriate. Aside from it being semantically the correct type (semantics count!), multiple bit fields (up to 8) in a single row (on SQL Server, anyway) can be consolidated into a single byte of storage. After the eighth, an additional byte is needed for the next 8, and so on.

我在适当的时候使用位。除了它在语义上是正确的类型（语义计数！），单行（在 SQL Server 上，无论如何）中的多个位字段（最多 8 个）可以合并到单个字节的存储中。在第 8 个之后，接下来的 8 个需要一个额外的字节，依此类推。

References:

参考：

• SQL 2000
• SQL 2005
• SQL 2008

• SQL 2000
• SQL 2005
• SQL 2008

回答by armandino

For MySql users - Why you should not use BIT columns in MySQL

对于 MySql 用户 -为什么不应该在 MySQL 中使用 BIT 列

回答by Matt

A previous StackOverflow post: What is the difference between BIT and TINYINT in MySQL?

以前的 StackOverflow 帖子：MySQL 中的 BIT 和 TINYINT 有什么区别？

When adding a new "BOOL" column, MySQL actually uses TINYINT.

添加新的“BOOL”列时，MySQL 实际上使用 TINYINT。

I'd just stick with BOOL(aka TINYINT) and move on with life.

我只是坚持使用BOOL（又名TINYINT）并继续生活。

回答by tvanfosson

Boolean, by definition, allows only two values. Why would you need anything more than a single bit for this? if you need a three (or more) state logic, then use a bigger datatype, but I would (and do) stick with bit fields for standard boolean logic.

根据定义，布尔值只允许两个值。为什么你需要的不仅仅是一点点呢？如果您需要三（或更多）状态逻辑，则使用更大的数据类型，但我会（并且确实）坚持使用位域来实现标准布尔逻辑。

回答by RedFilter

I use bit because it saves me having to use a check constraint, and because my ORM will automatically convert bit into a nullable boolean (C#), which I very much appreciate once coding.

我使用 bit 是因为它使我不必使用检查约束，并且因为我的 ORM 会自动将 bit 转换为可为空的布尔值 (C#)，我非常感谢编码后。

回答by Beejor

Zero Space for False

错误的零空间

Whatever your choice, you can set to NULLinstead of 0and it will take up no extra space(since the database almost always has a NULLflag for every field of every row, just sitting there; more info here). If you also make sure the default/most likely value is false, you'll save even more space!

无论您选择什么，您都可以设置为NULL而不是，0它不会占用额外的空间（因为数据库几乎总是NULL为每一行的每个字段都有一个标志，只是坐在那里；更多信息在这里）。如果您还确保默认/最可能的值是false，您将节省更多空间！

Some Space for True

一些真实的空间

The value to represent truerequires the space defined by the field type; using BITwill only save space if a table has multiple such columns, since it uses one byte per 8 fields (versus TINYINTwhich uses one byte per field).

要表示的值true需要字段类型定义的空间；BIT如果一个表有多个这样的列，using只会节省空间，因为它每 8 个字段TINYINT使用一个字节（而不是每个字段使用一个字节）。

TINYINThas the advantage of allowing you to customize an 8-value bitmaskwithout worrying about managing a bunch of extra columns, and searching is theoretically faster (a single integer field versus several bit fields). But there are some disadvantages such as slower ordering, fancy cross-indexing stuff, and lack of field names. Which to me, is the biggest loss; your database would require external documentation to note which bits did what in which bitmasks.

TINYINT具有允许您自定义 8 值位掩码而无需担心管理一堆额外列的优点，并且理论上搜索速度更快（单个整数字段与多个位字段）。但是有一些缺点，例如排序较慢、花哨的交叉索引以及缺少字段名称。这对我来说，是最大的损失；您的数据库需要外部文档来记录哪些位在哪些位掩码中做了什么。

In any case, avoid the temptation to use TEXTfields to store booleans or sets of them. Searching through text is a lot more work for the server, and arbitrary naming schemes like "on, off, off" can hurt interoperability.

在任何情况下，都要避免使用TEXT字段来存储布尔值或它们的集合的诱惑。搜索文本对服务器来说需要做更多的工作，而诸如“开、关、关”之类的任意命名方案可能会损害互操作性。

回答by Sheldmandu

All these theorentical 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 saldag

I don't think I saw it mentioned above, but there's the issue of not being able to aggregate BIT columns (e.g. MIN, MAX, and especially SUM). I just tested using 2008 and the issue is still there. That's the biggest reason I use tinyint lately - the other being I like how tinyint scales - it's always a pain when your "two-value" bit flag suddenly needs more possible values.

我不认为我在上面看到它，但是存在无法聚合 BIT 列（例如 MIN、MAX，尤其是 SUM）的问题。我刚刚使用 2008 进行了测试，问题仍然存在。这是我最近使用 tinyint 的最大原因——另一个原因是我喜欢 tinyint 的缩放方式——当你的“双值”位标志突然需要更多可能的值时，总是很痛苦。