MySQLのAUTO_INCREMENTカラムに連番を示す値を指定する
要約
MySQL の AUTO_INCREMENT カラムに 0 や NULL を指定すると自動連番の値が格納されます。
はじめに
MySQL の AUTO_INCREMENT 属性はユニークな連番生成の仕組みとして、IDのカラムなどでよく使用されます。
公式ドキュメントの例を借りると、以下のように AUTO_INCREMENT 属性を持つカラムは自動的に連番の値が格納されます。
mysql> CREATE TABLE animals (
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.20 sec)
mysql> INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin');
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM animals;
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
+----+---------+
3 rows in set (0.01 sec)
なお、MySQLでは自動採番のカラムを以下のように SERIAL としても定義できます。 SERIAL は BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE のエイリアスですが、SQL標準への準拠を考えると必要がなければ使わなくてもよいでしょう。
CREATE TABLE animals (
id SERIAL,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
自動連番を生成する記法について
値の指定を省略する
前述の例のように、INSERT文などで該当カラムへの値指定を省略した場合には、自動的に連番の値が格納されます。
INSERT INTO animals (name) VALUES ('dog');
これまでは多くのケースで慣例的にこのような書き方で連番を生成してきました。
ただし、カラム数が多い場合には VALUES の前に列挙する指定も増えるためSQLが冗長になるという問題があり、ときとして面倒さを感じることがありました。
連番を示す値を指定する
こちらが本題です。2通りの手段が存在します。
0 (ZERO)
ドキュメントでは以下のような記述があり、 0 を指定することで連番を生成できます。
You can also explicitly assign 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled.
mysql> INSERT INTO animals (id,name) VALUES(0,'groundhog');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM animals;
+----+-----------+
| id | name |
+----+-----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | groundhog |
+----+-----------+
4 rows in set (0.01 sec)
AUTO_INCREMENT は（特に指定をしない場合の）初期値が 1 であり、1以上の連番を格納することが想定されています。このため、 0 は特別な値として自動連番であることを明示的に指定する用途で使われるようでした。
この挙動はSQLモードで変更が可能で、 NO_AUTO_VALUE_ON_ZERO が有効になっている場合には自動連番ではなく 0 の値がそのまま格納されます。
NULL
同様に、 NULL を指定した場合にも自動連番の値が格納されます。
If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers.
mysql> INSERT INTO animals (id,name) VALUES(NULL,'squirrel');
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM animals;
+----+-----------+
| id | name |
+----+-----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | groundhog |
| 5 | squirrel |
+----+-----------+
5 rows in set (0.01 sec)
ただし、この動作はカラムが NOT NULL で定義されている場合のみの挙動であり、NULLが許容される場合には NULL がそのまま格納されます。
カラムが NOT NULL で定義されているにも関わらず NULL を挿入できる動作は、AUTO_INCREMENTカラムに特有の例外的な振る舞いです。これは自動連番生成のトリガーとして機能します。
補足
他データベースでの自動連番の指定について
このままだと少し物足りないのと、MySQLでの自動連番指定が少し独特にも感じたので、他データベースではどのようになっているのか少し調べてみました。（なお、筆者があまり使っていないデータベースも含まれているため、見当違いのことが書かれている可能性があります）
PostgreSQL
PostgreSQLでは自動連番のカラムにSERIAL型を使用します。自動連番の値であることを明示的に指定する場合には DEFAULT を使用します。
CREATE TABLE animal (
id SERIAL NOT NULL,
name VARCHAR(30) NOT NULL,
PRIMARY KEY(id)
);
INSERT INTO animal VALUES (DEFAULT, 'dog');
SERIALは内部的には以下のようなsequenceを紐づける動作となり、実質的には CREATE SEQUENCE と次の値を取得する nextval を併用して自動連番が実現されている形になります。（後述のSQL標準である NEXT VALUE FOR には非対応です）
CREATE SEQUENCE animal_id_seq AS integer;
CREATE TABLE animal (
id INTEGER NOT NULL DEFAULT nextval('animal_id_seq'),
name VARCHAR(30) NOT NULL,
PRIMARY KEY(id)
);
ALTER SEQUENCE animal_id_seq OWNED BY animal.id;
PostgreSQL 10以降では、SERIAL型に加えてSQL標準に準拠したIDENTITYカラムによる自動採番もサポートしています。
CREATE TABLE animal (
id INTEGER GENERATED ALWAYS AS IDENTITY,
name VARCHAR(30) NOT NULL,
PRIMARY KEY(id)
);
MariaDB
MySQLからforkされたデータベースであるMariaDBでは、MySQLと互換性のある AUTO_INCREMENT も当然利用できますが、10.3以降は sequence object を使った自動採番に対応しています。
CREATE SEQUENCE でシーケンスを作成して、 NEXTVAL で次の値を取得します。MariaDBではこの他にSQL標準である NEXT VALUE FOR での取得や、Oracleモードでの sequence_name.nextval といった取得方法にも対応していることが特徴です。
CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;
SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
| 100 |
+------------+
MariaDBのシーケンスは Sequence Storage Engine と呼ばれるストレージエンジンで実現されており、整数のシーケンスを取得できる仮想テーブルのような振る舞いをする仕組みにより自動採番にも応用できるようになっています。
Oracle
Oracleでの自動連番については、ドキュメントの How to Use Sequence Values に記載があります。
CREATE SEQUENCE を使用してシーケンスを作成して NEXTVAL で次の値を取得しますが、この NEXTVAL は example_seq.nextval のようにシーケンス名の末尾に付加する記法となります。
CREATE SEQUENCE employees_seq START WITH 1000 INCREMENT BY 1;
INSERT INTO employees
VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe', '555-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null, 30);
SQL Server
SQL Serverでは、ドキュメントの Sequence Numbers によると、 CREATE SEQUENCE でシーケンスを作成して NEXT VALUE FOR で次の値を取得します。
CREATE TABLE Test.Orders
(
OrderID INT PRIMARY KEY,
Name VARCHAR (20) NOT NULL,
Qty INT NOT NULL
);
GO
CREATE SEQUENCE Test.CountBy1
START WITH 1
INCREMENT BY 1;
GO
INSERT Test.Orders (OrderID, Name, Qty)
VALUES (NEXT VALUE FOR Test.CountBy1, 'Tire', 2);
標準SQL規格での自動連番
SQL規格はISOで仕様が標準化されています。データベース毎に対応状況は異なり、必ずしも規格通りの機能が提供されることは保証されていませんが、今後に機能追加・仕様変更などが行われる場合には標準SQL規格に準じた変更が行われる可能性は高いと思われます。
各データベースのドキュメントではSQL標準への対応状況が記載されていることがあります。以下にいくつか例を示します。
- MySQL Standards Compliance
- PostgreSQL: Documentation: 17: Appendix D. SQL Conformance
- Oracle and Standard SQL
さて、SQL99までは自動連番についての仕様は未定義でした。SQL:2003で Sequence Generators についての仕様が追加され、 CREATE SEQUENCE によるシーケンスの作成や NEXT VALUE FOR での値の取得などが定義されています。[1]
CREATE SEQUENCE PARTSEQ AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000
NO CYCLE;
INSERT INTO SHIPMENT (PARTNUM, DECRIPTION, QUANTITY)
VALUES (NEXT VALUE FOR PARTSEQ, 'Display', 20);
SQL:2003では、このほかに Identity Columns が定義されており、シーケンスを作成してそれを連番カラムに紐づけるというステップを簡略化する仕様が策定されています。この仕様は PostgreSQL や Oracle などで対応されています。
CREATE TABLE PARTS (
PARTNUM INTEGER GENERATED ALWAYS
AS IDENTITY (START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000
NO CYCLE),
DESCRIPTION VARCHAR (100),
QUANTITY INTEGER
);
まとめ
MySQL の AUTO_INCREMENT カラムに 0 や NULL を指定すると自動連番が格納されること、そこから派生して各データベースでの自動連番の仕様がどのようになっているかをまとめました。
SQL:2003では CREATE SEQUENCE による連番の仕組みが標準化され、多くの主要なデータベースがそれに概ね準拠していた中でMySQLだけは独自仕様のまま非対応を継続している姿勢（対応の計画があるかは調べられていません）なのも興味深いところでした。
MySQL自体は10年以上使っていつつ、慣れで指定省略の記法だけを使ってきて明示的な指定方法については完全に見落としていたので、小ネタではありますが今回メモとして残しています。
（ところで、MySQLのドキュメントでは 0 や NULL での指定をexplicitly（明示的）としているのですが、代理の値で自動連番を示すようにしているのでどちらかといえば implicitly（暗黙的）な指定なのではないかという気もしているのでした）
Discussion