在mysql中查询一个表中1至10000当中不存在的id示例

数据库  /  管理员 发布于 1年前   989

今天有个需求：在mysql查找一个表中从id为1至n的数据中不存在的id。

我感觉挺有用的，记录之。

sql语句编写思路：

首先使用 UNION 连接生成 1 至 10000 的数字序列，然后使用 LEFT JOIN 连接原表，得到原表中存在的 id，最后通过 WHERE 子句找到不存在的 id。

比如：

SELECT n
FROM (
  SELECT 1 AS n
  UNION SELECT 2
  UNION SELECT 3
  -- 依次类推，直到 10000
  UNION SELECT 10000
) AS t
LEFT JOIN 表名 ON t.n = 表名.id
WHERE 表名.id IS NULL;

注意：

如果原表中的 id 范围比较大，例如 1 至 1000000，那么手动编写 UNION 语句会比较麻烦，

可以考虑使用其他方法生成数字序列，例如使用 MySQL 内置函数生成数字序列。

下面就是使用MySQL内置函数生成数字序列示例：

SELECT n
FROM (
  SELECT @row := @row + 1 AS n
  FROM 表名, (SELECT @row := 0) AS t
  LIMIT 10000
) AS t
LEFT JOIN 表名 ON t.n = 表名.id
WHERE 表名.id IS NULL;

ps:

以上 SQL 语句使用 MySQL 的变量和内置函数生成数字序列，可以根据需要调整 LIMIT 子句的参数来设置数字范围。

测试示例操作：

我的一个文章表，里面大概有10万数据，中间有很多不存在的id,现在测试用上面的sql查出来。

测试表art:

CREATE TABLE `art` (
`art_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL DEFAULT '' COMMENT '标题',
PRIMARY KEY (`art_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=96343 DEFAULT CHARSET=utf8;

查询不存在id的sql:

SELECT
    n 
FROM
    ( 
    SELECT @ROW := @ROW + 1 AS n FROM art, ( SELECT @ROW := 0 ) AS t LIMIT 10000 ) AS t
    LEFT JOIN art ON t.n = art.art_id 
WHERE
art.art_id IS NULL;

看看效果：

执行sql

有兴趣的可以对比一下，需要的拿去用。