ON DUPLICATE KEY UPDATEをAUTO_INCREMENTのテーブルには発行しない方がいい

AUTO_INCREMENTはINSERTするたびにカウントアップして発番してくれるが、ON DUPLICATE KEY UPDATEを使用してINSERTではなく既存行のUPDATEになった場合もカウントアップされてしまう。実際にINSERTしている行数はたいしたことがないのに、ON DUPLICATE KEY UPDATEされる回数が多いせいで、AUTO_INCREMENT値が大きくなってしまい、INTなどの比較的小さい型にしていた場合にAUTO_INCREMENTが上限値に達してしまう。

以下のテーブルをもとにINSERTされていないのにAUTO_INCREMENT値が増えていることを実際に確認する。

AUTO_INCREMENTの初期値はデフォルトの1となっている。

まずはテーブルにデータを2件登録する。すると想定通り次に採番される値は3になる。

ここで既にあるukey = 'A'のデータを再度INSERTする。既にあるので、ON DUPLICATE KEY UPDATE句が適用されて、cntが現在の値 + 1 となる。

INSERTされていないのでAUTO_INCREMENT値が3のままかと思いきや、カウントアップされて4になっている。

実際に存在しないukeyをINSERTしてみると、idが4で発番されて、3が欠番になっている。

-MySQL