我们如何找到 MySQL 中顺序编号的间隙？

Question

我们有一个数据库，其中有一个表，其值是从另一个系统导入的。有一个自增列，并且没有任何重复值，但有缺失值。例如，运行此查询：

select count(id) from arrc_vouchers where id between 1 and 100

应返回 100，但它返回 87。我可以运行一个查询来返回缺失数字的值吗？例如，可能存在 id 1-70 和 83-100 的记录，但没有任何 id 为 71-82 的记录。我想返回71、72、73等，

可以吗？

Answer 1

更好的答案

JustPlainMJS 提供了一个更好的答案表现。

（不是尽可能快）答案

这是一个适用于任何大小的表（不仅仅是 100 行）的版本：

SELECT (t1.id + 1) as gap_starts_at,
       (SELECT MIN(t3.id) -1 FROM arrc_vouchers t3 WHERE t3.id > t1.id) as gap_ends_at
FROM arrc_vouchers t1
WHERE NOT EXISTS (SELECT t2.id FROM arrc_vouchers t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL

• gap_starts_at - 当前间隙中的第一个 id
• gap_ends_at - 当前间隙中的最后一个 ID

Answer 2

这正好帮助我找到超过 80k 行的表中的间隙：

SELECT
  CONCAT(z.expected, IF(z.got-1>z.expected, CONCAT(' thru ',z.got-1), '')) AS missing
FROM (
  SELECT
    @rownum:=@rownum+1 AS expected,
    IF(@rownum=YourCol, 0, @rownum:=YourCol) AS got
  FROM
    (SELECT @rownum:=0) AS a
    JOIN YourTable
    ORDER BY YourCol
  ) AS z
WHERE z.got!=0;

结果：

+------------------+
| missing          |
+------------------+
| 1 thru 99        |
| 666 thru 667     |
| 50000            |
| 66419 thru 66456 |
+------------------+
4 rows in set (0.06 sec)

请注意，expected 和 got 列的顺序至关重要。

如果您知道 YourCol 不是从 1 开始，这并不重要，您可以替换

(SELECT @rownum:=0) AS a

为

(SELECT @rownum:=(SELECT MIN(YourCol)-1 FROM YourTable)) AS a

新结果：

+------------------+
| missing          |
+------------------+
| 666 thru 667     |
| 50000            |
| 66419 thru 66456 |
+------------------+
3 rows in set (0.06 sec)

如果您需要对丢失的 ID 执行某种 shell 脚本任务，您可以还可以使用此变体直接生成一个可以在 Bash 中迭代的表达式。

SELECT GROUP_CONCAT(IF(z.got-1>z.expected, CONCAT('$(',z.expected,' ',z.got-1,')'), z.expected) SEPARATOR " ") AS missing
FROM ( SELECT @rownum:=@rownum+1 AS expected, IF(@rownum=height, 0, @rownum:=height) AS got FROM (SELECT @rownum:=0) AS a JOIN block ORDER BY height ) AS z WHERE z.got!=0;

这会产生如下输出，然后

$(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456)

您可以将其复制并粘贴到 bash 终端中的 for 循环中，以便为每个 ID 执行命令

for ID in $(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456); do
  echo $ID
  # Fill the gaps
done

。这与上面的内容相同，只是它既可读又可执行。通过更改上面的“CONCAT”命令，可以生成其他编程语言的语法。或者甚至是 SQL。

Answer 3

如果您使用 MariaDB 数据库，则可以使用 < 更快 (800%) 的选项a href="https://mariadb.com/kb/en/mariadb/sequence/" rel="nofollow noreferrer">序列存储引擎：

SELECT * FROM seq_1_to_50000 WHERE SEQ NOT IN (SELECT COL FROM TABLE);

Answer 4

一个快速而肮脏的查询应该可以解决这个问题：

SELECT a AS id, b AS next_id, (b - a) -1 AS missing_inbetween
FROM
 (
SELECT a1.id AS a , MIN(a2.id) AS b
FROM arrc_vouchers  AS a1
LEFT JOIN arrc_vouchers AS a2 ON a2.id > a1.id
WHERE a1.id <= 100
GROUP BY a1.id
) AS tab

WHERE
b > a + 1

这将为您提供一个表格，显示上面缺少 ids 的 id 和 next_id em> 存在，以及之间缺少多少个...例如，

id  next_id  missing_inbetween
 1        4                  2
68       70                  1
75       87                 11

Answer 5

创建一个包含 100 行和一列（包含值 1-100）的临时表。

外部 将此表连接到您的 arrc_vouchers 表，并选择 arrc_vouchers id 为 null 的单列值。

这应该有效：

select tempid from temptable 
left join arrc_vouchers on temptable.tempid = arrc_vouchers.id 
where arrc_vouchers.id is null

Answer 6

如果存在一个序列，两个数字之间的间隙最大为 1（例如
1,3,5,6) 那么可以使用的查询是：

