Auto IncrementとSequence

by afaik

投稿日: 1月 25, 2022

データベースにてテーブル設計する時に、テーブルの値に対してユニークなIDや連番を割り振ると言ったニーズは多いかと思います。SequenceやAuto Incrementで作成する連番は、複数のユーザーが一意の整数を生成するときに使用可能なデータベース・オブジェクトです。それらの値を利用する事で、テーブルにPrimary Keyを自動的に生成する事も可能です。ここでは、MySQLとPostgreSQLにおける、シーケンスの値に関しての挙動をまとめています。

PostgreSQLにおけるsequence

Sequenceの作成による連番の設定

POC=# \d trains;
                      Table "public.trains"
   Column   |       Type        | Collation | Nullable | Default
------------+-------------------+-----------+----------+---------
 id         | integer           |           | not null |
 route_name | character varying |           | not null |
Indexes:
    "idx_trains_id" btree (id)

POC=#  CREATE SEQUENCE train_id START 1;
CREATE SEQUENCE
POC=# insert into trains(id,route_name) values(nextval('train_id'),'Tokyo-Osaka');
INSERT 0 1
POC=# insert into trains(id,route_name) values(nextval('train_id'),'Tokyo-Fukuoka');
INSERT 0 1
POC=# insert into trains(id,route_name) values(nextval('train_id'),'Tokyo-Nagoya');
INSERT 0 1
POC=# insert into trains(id,route_name) values(nextval('train_id'),'Tokyo-ShinAomori');
INSERT 0 1
POC=# insert into trains(id,route_name) values(nextval('train_id'),'Tokyo-Ishikawa');
INSERT 0 1
POC=# select * from trains;
 id |    route_name
----+------------------
  1 | Tokyo-Osaka
  2 | Tokyo-Fukuoka
  3 | Tokyo-Nagoya
  4 | Tokyo-ShinAomori
  5 | Tokyo-Ishikawa
(5 rows)

POC=#

Alter TableにてSequenceをDefault値としての設定する

POC=# alter table trains alter id set default nextval('train_id');
ALTER TABLE
POC=# \d trains;
                                 Table "public.trains"
   Column   |       Type        | Collation | Nullable |            Default
------------+-------------------+-----------+----------+-------------------------------
 id         | integer           |           | not null | nextval('train_id'::regclass)
 route_name | character varying |           | not null |
Indexes:
    "idx_trains_id" btree (id)

POC=# insert into trains(route_name) values('Tokyo-Shizuoka');
INSERT 0 1


POC=# select * from trains;
 id |    route_name
----+------------------
  1 | Tokyo-Osaka
  2 | Tokyo-Fukuoka
  3 | Tokyo-Nagoya
  4 | Tokyo-ShinAomori
  5 | Tokyo-Ishikawa
  6 | Tokyo-Shizuoka
(6 rows)

POC=#

Create Table時にSequenceを指定

POC=# CREATE SEQUENCE trains_id START 1;
CREATE SEQUENCE
POC=# create table trains (id integer DEFAULT nextval('trains_id'),name varchar (100));
CREATE TABLE
POC=# insert into trains(id,name) values(nextval('trains_id'),'Tokyo-Osaka');
INSERT 0 1
POC=# insert into trains(id,name) values(nextval('trains_id'),'Tokyo-Nagoya');
INSERT 0 1
POC=# insert into trains(id,name) values(nextval('trains_id'),'Tokyo-Fukuoka');
INSERT 0 1
POC=# insert into trains(id,name) values(nextval('trains_id'),'Tokyo-Hiroshima');
INSERT 0 1
POC=# insert into trains(id,name) values(nextval('trains_id'),'Tokyo-Ishikawa');
INSERT 0 1
POC=# insert into trains(id,name) values(nextval('trains_id'),'Tokyo-ShinAomori');
INSERT 0 1
POC=# select * from trains;
 id |       name
----+------------------
  1 | Tokyo-Osaka
  2 | Tokyo-Nagoya
  3 | Tokyo-Fukuoka
  4 | Tokyo-Hiroshima
  5 | Tokyo-Ishikawa
  6 | Tokyo-ShinAomori
(6 rows)

