|
|
|
|
公众号矩阵

MySQL中记得用not null,不然就滚蛋!

上午我收到一条短信,内容是“尊敬的 null 你好,XXX”,当时我就笑了。

作者:陈哈哈来源:MySQL 江湖路|2021-06-07 07:59

图片来自包图网

真是外行看热闹,内行看门道,这是程序员都能 Get 的笑点,说明程序没有正确从数据库获取到我的姓名,然后把空值格式化为了 null。

我仿佛看到了那个程序员小姐姐被喷的场景,那是个温暖的午后,明媚的阳光洒在办公桌旁,小姐姐正撸着自己的代码,突然… …

“啪啪啪!!别睡了哈哥,老板叫你过去开会!”

我 c…

言归正传,出现这种情况的原因一般是数据库的数据问题造成的,我大胆猜测几种场景,同学们可以在评论区补充:

①首次名称入库时出错,把我的名称填写失败,MySQL 默认成 null 值,查询时格式化成了’null’字符串。

②我注册时故意在名称中加了 \n、\r 等下流的数据,导致查询时返回了空字符串’’,正则校验时又出现空指针。

③我把 id 设置为’null’(别,兄弟们,我还能这么无聊了?~~)

在 MySQL 中,NULL 表示未知的数据,我们在设计表时,常常有老司机告诉我们:

字段尽可能用 NOT NULL,而不是 NULL,除非有特殊情况!

但却都只给结论也不说明原因,就像喝鸡汤不给勺子一样,有点膈应,让不少同学对这些结论只知其一,不明其二。坦白说,老司机也不一定清楚为啥,可能就是他领导让他这么干而已~~

就像我领导,记得我刚来公司时,他语重心长的叮嘱我:MySQL 建表字段记得用 not null,不然就滚蛋!??????

今天我就带你来弄清楚为啥建议你建表字段尽量都使用 not null!

先看看 MySQL 官网文档提到 NULL 的地方:

NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

翻译官:

NULL 列在行中需要额外的空间以记录其值是否为 NULL。对于 MyISAM 表,每个 NULL 列都多花一位,四舍五入到最接近的字节。

其实这是官方在委婉的告诉你,别用 NULL 就完了~~

下面我们来看看 NULL 值有多少坑,这里我会结合 NULL 字段,和你着重说明 sum 函数、count 函数,以及查询条件为 NULL 值时可能踩的坑。

先给出我们的测试表:

  1. mysql> select * from demo0527; 
  2. +----+------------+-------+------+ 
  3. | id | name       | money | age  | 
  4. +----+------------+-------+------+ 
  5. |  1 | 陈哈哈1    |   100 | NULL | 
  6. |  2 | 陈哈哈2    |  NULL | NULL | 
  7. |  3 | NULL       |   100 | NULL | 
  8. +----+------------+-------+------+ 
  9. rows in set (0.00 sec) 
  10. ———————————————— 

我们通过下面三个用例,结合数据库中表 demo0527 的 null 值来看看:

示例一:通过 sum 函数统计一个只有 NULL 值的列的总和,比如 SUM(age)。

示例二:select 记录数量,count 使用一个允许 NULL 的字段,比如 COUNT(name)。

示例三:使用 =NULL 条件查询字段值为 NULL 的记录,比如 money=null 条件。

