logo标签列表

从MySQL表中获取缺失的ID

mysql
46

我在MySQL中有这个表，举例来说：

ID | Name
1  | Bob
4  | Adam
6  | Someguy

如果您注意到，没有ID编号（2、3和5）。

我该如何编写查询，使MySQL仅回答缺失的ID，例如："2,3,5"？

8个回答
52
这太聪明了！谢谢。 - McRui
34

一条更有效率的查询：

SELECT (t1.id + 1) as gap_starts_at, 
       (SELECT MIN(t3.id) -1 FROM my_table t3 WHERE t3.id > t1.id) as gap_ends_at
FROM my_table t1
WHERE NOT EXISTS (SELECT t2.id FROM my_table t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL
谢谢Ivan。这个运行速度快多了！ - MikeC
1
这对我起作用，但是它错过了从id = 1开始的初始间隙。 - egprentice
MySQL返回了一个空结果集（即零行）。 （查询花费60.3183秒。）
  • 检查超过200万条记录花费了一分钟多一点的时间。
 - Faizan Anwer Ali Rupani
你能调整一下这个查询，使其返回N个缺失的ID吗？也就是说，我想检索前10个缺失的主键，按它们的值排序。不需要报告所有的间隙。 - temuri
9
与其返回多个ID范围，如果你想要检索每个缺失的ID本身，每一个都在自己的行中，你可以执行以下操作：
SELECT id+1 FROM table WHERE id NOT IN (SELECT id-1 FROM table) ORDER BY 1

这个查询非常高效。但是，它还包括一个额外的行，在末尾，它等于最高ID号加1。可以通过检查返回的行数（mysqli_num_rows）并在行数大于1时使用for循环来忽略服务器脚本中的最后一行（该查询将始终返回至少一行）。

编辑： 我最近发现原始解决方案没有返回所有缺失的ID号，对于缺失号码是连续的情况（即相邻的数字），这个解决方案仍然有用，可以快速地判断是否存在缺失的ID号码，并节省时间，如果与hagensoft的查询（排名第一的答案）一起使用，可以帮助识别确切的缺失ID号码（不会节省时间，但几乎不会变慢）。如果未发现任何内容，则可能节省了大量时间，因为无需运行hagensoft的查询。

2
非常简单，非常聪明，非常快。事实上，如果表太大，接受的版本在合理的时间内是不可行的（大约意味着200,000条记录）。 - giordano
这是一个惊人的结果。 :) - yodann
AND id != ( SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'my_db' AND TABLE_NAME = 'users' ) ORDER BY 1;``` - Waseem Almoliky
4
为了补充Ivan的回答，这个版本会显示缺失的数字，如果1不存在的话：
SELECT 1 as gap_starts_at,
       (SELECT MIN(t4.id) -1 FROM testtable t4 WHERE t4.id > 1) as gap_ends_at
FROM testtable t5
WHERE NOT EXISTS (SELECT t6.id FROM testtable t6 WHERE t6.id = 1)
HAVING gap_ends_at IS NOT NULL limit 1
UNION
SELECT (t1.id + 1) as gap_starts_at, 
       (SELECT MIN(t3.id) -1 FROM testtable t3 WHERE t3.id > t1.id) as gap_ends_at
FROM testtable t1
WHERE NOT EXISTS (SELECT t2.id FROM testtable t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL;
4

在一次查询中获取间隙的起始点和在另一次查询中获取间隙的结束点将更加高效。

当我尝试在一个查询中同时获取这两个结果时，由于数据量达到了1800万条，查询超时了一小时。但是分别获取这两个结果，每个查询时间不到1秒。

获取间隙的起始点：

SELECT (t1.id + 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS 
    (SELECT t2.id 
    FROM sequence t2 
    WHERE t2.id = t1.id + 1);

获取间隙的末尾：
SELECT (t1.id - 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS 
    (SELECT t2.id 
    FROM sequence t2 
    WHERE t2.id = t1.id - 1);
1
只是澄清一下，我原本应该有1800万条记录。结果发现我的数据库中只有300万条记录，缺失了1500万条记录。 - James G
2
上述查询将给出两列，因此您可以尝试使用以下方法在单个列中获取丢失的数字。
select start from 
(SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM sequence AS a, sequence AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)) b
UNION
select c.end from (SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM sequence AS a, sequence AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)) c order by start;
通过这个单列版本，我得到（例如）475、477、506、508、513，但是使用双列版本，它给我 [475,475]、[477,506]、[508,513]，这告诉我我错过了 475、477-506 和 508-513 这些数字。 - Jo.
1
如果您想要一种更轻量级的方式来搜索数百万行数据，
SET @st=0,@diffSt=0,@diffEnd=0;
SELECT res.startID, res.endID, res.diff
  , CONCAT(
    "SELECT * FROM lost_consumer WHERE ID BETWEEN "
    ,res.startID+1, " AND ", res.endID-1) as `query`
FROM (
SELECT
  @diffSt:=(@st) `startID`
  , @diffEnd:=(a.ID) `endID`
  , @st:=a.ID `end`
  , @diffEnd-@diffSt-1 `diff`
  FROM consumer a 
ORDER BY a.ID
) res
WHERE res.diff>0;

看看这个http://sqlfiddle.com/#!9/3ea00c/9

1

通过使用 窗口函数（在mysql 8中可用），可以将在id列中找到的间隙表示为：

WITH gaps AS
(
    SELECT
        LAG(id, 1, 0) OVER(ORDER BY id) AS gap_begin,
        id AS gap_end,
        id - LAG(id, 1, 0) OVER(ORDER BY id) AS gap
    FROM test
)
SELECT
    gap_begin,
    gap_end
FROM gaps
WHERE gap > 1
;

如果您使用的是旧版的MySQL，您将不得不依赖于变量（所谓的“穷人窗口函数习惯用法”）。
SELECT
   gap_begin,
   gap_end
FROM (
     SELECT
         @id_previous AS gap_begin,
         id AS gap_end,
         id - @id_previous AS gap,
         @id_previous := id
     FROM (
         SELECT
             t.id
         FROM test t
         ORDER BY t.id
     ) AS sorted
     JOIN (
         SELECT
             @id_previous := 0
     ) AS init_vars
 ) AS gaps
WHERE gap > 1
;
1
非常好用（只试用了旧版本）。 - Sarp Başaraner
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接