A small problem about null values ​​in MySQL

Today, when testing the null value, I found a small problem. I recorded it here. I wonder if you have encountered it before.

Here's what's going on. When filtering the values ​​in a table, you need to remove the column with age=2 and then view the remaining column information. This operation seems relatively simple. I'll use a table to simulate the process:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `score` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_score` (`score`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

The table structure is as above. The id and score fields are both subject to the not null constraint, but the age field is not subject to this constraint. We will insert some data as follows:

mysql:yeyztest 23:32:57>>select * from test;
+----+------+-------+
| id | age | score |
+----+------+-------+
| 1 | 1 | 5 |
| 2 | 2 | 10 |
| 5 | 5 | 25 |
| 8 | 8 | 40 |
| 9 | 2 | 45 |
| 10 | 5 | 50 |
| 11 | 8 | 55 |
+----+------+-------+
7 rows in set (0.00 sec)

The current set of data is complete, and each field has a value. Then we use the following SQL statement to query the specified record:

select * from test where id ! = 2;

We look at the results:

mysql:yeyztest 23:33:14>>select * from test where age!=2;
+----+------+-------+
| id | age | score |
+----+------+-------+
| 1 | 1 | 5 |
| 5 | 5 | 25 |
| 8 | 8 | 40 |
| 10 | 5 | 50 |
| 11 | 8 | 55 |
+----+------+-------+
5 rows in set (0.00 sec)

At this point we insert two records:

mysql:yeyztest 23:33:17>>insert into test (id,score) values ​​(12,60);
Query OK, 1 row affected (0.00 sec)

mysql:yeyztest 23:34:02>>insert into test (id,score) values ​​(13,65);
Query OK, 1 row affected (0.00 sec)

mysql:yeyztest 23:34:10>>select * from test;
+----+------+-------+
| id | age | score |
+----+------+-------+
| 1 | 1 | 5 |
| 2 | 2 | 10 |
| 5 | 5 | 25 |
| 8 | 8 | 40 |
| 9 | 2 | 45 |
| 10 | 5 | 50 |
| 11 | 8 | 55 |
| 12 | NULL | 60 |
| 13 | NULL | 65 |
+----+------+-------+
9 rows in set (0.00 sec)

Use the above statement to query again, and you can see the results as follows:

mysql:yeyztest 23:34:15>>select * from test where age!=2;
+----+------+-------+
| id | age | score |
+----+------+-------+
| 1 | 1 | 5 |
| 5 | 5 | 25 |
| 8 | 8 | 40 |
| 10 | 5 | 50 |
| 11 | 8 | 55 |
+----+------+-------+
5 rows in set (0.00 sec)

That is to say, when the record contains a null value, use reverse matching age! =2 is not enough to get the full query results, which is obviously not in line with our expectations.

In fact, this problem has been mentioned in the previous article. In a data record, the null value field and the general field are not stored together. The null value field is stored in the null value list. This causes mismatches during retrieval. This is a relatively important point and I hope it will be useful to everyone.

For the record, the test environment is MySQL version 5.7.16.

The above is the details of a small problem about null values ​​in MySQL. For more information about MySQL null values, please pay attention to other related articles on 123WORDPRESS.COM!