1. Overview
NULL is a very special value in MySQL, officially expressed as "an unknown value", which is different from other data types.
This article will elaborate the particularity of NULL value from many angles.
2. Pre-preparation
To facilitate demonstration, first create a data table for operation. The table structure is as follows
CREATE TABLE `mytest_null_tbl` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL COMMENT 'name value', `phone` varchar(32) DEFAULT NULL COMMENT 'phone number', `age` tinyint(3) unsigned DEFAULT NULL COMMENT 'age value', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mytest table for null value'；
Then insert several pieces of data
mysql> insert into mytest_null_tbl(name,phone,age) values('Lucy','9299008',18);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mytest_null_tbl(name,phone) values('Taylor',0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mytest_null_tbl(name,phone) values('Arwiel','');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mytest_null_tbl(name,phone,age) values('Jenifor','5622890',16);
Query OK, 1 row affected (0.01 sec)
Now let's look at the inserted data.
mysql> select * from mytest_null_tbl; +----+---------+---------+------+ | id | name | phone | age | +----+---------+---------+------+ | 1 | Lucy | 9299008 | 18 | | 2 | Taylor | 0 | NULL | | 3 | Arwiel | | NULL | | 4 | Jenifor | 5622890 | 16 | +----+---------+---------+------+
3. Operation of NULL values
To operate on NULL values, it is not possible to compare operators mathematically, because NULL values are compared with all other values by FALSE.
For NULL operations, MySQL provides IS NULL and IS NOT NULL, as well as an IFNULL() method.
IS NULL means to judge a value as NULL, IS NOT NULL is just the opposite, indicating that a value is not NULL.
mysql> select * from mytest_null_tbl where age is null; +----+--------+-------+------+ | id | name | phone | age | +----+--------+-------+------+ | 2 | Taylor | 0 | NULL | | 3 | Arwiel | | NULL | +----+--------+-------+------+ 2 rows in set (0.00 sec) mysql> select * from mytest_null_tbl where age is not null; +----+---------+---------+------+ | id | name | phone | age | +----+---------+---------+------+ | 1 | Lucy | 9299008 | 18 | | 4 | Jenifor | 5622890 | 16 | +----+---------+---------+------+ 2 rows in set (0.01 sec)
IFNULL(param1, param2) is used to logically determine "if the value of parameter 1 is NULL, then the value of parameter 2 is returned; otherwise, the value of parameter 1 is returned".
mysql> select *,IFNULL(age,0) from mytest_null_tbl; +----+---------+---------+------+---------------+ | id | name | phone | age | IFNULL(age,0) | +----+---------+---------+------+---------------+ | 1 | Lucy | 9299008 | 18 | 18 | | 2 | Taylor | 0 | NULL | 0 | | 3 | Arwiel | | NULL | 0 | | 4 | Jenifor | 5622890 | 16 | 16 | +----+---------+---------+------+---------------+ 4 rows in set (0.02 sec)
4. Insertion and update of NULL values
Normally, NULL values can be inserted into fields of any data type. Take the table as an example, do an update operation to see the results of varchar and int operations.
mysql> update mytest_null_tbl set phone=null,age=null where id=4; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from mytest_null_tbl; +----+---------+---------+------+ | id | name | phone | age | +----+---------+---------+------+ | 1 | Lucy | 9299008 | 18 | | 2 | Taylor | 0 | NULL | | 3 | Arwiel | | NULL | | 4 | Jenifor | NULL | NULL | +----+---------+---------+------+ 4 rows in set (0.00 sec)
You can see that the NULL value has been successfully updated to the table.
So what if you don't want fields to be inserted into NULL values? At this time, setting the field attribute to NOT NULL can achieve the goal.
mysql> alter table mytest_null_tbl add address varchar(32) not null default '' comment 'address info'; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into mytest_null_tbl(name,phone,age) values(null,null,null); Query OK, 1 row affected (0.00 sec) mysql> insert into mytest_null_tbl(name,phone,age,address) values(null,null,null,null); ERROR 1048 (23000): Column 'address' cannot be null mysql> select * from mytest_null_tbl; +----+---------+---------+------+---------+ | id | name | phone | age | address | +----+---------+---------+------+---------+ | 1 | Lucy | 9299008 | 18 | | | 2 | Taylor | 0 | NULL | | | 3 | Arwiel | | NULL | | | 4 | Jenifor | NULL | NULL | | | 5 | NULL | NULL | NULL | | +----+---------+---------+------+---------+ 5 rows in set (0.00 sec)
What happens if some data types are set to NULL?
Look at the timestamp type.
mysql> alter table mytest_null_tbl add create_time timestamp; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from mytest_null_tbl; +----+---------+---------+------+---------+---------------------+ | id | name | phone | age | address | create_time | +----+---------+---------+------+---------+---------------------+ | 1 | Lucy | 9299008 | 18 | | 2019-07-25 15:32:54 | | 2 | Taylor | 0 | NULL | | 2019-07-25 15:32:54 | | 3 | Arwiel | | NULL | | 2019-07-25 15:32:54 | | 4 | Jenifor | NULL | NULL | | 2019-07-25 15:32:54 | | 5 | NULL | NULL | NULL | | 2019-07-25 15:32:54 | +----+---------+---------+------+---------+---------------------+ 5 rows in set (0.00 sec) mysql> update mytest_null_tbl set create_time=null where id=5; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from mytest_null_tbl; +----+---------+---------+------+---------+---------------------+ | id | name | phone | age | address | create_time | +----+---------+---------+------+---------+---------------------+ | 1 | Lucy | 9299008 | 18 | | 2019-07-25 15:32:54 | | 2 | Taylor | 0 | NULL | | 2019-07-25 15:32:54 | | 3 | Arwiel | | NULL | | 2019-07-25 15:32:54 | | 4 | Jenifor | NULL | NULL | | 2019-07-25 15:32:54 | | 5 | NULL | NULL | NULL | | 2019-07-25 15:34:29 | +----+---------+---------+------+---------+---------------------+ 5 rows in set (0.00 sec)
As you can see, when you insert NULL values into a field of type timestamp, MySQL actually inserts the latest time of the operation into the record.
What happens if you do this for a self-increasing int? Let's have a look.
The id column of mytest_null_tbl is auto_increment, but not null is set up, so a new table is needed to test.
mysql> create table mytest_null_tbl2 (
-> id int unsigned auto_increment,
-> primary key(id)
-> )engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mytest_null_tbl2;
Empty set (0.00 sec)
mysql> insert into mytest_null_tbl2(id) values(null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from mytest_null_tbl2;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
As you can see, when NULL values are inserted into fields with AUTO_INCREMENT attributes, MySQL is inserted into the table as an incremental value of the order in which columns are inserted.
5. Merge and Sort
NULL values can participate in distinct, group by, and order by operations in which all NULL values are considered equal.
It should be noted that NULL values are different from 0 and ", although they can be expressed as empty, they are not equal.
mysql> select * from mytest_null_tbl; +----+---------+---------+------+---------+---------------------+ | id | name | phone | age | address | create_time | +----+---------+---------+------+---------+---------------------+ | 1 | Lucy | 9299008 | 18 | | 2019-07-25 15:32:54 | | 2 | Taylor | 0 | NULL | | 2019-07-25 15:32:54 | | 3 | Arwiel | | NULL | | 2019-07-25 15:32:54 | | 4 | Jenifor | NULL | NULL | | 2019-07-25 15:32:54 | | 5 | NULL | NULL | NULL | | 2019-07-25 15:34:29 | +----+---------+---------+------+---------+---------------------+ 5 rows in set (0.00 sec) mysql> select distinct(phone) from mytest_null_tbl; +---------+ | phone | +---------+ | 9299008 | | 0 | | | | NULL | +---------+ 4 rows in set (0.00 sec)
In the data tested, distinct(phone) and group by phone yielded the same results.
In ORDER BY operation, NULL value exists as the minimum value, ASC ranks first, DESC ranks last.
mysql> update mytest_null_tbl set age=20 where id=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update mytest_null_tbl set age=12 where id=5; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from mytest_null_tbl; +----+---------+---------+------+---------+---------------------+ | id | name | phone | age | address | create_time | +----+---------+---------+------+---------+---------------------+ | 1 | Lucy | 9299008 | 18 | | 2019-07-25 15:32:54 | | 2 | Taylor | 0 | NULL | | 2019-07-25 15:32:54 | | 3 | Arwiel | | 20 | | 2019-07-25 15:50:04 | | 4 | Jenifor | NULL | NULL | | 2019-07-25 15:32:54 | | 5 | NULL | NULL | 12 | | 2019-07-25 15:50:14 | +----+---------+---------+------+---------+---------------------+ 5 rows in set (0.00 sec) mysql> select * from mytest_null_tbl order by age; +----+---------+---------+------+---------+---------------------+ | id | name | phone | age | address | create_time | +----+---------+---------+------+---------+---------------------+ | 2 | Taylor | 0 | NULL | | 2019-07-25 15:32:54 | | 4 | Jenifor | NULL | NULL | | 2019-07-25 15:32:54 | | 5 | NULL | NULL | 12 | | 2019-07-25 15:50:14 | | 1 | Lucy | 9299008 | 18 | | 2019-07-25 15:32:54 | | 3 | Arwiel | | 20 | | 2019-07-25 15:50:04 | +----+---------+---------+------+---------+---------------------+ 5 rows in set (0.00 sec) mysql> select * from mytest_null_tbl order by age desc; +----+---------+---------+------+---------+---------------------+ | id | name | phone | age | address | create_time | +----+---------+---------+------+---------+---------------------+ | 3 | Arwiel | | 20 | | 2019-07-25 15:50:04 | | 1 | Lucy | 9299008 | 18 | | 2019-07-25 15:32:54 | | 5 | NULL | NULL | 12 | | 2019-07-25 15:50:14 | | 2 | Taylor | 0 | NULL | | 2019-07-25 15:32:54 | | 4 | Jenifor | NULL | NULL | | 2019-07-25 15:32:54 | +----+---------+---------+------+---------+---------------------+ 5 rows in set (0.00 sec)
6. Statistics and Computation
For MySQL statistical and computational functions, NULL values are treated differently depending on the purpose of implementation.
For COUNT(), MIN(), SUM(), NULL is invalid and neglected in calculation.
COUNT(*) is a special case. It calculates all rows, even if there is a NULL value, it should pay attention to this when using it.
mysql> select * from mytest_null_tbl; +----+---------+---------+------+---------+---------------------+ | id | name | phone | age | address | create_time | +----+---------+---------+------+---------+---------------------+ | 1 | Lucy | 9299008 | 18 | | 2019-07-25 15:32:54 | | 2 | Taylor | 0 | NULL | | 2019-07-25 15:32:54 | | 3 | Arwiel | | 20 | | 2019-07-25 15:50:04 | | 4 | Jenifor | NULL | NULL | | 2019-07-25 15:32:54 | | 5 | NULL | NULL | 12 | | 2019-07-25 15:50:14 | +----+---------+---------+------+---------+---------------------+ 5 rows in set (0.00 sec) mysql> select count(*) from mytest_null_tbl; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> select count(age) from mytest_null_tbl; +------------+ | count(age) | +------------+ | 3 | +------------+ 1 row in set (0.00 sec) mysql> select min(age) from mytest_null_tbl; +----------+ | min(age) | +----------+ | 12 | +----------+ 1 row in set (0.02 sec) mysql> select sum(age) from mytest_null_tbl; +----------+ | sum(age) | +----------+ | 50 | +----------+ 1 row in set (0.00 sec)
7. Summary
1) NULL only supports IS NULL, IS NOT NULL, IFNULL() operations;
2) The results of NULL for mathematical comparison operators (>, =, <=, <>) are FALSE.
3) Indexed columns are allowed to exist NULL;
4) In DISTINCT, GROUP BY and ORDER BY, all NULL values are equal.
5) ORDER BY considers NULL to be the minimum value;
6) MIN(), SUM(), COUNT() will ignore NULL value in operation, but COUNT(*) will not be ignored;
7) When a field of TIMESTAMP type is inserted into NULL, the actual time written to the table is the current time.
8) When the field of AUTO_INCREMENT attribute is inserted into NULL, what is actually written into the table is the next self-increment in order.
9) If you want to prohibit a field from being set to NULL, set the NOT NULL attribute to that field.
10) If not necessary, do not use NULL, which will cause unexpected trouble.