题 用于存储布尔值的MySQL数据类型


由于MySQL似乎没有任何“布尔”数据类型,您在“滥用”哪种数据类型来存储MySQL中的真/假信息?

特别是在写入和读取PHP脚本的上下文中。

随着时间的推移,我使用并看到了几种方法:

  • tinyint,包含值0/1的varchar字段,
  • 包含字符串'0'/'1'或'true'/'false'的varchar字段
  • 最后枚举包含两个选项'true'/'false'的字段。

以上都不是最佳选择。我更倾向于使用tinyint 0/1变体,因为PHP中的自动类型转换非常简单地给出了布尔值。

那你使用哪种数据类型?有没有为布尔值设计的类型我忽略了?您是否看到使用某种类型的优点/缺点?


992
2017-11-14 10:36


起源


任何正在阅读此问题的旧答案的人都需要了解MySQL在版本5中添加了一些数据类型。尽可能使用该信息。 dev.mysql.com/doc/refman/5.0/en/bit-type.html - smp7d
与之相关的问题 MySQL中许多布尔值的替代品? - tereško
对于当前版本的MYSQL,布尔类型可用 - dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html 检查一下。根据该值零被认为是假的 - DevT
bit(1)在Excel中导入有点**。切换到 tinyint(1) 作品。 - Cees Timmerman
现在我们在5年后就有了布尔值 - V-SHY


答案:


对于MySQL 5.0.3及更高版本,您可以使用 BIT。手册说:

从MySQL 5.0.3开始,BIT数据类型用于存储位字段   值。一种BIT(M)允许存储M位值。 M可以范围   从1到64。

否则,根据MySQL手册,您可以使用bool和boolean,它们目前是别名 TINYINT(1):

Bool,Boolean:这些类型是同义词 TINYINT(1)。价值   零被认为是假的。非零   值被认为是真实的。

MySQL还声明:

我们打算实现完整的布尔值   类型处理,按照   标准SQL,在未来的MySQL中   发布。

参考文献: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

顺便说一句:这只是一个问题 https://google.com/search?q=mysql+boolean+datatype

有趣的不是它,这个链接,几年前发布,已成为递归。


1067
2017-11-14 10:50



是的,根据具体情况,我会选择这个或者为CHAR(1)并存储'Y'/'N'或'T'/'F'等。使用小整数类型的优点是可以跨RDBMS-es获得最大的可移植性 - Roland Bouman
至少在PHP中使用char会导致更多的代码 !$boolean 如果没有进一步的处 - Mild Fuzz
怎么样BIT(1)???? - BMiner
@Pecerier没有什么你不能自己谷歌,但好吧,我会咬人。首先,请看一下data0type.h。请注意,innodb本身并没有在那里定义BIT类型。如果它以你描述的方式处理BIT字段,我们肯定会在那里找到它的存在。其次,阅读 mysqlperformanceblog.com/2008/04/23/...。并且毫不犹豫地告诉我们“marktetplace”中哪些令人惊叹的MySQL客户端在BIT字段中表现得很好。毫无疑问,对于那些错过了那篇文章的人来说,它们会派上用场。 - Roland Bouman
当我从标准的mysql命令行中选择时,客户端位字段显示为完全空白。因此,我更喜欢TINYINT(1)。 - User


BOOL 和 BOOLEAN 是的同义词 TINYINT(1)。零是 false,其他的都是 true。更多信息 这里


202
2017-11-14 10:55