Deleteしてリストアした時のSequenceの値 (Dumpファイルからのリストア)

POC=# select * from trains;
 id |       name
----+------------------
  1 | Tokyo-Osaka
  2 | Tokyo-Nagoya
  3 | Tokyo-Fukuoka
  4 | Tokyo-Hiroshima
  5 | Tokyo-Ishikawa
  6 | Tokyo-ShinAomori
(6 rows)

POC=# delete from trains;   /*** こちらでDELETEした後にリストア ***/
DELETE 6

POC=# select * from trains;
 id |       name
----+------------------
  1 | Tokyo-Osaka
  2 | Tokyo-Nagoya
  3 | Tokyo-Fukuoka
  4 | Tokyo-Hiroshima
  5 | Tokyo-Ishikawa
  6 | Tokyo-ShinAomori
(6 rows)

POC=# select * from trains_id;
 last_value | log_cnt | is_called
------------+---------+-----------
          6 |      27 | t
(1 row)

POC=# insert into trains(id,name) values(nextval('trains_id'),'Tokyo-ShinHakodateHokuto');
INSERT 0 1
POC=# select * from trains_id;
 last_value | log_cnt | is_called
------------+---------+-----------
          7 |      32 | t
(1 row)

POC=#

備考：バックアップリストア処理

~/backup [ 7:51:54]> pg_dump -h 127.0.0.1 -U postgres -a -t trains POC > trains_dump_data_only.sql
~/backup [ 7:52:06]> psql -h 127.0.0.1 -U postgres -f trains_dump_data_only.sql POC

Deleteしてバックアップテーブルからデータのリカバリーした時のSequenceの値

POC=# create table trains_backup (id integer,name varchar (100));
CREATE TABLE
POC=# insert into trains_backup select * from trains;
INSERT 0 7
POC=# begin transaction;
BEGIN
POC=*# delete from trains;
DELETE 7
POC=*# select * from trains;
 id | name
----+------
(0 rows)

POC=*# insert into trains select * from trains_backup;
INSERT 0 7
POC=*# select * from trains;
 id |           name
----+--------------------------
  1 | Tokyo-Osaka
  2 | Tokyo-Nagoya
  3 | Tokyo-Fukuoka
  4 | Tokyo-Hiroshima
  5 | Tokyo-Ishikawa
  6 | Tokyo-ShinAomori
  7 | Tokyo-ShinHakodateHokuto
(7 rows)

POC=*# commit;
COMMIT
POC=# insert into trains(id,name) values(nextval('trains_id'),'Tokyo-Kokura');
INSERT 0 1
POC=# select * from trains;
 id |           name
----+--------------------------
  1 | Tokyo-Osaka
  2 | Tokyo-Nagoya
  3 | Tokyo-Fukuoka
  4 | Tokyo-Hiroshima
  5 | Tokyo-Ishikawa
  6 | Tokyo-ShinAomori
  7 | Tokyo-ShinHakodateHokuto
  8 | Tokyo-Kokura
(8 rows)

POC=# select * from trains_id;
 last_value | log_cnt | is_called
------------+---------+-----------
          8 |      32 | t
(1 row)

POC=#

TruncateとSequenceの値 (PKの無いテーブルでのrestart identity)

POC=# truncate table trains;
TRUNCATE TABLE
POC=# insert into trains(id,name) values(nextval('trains_id'),'Osaka-Tokyo');
INSERT 0 1
POC=# select * from trains;
 id |    name
----+-------------
  9 | Osaka-Tokyo
(1 row)

POC=# truncate table trains restart identity;
TRUNCATE TABLE
POC=# insert into trains(id,name) values(nextval('trains_id'),'Osaka-Tokyo');
INSERT 0 1
POC=# select * from trains;
 id |    name
----+-------------
 10 | Osaka-Tokyo
(1 row)

POC=# \d trains;
                                  Table "public.trains"
 Column |          Type          | Collation | Nullable |            Default
--------+------------------------+-----------+----------+--------------------------------
 id     | integer                |           |          | nextval('trains_id'::regclass)
 name   | character varying(100) |           |          |

POC=#