以上三个示例对应的测试 SQL 如下:

  1. SELECT SUM(age) from demo0527; 
  2. SELECT count(namefrom demo0527; 
  3. SELECT * FROM demo0527 WHERE money=null

查询结果:

  1. mysql> SELECT SUM(age) from demo0527; 
  2. +----------+ 
  3. SUM(age) | 
  4. +----------+ 
  5. |     NULL | 
  6. +----------+ 
  7. 1 row in set (0.00 sec) 
  8.  
  9. mysql> SELECT count(namefrom demo0527; 
  10. +-------------+ 
  11. count(name) | 
  12. +-------------+ 
  13. |           2 | 
  14. +-------------+ 
  15. 1 row in set (0.00 sec) 
  16.  
  17. mysql> SELECT * FROM demo0527 WHERE money=null
  18. Empty set (0.00 sec) 

得到的结果,分别是 NULL、2、空 List;显然,这三条 SQL 语句的执行结果和我们的期望不同:

  • 虽然表中的 age 都是 NULL,但 SUM(age) 的结果应该是 0 才对。
  • 虽然第三行记录的 name 是 NULL,但查记录总行数应该是 3 才对。
  • 使用 money=NULL 并没有查询到 id=2 的记录,查询条件失效。

三个示例的原因分别是:

①MySQL 中 sum 函数没统计到任何记录时,会返回 null 而不是 0,可以使用 IFNULL(null,0) 函数把 null 转换为 0。

②在MySQL中使用count(字段),不会统计 null 值,COUNT(*) 才能统计所有行。

③MySQL 中使用诸如 =、<、> 这样的算数比较操作符比较 NULL 的结果总是 NULL,这种比较就显得没有任何意义,需要使用 IS NULL、IS NOT NULL 或 ISNULL() 函数来比较。

让我们根据上述原因来相应修改一下 SQL:

  1. SELECT IFNULL(SUM(age),0) FROM demo0527; 
  2. SELECT COUNT(*) FROM demo0527; 
  3. SELECT * FROM demo0527 WHERE age IS NULL

修改后我们查询的结果就是我们想要的了:

  1. mysql> SELECT IFNULL(SUM(age),0) FROM demo0527; 
  2. +--------------------+ 
  3. | IFNULL(SUM(age),0) | 
  4. +--------------------+ 
  5. |                  0 | 
  6. +--------------------+ 
  7. 1 row in set (0.00 sec) 
  8.  
  9. mysql> SELECT COUNT(*) FROM demo0527; 
  10. +----------+ 
  11. COUNT(*) | 
  12. +----------+ 
  13. |        3 | 
  14. +----------+ 
  15. 1 row in set (0.00 sec) 
  16.  
  17. mysql> SELECT * FROM demo0527 WHERE age IS NULL
  18. +----+------------+-------+------+ 
  19. | id | name       | money | age  | 
  20. +----+------------+-------+------+ 
  21. |  1 | 陈哈哈1    |   100 | NULL | 
  22. |  2 | 陈哈哈2    |  NULL | NULL | 
  23. |  3 | NULL       |   100 | NULL | 
  24. +----+------------+-------+------+ 
  25. rows in set (0.00 sec) 

另外值得注意的是,不仅 money=NULL 条件查不到字段值为 NULL 的记录,当我们使用 SELECT * FROM demo0527 WHERE money <>100;来查询 id=2 这行时,也是查不到任何数据的。

我们在工作中往往会在这里栽跟头,导致统计不准确,给大家 Mark 一下。

  1. mysql> SELECT * FROM demo0527 WHERE money <>100; 
  2. Empty set (0.02 sec) 

可见 MySQL 库中的 NULL 值很容易导致我们在统计、查询表数据时出错。

这里有些同学可能会问有没有性能上的提升,算不算 SQL 优化,其实把 NULL 列改为 NOT NULL 带来的性能提升可以忽略,除非确定它带来了问题,否则不需要把它当成优先的优化措施。

作者:陈哈哈

编辑:陶家龙

出处:转载自公众号 MySQL 江湖路(ID:mysql_chenhaha)

【编辑推荐】

  1. 鸿蒙官方战略合作共建——HarmonyOS技术社区
  2. 关于MySQL库表名大小写问题
  3. MySQL next-key lock 加锁范围是什么?
  4. 数据库篇:MySQL内置函数
  5. MySql+SSCMS系统
  6. MySQL 加锁范围三——普通索引和普通字段
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

带你轻松入门 RabbitMQ

带你轻松入门 RabbitMQ

轻松入门RabbitMQ
共4章 | loong576

9人订阅学习

数据湖与数据仓库的分析实践攻略

数据湖与数据仓库的分析实践攻略

助力现代化数据管理:数据湖与数据仓库的分析实践攻略
共3章 | 创世达人

9人订阅学习

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

40人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微