MySQLで連番を扱うサンプル

欠番を探す

select t1.id , t2.seq from table as t1
right outer join
(SELECT @seq_no := 1 AS seq
    UNION
    SELECT @seq_no := @seq_no + 1 AS seq FROM table
    LIMIT 10000) as t2
on t1.id = t2.seq
where t1.id is null

重複をチェックする

SELECT *
FROM table
WHERE column
IN
(SELECT column FROM table GROUP BY column HAVING count(*) > 1);

連番を返す

SELECT @seq_no := 1 AS seq
UNION
SELECT @seq_no := @seq_no + 1 AS seq FROM table
LIMIT 10000

既存テーブルに連番をセットする

set @i := 0;
update TABLE_NAME set id = (@i := @i +1);
日付妥当性をチェックする

グループ毎に連番をふる(target TABLE には主キーidがあるとする)

UPDATE target as t1
INNER JOIN
(
    SELECT tA.group_id, COUNT(tA.group_id) AS seq, tA.id AS dmyid
    FROM target as tA, target as tB
    WHERE tA.group_id = tB.group_id AND tA.id >= tB.id
    GROUP BY tA.group_id, tA.id
) as t2
ON t1.id = t2.dmyid
SET t1.seq = t2.seq;

ID以外が重複する行を削除する（一時table 利用）

INSERT INTO works(id)
    SELECT MIN(id) FROM table GROUP BY other1, other2;

DELETE FROM table WHERE id NOT IN
    (SELECT id FROM works);