MySQL get missing IDs

เคสแรกหล่ะกัน กรณีกำหนด Key เป็น Auto increment ใน MySQL แล้วเลขลำดับ/Key/IDs บางตัวหายไป (หลายสาเหตุ) ลองมาทำ Dummy Table ทดสอบดูกัน

CREATE TABLE numbers(
  startNumber INTEGER UNSIGNED,
  PRIMARY KEY (`startNumber`)
);
ALTER TABLE numbers ADD INDEX idx1 (startNumber);

INSERT INTO numbers VALUES
(1),(2),(3),(4),(5),(6),(8),(9),(20),(21),(22),(53),(54),
(71),(72),(74),(80);

จากตารางดัมมี่ข้างต้นผลลัพธ์ที่เราต้องการคือตัวเลข

ลองเขียนคำสั่งเพื่อให้ได้คำตอบด้านบนหลายวิธี วิธีแรกต้องใช้ตาราง Sequence Number ใน MySQL อาจต้องทำ Manual ไปก่อน

วิธีแรก

CREATE TABLE seq (`seqno` INTEGER UNSIGNED, PRIMARY KEY (`seqno`));
INSERT INTO `seq` VALUES (0),(1),(2),(3),(4),(5),(6),(7);


INSERT INTO `seq` SELECT seqno+8 from `seq`;
INSERT INTO `seq` SELECT seqno+16 from `seq`;
INSERT INTO `seq` SELECT seqno+32 from `seq`;
INSERT INTO `seq` SELECT seqno+64 from `seq`;
INSERT INTO `seq` SELECT seqno+128 from `seq`;
INSERT INTO `seq` SELECT seqno+256 from `seq`;
INSERT INTO `seq` SELECT seqno+512 from `seq`;

ตามด้วยใช้วิธีการ Left Join

SELECT
  s.*
FROM seq s 
  LEFT JOIN numbers n ON s.seqno = n.startNumber
WHERE n.startNumber IS NULL 
AND s.seqno < (SELECT MAX(startNumber) FROM numbers);

วิธีที่ 2

SELECT nstart, 
       nend
FROM (SELECT m.startNumber + 1 AS nstart,
              (SELECT MIN(startNumber) - 1 
                 FROM numbers x 
                 WHERE x.startNumber > m.startNumber) AS nend
          FROM numbers m 
                LEFT JOIN
                   (SELECT startNumber-1 startNumber 
                      FROM numbers r) r 
                ON (m.startNumber = r.startNumber)
         WHERE r.startNumber IS NULL
       ) x
WHERE nend IS NOT NULL
ORDER BY nstart;

วิธีที่ 3 ใช้ NOT IN 

SELECT
  s.*
FROM seq s 
WHERE s.seqno NOT IN (SELECT startNumber FROM numbers) 
AND s.seqno < (SELECT MAX(startNumber) FROM numbers);

วิธีที่ 4 ใช้ GROUP BY เพื่อใช้ SQL aggregate functions

SELECT a.startNumber+1 AS start, MIN(b.startNumber) - 1 AS end
FROM numbers AS a, numbers AS b
WHERE a.startNumber < b.startNumber
GROUP BY a.startNumber
HAVING start < MIN(b.startNumber);

ผลลัพธ์ที่ได้ก็ไปลองเล่นตามนี้ ได้เลย http://sqlfiddle.com/#!9/bbc3e7/3

ท้ายทีสุดแล้วมันอาจมีวิธีการที่มากกว่านี้แหล่ะ ผลลัพธ์ก็ได้ได้เช่นกันแต่ความแตกต่างคือ Big O คนซีเรียสก็ควรซีเรียส 5555