该 (1) 如果你意识到你想要使用的存储大小,那么只会确定值的显示方式 BIT 代替 - JamesHalsall
@JamesHalsall:实际上, BIT(1) 和 TINYINT(1) 将使用一个字节的存储空间。直到MySQL 5.0.3, BIT 实际上是一个同义词 TINYINT。更高版本的MySQL改变了BIT的实现。但即使实施改变,仍然没有“存储大小”的好处 BIT 数据类型(至少使用InnoDB和MyISAM;其他存储引擎,例如NDB可能对多个BIT列声明有一些存储优化。)更大的问题是某些客户端库无法识别或正确处理返回 BIT 数据类型列。一个 TINYINT 效果更好。 - spencer7593
MySQL 5.0手册明确指出布尔值为1或0.短语“其他任何东西都是 true“不是真的。 - Walter
@Walter:实际上有点真实,解释有点缺乏。简而言之,在布尔上下文中,表达式可以计算为NULL,FALSE或TRUE。在MySQL语句中,在布尔上下文中计算的表达式首先被计算为整数(十进制和浮点值被舍入,字符串以通常奇怪的方式转换,MySQL将字符串转换为整数)。 NULL显然是NULL(既不是TRUE也不是FALSE)。整数值0表示为FALSE,和 任何 其他整数值(1,2,-7等)的计算结果为TRUE。为了兼容性,我们模仿TINYINT布尔值的逻辑/处理 - spencer7593
@Walter:这很容易测试,例如 SELECT 'foo' AS bar FROM dual WHERE -7。表达式-7在布尔上下文中计算,查询返回一行。我们可以使用0或任何计算结果为整数值0的表达式进行测试,并且不返回任何行。如果WHERE子句中的表达式求值为除零以外的任何非空整数值,则表达式为TRUE。 (我相信decimal和float值会被“舍入”为整数,例如 WHERE 1/3 评估为 WHERE 0。我们得到了相同的结果 WHERE 'foo',因为字符串 'foo' 也计算整数值0。 - spencer7593


这是一个优雅的解决方案,我非常欣赏,因为它使用零数据字节:

some_flag CHAR(0) DEFAULT NULL

要将其设置为true,请设置 some_flag = '' 并将其设置为false,设置 some_flag = NULL

然后测试是否为true,检查是否some_flag IS NOT NULL,并测试false,检查some_flag IS NULL

(这种方法在Jon Warren Lentz,Baron Schwartz和Arjen Lentz的“High Performance MySQL:Optimization,Backups,Replication,and More”中有描述。)


64
2018-02-10 18:09



花哨的把戏!如果使用MySQL <5并且甚至可能比BIT更轻的占用空间,这是有帮助的,但是为了遵守约定并且稍微减少计算开销(逻辑与精确值),我会说BIT是更好的方法。 - zamnuts
可能是“快速”,但它会混淆数据,以至于任何新的开发人员都不知道该列代表什么。 - Richthofen
这使用与BIT(1)相同的字节数 - ITS Alaska
祝ORM能够很好地映射到这一点。 - Craig Labenz
我同意@Richthofen,并且很难想象我会提倡使用这个解决方案的情况。但是,如果要使用它,则指定为 COMMENT 在列的定义中 NULL 表示错误和 '' 表明真实可能会以一种非常小的方式帮助未来的理解。 - eggyal


这个问题已得到解答,但我想我会投入0.02美元。我经常使用CHAR(0),其中''== true和NULL == false。

mysql文档

当你需要一个只能采用的列时,CHAR(0)也相当不错   两个值:定义为CHAR(0)的列只占一个NULL   bit,只能取值NULL和''(空字符串)。


32
2018-04-30 17:39



嗯,如果你像我一样,这似乎在惹麻烦。我的意思是,根据语言,可能很容易找不到NULL和''之间的差异(例如PHP)。 - Roland Bouman
在节省空间(用于表示布尔值的字节数)方面,这种方法是明显的赢家。这样可以在TINYINT上保存一个字节。缺点(正如一些评论所指出的)是一些客户可能难以区分NULL和空字符串。甚至一些关系数据库(例如Oracle)也不区分零长度字符串和NULL。 - spencer7593
这很聪明!我曾经写过聪明的代码,现在我像瘟疫一样避免使用它。我现在希望我的代码具有明确的意图,而不仅仅是正确的行为。我的建议?只有当你想混淆那些必须支持代码/数据库的人时才这样做。例如,在PHP中 '' 和 null 是虚假的价值观。 - CJ Dennis


如果使用BOOLEAN类型,则将其别名为TINYINT(1)。如果你想使用标准化的SQL并且不介意该字段可能包含超出范围的值(基本上任何非0的都是'true'),这是最好的。

ENUM('False','True')将允许您在SQL中使用字符串,并且MySQL将在内部将字段存储为整数,其中'False'= 0并且'True'= 1,基于指定Enum的顺序。

在MySQL 5+中,您可以使用BIT(1)字段来指示1位数字类型。我不相信这实际上在存储中使用了更少的空间,但是再次允许您将可能的值约束为1或0。

以上所有内容都将使用大致相同的存储空间,因此最好选择最容易使用的存储空间。


28
2017-11-14 14:59



你关于ENUM的评论不正确:尝试CAST(yourenumcol AS UNSIGNED),你会注意到False将是1,而True将是2. ENUM的另一个问题是它太容易插入''(空字符串) )。我不赞成使用它。 - Roland Bouman
根据我的经验,使用PHP代码中的BIT(1)字段有点麻烦。 TINYINT(1)更容易,并且生成更易读的代码。 - M-Peror
@ M-Peror - “使用PHP代码中的BIT(1)字段是一个 位 麻烦“......没有双关语。:)但是,是的,我同意。我记得TINYINT(1)也更容易......只是记不起来。其他人对此有什么想法?BIT(1)似乎表面上更好,因为你可以限制为0或1.我认为BIT有时被解释为二进制数据(取决于编程语言和驱动程序/库);而TINYINT被视为更像数字。 - BMiner
@BMiner - 哈哈,它真的是无意识的,没有注意到:)但实际上,如果我没记错,位字段被解释为二进制,而tinyint更容易被视为一个数字,因此,更容易在(布尔)表达式中使用。 - M-Peror
TIL反对推荐 - elwarren


