【Mysql/MariaDB】AUTO_INCREMENTをリセットする

Linux

2019.05.08

最近DBをよくいじっているのでそれ系の備忘録が増えていきますね・・・

概要
実践
解決方法

概要

Mysqlの機能で、かなり便利なAUTO_INCREMENT

こいつをセットしたカラムは、レコードが増えると自動的に番号を振って登録してくれます。

ので、INSERTするときにそのカラムを意識しなくてよくなるわけです。

しかしレコードを削除した場合、削除分を考えてくれないため、AUTO_INCREMENTを設定したカラムが「穴あき登録」されてしまいます。

？？？

って方もいると思いますので、下記で例を交えて実践します。

実践

対象テーブル

show create table tbl_env;

| tbl_env | CREATE TABLE tbl_env (
   env_id int(11) NOT NULL AUTO_INCREMENT,
   env_name varchar(255) NOT NULL,
   comment text DEFAULT NULL,
   created_at datetime NOT NULL DEFAULT current_timestamp(),
   update_at datetime DEFAULT NULL ON UPDATE current_timestamp(),
   delete_at datetime 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 TABLE tbl_env (
   env_id int(11) NOT NULL AUTO_INCREMENT,
   env_name varchar(255) NOT NULL,
   comment text DEFAULT NULL,
   created_at datetime NOT NULL DEFAULT current_timestamp(),
   update_at datetime DEFAULT NULL ON UPDATE current_timestamp(),
   delete_at datetime 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 TABLE tbl_env (
   env_id int(11) NOT NULL AUTO_INCREMENT,
   env_name varchar(255) NOT NULL,
   comment text DEFAULT NULL,
   created_at datetime NOT NULL DEFAULT current_timestamp(),
   update_at datetime DEFAULT NULL ON UPDATE current_timestamp(),
   delete_at datetime DEFAULT NULL,
   PRIMARY KEY (env_id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |