如何在一张ID连续的表中找寻出缺失的ID

如何在一张ID连续的表中找寻出缺失的ID？

首先要先判断表中的ID是否有缺失？
如果表中的数量(count(*))不等于应存在的ID数(MAX( id ) - MIN( id ) + 1表示应该有这么多条记录)，那么就是有缺失的ID。
判断是否缺失ID的SQL：

SELECT
CASE WHEN   COUNT(*) = 0 THEN   '表为空' 
         WHEN COUNT( * ) <> MAX( id ) - MIN( id ) + 1 THEN '存在缺失的编号'
         ELSE '连续' 
         END AS gap 
FROM
    admin_user;

判断是否连续的查询结果.png

查询表中的id详情。

查询表中的id详情.png

查询出来表中有缺失的ID,怎么把缺失的ID查询出来？
方法1，查询出缺失ID的SQL如下：

##不连续的ID的特征，相邻的两个ID相减大于1。
##利用id-1在自身中找，找不到补空的记录即为要找的记录，去除掉最小的id（最小id-1是0，一般id不为0）。
SELECT
    a.id - 1 as 缺失的id
FROM
    admin_user a
    LEFT JOIN admin_user b ON b.id = a.id - 1 
WHERE
    b.id IS NULL 
    AND a.id <> ( SELECT min(id) FROM admin_user );

缺失ID查询结果1.png

看着结果对比表中的id详情，发现查的不够完善，表中的id缺少的是6,7和14-28。查询结果显示的是7和28，还少6和14-27。

方法2，那再换一种SQL写法，如下：

缺失ID查询结果2.png
再次对比表中的id，查询出来的结果是6和14，缺少7和15-28。

方法3，

/*逻辑：先用最大的ID号减去总的记录条数，获取缺失的记录条数，按“缺失的记录条数+1”,作为分组大小CNT，如果当前组的数量小于CNT,那么则显示以下信息：
1、当前段起始编号
2、当前段结束编号
3、每段总个数
4、当前段缺失ID个数
5、当前段拥有的编号
缺点：不能直接得出缺失的ID号，需要肉眼观察，建议在缺失量小于10的时候使用。*/

SELECT
 CEIL(id / X.cnt) * X.cnt - X.cnt + 1 AS '当前段起始编号',
 (CEIL(id / X.cnt)) * X.cnt AS '当前段结束编号',
 X.cnt AS '每段总个数',
 X.cnt - COUNT(*) AS '当前段缺失ID个数',
 GROUP_CONCAT(DISTINCT id ORDER BY id ASC SEPARATOR "," ) AS '当前段拥有编号'
FROM admin_user
JOIN (SELECT max(id) - COUNT(*) + 1 AS cnt FROM admin_user) AS X
GROUP BY CEILING(id/ X.cnt)
HAVING  COUNT(*) < (SELECT max(id) - COUNT(*) + 1 AS cnt FROM admin_user );

查询出的结果如图：

缺失ID查询结果3.png

如果数据特别多的话，这种方法不太友好，不可能把缺失的一个个的罗列出来。

方法4，存储过程的处理方法，写一个存储过程，获取最大的ID与最小的ID，然后从小到大遍历，把查询结果为null的ID,拼接到结果数据里，最后输出。

存储过程代码如下（入参是表名和列名）：

CREATE DEFINER = CURRENT_USER PROCEDURE `FindLessId`(IN `tbName` varchar(50),IN `colName` varchar(50))
BEGIN
    DECLARE SQL_TEXT1 VARCHAR(500);
    DECLARE SQL_TEXT2 VARCHAR(500);
    DECLARE SQL_TEXT3 VARCHAR(500);
 
    DECLARE LESS_ID_ARRAY TEXT;
    DECLARE nStart INT;
        DECLARE nEnd INT;
    DECLARE nId INT;        
 
    SET SQL_TEXT1  = CONCAT('SELECT min(',colName,') into @numStart From ',tbName);
  SET SQL_TEXT2  = CONCAT('SELECT max(',colName,') into @numEnd From ',tbName);
 
  SET @tmp = CONCAT(SQL_TEXT1);
    PREPARE stmt FROM @tmp ;
  EXECUTE stmt;
    SET nStart=  @numStart; 
  DEALLOCATE PREPARE stmt;
 
  SET @tmp = CONCAT(SQL_TEXT2);
  PREPARE stmt FROM @tmp ;
  EXECUTE stmt;
  SET nEnd=  @numEnd;
  DEALLOCATE PREPARE stmt;
 
  SET LESS_ID_ARRAY = '';
  IF nStart is not NULL AND nEnd IS NOT NULL THEN 
        WHILE nStart < nEnd DO
            SET SQL_TEXT3 =CONCAT('SELECT ',colName,' into @numId FROM ',tbName,' WHERE ',colName,'=',nStart,' limit 1;');
 
            SET @tmp = CONCAT(SQL_TEXT3);
            PREPARE stmt FROM @tmp ;
            EXECUTE stmt;
            
            IF (ISNULL(@numId)) THEN
                IF LESS_ID_ARRAY <>  '' THEN
                    SET LESS_ID_ARRAY = CONCAT(LESS_ID_ARRAY,',',nStart);
                ELSE
                    SET LESS_ID_ARRAY = CONCAT(LESS_ID_ARRAY,nStart);
                    END IF;
            END IF;
 
            DEALLOCATE PREPARE stmt;
 
            SET @numId = NULL;
            SET nStart= nStart+1;
        END WHILE;
        SELECT LESS_ID_ARRAY;
    end if;
END

这里用的是Mysql，直接用的Navicat建的存储过程。

缺失ID查询结果3.png

总结：
1.缺少一条记录或不连续缺失时，方法1和方法2适用。
2.连续缺失量比较少的时候，方法3适用。
3.无论是否连续缺失时，方法4都适用。