mysqlの自動採番の挙動について

Question

前提・実現したいこと

mysqlのテーブルでprimary keyを設定すると、例えば"INSERT ~ ON DUPLICATE KEY UPDATE ~"で行の挿入が行われなかったとしてもprimary keyの値はどんどんと加算されていきます。頻繁に行の挿入や更新が行われるこのテーブルでは、いずれINT型の最大値に到達してしまい、それ以上行の挿入ができなくなります。この問題の解決方法をお教え願いたいです。

###試したこと
phpでsqlを発行する際に、"~ VALUES((SELECT(id ORDER BY id DESC LIMIT 1)+1), ~),((SELECT(id ORDER BY id DESC LIMIT 1)+1), ~),..."としてsqlを構成してみましたが、全くうまくいきませんでした。

Answer 1

bigintのunsignedであれば最大値は18446744073709551615です

Answer 2

mysqlのテーブルでprimary keyを設定すると、例えば"INSERT ~ ON DUPLICATE KEY UPDATE ~"で行の挿入が行われなかったとしてもprimary keyの値はどんどんと加算されていきます。

primary keyが理由ではなく、primary keyにAUTO_INCREMENTを指定しているからですね。

頻繁に行の挿入や更新が行われるこのテーブルでは、いずれINT型の最大値に到達してしまい、それ以上行の挿入ができなくなります。

更新は関係なく追加の場合だけですね。
AUTO_INCREMENTの指定は活かしたいのなら、INTではなくもっと範囲の大きいものに変更して下さい。
符号なしのBIGINTなら18,446,744,073,709,551,615まで使えます
10の-19乗で六徳という聞いた事も無い単位までなので、システムの寿命の方が先だと思います。

Comments

[退会済みユーザー]

DELETEしてINSERTしているようだと、やり方によってはガンガン数字が進んでしまうのかもね。
そのためのUPSERTなんじゃないのかなって思いますが、進まないような工夫をしているかどうか気になりますね。

[sazi]

更新でカウントアップされるとしたら、DELETE/INSERTという処理にしている場合ですが、シーケンスはロールバックしても元には戻りませんから、空き番がないように工夫するならシーケンスは使わずに自前で採番する事だと思います。

Answer 3

SELECT COALESCE(max(対象id), 0) FROM 対象テーブル) + 1

この式をインクリメントしたいidのところに置きます。これでもだめですかね？

Answer 4

INT最大値って2147483647なので、そんなすぐ食いつぶすものではないとは思いますが・・・（毎回DELETE/INSERTしてるなら別）

• AUTO INCREMENT ではなくシステム側で計算して絶対必要な時にだけ払い出す
• INTではなくもっと大きい整数型にする