MySQL中null值的一個小坑

  • 2019 年 11 月 6 日
  • 筆記

01、MySQL中null值的一個小坑

今天在測試null值的時候，發現了一個小問題，記錄在這裡，不知道大家以前遇到過沒。

事情發展是這樣的，在過濾一個表中的數值的時候，需要把age=2的列給剔除掉，然後查看剩餘的列資訊，這個操作看起來比較簡單，我用一個表模擬一下過程：

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

表結構如上，其中id和score都加了not null的限制，而age欄位沒有添加這個約束，然後我們先插入一部分數據，如下：

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)

當前這組數據是全量的，每個欄位都有值，然後我們使用下面的SQL語句來查詢制定記錄：

select * from test where id ！= 2；

我們查看結果：

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)

此時我們插入兩條記錄進去：

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)

再次使用上面的語句進行查詢，可以看到結果如下：

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)

也就是說，當記錄中包含null值的時候，使用反向匹配age！=2是無法得到全量的查詢結果的，這明顯不符合我們的預期。

其實這個問題，在之前的4月29號的文章中有說到過，就是在一條數據記錄裡面，null值欄位和一般的欄位是不在一起存儲的，null值欄位是存儲在null值列表裡面的。所以造成了檢索時候不匹配的現象，這個還是比較重要的一個點，希望對大家有用。

聲明一下，測試環境是5.7.16版本的MySQL。

Tags:

VirMach 便宜 VPS