select s.id+1 from source1 s where s.id+1 not in(select id from source1) and s.id+1<(select max(id) from source1);

• table_name - source1
• column_name - id

Answer 7

另一种需要查询 + 一些代码执行某些处理的解决方案是：

select l.id lValue, c.id cValue, r.id rValue
  from
  arrc_vouchers l
  right join arrc_vouchers c on l.id=IF(c.id > 0, c.id-1, null)
  left  join arrc_vouchers r on r.id=c.id+1
where 1=1
  and c.id > 0
  and (l.id is null or r.id is null)
order by c.id asc;

请注意，查询不包含任何我们知道 MySQL 规划器无法高效处理的子选择。

这将返回每个centralValue (cValue) 的一个条目，该条目没有较小的值(lValue) 或较大的值(rValue)，即：

lValue |cValue|rValue
-------+------+-------
{null} | 2    | 3
8      | 9    | {null}
{null} | 22   | 23
23     | 24   | {null}
{null} | 29   | {null}
{null} | 33   | {null}

无需进一步详细说明（我们将在下一段中看到它们），此输出意味着：

• 0 到 2 之间没有值
• 9 到 22 之间没有值
• 24 到 29 之间没有
• 值 29 到 33 之间
• 没有值 33 到 MAX VALUE 之间没有值

所以基本的想法是对同一个表进行 RIGHT 和 LEFT 连接，看看我们是否可以每个值都有相邻值（即，如果中心值为“3”，则我们检查左侧是否有 3-1=2，右侧是否有 3+1），并且当 ROW 在右侧或左侧有 NULL 值时，我们就知道那里没有相邻值。

我的表的完整原始输出是：

select * from arrc_vouchers order by id asc;

0
2
3
4
5
6
7
8
9
22
23
24
29
33

一些注释：

1. 如果将“id”字段定义为 UNSIGNED，则需要连接条件中的 SQL IF 语句，因此它不允许您将其减少到零以下。如果您保持 c.value > 则这并不是绝对必要的。 0 正如下一个注释中所述，但我将其包含为 doc.
2. 我正在过滤零中心值，因为我们对任何先前的值都不感兴趣，并且我们可以从下一行导出后值。

Answer 8

我尝试了以不同的方式，我发现的最佳性能就是这么简单query:

select a.id+1 gapIni
    ,(select x.id-1 from arrc_vouchers x where x.id>a.id+1 limit 1) gapEnd
    from arrc_vouchers a
    left join arrc_vouchers b on b.id=a.id+1
    where b.id is null
    order by 1
;

... 一个左连接检查下一个id是否存在，只有如果没有找到下一个if，则子查询查找存在的下一个id以找到间隙的末尾。我这样做是因为使用等于 (=) 的查询比大于 (>) 的查询性能更好操作员。

使用 sqlfiddle 与其他查询相比，它没有显示出不同的性能，但是在真实的数据库中，上面的查询结果比其他查询快 3 倍。

架构：

CREATE TABLE arrc_vouchers (id int primary key)
;
INSERT INTO `arrc_vouchers` (`id`) VALUES (1),(4),(5),(7),(8),(9),(10),(11),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29)
;

遵循下面我为比较性能而进行的所有查询：

select a.id+1 gapIni
    ,(select x.id-1 from arrc_vouchers x where x.id>a.id+1 limit 1) gapEnd
    from arrc_vouchers a
    left join arrc_vouchers b on b.id=a.id+1
    where b.id is null
    order by 1
;
select *, (gapEnd-gapIni) qt
    from (
        select id+1 gapIni
        ,(select x.id from arrc_vouchers x where x.id>a.id limit 1) gapEnd
        from arrc_vouchers a
        order by id
    ) a where gapEnd <> gapIni
;
select id+1 gapIni
    ,(select x.id from arrc_vouchers x where x.id>a.id limit 1) gapEnd
    #,coalesce((select id from arrc_vouchers x where x.id=a.id+1),(select x.id from arrc_vouchers x where x.id>a.id limit 1)) gapEnd
    from arrc_vouchers a
    where id+1 <> (select x.id from arrc_vouchers x where x.id>a.id limit 1)
    order by id
;
select id+1 gapIni
    ,coalesce((select id from arrc_vouchers x where x.id=a.id+1),(select x.id from arrc_vouchers x where x.id>a.id limit 1)) gapEnd
    from arrc_vouchers a
    order by id
;
select id+1 gapIni
    ,coalesce((select id from arrc_vouchers x where x.id=a.id+1),concat('*** GAT *** ',(select x.id from arrc_vouchers x where x.id>a.id limit 1))) gapEnd
    from arrc_vouchers a
    order by id
