解读mysql中的null问题

目录
  • mysql的null问题
  • SQL中的NULL你真的懂了吗?
    • 查询某列值为 NULL 的数据
    • NULL 是「未知」
    • 三元逻辑(原文为 Ternary Logic)
    • NOT IN 和 NULL

mysql的null问题

(1) 使用统计函数的时候会出现null值得情况

注意点 sum avg max min 都有可能出现为null的问题 而且 sum avg max min count(具体字段) 都会忽略null的数据 count(*) 不会忽略null数据

(2) 使用count统计需要注意的事项

(3)sum()函数实现统计指定字段值之和,在具体统计时将忽略NULL值,但是不会忽略0值

(4)MAX函数和MIN函数会忽略值为NULL的数据,但是不会忽略值为0的数据

(5)distinct数据丢失

(6)select数据丢失

(7)group by 会让null值独立分组

(8)通配符不能匹配到列为 NULL 的数据

建表的时候尽量设置默认值(0或者’')

SQL中的NULL你真的懂了吗?

SQL 中的 NULL 到底是怎样一个概念呢?有什么要注意的吗?这篇文章就是要把它讲清楚。

查询某列值为 NULL 的数据

当想查询某一列值为 NULL 的数据时,下面两种哪个更好呢?

SELECT * FROM SOME_TABLE
WHERE SOME_COLUMN = NULL

还是

SELECT * FROM SOME_TABLE
WHERE SOME_COLUMN IS NULL

答案是,第二种更好。

为什么呢?为什么其他的比较都不用 IS 呢?比如想知道一个字段值是不是等于1,可以用一个简单的 WHERE 子句:

WHERE SOME_COLUMN = 1

所以到底为什么对 NULL 区别对待使用 IS 呢?

因为:在 SQL 中,NULL 表示「未知」的意思,就是「未知」,不知道,不了解,未知!(原文用词:「unknown」)

NULL 是「未知」

在大多数数据库中,NULL 和空字符串是有区别的。

但也有例外,比如在 Oracle 中,根本就不允许一个值是空字符串,Oracle 中所有的空字符串都会自动转换成 NULL。

不过对于其他大多数数据库来说,NULL 和空字符串是区别对待的:

  • 空字符串也是一种值,只不过是空的而已。
  • NULL 是一个「未知」值。(或者说是「未知」,没有「值」的概念)

举个例子,就好像问:美国总统西奥多·罗斯福的中间名是什么?

  • 一种回答可能是:我不知道西奥多·罗斯福的中间名是什么。(这种情况「中间名」字段就应该是 NULL)
  • 还有一种回答可能是:西奥多·罗斯福没有中间名,他父母没给他起中间名,我知道的事实就是西奥多·罗斯福没有中间名。(这种情况「中间名」就应该为空字符串)

谨记 NULL 就是「未知」这个概念,就可以很容易处理一些使用 NULL 时可能遇到的麻烦。

比如下面的 WHERE 子句就会得到 true,那就能查到数据(如果数据库有数据的话):

SELECT * FROM SOME_TABLE
WHERE 1 = 1

下面的子句会得到 false,永远都查不到数据:

SELECT * FROM SOME_TABLE
WHERE 1 = 0

而下面的 WHERE 子句会得到 NULL,因为数据库并不知道 1 和 NULL(「未知」)是什么关系,他们相等不相等,数据库不清楚,所以 WHERE 子句得到的又是 NULL(「未知」)。所以下面的查询永远也不会返回任何数据。

SELECT * FROM SOME_TABLE
WHERE 1 = NULL

三元逻辑(原文为 Ternary Logic)

一个 SQL 语句中 WHERE 子句有三种不同的结果

  • true(会返回数据)
  • false(不会返回数据)
  • NULL(「未知」也不会返回数据)

好了,那既然 false 和 NULL 都不会返回数据,那干嘛还要关注它们的区别呢?

当遇上 NOT() 的时候就有问题了。

比如下面这个语句,1 肯定等于 1,显然经过 NOT() 后就会变成 false,那就永远不会返回数据。

SELECT * FROM SOME_TABLE
WHERE NOT(1 = 1)

下面这句呢,显然 NOT() 后会得到 true,当然会返回数据。

SELECT * FROM SOME_TABLE
WHERE NOT(1 = 0)

但是这句呢?

SELECT * FROM SOME_TABLE
WHERE NOT(1 = NULL)

上面这句 1 = NULL 由于数据库不知道 NULL 是什么,「未知」,所以其结果是 NULL。对 NOT() 来说呢,它也不知道 NULL 是什么,该怎么处理,所以也会返回 NULL,所以 WHERE 子句得到的是 NULL,既不是 true 也不是 false 而是 NULL,所以上面这条语句永远都不会返回数据。

那么好了,看下面这两条语句,虽然是相反的条件,但结果一致:都不会查询到数据。

SELECT * FROM SOME_TABLE
WHERE NOT(1 = NULL)
SELECT * FROM SOME_TABLE
WHERE 1 = NULL

NOT IN 和 NULL

NOT IN 也是非常值得注意的。

比如下面这个 SQL,1 显然在后面的列表中,WHERE 就会得到 true,那么就会查询到数据。

SELECT * FROM SOME_TABLE
WHERE 1 IN (1, 2, 3, 4, NULL)

再看下面这句,显然 1 是在数组中的,那么 NOT IN 就会得到 false,那么就不能查询到数据。

SELECT * FROM SOME_TABLE
WHERE 1 NOT IN (1, 2, 3, 4, NULL)

再看这个:

SELECT * FROM SOME_TABLE
WHERE 5 NOT IN (1, 2, 3, 4, NULL)

先说答案:这句语句不能查询到数据。

5 在不在后面的列表中呢?数据库是不知道的,因为里面有个 NULL,谁知道 5 等不等于 NULL(「未知」),不知道,所以 5 NOT IN (1, 2, 3, 4, NULL) 得到的是 NULL,所以查询不到数据。

小结:以上,NULL 就是 NULL,是「未知」,这样一种概念的重要性就介绍完了。理解这一点,在构建复杂 SQL 时将很有用。

这些仅为个人经验,希望能给大家一个参考,也希望大家多多支持我们。

(0)

相关推荐

  • MySQL中设置NULL和空白字符串的问题及解决

    目录 MySQL设置NULL和空白字符串问题 在MySQL中判断是否为NULL MySQL中空值(null)和空字符(‘‘) 长度和判空 ifnull 建议 MySQL设置NULL和空白字符串问题 今天在修改一个bug,发现了很多问题. 记录其中一个比较有意识的bug. 数据库使用的是MySQL 先不谈问题,我就是觉得同一个字段,有人存了设置为空白字符串,有人设置了NULL. 空值是不占用空间的,MySQL中的NULL其实是占用空间的. 还有啊,有时候你虽然插入了NULL,但在数据库中还是有值的

  • mysql实现批量修改字段null值改为空字符串

    目录 批量修改字段null值改为空字符串 将某个字段修改为null(从不允许为空not null修改为null) 批量修改字段null值改为空字符串 UPDATE 表名 SET 字段名 = ( CASE WHEN IFNULL(字段名, '') = '' THEN '' ELSE 字段名 END ) WHERE 字段名 IS NULL; 将某个字段修改为null(从不允许为空not null修改为null) 以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们.

  • MySQL ifnull()函数的具体使用

    MySQL IFNULL函数简介 MySQL IFNULL函数是MySQL控制流函数之一,它接受两个参数,如果不是NULL,则返回第一个参数. 否则,IFNULL函数返回第二个参数.两个参数可以是文字值或表达式. 以下说明了IFNULL函数的语法: IFNULL(expression_1,expression_2); 如果expression_1不为NULL,则IFNULL函数返回expression_1; 否则返回expression_2的结果. IFNULL函数根据使用的上下文返回字符串或数

  • 详解MySQL的字段默认null对唯一索引的影响

    目录 正文 看一下为何唯一索引为影响insert速度 MySQL版本:在docker中启动一个mysql 假设只存在邮箱注册: insert数据 经验 正文 在日常业务开发中,会经常遇到需要保证唯一性的数据业务,如用户注册业务.一般注册业务中允许用户以手机号或email注册账号,且需要保证唯一,不允许重复注册.当用户输入手机号或email登录时,程序会判定输入信息的存在与否性,存在则走登录,不存在则走注册.而保证唯一性就不仅仅需要在程序端做判断,还需要MySQL的唯一索引去做最后一道防线.那么唯

  • MySQL如何判断字符串为NULL或者为空字符串

    目录 判断字符串为NULL或者为空字符串 MySQL中NULL和空字符串区别 一般会有以下疑问 注意事项 判断字符串为NULL或者为空字符串 函数名 说明 ISNULL(expr) 如果expr为null返回值1,否则返回值为0 IFNULL(expr1,expr2) 如果expr1值为null返回expr2的值,否则返回expr1的值 NULLIF(expr1,expr2) 如果expr1==expr2返回值为null,否则返回expr1的值:相当于case when expr1=expr2

  • mysql count 为null时,显示0的问题

    目录 mysql count 为null时,显示0 1.使用ifnull 2.运行结果 mysql让count为0的记录也显示出来 在mysql 下执行如下命令 mysql count 为null时,显示0 1.使用ifnull ifnull(字段名,目标值) SELECT a.*,IFNULL(r.count,0) from (SELECT act_id,poster_id,type,style_type,status,status_time,title,content,images,star

  • 解读mysql中的null问题

    目录 mysql的null问题 SQL中的NULL你真的懂了吗? 查询某列值为 NULL 的数据 NULL 是「未知」 三元逻辑(原文为 Ternary Logic) NOT IN 和 NULL mysql的null问题 (1) 使用统计函数的时候会出现null值得情况 注意点 sum avg max min 都有可能出现为null的问题 而且 sum avg max min count(具体字段) 都会忽略null的数据 count(*) 不会忽略null数据 (2) 使用count统计需要注

  • Mysql中的NULL和Empty String

    最近新接触Mysql,昨天新建一个表用于存储表结构信息: create table tablist(TABLE_SCHEMA varchar(40),TABLE_NAME varchar(40),COLUMN_NAME varchar(40),COLUMN_TYPE varchar(40), IS_NULLABLE varchar(10),COLUMN_DEFAULT varchar(40),COLUMN_COMMENT varchar(1000),REMARK varchar(2000));

  • python 在mysql中插入null空值的操作

    python在mysql中插入null空值 sql = "INSERT INTO MROdata (MmeUeS1apId) VALUES (%s)"%'NULL' %s没有引号,可以将"null"中null写进数据库,达到NULL值效果. %s加引号 values就是字符串,导致类型错误,插入错误. sql = "INSERT INTO MROdata (MmeUeS1apId) VALUES ('%s')"%'NULL' 补充:数据库中的空值

  • MySQL中关于null值的一个小问题

    今天在测试null值的时候,发现了一个小问题,记录在这里,不知道大家以前遇到过没. 事情发展是这样的,在过滤一个表中的数值的时候,需要把age=2的列给剔除掉,然后查看剩余的列信息,这个操作看起来比较简单,我用一个表模拟一下过程: CREATE TABLE `test` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `age` int(11) DEFAULT NULL,   `score` varchar(20) NOT NULL DEFAULT '',

  • 一文带你探究MySQL中的NULL

    目录 前言 1 MySQL 中的NULL 2 NULL占用的长度 3 对NULL值的比较 4 SQL对NULL值进行处理 5 值为NULL 对查询条件的影响 6 值为NULL对索引的影响 7 值为NULL对排序的影响 8 NULL和空值区别 总结 前言 不知道大家有没有遇到这样的问题,当我们在对MySQL数据库进行查询操作时,条件写的是status!=1,理论上会将所有不符合条件的查询出来,但奇怪的是结果为NULL的就查不出来,必须得拼接上条件or status IS NULL.本篇文章我们就一

  • MySQL中空值Null和空字符‘‘的具体使用

    目录 1. 空值 Null 和空字符 '' 2. 空值 Null 和空字符 '' 在查询方式上的对比 2.1. 创建 SQL 脚本 2.2. 查询 username 列为空字符串 '' 的所有数据 2.3. 查询 username 列为空值 null 的所有数据 2.4. 查询 username 列不为空值 null 的所有数据 3. 空值 Null 和空字符 '' 在 count() 函数查询的对比 3.1. 查询 username 列数据总数 4. MySQL 中的比较运算符 1. 空值 N

  • 小心陷阱!MySQL中处理Null时需注意两点

    MySQL数据库是一个基于结构化数据的开源数据库.SQL语句是MySQL数据库中核心语言.不过在MySQL数据库中执行SQL语句,需要小心两个陷阱. 陷阱一:空值不一定为空 空值是一个比较特殊的字段.在MySQL数据库中,在不同的情形下,空值往往代表不同的含义.这是MySQL数据库的一种特性.如在普通的字段中(字符型的数据),空值就是表示空值.但是如果将一个空值的数据插入到TimesTamp类型的字段中,空值就不一定为空.此时为出现什么情况呢(如下图)? 我先创建了一个表.在这个表中有两个字段:

  • MySQL中对于NULL值的理解和使用教程

    NULL值的概念是造成SQL的新手的混淆的普遍原因,他们经常认为NULL是和一个空字符串''的一样的东西.不是这样的!例如,下列语句是完全不同的: mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES (""); 两个语句把值插入到phone列,但是第一个插入一个NULL值而第二个插入一个空字符串.第一个的含义可以认为是"电话号码不

  • mysql中替代null的IFNULL()与COALESCE()函数详解

    在MySQL中isnull()函数不能作为替代null值! 如下: 首先有个名字为business的表: SELECT ISNULL(business_name,'no business_name') AS bus_isnull FROM business WHERE id=2 直接运行就会报错: 错误代码: 1582 Incorrect parameter count in the call to native function 'isnull' 所以,isnull()函数在mysql中就行不

随机推荐