我有强迫症：Z-Blog 博客主键 ID 复用指南

-- ============================================
-- Z-Blog 主键 ID 复用方案
-- 适用于 MySQL 5.x（不支持递归CTE的版本）
-- ============================================

-- 第一步：创建数字辅助表
DROP TABLE IF EXISTS numbers;
CREATE TABLE numbers (n INT PRIMARY KEY);

-- 第二步：创建存储过程，填充数字表
DELIMITER $$
CREATE PROCEDURE fill_numbers(IN max_num INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < max_num DO
    INSERT IGNORE INTO numbers VALUES (i+1);
    SET i = i + 1000;
    INSERT IGNORE INTO numbers VALUES 
      (i),(i+1),(i+2),(i+3),(i+4),(i+5),(i+6),(i+7),(i+8),(i+9),
      (i+10),(i+11),(i+12),(i+13),(i+14),(i+15),(i+16),(i+17),(i+18),(i+19),
      (i+20),(i+21),(i+22),(i+23),(i+24),(i+25),(i+26),(i+27),(i+28),(i+29),
      (i+30),(i+31),(i+32),(i+33),(i+34),(i+35),(i+36),(i+37),(i+38),(i+39),
      (i+40),(i+41),(i+42),(i+43),(i+44),(i+45),(i+46),(i+47),(i+48),(i+49),
      (i+50),(i+51),(i+52),(i+53),(i+54),(i+55),(i+56),(i+57),(i+58),(i+59),
      (i+60),(i+61),(i+62),(i+63),(i+64),(i+65),(i+66),(i+67),(i+68),(i+69),
      (i+70),(i+71),(i+72),(i+73),(i+74),(i+75),(i+76),(i+77),(i+78),(i+79),
      (i+80),(i+81),(i+82),(i+83),(i+84),(i+85),(i+86),(i+87),(i+88),(i+89),
      (i+90),(i+91),(i+92),(i+93),(i+94),(i+95),(i+96),(i+97),(i+98),(i+99);
    SET i = i + 900;
  END WHILE;
END$$
DELIMITER ;

-- 第三步：填充数字表（替换为你的最大ID）
-- 先查一下最大ID：SELECT MAX(log_ID) FROM zbp_post;
CALL fill_numbers(15000000);

-- 第四步：创建可复用ID存储表
DROP TABLE IF EXISTS zbp_delete;
CREATE TABLE zbp_delete (
  id INT PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 第五步：用 LEFT JOIN 找出所有缺失的ID（最关键的一步！）
INSERT INTO zbp_delete (id)
SELECT n.n 
FROM numbers n
LEFT JOIN zbp_post p ON n.n = p.log_ID
WHERE p.log_ID IS NULL;

-- 第六步：查看结果
SELECT COUNT(*) AS '可复用ID数量' FROM zbp_delete;
SELECT * FROM zbp_delete ORDER BY id LIMIT 100;

可复用ID数量: 1192278

-- MySQL 8.0+ 递归CTE方案（更简洁，但不兼容低版本）
WITH RECURSIVE number_sequence AS (
  SELECT 1 AS id
  UNION ALL
  SELECT id + 1 FROM number_sequence 
  WHERE id < (SELECT MAX(log_ID) FROM zbp_post)
)
SELECT id FROM number_sequence
WHERE id NOT IN (SELECT log_ID FROM zbp_post);

-- 获取一个可复用的ID
SELECT id FROM zbp_delete ORDER BY id LIMIT 1;

-- 插入文章后，删除该ID
DELETE FROM zbp_delete WHERE id = ?;