我们如何在 MySQL 中找到序列编号的空白？

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

将这个表连接到您的 arrc _  购买者表，并选择 arrc _ 购买者 id 为 null 的单个列值。

这应该会奏效:

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

一个简单快捷的查询应该可以解决这个问题:

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

这将给出一个表格，显示上面缺少 身份证的 身份证，以及存在的 下一个 _ id，以及在... ... 之间缺少多少... ... 例如,

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

更好的答案

ConfexianMJS 在性能方面提供了 一个更好的回答。

答案(不尽快)

下面是一个适用于任何大小(不仅仅是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

• 电流间隙中的第一个 id
• 电流间隙中的最后一个 ID

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

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 的规划程序处理的。

这将返回每个 entralValue (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和最大值之间的值

所以基本思想是对同一个表执行右和左连接，看看是否每个值都有相邻的值(例如，如果中心值是“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. 我过滤的零中心值，因为我们不感兴趣的任何以前的值，我们可以从下一行的岗位值。

尽管所有这些似乎都可以工作，但是当有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;

基于 Matt 的回答，这个存储过程允许您指定您希望测试的表和列名来查找非连续记录——从而回答了最初的问题，并演示了如何使用@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

这只是让我找到了一个行超过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 脚本任务，也可以使用此变体直接生成可以在 巴斯中迭代的表达式。

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。

如果您使用的是 MariaDB数据库，那么使用 顺序存储引擎顺序存储引擎有一个更快(800%)的选项:

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

如果在两个数字之间有一个最大间隙的序列(如 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

我用不同的方式尝试了 ，我发现最好的性能是这个简单的查询:

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
;

... 一个左联接来检查下一个 身份证是否存在，只有当下一个 if 没有找到时，子查询才会找到下一个存在的 id 来查找间隙的结尾。我这样做是因为使用 等于(=)的查询比使用 < strong > 大于 (>)操作符的查询性能更好。

使用 小提琴与其他查询相比，它并没有显示出如此不同的性能，但是在实际的数据库中，上述查询的结果比其他查询快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

这可能是不相关的，但我正在寻找这样的东西，以列出数字序列中的差距，并发现这篇文章有多个不同的解决方案，这取决于你正在寻找的东西。我正在寻找序列中第一个可用的间隙(即，下一个可用的数字) ，这似乎工作得很好。

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 查找序列中缺少的值

一个简单而有效的方法可以找到缺失的自动增量值:

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

另一个简单的答案，确定差距。我们执行一个查询，只选择奇数，然后将其连接到一个包含所有偶数的查询。只要您没有遗漏 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;

从 User933161发表评论开始,

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这样的东西了解得不够多，所以无法想出办法来自动处理有越来越多间隔的大型集合。

这对我有用:

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;