擷取缺失的最小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`
	);
           

總結

軟體開發，知其然而不知其是以然，那就是在給自己挖坑。