php MySQL 中布尔值的 TINYINT 与 ENUM(0, 1)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3546186/
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
TINYINT vs ENUM(0, 1) for boolean values in MySQL
提问by Wiliam
Which one is better, Tinyint with 0 and 1 values or ENUM 0,1 in MyISAM tables and MySQL 5.1?
哪个更好，在 MyISAM 表和 MySQL 5.1 中具有 0 和 1 值的 Tinyint 或 ENUM 0,1？
采纳答案by Adeel
You can use BIT(1)as mentioned in mysql 5.1 reference. i will not recommend enumor tinyint(1)as bit(1)needs only 1 bit for storing boolean value while tinyint(1)needs 8 bits.
您可以BIT(1)按照mysql 5.1 参考中的说明使用。我不会推荐enum或tinyint(1)作为bit(1)仅需要1比特，用于存储而布尔值tinyint(1)需要8位。
回答by BradChesney79
My research shows that BIT(1) is a synonym for TINYINT(1) for versions of MySQL before 5.0.3.
我的研究表明，对于 5.0.3 之前的 MySQL 版本，BIT(1) 是 TINYINT(1) 的同义词。
MySQL versions after 5.0.3 change how the BIT datatype works. It is no longer a synonym for TINYINT and is the only data type that allows you to store anything in less than one byte.
5.0.3 之后的 MySQL 版本更改了 BIT 数据类型的工作方式。它不再是 TINYINT 的同义词，而是唯一允许您以少于一个字节存储任何内容的数据类型。
This datatype may be preferrable to using TINYINT or ENUM. I plan on testing to see which is fastest and the space usage of the three on my blog. There is a link at the bottom if you care to see the size and speed results. Testbed: crummy consumer grade Pentium III box running OpenBSD and MySQL. (With a slower DB dev box, you can really feel the effects of bad code. Also, differences between test queries are more discernible. Alternatively, try using a VM with barely enough resources allocated.)
这种数据类型可能比使用 TINYINT 或 ENUM 更可取。我计划在我的博客上进行测试，看看哪个最快，以及这三个的空间使用情况。如果您想查看大小和速度结果，底部有一个链接。测试平台：运行 OpenBSD 和 MySQL 的糟糕的消费级 Pentium III 机器。（使用较慢的 DB 开发框，您可以真正感受到糟糕代码的影响。此外，测试查询之间的差异更明显。或者，尝试使用分配了几乎没有足够资源的 VM。）
The MySQL Official Documentation.
MySQL 官方文档。
- v5.1 - http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html
- v5.6 - http://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html
- v5.1 - http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html
- v5.6 - http://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html
Baron Schwartz has this to say about it.
Baron Schwartz 对此有话要说。
回答by kalabog76
Enum, in a way gives a "hint" for developers or programmers. But usually, it's better to handle it programmatically. So whether it is ENUM(0,1), BIT(1) AND TINYINT(1), all using 1 byte, it would be better, in most cases, handled on the client side, rather than sending 2 in bit(1) or enum(0,1) to the server and then the server would return an error that you will have to handle anyways - uses more resources (network + server CPU + client CPU x 2)
枚举，在某种程度上为开发人员或程序员提供了“提示”。但通常，最好以编程方式处理它。所以不管是ENUM(0,1), BIT(1) AND TINYINT(1)，都用1个字节，大多数情况下在客户端处理会更好，而不是在bit(1)中发送2或 enum(0,1) 到服务器，然后服务器将返回一个错误，您无论如何都必须处理 - 使用更多资源（网络 + 服务器 CPU + 客户端 CPU x 2）
0 usually means false, 1 true.
0 通常表示假，1 表示真。
回答by Brian Hooper
I'd suggest the ENUM is preferable because it makes clear what is expected; if it detracts from performance in any measurable way I would be very surprised. To make a tinyint do this work would require CHECK a constraint on the column; none of the MySQL storage engines currently support this.
我建议 ENUM 更可取，因为它清楚地表明了预期；如果它以任何可衡量的方式降低性能，我会感到非常惊讶。要让 tinyint 完成这项工作，需要检查列上的约束；目前没有任何 MySQL 存储引擎支持这一点。
回答by Jens
For the best performance and spacerequirements you should collect your boolean values and save them in the same TINYINT. Eg. Save up to 8 boolean values in a TINYINT. 16 boolean values in a SMALLINT etc.
Both BIT(1) and ENUM uses at least 1 byte BIT(M) - approximately (M+7)/8 bytessee: https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html. So if you are storing 1 boolean value I would use TINYINT as it has the same overhead as BIT and ENUM but gives you the option to store 7 more boolean values later if you need.
为了获得最佳性能和空间要求，您应该收集布尔值并将它们保存在同一个 TINYINT 中。例如。在 TINYINT 中最多保存 8 个布尔值。SMALLINT 等中的 16 个布尔值。 BIT(1) 和 ENUM 都使用至少 1 个字节，BIT(M) - approximately (M+7)/8 bytes请参阅：https: //dev.mysql.com/doc/refman/8.0/en/storage-requirements.html。因此，如果您要存储 1 个布尔值，我将使用 TINYINT，因为它具有与 BIT 和 ENUM 相同的开销，但如果您需要，可以选择稍后再存储 7 个布尔值。
