はじめに

こんにちは、エンジニアのmiyakawaです。

DBでテーブルを作成するときに主キーが必要になりますが、主キーに自動でユニークな識別子を与える方法としてAUTO_INCREMENTにより自動採番された連番を用いる方法がよく使用されます。

しかし、AUTO_INCREMENTによる主キーを用いる場合、SQLのTRUNCATE文を使う際に注意すべき点があります。TRUNCATE文を使用すると指定したテーブルの全レコードをDELETE文より高速／低負荷で削除できますが、MySQLの場合は自動採番の連番も初期化され、新しく作成するレコードの主キーが削除したレコードの主キーと同一になってしまうなどの事故が発生する可能性があります。

本記事ではMySQLのAUTO_INCREMENTによる主キーを用いたテーブルにおけるTRUNCATE文の適切な運用方法と、おまけでPostgreSQLなら

後述の説明事例の前提とするテーブル構成

以下にMySQLの場合での一例を示します。例えばusersテーブルとpostsテーブルがあり、このテーブルはそれぞれ1対多の関係にあり、postsテーブルのuser_idがusersテーブルの主キーと結びついています。

usersテーブルのレコードにtaroという名前のユーザーがいます。TRUNCATE文でtaroのレコードを削除し、新しくjohnという名前のユーザーを作成します。このときjohnの主キーがtaroの主キーと同じ場合、taroのpostがjohnのpostになってしまいusersテーブルとpostsテーブルに矛盾が生じます。

taroのpostがjohnのpostになる問題を回避するためには削除したレコードとは異なる値を主キーにする必要があります。つまりTRUNCATE文実行前の自動採番の連番を維持する必要があります。

このTRUNCATE実行後の自動採番の連番設定に関してMySQLとPostgreSQLの違いを比較をしました。

実行環境

MySQLはMariaDB10.6.15で検証しました。

SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 10.6.15-MariaDB |
+-----------------+

PostgreSQLはMac上のPostgreSQL14.10で検証しました。

SELECT VERSION();
                                                             version                                                             
---------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.10 (Homebrew) on x86_64-apple-darwin22.6.0, compiled by Apple clang version 14.0.3 (clang-1403.0.22.14.1), 64-bit
(1 row)

MySQLでの連番の設定

MySQLでは自動採番による連番を設定する際にはINT型にAUTO_INCREMENT属性を使用します。主キーがAUTO_INCREMENT属性を持つusersテーブルを作成します。

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(10) DEFAULT NULL
);

usersテーブルにレコードを挿入します。

INSERT INTO users (name)
VALUES ("taro"), ("hanako");

usersテーブルを読み込むと、連番が振られていることが確認できます。

SELECT *
FROM users;
+----+--------+
| id | name   |
+----+--------+
|  1 | taro   |
|  2 | hanako |
+----+--------+
2 rows in set (0.006 sec)

MySQLではTRUNCATE文を実行すると全レコードが削除され、AUTO_INCREMENT値が1になります。AUTO_INCREMNET値をTRUNCATE文実行前の値に設定するには、以下の順番でSQL文を実行します。

1. TRUNCATE文を実行前の対象テーブルのAUTO_INCREMENT値取得
2. TRUNCATE文を実行
3. 1で取得したAUTO_INCREMENT値を対象テーブルに設定

TRUNCATE文を実行するテーブルの削除前のAUTO_INCREMENT値の取得

以下のSELECT文を実行することでAUTO_INCREMENT値を取得できます。

SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test'
AND TABLE_NAME   = 'users';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              3 |
+----------------+
1 row in set (0.022 sec)

INFORMATION_SCHEMA.TABLESとは、MySQLサーバー上にある全てのDBのテーブルの情報を持つテーブルになります。対象となるテーブルのAUTO_INCREMENTカラムからAUTO_INCREMENT値を取得します。

TRUNCATE文を実行

次にTRUNCATE文を実行します。

TRUNCATE TABLE users;

ここでレコードが存在していないことを確認します。

SELECT *
FROM users;
Empty set (0.001 sec)

また、AUTO_INCREMENT値が初期化されていることも確認できます。

SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test'
AND TABLE_NAME   = 'users';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              1 |
+----------------+
1 row in set (0.003 sec)

AUTO_INCREMENT値をテーブルに設定

最後にTRUNCATE文を実行したusersテーブルにAUTO_INCREMENT値を設定します。

ALTER TABLE users
AUTO_INCREMENT = 3;

これでAUTO_INCREMENT値の設定ができました。レコードを挿入すると、usersテーブルの連番が3から始まっていることが確認できます。

INSERT INTO users (name)
VALUES ("taro"), ("hanako");
Query OK, 2 rows affected (0.015 sec)
Records: 2  Duplicates: 0  Warnings: 0

SELECT *
FROM users;
+----+--------+
| id | name   |
+----+--------+
|  3 | taro   |
|  4 | hanako |
+----+--------+
2 rows in set (0.000 sec)

MySQLではこの一定の動作によってTRUNCATE後の自動採番の連番設定を行います。

PostgreSQLの連番の設定

PostgreSQLでは自動採番による連番を設定する際にはSERIAL型を使用します。主キーがSERIAL型のusersテーブルを作成します。

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(10) DEFAULT NULL
);

usersテーブルにレコードを挿入します。

INSERT INTO users (name)
VALUES ('taro'), ('hanako');

usersテーブルを読み込むと、連番が振られていることを確認できます。

SELECT *
FROM users;
 id |  name  
----+--------
  1 | taro
  2 | hanako
(2 rows)

PostgreSQLではTRUNCATE文を実行するとレコードは全削除されます。MySQLとは違って連番の値が1になることはないため、自動採番の連番設定の必要はありません。

TRUNCATE TABLE users;                 
INSERT INTO users (name)
VALUES ('taro'), ('hanako');
SELECT *                     
FROM users;
TRUNCATE TABLE
INSERT 0 2
 id |  name  
----+--------
  3 | taro
  4 | hanako
(2 rows)

そのためPostgreSQLでは削除したレコードの主キーが新しく作成した主キーと被ることは基本的にありません。

まとめ

MySQLとPostgreSQLでのTRUNCATE実行後の自動採番の連番設定の違いを説明しました。

MySQLではTRUNCATE文を実行すると自動採番の連番が初期化されるので、自動採番の連番設定の必要があります。

PostgreSQLではTRUNCATE文を実行する場合は自動採番の連番設定の必要はありません。またPostgreSQLではTRUNCATE文にRESTART IDENTITYオプションをつけると自動採番の連番を初期化できます。PostgreSQLではTRUNCATE後の自動採番の連番設定をユーザー側で選べるため、運用面に関しても便利になります。

補足事項

テーブル同士が外部キーによる関連性をもつ場合に、外部キー制約を使うとデータの整合性を自動で保つため、親テーブルのみにTRUNCATE文を実行するなどデータ不正号が発生する可能性がある場合にエラーを発生させ、関連テーブル同士のデータの整合性を保つことができます。