mysql - SQL - 获取不存在的行

Question

标题可能听起来很奇怪，但我会尽可能简单地解释问题。让我们从例子开始。我有一个包含 2 列的表 - id，ip。假设我有 3 行，id 为 2、3、5。现在，我需要获取不在 id 1 和 5 之间的任何行，这显然是 1 和 4。目前我遇到了这个查询:

SELECT * 
FROM `votes` 
WHERE ip = "1.1.1.1."  
AND question_id BETWEEN 1 AND 5

Answer 1

听起来很奇怪，但这是许多人所做的。

创建一个帮助表。将其用于左连接

create table amfn
(   -- All My Favorite Numbers  
    id int auto_increment primary key,
    theWhat char(1) null
)engine=MyIsam;   --  <----- somewhat important

insert amfn(theWhat) values (null),(null),(null),(null),(null),(null),(null),(null),(null),(null); -- 10
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;

select count(*),min(id),max(id) from amfn;
+----------+---------+---------+
| count(*) | min(id) | max(id) |
+----------+---------+---------+
|  1310720 |       1 | 1310720 |
+----------+---------+---------+
1 row in set (0.00 sec)

您的架构:

create table votes
(   question_id int not null,
    ip varchar(20) not null
);
insert votes (question_id,ip) values (1,'xxxx'),(2,'1.1.1.1'),(3,'1.1.1.1'),(4,'1.6.1.1'),(5,'1.1.1.1');

查询:

select a.id,v.question_id,v.ip
from amfn a
left join votes v
on v.question_id=a.id and v.ip='1.1.1.1'
where a.id between 1 and 5 and v.question_id is null;
+----+-------------+------+
| id | question_id | ip   |
+----+-------------+------+
|  1 |        NULL | NULL |
|  4 |        NULL | NULL |
+----+-------------+------+
2 rows in set (0.00 sec)     <------------- boy that is fast

编辑(向 Conrad Frix 显示时差)。

我上面的方法创建了 5242880 行，23.5 秒。 Conrad 的方法，168.5 秒。我会坚持我的方法:>