テーブルに自動採番(オートインクリメント)する列を作成する

今回もDBの話です。DBはMySQLやその互換のAuroraを想定しています。
テーブルに行を追加していくとき、自動的に通し番号でIDをふって欲しいことはよくあります。
MySQLにはそれ専用の、AUTO_INCREMENT属性があります。

参考: 3.6.9 AUTO_INCREMENT の使用

さて、CREATE TABLEするときに列名の横に、 AUTO_INCREMENT をつけるだけで使えるように見えますが、それだとエラーになります。


> CREATE TABLE
    sample_table1 (
        id INTEGER AUTO_INCREMENT,
        name VARCHAR(255)
    );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

it must be defined as a key とある通り、主キーかユニークキーを設定した列でないと、AUTO_INCREMENT属性は設定できません。
ついでに説明しておくと、 there can be only one auto columnとある通り、1テーブルに設定できるのは1列までです。


> CREATE TABLE
    sample_table1 (
        id INTEGER PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255)
    );
Query OK, 0 rows affected (0.06 sec)

さて、これで連番が振られることを見ておきましょう。


> INSERT INTO 
    sample_table1 (name)
VALUES
   ('suzuki'), ('sato'), ('tanaka');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

> SELECT * FROM sample_table1;
+----+--------+
| id | name   |
+----+--------+
|  1 | suzuki |
|  2 | sato   |
|  3 | tanaka |
+----+--------+
3 rows in set (0.01 sec)

連番が振られていますね。

僕はこれ、列の最大値の次の値が採番されていくと勘違いしていたのですが、実はAUTO_INCREMENTで振られる番号は別のところに保存されています。


> SHOW CREATE TABLE sample_table1\G
*************************** 1. row ***************************
       Table: sample_table1
Create Table: CREATE TABLE `sample_table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)

AUTO_INCREMENT=4 と入っているように、テーブルが属性として次の番号を持っています。
そのため、今入っているレコードを消して、新たに行を追加すると、4番から採番されます。


> DELETE FROM sample_table1 WHERE id >= 2;
Query OK, 2 rows affected (0.02 sec)

> INSERT INTO 
    sample_table1 (name)
VALUES
   ('kobayashi'), ('ito'), ('takahashi');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

> SELECT * FROM sample_table1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | suzuki    |
|  4 | kobayashi |
|  5 | ito       |
|  6 | takahashi |
+----+-----------+
4 rows in set (0.01 sec)

ユニーク性を担保する上では便利な仕組みですね。

テーブルの属性としてカウンタを持っているので、 ALTER TABLE 文を使ってリセットしたり、任意の値に設定したりできます。
構文はこうです。


ALTER TABLE [テーブル名] AUTO_INCREMENT = [値];

やってみます。


> ALTER TABLE sample_table1 AUTO_INCREMENT = 100;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

> INSERT INTO 
    sample_table1 (name)
VALUES
   ('watanabe');
Query OK, 1 row affected (0.01 sec)

> SELECT * FROM sample_table1;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | suzuki    |
|   4 | kobayashi |
|   5 | ito       |
|   6 | takahashi |
| 100 | watanabe  |
+-----+-----------+
5 rows in set (0.00 sec)

予想通りに動きました。

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル