Mysql NULL caused the pit

Mysql NULL caused the pit

Using NULL in comparison operators

mysql> select 1>NULL;
+--------+
| 1>NULL |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)

mysql> select 1<NULL;
+--------+
| 1<NULL |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)

mysql> select 1<>NULL;
+---------+
| 1<>NULL |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

mysql> select 1>NULL;
+--------+
| 1>NULL |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)

mysql> select 1<NULL;
+--------+
| 1<NULL |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)

mysql> select 1>=NULL;
+---------+
| 1>=NULL |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

mysql> select 1<=NULL;
+---------+
| 1<=NULL |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

mysql> select 1!=NULL;
+---------+
| 1!=NULL |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

mysql> select 1<>NULL;
+---------+
| 1<>NULL |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

mysql> select NULL=NULL,NULL!=NULL;
+-----------+------------+
| NULL=NULL | NULL!=NULL |
+-----------+------------+
| NULL | NULL |
+-----------+------------+
1 row in set (0.00 sec)

mysql> select 1 in (null),1 not in (null),null in (null),null not in (null);
+-------------+-----------------+----------------+--------------------+
| 1 in (null) | 1 not in (null) | null in (null) | null not in (null) |
+-------------+-----------------+----------------+--------------------+
| NULL | NULL | NULL | NULL |
+-------------+-----------------+----------------+--------------------+
1 row in set (0.00 sec)

mysql> select 1=any(select null),null=any(select null);
+--------------------+-----------------------+
| 1=any(select null) | null=any(select null) |
+--------------------+-----------------------+
| NULL | NULL |
+--------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select 1=all(select null),null=all(select null);
+--------------------+-----------------------+
| 1=all(select null) | null=all(select null) |
+--------------------+-----------------------+
| NULL | NULL |
+--------------------+-----------------------+
1 row in set (0.00 sec)

Conclusion: When any value is compared with NULL using operators (>, <, >=, <=, !=, <>) or (in, not in, any/some, all), the return value is NULL. When NULL is used as a Boolean value, it is neither 1 nor 0.

Prepare the data

mysql> create table test1(a int,b int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values ​​(1,1),(1,null),(null,null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from test1;
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 1 | NULL |
| NULL | NULL |
+------+------+
3 rows in set (0.00 sec)

Take a close look at the three pieces of data above, especially the NULL records.

IN, NOT IN, and NULL comparisons

IN and NULL comparisons

mysql> select * from test1;
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 1 | NULL |
| NULL | NULL |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from test1 where a in (null);
Empty set (0.00 sec)

mysql> select * from test1 where a in (null,1);
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 1 | NULL |
+------+------+
2 rows in set (0.00 sec)

Conclusion: When IN is compared with NULL, records with NULL cannot be queried.

NOT IN and NULL comparison

mysql> select * from test1 where a not in (1);
Empty set (0.00 sec)

mysql> select * from test1 where a not in (null);
Empty set (0.00 sec)

mysql> select * from test1 where a not in (null,2);
Empty set (0.00 sec)

mysql> select * from test1 where a not in (2);
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 1 | NULL |
+------+------+
2 rows in set (0.00 sec)

Conclusion: When there is a NULL value after NOT IN, the query result of the entire SQL is empty no matter what the situation is.

EXISTS, NOT EXISTS, and NULL Comparisons

mysql> select * from test2;
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 1 | NULL |
| NULL | NULL |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from test1 t1 where exists (select * from test2 t2 where t1.a = t2.a);
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 1 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from test1 t1 where not exists (select * from test2 t2 where t1.a = t2.a);
+------+------+
| a | b |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

Above we copied table test1 and created table test2.

Use exists and not exists in the query statement to compare test1.a=test2.a. Because = cannot compare NULL, the result is consistent with expectations.

To judge NULL, you can only use IS NULL and IS NOT NULL

mysql> select 1 is not null;
+---------------+
| 1 is not null |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)

mysql> select 1 is null;
+-----------+
| 1 is null |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)

mysql> select null is null;
+--------------+
| null is null |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)

mysql> select null is not null;
+------------------+
| null is not null |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

Looking at the effect above, the returned result is 1 or 0.

Conclusion: IS NULL and IS NOT NULL can only be used to determine whether a value is empty.

The pitfall of NULL in aggregate functions

Example

mysql> select count(a),count(b),count(*) from test1;
+----------+----------+----------+
| count(a) | count(b) | count(*) |
+----------+----------+----------+
| 2 | 1 | 3 |
+----------+----------+----------+
1 row in set (0.00 sec)
  • count(a) returns 2 rows, but the rows with NULL in the a field are not counted.
  • count(b) returns 1 row of records, and the 2 rows of records with NULL values ​​are not counted.
  • count(*) can count all data, regardless of whether the data in the field is NULL.

