MySQL null will cause five problems, all fatal!

itread01 2021-01-05 16:59:21
mysql null cause problems fatal


Before the official start , Let's see first MySQL Server configuration and version number information , As shown in the figure below :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609331580357-f0108445-c85c-449f-9950-4f2b6c075d1e.png#align=left&display=inline&height=279&margin=%5Bobject%20Object%5D&name=image.png&originHeight=558&originWidth=1096&size=70337&status=done&style=none&width=548)“ The army and the horse do not move, the grain and grass go first ”, After watching the relevant configuration , Let's set up a test table and some test data first .```sql-- If there is person Delete table first DROP TABLE IF EXISTS person; -- establish person surface , among username Field can be empty , And set a normal index for it CREATE TABLE person ( id INT PRIMARY KEY auto_increment, name VARCHAR(20), mobile VARCHAR(13), index(name)) ENGINE='innodb';-- person Table added test data insert into person(name,mobile) values('Java','13333333330'), ('MySQL','13333333331'), ('Redis','13333333332'), ('Kafka','13333333333'), ('Spring','13333333334'), ('MyBatis','13333333335'), ('RabbitMQ','13333333336'), ('Golang','13333333337'), (NULL,'13333333338'), (NULL,'13333333339'); select * from person;``` Built test data , As shown in the figure below :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609334360387-b21af2f9-ed74-413d-bbea-6c0aa0b00869.png#align=left&display=inline&height=333&margin=%5Bobject%20Object%5D&name=image.png&originHeight=666&originWidth=530&size=55894&status=done&style=none&width=265) With the information , Let's see when there's a column `NULL`  Value , What problems will it lead to ?# 1.count Data loss when a column exists `NULL`  Value , Reuse `count`  Query the column , The information will appear “ Loss ” The problem is , as follows SQL Shown :```sqlselect count(*),count(name) from person;``` The query execution results are as follows :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609334410752-bc9bd6ca-9aee-4d1c-a930-c0d098751bf7.png#align=left&display=inline&height=56&margin=%5Bobject%20Object%5D&name=image.png&originHeight=112&originWidth=326&size=5890&status=done&style=none&width=163) It can be seen from the above results that , When using `count(name)`  When inquiring , You lose two values that are `NULL`  The data of is missing .#### Solution if a column exists `NULL`  Value , Just use `count(*)`  Make statistics .#### Expand kit knowledge : Do not use count( Constant )> Alibaba 《Java Development Manual 》 It's mandatory : Do not use count( Name ) or count( Constant ) Instead of count(*),count(*) yes SQL92 Define the syntax of the standard line count , It's not about the database , Follow NULL He Fei NULL Irrelevant .> > Explain :count(*) The statistical value is NULL The line of , and count( Name ) This column will not be counted as NULL Row of values .# 2.distinct Data loss when using `count(distinct col1, col2)` When inquiring , If one of the columns is `NULL`, So even if the other column has different values , Then the query result will also lose the data , as follows SQL Shown :```sqlselect count(distinct name,mobile) from person;``` The query execution results are as follows :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609334455638-43ff8d29-4963-44bd-b331-174b93832f1e.png#align=left&display=inline&height=54&margin=%5Bobject%20Object%5D&name=image.png&originHeight=108&originWidth=336&size=5988&status=done&style=none&width=168) The original data of the database are as follows :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609334659475-5a4e8691-d083-4244-96fb-8900fbf3d1dc.png#align=left&display=inline&height=335&margin=%5Bobject%20Object%5D&name=image.png&originHeight=670&originWidth=538&size=58717&status=done&style=none&width=269) From the above results, we can see that the number of mobile phone is 10 All the data are different , But the result of the query is 8.# 3.select Data loss if a column exists `NULL` Value , If the execution is not equivalent to a query (<>/!=) Will lead to `NULL`  The result of the value is missing . For example, the following information :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609336414923-78962e7f-e789-4fa1-9445-077a597e7327.png#align=left&display=inline&height=339&margin=%5Bobject%20Object%5D&name=image.png&originHeight=678&originWidth=580&size=57554&status=done&style=none&width=290) I need to check except name Equal to “Java” All the information except , The expected return result is id From 2 To 10 Information about , But when you execute the following query :```sqlselect * from person where name<>'Java' order by id;-- or select * from person where name!='Java' order by id;``` The query results are as follows :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609336573893-50feecb0-e944-46fe-b4e5-ab4ce7845dec.png#align=left&display=inline&height=269&margin=%5Bobject%20Object%5D&name=image.png&originHeight=538&originWidth=536&size=41888&status=done&style=none&width=268) It can be seen that `NULL` The two pieces of information of the company disappeared out of thin air , This result is not in line with our normal expectations .### The solution is to solve the above problems , Just spell... In the query results `NULL`  The result of the value is , Execute SQL as follows :```sqlselect * from person where name<>'Java' or isnull(name) order by id;``` The final results are as follows :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609336783906-9c4663ef-af27-4af0-b96d-cf4b18ebfb80.png#align=left&display=inline&height=307&margin=%5Bobject%20Object%5D&name=image.png&originHeight=614&originWidth=552&size=52225&status=done&style=none&width=276)# 4. Cause null index exception if a column exists `NULL` Value , May cause `sum(column)` The return result of is `NULL`  Instead of 0, If `sum`  The result of the query is `NULL`  Can cause the program to run time and space index exception (NPE), Let's demonstrate this problem . First , Let's build a table and some test data first :```sql-- If there is goods Delete table first DROP TABLE IF EXISTS goods; -- establish goods surface CREATE TABLE goods ( id INT PRIMARY KEY auto_increment, num int) ENGINE='innodb';-- goods Table added test data insert into goods(num) values(3),(6),(6),(NULL);select * from goods;``` The original data in the table are as follows :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609335198639-dbce071a-81e6-4dab-97e7-591e40e2e7e2.png#align=left&display=inline&height=146&margin=%5Bobject%20Object%5D&name=image.png&originHeight=292&originWidth=318&size=11106&status=done&style=none&width=159) Next we use `sum` Inquire about , Perform the following SQL:```sqlselect sum(num) from goods where id>4;``` The query execution results are as follows :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609335521443-859a5429-bd87-46cc-a054-f8acf2fa2f01.png#align=left&display=inline&height=51&margin=%5Bobject%20Object%5D&name=image.png&originHeight=102&originWidth=192&size=4774&status=done&style=none&width=96) When the result of the query is `NULL`  Instead of 0 When , Can cause null index exception .### To solve the null index exception, you can use the following methods to avoid null index exception :```sqlselect ifnull(sum(num), 0) from goods where id>4;``` The query execution results are as follows :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609335659208-1181702b-66d9-474a-8e92-8da30c8e36bc.png#align=left&display=inline&height=59&margin=%5Bobject%20Object%5D&name=image.png&originHeight=118&originWidth=286&size=4975&status=done&style=none&width=143)# 5. Increase the difficulty of query when a column value has `NULL`  Value , In progress `NULL`  Value or not `NULL`  The query difficulty of value increases . The so-called query difficulty increase refers to when `NULL`  Value query , You have to use `NULL`  Value matching query method , such as `IS NULL`  perhaps `IS NOT NULL`  Or is `IFNULL(cloumn)`  Query with such a representation , And the traditional `=、!=、<>...`  You can't use these expressions , This makes the query more difficult , Especially for Xiaobai programmers , Now let's demonstrate these problems . Or with `person`  Table as an example , Its original information is as follows :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609337488772-bdd4465b-0b13-4c9e-b39c-d4e8aed9630a.png#align=left&display=inline&height=340&margin=%5Bobject%20Object%5D&name=image.png&originHeight=680&originWidth=598&size=57387&status=done&style=none&width=299)#### Wrong usage 1:```sqlselect * from person where name<>null;``` Execution result is empty , We didn't get any information , As shown in the figure below :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609337528911-4349d7e3-987b-4a64-a9c0-f53a4991b127.png#align=left&display=inline&height=75&margin=%5Bobject%20Object%5D&name=image.png&originHeight=150&originWidth=592&size=5715&status=done&style=none&width=296)#### Wrong usage 2:```sqlselect * from person where name!=null;``` The execution result is also empty , No information was found , As shown in the figure below :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609337528911-4349d7e3-987b-4a64-a9c0-f53a4991b127.png#align=left&display=inline&height=75&margin=%5Bobject%20Object%5D&name=image.png&originHeight=150&originWidth=592&size=5715&status=done&style=none&width=296)#### Use correctly 1:```sqlselect * from person where name is not null;``` The results are as follows :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609337620037-806f72a0-e7d5-44ea-9a86-5701bef85ec1.png#align=left&display=inline&height=275&margin=%5Bobject%20Object%5D&name=image.png&originHeight=550&originWidth=616&size=46381&status=done&style=none&width=308)#### Use correctly 2:```sqlselect * from person where !isnull(name);``` The results are as follows :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609337620037-806f72a0-e7d5-44ea-9a86-5701bef85ec1.png#align=left&display=inline&height=275&margin=%5Bobject%20Object%5D&name=image.png&originHeight=550&originWidth=616&size=46381&status=done&style=none&width=308)#### Recommended usage ** Alibaba 《Java Development Manual 》 It is recommended that we use `ISNULL(cloumn)`  To judge `NULL`  value **, The reason is that SQL In the sentence , If in null Front line , Affect readability ; and `ISNULL(column)`  It's a whole , Simple and easy to understand . From the analysis of efficiency data `ISNULL(column)`  It's also faster to execute .# Expand kit knowledge :NULL Will not affect the index, careful friends may find out , I'm building `person`  Tabular `name`  In the field , A general index is set up for it , As shown in the figure below :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609337990811-051a80ec-8cfd-495c-b3a0-af2f66c4ddc4.png#align=left&display=inline&height=191&margin=%5Bobject%20Object%5D&name=image.png&originHeight=382&originWidth=1256&size=63266&status=done&style=none&width=628) Then we use `explain`  To analyze the query plan , Watch it `name`  There is `NULL`  Whether the selection of index will be affected by the value of .`explain` The execution result of is shown in the figure below :![image.png](https://cdn.nlark.com/yuque/0/2020/png/92791/1609338106540-8fd804cf-0cef-4d42-b122-e5d6d12e93d5.png#align=left&display=inline&height=180&margin=%5Bobject%20Object%5D&name=image.png&originHeight=360&originWidth=1960&size=64177&status=done&style=none&width=980) It can be seen from the above results that , Even if `name` There is `NULL` And the value doesn't affect MySQL Using indexes for queries .# To sum up, we talk about when a is listed as `NULL`  It can lead to 5 It's a problem : Missing query results 、 It leads to null index exception and increases the difficulty of query . Therefore, in the end, it is recommended that you try your best to set `is not null`  The constraints of , If a column does not have a value , You can set a null value ('') or 0 As its default .> Finally : You also have because NULL And all kinds of pits ? Welcome to comment area to add a message .##### Refer to & Thank Alibaba 《Java Development Manual 》> Follow public account 「Java Chinese community 」 Find more dry goods . Look at Github Discover more :https://github.com/vipstone/a
版权声明
本文为[itread01]所创,转载请带上原文链接,感谢
https://javamana.com/2020/12/20201231121002439P.html

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云