我使用TINYINT(1)来在Mysql中存储布尔值。

我不知道使用这个是否有任何优势...但如果我没有错,mysql可以存储boolean(BOOL)并将其存储为tinyint(1)

http://dev.mysql.com/doc/refman/5.0/en/other-vendor-data-types.html


16
2017-11-14 10:42





如果你有很多布尔字段,Bit只对各种字节选项(tinyint,enum,char(1))有利。一位字段仍占用一个完整字节。两个位字段适合同一个字节。三,四,五,六,七,八。之后他们开始填写下一个字节。最终节省的成本非常小,您应该关注数以千计的其他优化。除非您处理大量数据,否则这几个字节不会增加太多。如果您在PHP中使用bit,则需要对输入和输出的值进行类型转换。


14
2018-01-12 16:18



用于类型转换注释的+1。在使用编程语言时添加此功能可避免使用惰性编程技术来支持一致性。使用相同的运算符而不是equals。在PHP的情况下if($ var ==“”)将为0,false,null,undefined和“”。测试通常最好使用的所有值(true === empty($ var)),因为它也可以避免未定义的错误。您还应验证正在使用的数据类型if(is_int($ var)&& $ var === 0)或对其进行类型转换以强制它成为任务的特定数据类型(int)$ var。 - fyrye
@Thor对于MySQL来说是如此,对于MSSQL来说也是如此吗?我正在迁移尚未从MSSQL到MySQL生产的新应用程序。我不是在使用PHP而是将C#转换为Java 8.鉴于Java是一种强类型语言,我并不担心类型处理......只是所有的位标志都会从一个字节移动到最多8个标志给定TINYINT(1)的每个标志1个字节。你知道关于MySQL这个主题的任何文档吗? - Zack Jannsen
@Thor做一些更深入的研究,很清楚答案应该是什么。变化确实发生了,我们已经看到了这种处理的改进。了解将在应用层/数据访问层中的语言并了解您的库支持。我目前正在使用Java,而对于像Hybernate这样的库和使用JDBC,BIT(1)是目前推荐的选择。这是URL [见表5.2]: dev.mysql.com/doc/connector-j/en/... - Zack Jannsen


在MySQL实现位数据类型之前,如果您的处理真正按空间和/或时间(例如高容量事务),请创建一个名为TINYINT的字段 bit_flags 对于所有布尔变量,并在SQL查询中屏蔽并移动所需的布尔位。

例如,如果你的最左边的位代表你的bool字段,而最右边的7位代表什么,那么你的 bit_flags 字段将等于128(二进制10000000)。掩盖(隐藏)最右边的七个位(使用按位运算符) &),并将第8位七个空格向右移动,最后以00000001结束。现在整个数字(在本例中为1)是您的值。

SELECT (t.bit_flags & 128) >> 7 AS myBool FROM myTable t;

if bit_flags = 128 ==> 1 (true)
if bit_flags = 0 ==> 0 (false)

您可以在测试时运行这些语句

SELECT (128 & 128) >> 7;

SELECT (0 & 128) >> 7;

等等

由于您有8位,因此一个字节可能有8个布尔变量。一些未来的程序员将总是使用接下来的七位,所以你 必须 面具。不要只是转移,否则你将来会为自己和他人创造地狱。确保你有MySQL进行屏蔽和转移 - 这比使用网络脚本语言(PHP,ASP等)做得快得多。另外,请确保在MySQL注释字段中添加注释 bit_flags 领域。