POC=# truncate table trains restart identity;
TRUNCATE TABLE
POC=# select setval('trains_id', 1, false);
 setval
--------
      1
(1 row)

POC=# insert into trains(id,name) values(nextval('trains_id'),'Osaka-Tokyo');
INSERT 0 1
POC=# select * from trains;
 id |    name
----+-------------
  1 | Osaka-Tokyo
(1 row)

POC=#

注意：

上記方法でSequenceを1から振り直した場合にデータをリストアしてデータをINSERTする場合はSequenceの値を必要に応じて設定しなおしてください。PKやUKがある場合は、値が重複してしまうのでエラーになります。

POC=# truncate table trains restart identity;
TRUNCATE TABLE
POC=# select setval('trains_id', 1, false);
 setval
--------
      1
(1 row)

POC=# insert into trains select * from trains_backup;
INSERT 0 7
POC=# select * from trains;
 id |           name
----+--------------------------
  1 | Tokyo-Osaka
  2 | Tokyo-Nagoya
  3 | Tokyo-Fukuoka
  4 | Tokyo-Hiroshima
  5 | Tokyo-Ishikawa
  6 | Tokyo-ShinAomori
  7 | Tokyo-ShinHakodateHokuto
(7 rows)

POC=# select * from trains_id;
 last_value | log_cnt | is_called
------------+---------+-----------
          1 |       0 | f
(1 row)

POC=# select setval('trains_id', 8, false);
 setval
--------
      8
(1 row)

POC=# insert into trains(name) values('Osaka-Tokyo');
INSERT 0 1
POC=# select * from trains;
 id |           name
----+--------------------------
  1 | Tokyo-Osaka
  2 | Tokyo-Nagoya
  3 | Tokyo-Fukuoka
  4 | Tokyo-Hiroshima
  5 | Tokyo-Ishikawa
  6 | Tokyo-ShinAomori
  7 | Tokyo-ShinHakodateHokuto
  8 | Osaka-Tokyo
(8 rows)

POC=# select * from trains_id;
 last_value | log_cnt | is_called
------------+---------+-----------
          8 |      32 | t
(1 row)

POC=#

メモ： "ALTER SEQUENCE trains_id RESTART WITH 1;" でもSequenceの値を設定し直す事が出来ます。

TruncateとSequenceの値 (PKの有るテーブルでのrestart identity)

PKが無い場合と挙動に変化は有りません。Sequenceの値はリセットされず、最後の値を保持しています。

POC=# \d trains;
                                  Table "public.trains"
 Column |          Type          | Collation | Nullable |            Default
--------+------------------------+-----------+----------+--------------------------------
 id     | integer                |           |          | nextval('trains_id'::regclass)
 name   | character varying(100) |           |          |

POC=# alter table trains add constraint trains_pk primary key(id);
ALTER TABLE
POC=# \d trains;
                                  Table "public.trains"
 Column |          Type          | Collation | Nullable |            Default
--------+------------------------+-----------+----------+--------------------------------
 id     | integer                |           | not null | nextval('trains_id'::regclass)
 name   | character varying(100) |           |          |
Indexes:
    "trains_pk" PRIMARY KEY, btree (id)

POC=# insert into trains select * from trains_backup;
INSERT 0 7
POC=# select * from trains;
 id |           name
----+--------------------------
  1 | Tokyo-Osaka
  2 | Tokyo-Nagoya
  3 | Tokyo-Fukuoka
  4 | Tokyo-Hiroshima
  5 | Tokyo-Ishikawa
  6 | Tokyo-ShinAomori
  7 | Tokyo-ShinHakodateHokuto
(7 rows)

POC=# select * from trains_id;
 last_value | log_cnt | is_called
------------+---------+-----------
          1 |       0 | f
(1 row)

POC=# select setval('trains_id', 8, false);
 setval
--------
      8
(1 row)

POC=# select * from trains_id;
 last_value | log_cnt | is_called
------------+---------+-----------
          8 |       0 | f
(1 row)

POC=# insert into trains(name) values('Osaka-Tokyo');
INSERT 0 1
POC=# select * from trains;
 id |           name