Continue watching

mysql> select * from test1 where a is null;
+------+------+
| a | b |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> select count(a) from test1 where a is null;
+----------+
| count(a) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

The first sql above uses is null to query the result, and count(a) in the second sql returns 0 rows.

Conclusion: count(field) cannot count the value of the field that is NULL, but count(*) can count the rows whose value is null.

NULL cannot be used as a primary key value

mysql> create table test3(a int primary key,b int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test3 values ​​(null,1);
ERROR 1048 (23000): Column 'a' cannot be null

We created a table test3 above. Field a is not specified and cannot be empty. A NULL data is inserted. The error reason is: the value of field a cannot be NULL. Let's take a look at the table creation statement:

mysql> show create table test3;
+-------+------------+
| Table | Create Table |
+-------+------------+
| test3 | CREATE TABLE `test3` (
 `a` int(11) NOT NULL,
 `b` int(11) DEFAULT NULL,
 PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
+-------+------------+
1 row in set (0.00 sec)

As can be seen from the above script, when a field is a primary key, the field will be automatically set to not null.

Conclusion: When a field is a primary key, it will be automatically set to not null.

After reading all of the above, I am still a little confused. The NULL situation is indeed difficult to handle and prone to errors. The most effective way is to avoid using NULL. Therefore, it is strongly recommended that when creating a field, the field does not allow NULL and a default value is set.

Summarize

  • NULL is a Boolean value that is neither 1 nor 0.
  • Any value and NULL use operators (>, <, >=, <=, !=, <>) or (in, not in, any/some, all), the return value is NULL
  • When IN is compared with NULL, records with NULL cannot be queried.
  • When there is a NULL value after NOT IN, the query result of the entire SQL statement is empty no matter what the case.
  • To determine whether it is empty, you can only use IS NULL and IS NOT NULL
  • count(field) cannot count the value of the field that is NULL, but count(*) can count the rows with null values.
  • When a field is the primary key, the field is automatically set to not null
  • The pitfalls caused by NULL are hard to guard against. It is strongly recommended that when creating a field, the field does not allow NULL and gives a default value

This is the end of this article about the pitfalls caused by MySQL NULL. For more relevant content about pitfalls caused by MySQL NULL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Why should MySQL fields use NOT NULL?
  • A small problem about null values ​​in MySQL
  • Some common mistakes with MySQL null
  • Detailed explanation of the difference between MySQL null and not null and null and empty value''''''''
  • Solution to MySQL IFNULL judgment problem
  • Distinguish between null value and empty character ('''') in MySQL
  • Detailed explanation of how to write mysql not equal to null and equal to null
  • Summary of knowledge points related to null (IFNULL, COALESCE and NULLIF) in MySQL
  • Detailed explanation of MySQL three-value logic and NULL

<<:  Docker container connection implementation steps analysis

>>:  Six ways to reduce the size of Docker images

Recommend

MySql 5.7.20 installation and configuration of data and my.ini files

1. First download from the official website of My...

Detailed explanation of the solution to docker-compose being too slow

There is only one solution, that is to change the...

Summary of principles for writing HTML pages for emails

Since HTML email is not an independent HOST page o...

Solution to mysql server 5.5 connection failure

The solution to the problem that mysql cannot be ...

Tutorial on installing Pycharm and Ipython on Ubuntu 16.04/18.04

Under Ubuntu 18.04 1. sudo apt install python ins...

Detailed explanation of the solution to garbled characters when JDBC connects to MySQL to process Chinese

Detailed explanation of the solution to garbled c...

Write a formal blog using XHTML CSS

The full name of Blog should be Web log, which me...

Implementation of socket options in Linux network programming

Socket option function Function: Methods used to ...

MySQL series: Basic concepts of MySQL relational database

Table of contents 1. Basic Concepts 2. Developmen...

Detailed example of mysql similar to oracle rownum writing

Rownum is a unique way of writing in Oracle. In O...

Index Skip Scan in MySQL 8.0

Preface MySQL 8.0.13 began to support index skip ...

Vue implements the sample code of associating the side navigation bar with the Tab page

Table of contents Technology Stack Effect analyze...

Share the responsive frameworks commonly used by web design masters (summary)

This article introduces and shares the responsive...

Vue implements a search box with a magnifying glass

This article shares with you how to use Vue to im...

Vue Learning - VueRouter Routing Basics

Table of contents 1. VueRouter 1. Description 2. ...