AUTO_INCREMENTで削除した値を無視するにはどうしたらよいのでしょうか？

Question

カラムに AUTO_INCREMENT をつけると、データを追加した時にカラムに対して現在格納されている最大の数値に 1 を追加した数値を自動で格納することができますが、最大の数値を削除してから、レコードを追加すると削除した最大の数値+1を格納してしまいます。
どうすれば削除した数値を無視することができますか？

Answer 1

過去ログ にあるように放置します。いちど決定したPRIMARY KEYは変更しないのが鉄則です。退会者が出たからといって会員IDを振り直していたら、その会員IDが関連するテーブルも全部振り直さなければなりません。大仕事です。

Comments

[yuki1111]

回答ありがとうございます。
よく考えたらそうですね。「PRIMARY KEYは変更しないのが鉄則」ということを覚えておきます。

Answer 2

auto_incrementの連番性を利用したくて、欠番の無い状態にしたいのであれば、そういう番号を作り出すしかありません。

単に表示の際の番号であれば、create_at（作成日時)などの項目順で番号を取得すれば良いかと思います。
row_number()が使えるバージョンであれば楽ですけど、そうでない場合は以下などを参考にすれば良いかと思います。
【SQL】row_numberのないMySQLでユーザー変数を使わずに連番を振る

Comments

[m.ts10806]

>row_number()が使えるバージョン
MySQLって8で随分進化したんですね

[sazi]

@m.ts10806さん
みたいですね。
8でもフリーのエディションはあるみたいですけど、MySQLはoracle配下なので。。

[maisumakun]

MariaDBでも10.2で入っているようです。

https://mariadb.com/kb/en/row_number/

[yuki1111]

回答ありがとうございます。
「row_number()」覚えておきます！

[sazi]

@maisumakunさん
去年の11月にリリースされたんですね。
MySQL系はあまり触る事が無いので、確認していませんでした。
情報提供ありがとうございます。

Answer 3

MySQLのAUTO_INCREMENTは、削除しなくても番号が飛ぶことがあります。

「番号が1つも飛ばない連番であることに金銭的な価値が発生する」ような場面であればAUTO_INCREMENTを使うこと自体が不適当ですし、そうでないなら「番号が飛ぶこともありうる」と割り切って使うしかないです。

Comments

[maisumakun]

> どうすれば削除した数値を無視することができますか？

データベースを使う側で（番号が飛んでいることを）無視してください。、

[yuki1111]

回答ありがとうございます。
はい、番号が飛んでいることを無視します。

Answer 4

用途が明記されてないので的確なアドバイスは難しいのですけど、
そこまで気にするのでしたら自身でシーケンス管理テーブルでも作って
そこから払い出しては。

Comments

[yuki1111]

回答ありがとうございます。
シーケンス管理テーブルですかぁ、初めてききました。
覚えておきます。

[m.ts10806]

PostgreSQLならそもそも「シーケンス」が作れるのですけどね。
MySQLは自分でやる必要があります。

Answer 5

以下のSQLのauto_incrementの部分を削除した値に変更すればその削除した数値からインサートされるようになります。
そもそもauto_incrementはユニークになるようにレコード追加時にプラス1されるのでその挙動で普通です。数値を制御したかったら別のカラムに数値をもたせそのカラムを好きなように操作すればいいかと

ALTER TABLE `tablename` auto_increment = 1;

Comments

[yuki1111]

回答ありがとうございます。
ありがとうございます。覚えておきます。