----+--------------------------
  1 | Tokyo-Osaka
  2 | Tokyo-Nagoya
  3 | Tokyo-Fukuoka
  4 | Tokyo-Hiroshima
  5 | Tokyo-Ishikawa
  6 | Tokyo-ShinAomori
  7 | Tokyo-ShinHakodateHokuto
  8 | Osaka-Tokyo
(8 rows)

POC=# truncate table trains restart identity;
TRUNCATE TABLE
POC=# select * from trains;
 id | name
----+------
(0 rows)

POC=# select * from trains_id;
 last_value | log_cnt | is_called
------------+---------+-----------
          8 |      32 | t
(1 row)

Owned ByでTruncateした場合のSequence値

OWNED BYオプションにより、シーケンスは指定されたテーブル列に関連付けされ、その列（やテーブル全体）が削除されると、自動的にシーケンスも同様に削除されるようになります。truncate table trains restart identityした時に、Sequenceの値もリセットしたい場合はこちらを利用する事で、MySQLのAuto Incrementの様な挙動になります。

POC=# \d trains;
                                  Table "public.trains"
 Column |          Type          | Collation | Nullable |            Default
--------+------------------------+-----------+----------+--------------------------------
 id     | integer                |           | not null | nextval('trains_id'::regclass)
 name   | character varying(100) |           |          |
Indexes:
    "trains_pk" PRIMARY KEY, btree (id)

POC=# \d trains_id;
                         Sequence "public.trains_id"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

POC=# select * from trains;
 id |           name
----+--------------------------
  1 | Tokyo-Osaka
  2 | Tokyo-Nagoya
  3 | Tokyo-Fukuoka
  4 | Tokyo-Hiroshima
  5 | Tokyo-Ishikawa
  6 | Tokyo-ShinAomori
  7 | Tokyo-ShinHakodateHokuto
 10 | Osaka-Tokyo
(8 rows)

POC=# select * from trains_id;
 last_value | log_cnt | is_called
------------+---------+-----------
         10 |      31 | t
(1 row)

POC=# alter sequence trains_id owned by trains.id;
ALTER SEQUENCE
POC=# \d trains;
                                  Table "public.trains"
 Column |          Type          | Collation | Nullable |            Default
--------+------------------------+-----------+----------+--------------------------------
 id     | integer                |           | not null | nextval('trains_id'::regclass)
 name   | character varying(100) |           |          |
Indexes:
    "trains_pk" PRIMARY KEY, btree (id)

POC=# \d trains_id;
                         Sequence "public.trains_id"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.trains.id

POC=# truncate table trains restart identity;
TRUNCATE TABLE
POC=# select * from trains;
 id | name
----+------
(0 rows)

POC=# select * from trains_id;
 last_value | log_cnt | is_called
------------+---------+-----------
          1 |       0 | f
(1 row)

POC=#

Sequenceの値の確認

参考：

9.17. シーケンス操作関数

ALTER SEQUENCE

MySQLにおけるAuto Increment

Auto Incrementを設定してテーブルを作成

mysql> CREATE TABLE `temp1` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'PKとなるIDで連番',
    ->   `note` varchar(100) NOT NULL DEFAULT '-' COMMENT 'メモ書き',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> insert into temp1(note) values('連番IDの確認');
Query OK, 1 row affected (0.01 sec)

mysql> insert into temp1(note) values('連番IDの確認');
Query OK, 1 row affected (0.00 sec)

mysql> insert into temp1(note) values('連番IDの確認');
Query OK, 1 row affected (0.01 sec)

mysql> select * from temp1;
+----+-------------------+
| id | note              |
+----+-------------------+
|  1 | 連番IDの確認      |
|  2 | 連番IDの確認      |
|  3 | 連番IDの確認      |
+----+-------------------+
3 rows in set (0.00 sec)

mysql>

Auto Incrmentを後から設定する場合

MySQLの場合は、Auto_incrementの値も既存のデータと連動して適切に処理してくれる。

mysql> create table t_autoincrement (
    -> `id` bigint NOT NULL COMMENT 'PKとなるIDで連番',
    -> `note` varchar(100) NOT NULL DEFAULT '-' COMMENT 'メモ書き',
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.06 sec)