;

您可以使用此 SQL Fiddle：

http://sqlfiddle.com/#!9/ 6bdca7/1

Answer 9

它可能不相关，但我一直在寻找类似的东西来列出数字序列中的间隙，并发现这篇文章有多种不同的解决方案，具体取决于您正在寻找的内容。我正在寻找序列中的第一个可用间隙（即下一个可用数字），这似乎工作正常。

SELECT MIN(l.number_sequence + 1) as nextavabile
from patients as l
LEFT OUTER JOIN patients as r on l.number_sequence + 1 = r.number_sequence
WHERE r.number_sequence is NULL

从 2005 年开始，那里讨论了其他几个场景和解决方案！

如何查找使用 SQL 的序列中缺少值

Answer 10

一个简单但有效的解决方案来查找丢失的自动增量值：

SELECT `id`+1
FROM `table_name`
WHERE `id`+1 NOT IN (SELECT id FROM table_name)

Answer 11

虽然这些看起来都有效，但是当有 50,000 条记录时，结果集返回的时间非常长。

我使用了这个，它找到了间隙或下一个可用的（上次使用的 + 1），并且从查询中返回的速度要快得多。

SELECT a.id as beforegap, a.id+1 as avail
FROM table_name a
where (select b.id from table_name b where b.id=a.id+1) is null
limit 1;

Answer 12

基于马特给出的答案，此存储过程允许您指定要测试以查找非连续记录的表和列名称 - 从而回答原始问题并演示如何使用 @var 来表示表和/或列存储过程。

create definer=`root`@`localhost` procedure `spfindnoncontiguous`(in `param_tbl` varchar(64), in `param_col` varchar(64))
language sql
not deterministic
contains sql
sql security definer
comment ''
begin
declare strsql varchar(1000);
declare tbl varchar(64);
declare col varchar(64);

set @tbl=cast(param_tbl as char character set utf8);
set @col=cast(param_col as char character set utf8);

set @strsql=concat("select
    ( t1.",@col," + 1 ) as starts_at,
  ( select min(t3.",@col,") -1 from ",@tbl," t3 where t3.",@col," > t1.",@col," ) as ends_at
    from ",@tbl," t1
        where not exists ( select t2.",@col," from ",@tbl," t2 where t2.",@col," = t1.",@col," + 1 )
        having ends_at is not null");

prepare stmt from @strsql;
execute stmt;
deallocate prepare stmt;
end

Answer 13

另一个简单的答案可以找出差距。我们执行仅选择奇数的查询，然后将其右连接到包含所有偶数的查询。只要你没有丢失 id 1;这应该会给你一个完整的列表，说明差距从哪里开始。

您仍然需要查看数据库中的该位置来找出差距有多少个数字。我发现这种方法比建议的解决方案更容易，并且更容易根据独特的情况进行定制。

SELECT *
FROM (SELECT * FROM MyTABLE WHERE MYFIELD % 2 > 0) AS A
RIGHT JOIN FROM (SELECT * FROM MyTABLE WHERE MYFIELD % 2 = 0) AS B
ON A.MYFIELD=(B.MYFIELD+1)
WHERE a.id IS NULL;

Answer 14

这对我有用：

SELECT distinct(l.membership_no + 1) as nextavabile
from Tablename as l
LEFT OUTER JOIN Tablename as r on l.membership_no + 1 = r.membership_no
WHERE r.membership_no is NULL and l.membership_no is not null order by nextavabile asc;

Answer 15

从用户933161发布的评论，

select l.id + 1 as start from sequence as l inner join sequence as r on l.id + 1 = r.id where r.id is null;

更好的是它不会对记录列表的末尾产生误报。 （我不确定为什么这么多人使用左外连接。）
另外，

insert into sequence (id) values (#);

如果 # 是间隙的起始值，则将填充该起始值。 （如果某些字段不能为空，则必须添加具有虚拟值的字段。）

您可以交替查询起始值和填充每个起始值，直到起始值查询返回空集。

当然，只有当您使用足够小的数据集并且手动迭代是合理的时，这种方法才有用。我对 phpMyAdmin 这样的事情了解不够，无法想出自动化更大的方法设置有更多更大的间隙。

Answer 16

CREATE TABLE arrc_vouchers (id int primary key);
INSERT INTO `arrc_vouchers` (`id`) VALUES (1),(4),(5),(7),(8),(9),(10),(11),(15),(16);

WITH RECURSIVE odd_num_cte (id) AS  
(  
    SELECT (select min(id) from arrc_vouchers)   
    union all  
    SELECT id+1 from odd_num_cte where id <(SELECT max(id) from arrc_vouchers)   
)  
SELECT cte.id 
from arrc_vouchers ar right outer join odd_num_cte cte on ar.id=cte.id
where ar.id is null;