主キーの値を自動で設定してもらいたい

Question

MySQLを使っていて、たとえば以下のようなテーブルがあります。

CREATE TABLE body.artists
(
    artist_id                   INT NOT NULL,
    artist_name                 CHAR(200),
    description                 TEXT,
    CONSTRAINT PRIMARY KEY (artist_id)
);

このテーブルにレコードを追加するときに値を3カラム分指定しないといとエラーになります。
主キーのartist_idをinsertの時に指定するのではなく自動で連番にしてもらいたいのですが、どのように設定すれば良いのでしょうか?

Answer 1

オートインクリメントで実現できます。
https://dev.mysql.com/doc/refman/5.6/ja/example-auto-increment.html

---

last_insert_id()関数について

last_insert_id()関数にFROM句は不要です。

公式のリファレンスによると、引数なしのlast_insert_id()の戻り値は

最近実行された INSERT ステートメントの結果として、最初に自動的に生成され、正常に AUTO_INCREMENT カラムに挿入された値を表す 64 ビット値

だからです。
https://dev.mysql.com/doc/refman/5.6/ja/information-functions.html#function_last-insert-id

なにやら難しいことを言っていますが、要するに
「最後に実行されたINSERT文で挿入されたレコードのうち、
（レコードが複数ある場合は）最初に挿入されたレコードのオートインクリメント値」
が返却されるわけで、テーブルを指定することができないためです。

mysql> CREATE TABLE sample1 (id INT PRIMARY KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.01 sec)

# sample2テーブルはオートインクリメントを11から開始
mysql> CREATE TABLE sample2 (id INT PRIMARY KEY AUTO_INCREMENT) AUTO_INCREMENT = 11;
Query OK, 0 rows affected (0.01 sec)


mysql> INSERT INTO sample1 VALUES ();
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO sample2 VALUES ();
Query OK, 1 row affected (0.00 sec)


# "FROM sample1"を付けても、返却されるのは最後にインサートしたsample2テーブルのオートインクリメント値
mysql> SELECT last_insert_id() FROM sample1;
+------------------+
| last_insert_id() |
+------------------+
|               11 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               11 |
+------------------+
1 row in set (0.00 sec)

加えて、FROM句を付けると、指定したテーブルに格納されているレコードの数だけ、結果セットの行が返ってしまいます。

mysql> INSERT INTO sample1 VALUES (), (), ();
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT last_insert_id() FROM sample1;
+------------------+
| last_insert_id() |
+------------------+
|                2 |
|                2 |
|                2 |
|                2 |
+------------------+
4 rows in set (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

Comments

[Ken.sakanakana]

そして、自動発行された番号を取得できます！
select last_insert_id() from body.artists;

PHPでPDOお使いでしたら、簡単に取得できます~
$id = $dbh->lastInsertId();

この仕組みはパラレルに大量にクエリが発行されている状況でも、必ず自身のコネクションで実行された直前の値が取得できます。ご参考まで。

[KiyoshiMotoki]

Ken.sakanakana様、コメントありがとうございます。

last_insert_id()関数の使用方法について気になる点がございましたので、
回答のほうに追記させていただきました。

[Ken.sakanakana]

ここまで詳しく書いて頂いてありがとうございます！これで少しでも同じ悩みの方が、この投稿をみて躓かないようになれば良いですね。