はじめに
こんにちは。
MySQLには、整数型のカラムで自動採番を行うことのできる AUTO_INCREMENT という属性があります。
AUTO_INCREMENT にNULLや0を指定したり、値を明示的に入れたりした場合、挙動はどうなるのでしょうか？
この記事では、AUTO_INCREMENT の基本的な挙動について調べたことをまとめます。
実行環境
- MySQL 8.0.46
AUTO_INCREMENTとは
AUTO_INCREMENT は、整数型のカラムに自動で連番を割り当てるための属性です。
主キーのIDカラムで使われることがほとんどです。
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
値を指定しないと自動採番される
id を指定せずにINSERTすると、MySQLが自動で値を割り当てます。
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Carol');
SELECT * FROM users;
+----+-------+
| id | name |
+----+-------+
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
+----+-------+
NULLを入れても自動採番される
AUTO_INCREMENT カラムにNULLを指定した場合も、自動採番されます。
INSERT INTO users (id, name) VALUES (NULL, 'Dave');
SELECT * FROM users WHERE name = 'Dave';
+----+------+
| id | name |
+----+------+
| 4 | Dave |
+----+------+
NULLを入れたのに4が割り当てられています。
0を入れた場合はSQLモードに依存する
0を入れた場合の挙動は、SQLモードによって変わります。
デフォルトでは、0を指定してもNULLと同様に自動採番されます。
0という値をそのまま保存したい場合は、NO_AUTO_VALUE_ON_ZERO モードを有効にする必要があります。
現在のグローバル、またはセッションのSQLモードは次のクエリで確認できます。
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
グローバル、またはセッションで NO_AUTO_VALUE_ON_ZERO を有効にするには、次のように設定します。
SET GLOBAL sql_mode = CONCAT(@@GLOBAL.sql_mode, ',NO_AUTO_VALUE_ON_ZERO');
SET SESSION sql_mode = CONCAT(@@SESSION.sql_mode, ',NO_AUTO_VALUE_ON_ZERO');
明示的に値を入れると採番に影響する
AUTO_INCREMENT カラムには、明示的に値を指定することもできます。
INSERT INTO users (id, name) VALUES (100, 'Eve');
INSERT INTO users (name) VALUES ('Frank');
SELECT * FROM users WHERE name IN ('Eve', 'Frank');
+-----+-------+
| id | name |
+-----+-------+
| 100 | Eve |
| 101 | Frank |
+-----+-------+
100を手動で入れると、次の自動採番は101になりました。
AUTO_INCREMENT は、カラムの現在の最大値をもとに次の値を決めます。
LAST_INSERT_ID() で採番された値を確認できる
直前に自動生成された値は、LAST_INSERT_ID() で取得できます。
INSERT INTO users (name) VALUES ('Grace');
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 102 |
+------------------+
この値は接続（セッション）ごとに管理されます。
他のクライアントが同時にINSERTしても、自分の LAST_INSERT_ID() には影響しません。
複数行を1回のINSERTで入れた場合は、最初に生成された値が返ります。
INSERT INTO users (name) VALUES ('Heidi'), ('Ivan');
SELECT LAST_INSERT_ID(); -- 103（最初の行のID）
まとめ
-
AUTO_INCREMENTは整数カラムに自動で連番を割り当てる属性 - 値を省略した場合、NULLを指定した場合に自動採番される
- 0を指定した場合の挙動はSQLモード（
NO_AUTO_VALUE_ON_ZERO）に依存する - 明示的に大きな値を入れると、採番に影響が出る
-
LAST_INSERT_ID()で直前の採番値を確認できる
お読みいただきありがとうございました。