获取缺失的最小id用于插入下一条数据（mysql）

获取缺失的最小id用于插入下一条数据

文章目录

• 获取缺失的最小id用于插入下一条数据
• • 需求
  • 实现
  • 测试
  • • 1. 创建测试数据
    • 2. 执行目标语句
    • 3. 修改语句满足需求
  • 分析
  • • 1. 笛卡尔积
    • 2. WERE语句
    • 3. GROUP 语句
  • 错误
  • 优化
  • 总结

需求

工作中需要给设备添加编号，但是位数比较少。

编号有可能经常的批量的添加和删除，使用单纯的自增值作为主键，

有限的编号位数很快会用完，所以需要找到已经删除的ID，

使用空缺的ID进行插入。

实现

百度一下就找到一个帖子：

MySQL 如何快速查出缺失数据的主键 id https://blog.csdn.net/qq_26249609/article/details/95503685

摘录其的sql语句

SELECT a.id+1 AS START, MIN(b.id) - 1 AS END
    FROM limit_up_predict AS a, limit_up_predict AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING START < MIN(b.id)
           

测试

1. 创建测试数据

2. 执行目标语句

SELECT a.id+1 AS START, MIN(b.id) - 1 AS END
    FROM limit_up_predict AS a, limit_up_predict AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING START < MIN(b.id)
           

结果是2-2,4-6,9-9三个空缺的ID段落

3. 修改语句满足需求

SELECT a.id+1 AS `next_id`
    FROM limit_up_predict AS a, limit_up_predict AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING `next_id` < MIN(b.id)
    ORDER BY `next_id`
    LIMIT 1
           

很随意，需求完成。（简单一分析，啪啪打脸）

分析

1. 笛卡尔积

2. WERE语句

SELECT * FROM limit_up_predict AS a, limit_up_predict AS b 
WHERE a.id < b.id;
           

3. GROUP 语句

SELECT a.id,MIN(b.id),MAX(b.id),COUNT(b.id) FROM limit_up_predict AS a, limit_up_predict AS b WHERE a.id < b.id GROUP BY a.id
           

基本明了，就是找出比自己大的ID中最小.

错误

简单分析一下果然是存在问题，立马发现错误

1. 改造语句获得的最小ID，肯定比最小的ID要大，
2. 空表获得不到ID
3. 没有空缺获得不到ID

优化

SELECT
    MIN(`n`.`next_id`) as `next_id`
FROM
    (
	    SELECT 1 AS `next_id`
	    UNION
		SELECT `t1`.`id`+1 AS `next_id` FROM `target_table` `t1`
	) AS `n`
WHERE 
	NOT EXISTS
	(
	    SELECT
	        `t2`.`id`
	    FROM
	        `target_table` `t2`
	    WHERE
	        `t2`.`id` = `n`.`next_id`
	);
           

总结

软件开发，知其然而不知其所以然，那就是在给自己挖坑。