在实现此方法时,您会发现这些网站很有用:


12
2017-07-13 17:13



这似乎是一种混淆未来程序员意图的可怕方式。当然,保存7个字节似乎很麻烦(假设您在该单个表中使用了所有8个bool!) - yep
@yep根本就没有混淆!写 文件 和MySQL 注释 解释表中的每个字段(作为答案提到)!建议的MySQL取消屏蔽策略看起来很稳健 最多可存储16个不同的布尔字段 只有一个 几列 比其中16个更好。如果使用位操作太混乱并且您更喜欢使用Web脚本语言来获取每个布尔值,只需将其存储为 VARCHAR并在代码中执行取消屏蔽过程(您也不需要将其限制为8个字段)... - CPHPython


我厌倦了尝试获得零,NULLS,并且''准确地围绕PHP,MySql和POST值循环,所以我只使用'是'和'否'。

这种方法完美无缺,不需要特别的处理,这种处理不明显且容易做到。


9
2017-07-09 20:10



如果你真的想浪费这么多空间并牺牲性能,你至少可以用Y和N选项完成CHAR(1)。 - ILikeTacos
在大多数现实世界中,“不”和仅缺少信息之间存在真正的差异。例如,如果用户实际上没有说“否”,您可能希望默认选中一个复选框。确切地说你认为你节省了多少空间,每次你需要区分一个假和一个NULL时你做了多少处理 - 如果你确实可以区分?在存储图像和数字视频的世界中,节省空间的一两个点完全无关紧要,但清晰度和减少的处理是真实的。 - Geoff Kendall
这个答案没有错,因为它会起作用,而且没有人们给予信任那么糟糕。对于大多数项目(即:表格大小<1mil行)提供的解决方案之间的性能差异将是可忽略的。如果我的查询在7到5毫秒内返回,我不会抱怨......但是,如果你的表增长到10mil或更多行,这可能不是首选解决方案。 - Brad
我使用ENUM数据类型+1。我个人更喜欢这种表示法:ENUM('y','n')。它是紧凑的(只有一个字节长),直观和良好的外观作为所有布尔标志的应用程序级约定。您可以直接在HTML表单字段中使用它。例如使用PHP:<select name =“production”> <option value =“y”<?= $ production ==='y'? 'selected =“selected”':''?>>是</ option> <option value =“n”<?= $ production ==='n'? 'selected =“selected”':''?>>否</ option> </ select> - Vlado
大声笑,但我不得不说@GeoffKendall是对的。在很多情况下,不需要最佳性能,无论采用何种方法,您的工作都是正确的方法。 - Madmenyo


参考这个链接      Mysql中的Boolean数据类型根据应用程序的用法,如果只想存储0或1,则bit(1)是更好的选择。


4
2018-02-23 10:41



这是真的 BIT(1) 只允许一个 b'0' 要么 b'1' 要存储的值。最大的问题 BIT 数据类型是各种客户端库对数据类型进行各种不稳定的处理。检查各种SQL工具(SQLyog,TOAD for MySQL,SQL Developer)中的行为,“反向工程”数据库模型的工具,以及各种客户端,如JDBC,PHP,Perl DBI,并且为了更好的衡量,测试一些ORM框架( Hibernate,Mybatis,JPA)。在易用性,工具/框架兼容性/本机支持方面, TINYINT(1) 是明显的赢家。 - spencer7593
是。它完成取决于为应用程序考虑的框架。例如,PHP的Phalcon框架不处理Bit数据类型 - Vidz
为了记录,MyBatis支持两者 BIT 和 TINYINT。参考MyBatis的JdbcType类, mybatis.org/mybatis-3/apidocs/reference/org/apache/ibatis/type/... - Lucky
@Vidz我提到你加一个提到BIT(1),但也会指出开发人员读这个 - 了解你的语言将在应用层/数据访问层,并知道你的库支持。我目前正在使用Java,而对于像Hybernate这样的库和使用JDBC,BIT(1)是目前推荐的选择。这是URL [见表5.2]: dev.mysql.com/doc/connector-j/en/... - Zack Jannsen


在阅读了这里的答案后,我决定使用 bit(1) 是的,它在某种程度上在空间/时间上更好,  过了一会儿我改变了主意,我再也不会用了。当使用预处理语句,库等(php)时,它使我的开发变得很复杂。

从那时起,我总是使用 tinyint(1),似乎很好。


0
2018-06-26 10:14