• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL中NULL值讨论

MySQL 彭东稳 8年前 (2017-06-26) 36884次浏览 已收录 0个评论

学习过关系型数据库的伙伴都知道,NULL 是指不确定的值,在数据库中绝对是噩梦的存在;而空值,一般对字符串类型而言,指没有任何值的字符串类型,为字符类型的变量设置为空值:set @vs=””,空值跟无值不同。有人可能会问,无值是什么?无值,是指数据表中没有任何数据。无值和不确定值,单从字面意思上来看,两者之间的定义很清楚,一旦深究,这两者之间的关系,有时令人十分迷惑(confused)。

为什么不建议使用 NULL
所有使用 NULL 值的情况,都可以通过一个有意义的值的表示,这样有利于代码的可读性和可维护性,并能从约束上增强业务数据的规范性。
NULL 值在 timestamp 类型下容易出问题,特别是没有启用参数explicit_defaults_for_timestamp
NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空结果,查询容易出错
Null 列需要更多的存储空间:需要一个额外字节作为判断是否为 NULL 的标志位
NULL值到非NULL的更新无法做到原地更新,更容易发生索引分裂,从而影响性能。

MySQL数据库是一个基于结构化数据的开源数据库,SQL语句是MySQL数据库中核心语言。不过在MySQL数据库中执行SQL语句,需要小心两个陷阱。

陷阱一:空值不一定为空

空值是一个比较特殊的字段。在MySQL数据库中,在不同的情形下,空值往往代表不同的含义。这是MySQL数据库的一种特性。如在普通的字段中(字符型的数据),空值就是表示空值。但是如果将一个空值的数据插入到TimesTamp类型的字段中,空值就不一定为空。此时为出现什么情况呢?

我先创建了一个表。在这个表中有两个字段:User_id(其数据类型是int)、Date(其数据类型是TimesTamp)。现在往这个表中插入一条记录,其中往Date字段中插入的是一个NULL空值。可是当我们查询时,其结果显示的却是插入记录的当前时间。这是怎么一回事呢?其实这就是在MySQL数据库中执行SQL语句时经常会遇到的一个陷阱:空值不一定为空。在操作时,明明插入的是一个空值的数据,但是最后查询得到的却不是一个空值。

在MySQL数据库中,NULL对于一些特殊类型的列来说,其代表了一种特殊的含义,而不仅仅是一个空值。对于这些特殊类型的列,主要是要记住两个。一个就是笔者上面举的TimesTamp数据类型。如果往这个数据类型的列中插入Null值,则其代表的就是系统的当前时间。另外一个是具有auto_increment属性的列。如果往这属性的列中插入Null值的话,则系统会插入一个正整数序列。而如果在其他数据类型中,如字符型数据的列中插入Null的数据,则其插入的就是一个空值。

陷阱二:空值不一定等于空字符

在MySQL中,空值(Null)与空字符(’’)相同吗?答案是否定的。

在同一个数据库表中,同时插入一个Null值的数据和一个’’空字符的数据,然后利用Select语句进行查询。显然其显示的结果是不相同的。从这个结果中就可以看出,空值不等于空字符。这就是在MySQL中执行SQL语句遇到的第二个陷阱。在实际工作中,空值数据与空字符往往表示不同的含义。数据库管理员可以根据实际的需要来进行选择。如对于电话号码等字段,可以默认设置为空值(表示根本不知道对方的电话号码)或者设置为空字符(表示后来取消了这个号码)等等。由于他们在数据库中会有不同的表现形式,所以数据库管理员需要区别对待。笔者更加喜欢使用空值,而不是空字符。这主要是因为针对空值这个数据类型有几个比较特殊的运算字符。如果某个字段是空字符,数据库中是利用字段名称来代替。相反,如果插入的是空值,则直接显示的是NULL。这跟其他数据库的显示方式也是不同的。

IS NULL和IS NOT NULL关键字。如果要判断某个字段是否含用空值的数据,需要使用特殊的关键字。其中前者表示这个字段为空,后者表示这个字段为非空。在Select语句的查询条件中这两个关键字非常的有用。如需要查询所有电话号码为空的用户(需要他们补充电话号码信息),就可以在查询条件中加入is not null关键字。判断NULL用is null或者is not null,SQL语句里可以用ifnull函数来处理。判断空字符串‘’,要用=”或者<>”,SQL语句里可以用if(col,col,0)处理,即:当col为true时(非null,及非”)显示,否则打印0。

一般情况下,除了count(0),count(*)之外,聚合函数都会忽略NULL值,而统计非NULL值。另外空表也会产生结果为NULL的聚合值。当聚合列值都是NULL值时,由于聚合函数忽略NULL值,因此,当计算聚合函数(max,min,avg和sum)的聚合值时,由于无值可以聚合,数据库引擎不能确定这些聚合函数的返回值,因此,数据库引擎返回NULL值。

聚合函数(max,min,sum,avg和count)忽略null值,但不代表聚合函数不返回null值:如果数据表为空表,或聚合列值都是null,那么max,min,sum,avg聚合函数返回null值,而count 聚合函数返回0。聚合函数的共性:Null values are ignored。

不再迷惑:当不返回任何值时,数据库引擎不确定返回值,就把无值转换为NULL值。


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (3)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!