MySQL 5.1中IN查询不要用到NULL条件

原文见:http://pento.net/2009/04/08/dont-put-a-null-in-the-in-clause-in-51/

如果在 IN 语句中用到 NULL 条件, 会导致全表扫描:

CREATE TABLE foo (
a INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (a)
);

来看看2次查询的区别:

mysql> EXPLAIN * FROM foo WHERE a IN (160000, 160001, 160002)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.06 sec)
mysql> EXPLAIN SELECT * FROM foo WHERE a IN (NULL, 160000, 160001, 160002)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 327680
Extra: Using where
1 row in set (0.00 sec)

 

再加上之前其他同学测试出来的问题:MySQL 5.1 中 Innodb 的事务完整性Bug,看来mysql5.1还是不太靠谱啊,如果不是迫不得已,还是少用吧 :)

技术相关: 

评论

好像正常的呀! 你看我的Explain输出,我用的mysql版本是5.1.31-community-Windows
EXPLAIN SELECT * FROM foo WHERE a IN (NULL, 160000, 160001, 160002)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
Extra: Using where; Using index
1 row in set (0.00 sec)
******************************* ***************************
+------------------+
| version() |
+------------------+
| 5.1.31-community |
+------------------+