mysql> insert into t_autoincrement(id, note) values (1, "連番IDの確認");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_autoincrement(id, note) values (2, "連番IDの確認");
Query OK, 1 row affected (0.02 sec)

mysql> insert into t_autoincrement(id, note) values (3, "連番IDの確認");
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_autoincrement;
+----+-------------------+
| id | note              |
+----+-------------------+
|  1 | 連番IDの確認      |
|  2 | 連番IDの確認      |
|  3 | 連番IDの確認      |
+----+-------------------+
3 rows in set (0.00 sec)


mysql> show create table t_autoincrement\G
*************************** 1. row ***************************
       Table: t_autoincrement
Create Table: CREATE TABLE `t_autoincrement` (
  `id` bigint NOT NULL COMMENT 'PKとなるIDで連番',
  `note` varchar(100) NOT NULL DEFAULT '-' COMMENT 'メモ書き',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)


mysql> alter table t_autoincrement modify id bigint auto_increment  COMMENT 'PKとなるIDで連番';
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table t_autoincrement\G
*************************** 1. row ***************************
       Table: t_autoincrement
Create Table: CREATE TABLE `t_autoincrement` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'PKとなるIDで連番',
  `note` varchar(100) NOT NULL DEFAULT '-' COMMENT 'メモ書き',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)


mysql> select * from t_autoincrement;
+----+-------------------+
| id | note              |
+----+-------------------+
|  1 | 連番IDの確認      |
|  2 | 連番IDの確認      |
|  3 | 連番IDの確認      |
+----+-------------------+
3 rows in set (0.00 sec)

mysql> insert into t_autoincrement(note) values('Auto Increment付与後の連番');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_autoincrement(note) values('Auto Increment付与後の連番');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_autoincrement;
+----+----------------------------------+
| id | note                             |
+----+----------------------------------+
|  1 | 連番IDの確認                     |
|  2 | 連番IDの確認                     |
|  3 | 連番IDの確認                     |
|  4 | Auto Increment付与後の連番       |
|  5 | Auto Increment付与後の連番       |
+----+----------------------------------+
5 rows in set (0.00 sec)

mysql>

Auto Incrementを含むテーブルをTruncate

MySQLのAuto Incrementの値はTableをTruncateするとResetされる。DELETEの場合はリセットされないので、必要であればAlter句 ” alter table temp1 auto_increment = 1; ” 等で値を設定してあげる。

mysql> select * from temp1;
+----+-------------------+
| id | note              |
+----+-------------------+
|  1 | 連番IDの確認      |
|  2 | 連番IDの確認      |
|  3 | 連番IDの確認      |
+----+-------------------+
3 rows in set (0.00 sec)

mysql> truncate table temp1;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into temp1(note) values('連番IDの確認');
Query OK, 1 row affected (0.01 sec)

mysql> insert into temp1(note) values('連番IDの確認');
Query OK, 1 row affected (0.01 sec)

mysql> insert into temp1(note) values('連番IDの確認');
Query OK, 1 row affected (0.02 sec)

mysql> select * from temp1;
+----+-------------------+
| id | note              |
+----+-------------------+
|  1 | 連番IDの確認      |
|  2 | 連番IDの確認      |
|  3 | 連番IDの確認      |
+----+-------------------+
3 rows in set (0.00 sec)

mysql>

備考：値の初期値 (以下の変数で設定されている、InnoDB Cluster等でマルチマスター構成を組む時は変更)

mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

Auto Incrementの値を確認

show table status like 'temp1'\G

select * from information_schema.tables where table_name = 'temp1'\G

参考：

3.6.9 AUTO_INCREMENT の使用

WL#6204: InnoDB persistent max value for autoinc columns

カテゴリー:

タグ:

Auto Increment MySQL PostgreSQL Sequence 連番

Auto IncrementとSequence

PostgreSQLにおけるsequence

注意：

上記方法でSequenceを1から振り直した場合にデータをリストアしてデータをINSERTする場合はSequenceの値を必要に応じて設定しなおしてください。PKやUKがある場合は、値が重複してしまうのでエラーになります。

Sequenceの値の確認

MySQLにおけるAuto Increment

最近の投稿

最近のコメント