最近DBをよくいじっているのでそれ系の備忘録が増えていきますね・・・
概要
Mysqlの機能で、かなり便利なAUTO_INCREMENT
こいつをセットしたカラムは、レコードが増えると自動的に番号を振って登録してくれます。
ので、INSERTするときにそのカラムを意識しなくてよくなるわけです。
しかしレコードを削除した場合、削除分を考えてくれないため、AUTO_INCREMENTを設定したカラムが「穴あき登録」されてしまいます。
？？？
って方もいると思いますので、下記で例を交えて実践します。
実践
対象テーブル
show create table tbl_env;
| tbl_env | CREATE TABLEtbl_env(
env_idint(11) NOT NULL AUTO_INCREMENT,
env_namevarchar(255) NOT NULL,
commenttext DEFAULT NULL,
created_atdatetime NOT NULL DEFAULT current_timestamp(),
update_atdatetime DEFAULT NULL ON UPDATE current_timestamp(),
delete_atdatetime DEFAULT NULL,
PRIMARY KEY (env_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
今回はこのテーブルを利用します。
env_idというカラムにAUTO_INCREMENTが設定されていますね。
レコードの登録
3つほどレコードを登録してみます。
MariaDB [dev_ansible]> INSERT INTO tbl_env (env_name, comment) VALUES ('PRD', '本番環境');
Query OK, 1 row affected (0.003 sec)
MariaDB [dev_ansible]> INSERT INTO tbl_env (env_name, comment) VALUES ('STG', 'ステージング環境');
Query OK, 1 row affected (0.002 sec)
MariaDB [dev_ansible]> INSERT INTO tbl_env (env_name, comment) VALUES ('DEV', '開発環境');
Query OK, 1 row affected (0.002 sec)
ちゃんと登録されているか確認。
MariaDB [dev_ansible]> select * from tbl_env;
+--------+----------+--------------------------+---------------------+-----------+-----------+
| env_id | env_name | comment | created_at | update_at | delete_at |
+--------+----------+--------------------------+---------------------+-----------+-----------+
| 1 | PRD | 本番環境 | 2019-05-08 15:32:37 | NULL | NULL |
| 2 | STG | ステージング環境 | 2019-05-08 15:32:49 | NULL | NULL |
| 3 | DEV | 開発環境 | 2019-05-08 15:32:58 | NULL | NULL |
+--------+----------+--------------------------+---------------------+-----------+-----------+
ちゃんと登録されていますね。
レコードの削除
登録したレコードを削除してみます。
指定してもよいですが、今回は全部削除。
MariaDB [dev_ansible]> DELETE FROM tbl_env;
Query OK, 3 rows affected (0.002 sec)
んで確認。
MariaDB [dev_ansible]> select * from tbl_env;
Empty set (0.001 sec)
ちゃんと全部削除されていますね。
再度レコードの登録
MariaDB [dev_ansible]> INSERT INTO tbl_env (env_name, comment) VALUES ('PRD', '本番環境');
Query OK, 1 row affected (0.002 sec)
すべて削除した後の登録なので、レコードは1つになったはずです。
なので、env_idも1になっていることが理想。
確認。
MariaDB [dev_ansible]> select * from tbl_env;
+--------+----------+--------------+---------------------+-----------+-----------+
| env_id | env_name | comment | created_at | update_at | delete_at |
+--------+----------+--------------+---------------------+-----------+-----------+
| 4 | PRD | 本番環境 | 2019-05-08 15:43:51 | NULL | NULL |
+--------+----------+--------------+---------------------+-----------+-----------+
1 row in set (0.000 sec)
はい。
env_idは4から始まってしまっています。
解決方法
上記の状態のとき、テーブルの詳細を見てみると、以下となっております。
| tbl_env | CREATE TABLEtbl_env(
env_idint(11) NOT NULL AUTO_INCREMENT,
env_namevarchar(255) NOT NULL,
commenttext DEFAULT NULL,
created_atdatetime NOT NULL DEFAULT current_timestamp(),
update_atdatetime DEFAULT NULL ON UPDATE current_timestamp(),
delete_atdatetime DEFAULT NULL,
PRIMARY KEY (env_id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
一番下に「AUTO_INCREMENT=4」と表示されていました。
これがAUTO_INCREMENTが設定されているカラムに、次に入る値となります。
この値はレコードをDELETEしてもリセットされないので、次のレコード登録したときの値が削除されたものの続きからになってしまっていたのですね。
ということは、この値を任意の数字に変えることで、AUTO_INCREMENTで挿入される値を自在に操ることができるのです。
というわけでそのSQLがこちら。
alter table [テーブル名] auto_increment = [任意の数字];
今回はすべて削除したので、また1から登録してほしい状態。
よって、リセットするコマンドは以下となります。
MariaDB [dev_ansible]> alter table tbl_env auto_increment = 1;
では確認してみましょう。
| tbl_env | CREATE TABLEtbl_env(
env_idint(11) NOT NULL AUTO_INCREMENT,
env_namevarchar(255) NOT NULL,
commenttext DEFAULT NULL,
created_atdatetime NOT NULL DEFAULT current_timestamp(),
update_atdatetime DEFAULT NULL ON UPDATE current_timestamp(),
delete_atdatetime DEFAULT NULL,
PRIMARY KEY (env_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
AUTO_INCREMENT=4 という出力が消えています。
これで、正常にAUTO_INCREMENTの設定が初期化されたわけです。
「別に穴あきで登録されててもいいじゃん」って思う人もいると思いますが、個人的にはこういうのとても気持ち悪いのでなるべく直したくなるわけです。
AUTO_INCREMENTを利用する際は気を付けておいたほうがいいかもですね。
ちなみに、設計でカバーするのであれば、レコードの物理削除をしないで、論理削除で対応するという方法もあります。
というか、そのために「delete_at」というカラムを用意しているので・・・
論理削除のいいところはレコードの使いまわしができることですね。
ただし、レコード数がひたすら増えていくので、キャパシティ設計は面倒になりますが・・・
コメント