MySQLで新規にテーブルを作って、プライマリーキーをAUTO_INCREMENTに設定したときに調べたことをメモ。AUTO_INCREMENTを1番から採番するパターンと特定の番号の連番で採番したいパターンが出てきたので調査。

データベースはMySQLのバージョン5.6.38で試しました。select version();のSQL文で確認。

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.6.38    |
+-----------+

テストデータを作成

とりあえずテストデータを作成します。キノコ好きなので、キノコテーブルを作成。

mysql> CREATE TABLE mashroom (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));

出来上がったテーブルの構造を確認します。憶えついでに、テーブルの構造を確認するSQL文2種類。DESCは、SHOW COLUMNS FROM テーブル名;と同じようですが、コンパクトにかけるので、DESCで。（ORDER BY の DESC と間違えそう）

mysql> DESC mashroom;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> SHOW FULL COLUMNS FROM mashroom;
+-------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type         | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int(11)      | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| name  | varchar(100) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
+-------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+

データの挿入（INSERT）：ID指定をしない

作成したテーブルにデータを挿入してみます。AUTO_INCREMENTの設定フィールド「id」には値を設定せず、「name」フィールドにのみ値セット。INSERT INTO文でまとめて挿入します。

mysql> INSERT INTO mashroom (name) VALUES ("shimeji"), ("shiitake"), ("enoki");

挿入されたデータを確認します。

mysql> SELECT * FROM mashroom;
+----+----------+
| id | name     |
+----+----------+
|  1 | shimeji  |
|  2 | shiitake |
|  3 | enoki    |
+----+----------+

1番から連番でidの値が挿入されました。

データの挿入（INSERT）：ID指定をする

先程のテーブルに今度は、idの値を指定して挿入してみます。連番ではない番号で挿入してみる。

INSERT INTO mashroom (id, name) VALUES (10, "maitake");

挿入されたデータの確認を。

mysql> SELECT * FROM mashroom;
+----+----------+
| id | name     |
+----+----------+
|  1 | shimeji  |
|  2 | shiitake |
|  3 | enoki    |
| 10 | maitake  |
+----+----------+

指定した番号で挿入されました。この状態でidの値を指定せずに挿入すると…

mysql> INSERT INTO mashroom (name) VALUES ("matustake");
mysql> SELECT * FROM mashroom;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | shimeji   |
|  2 | shiitake  |
|  3 | enoki     |
| 10 | maitake   |
| 11 | matustake |
+----+-----------+

11番が採番されました。

AUTO_INCREMENTの値を確認

AUTO_INCREMENTが次に採番する番号が何番かを確認する方法。テーブルの状態を見ることができるSQL文があるので、それで確認します。

mysql> SHOW TABLE STATUS WHERE name = "mashroom";

テーブルの情報が多いので、表示は割愛しますが、その中に Auto_increment: 12 と書いてある部分があります。これが次にセットされる値になります。

AUTO_INCREMENTの値を変更する

もしAUTO_INCREMENTの値を変更する機会があればということで。テストデータとか入れたりして、終わったあとにデータと共にきれいにするときに、よく使っています。

mysql> ALTER TABLE mashroom AUTO_INCREMENT = 20;

＼(^o^)／できた！
SQL文書くの好き。最近はフレームワークにお世話になるから、直接SQL文を書くことが少なくなった気がします。