LAST_INSERT_ID、AUTO_INCREMENT、SEQUENCE：どれを選ぶ？
2024-04-08
MySQLで整数のシーケンスを生成する方法
方法1：LAST_INSERT_IDを使用する
この方法は、以下の手順で実行します。
- 採番用のテーブルを用意します。
CREATE TABLE sequence (
id INT NOT NULL
);
LAST_INSERT_IDを使用して、シーケンスの次の値を取得します。
INSERT INTO sequence VALUES (NULL);
SELECT LAST_INSERT_ID();
この方法の利点は、シンプルで分かりやすいことです。ただし、以下の点に注意する必要があります。
- ロールバックすると、同一のIDが再度発行される可能性があります。
UPDATEステートメントで排他ロックがかかります。
方法2：AUTO_INCREMENTを使用する
CREATE TABLE seq_ai (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
- 新規レコードを挿入し、
LAST_INSERT_IDでシーケンスの次の値を取得します。
INSERT INTO seq_ai VALUES (NULL);
SELECT LAST_INSERT_ID();
- 不要になったレコードを削除します。
DELETE FROM seq_ai WHERE id < :id; -- 2で取得した最新のレコードを残して削除
この方法の利点は、以下のことです。
- 排他ロックがかかりません。
方法3：方法1 + ストレージエンジンをMyISAMにする
方法1の注意点を解決するために、採番用テーブルのストレージエンジンをMyISAMに変更する方法です。
CREATE TABLE sequence (
id INT NOT NULL
) ENGINE=MyISAM;
- シーケンスの次の値を取得します。
UPDATE sequence SET id=LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();
ただし、MyISAMはInnoDBに比べて機能が限定されているため、注意が必要です。
MySQLで整数のシーケンスを生成するには、いくつかの方法があります。それぞれの方法には利点と欠点があるため、目的に応じて適切な方法を選択する必要があります。
方法1：LAST_INSERT_IDを使用する
-- 採番用のテーブルを用意
CREATE TABLE sequence (
id INT NOT NULL
);
-- シーケンスの次の値を取得
INSERT INTO sequence VALUES (NULL);
SELECT LAST_INSERT_ID();
-- 例
-- INSERT INTO sequence VALUES (NULL);
-- SELECT LAST_INSERT_ID();
-- 結果: 1
-- ロールバックすると、同一のIDが再度発行される可能性がある
-- 例
-- START TRANSACTION;
-- INSERT INTO sequence VALUES (NULL);
-- SELECT LAST_INSERT_ID(); -- 結果: 2
-- ROLLBACK;
-- INSERT INTO sequence VALUES (NULL);
-- SELECT LAST_INSERT_ID(); -- 結果: 2
-- `UPDATE`ステートメントで排他ロックがかかる
-- 例
-- UPDATE sequence SET id = id + 1 WHERE id = 1;
-- SELECT LAST_INSERT_ID(); -- ロック解除まで待機が必要
方法2：AUTO_INCREMENTを使用する
-- 採番用のテーブルを用意
CREATE TABLE seq_ai (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
-- シーケンスの次の値を取得
INSERT INTO seq_ai VALUES (NULL);
SELECT LAST_INSERT_ID();
-- 不要になったレコードを削除
DELETE FROM seq_ai WHERE id < :id; -- 2で取得した最新のレコードを残して削除
-- 例
-- INSERT INTO seq_ai VALUES (NULL);
-- SELECT LAST_INSERT_ID(); -- 結果: 1
-- DELETE FROM seq_ai WHERE id < 1;
-- ロールバックしても、同一のIDが再度発行されることはない
-- 例
-- START TRANSACTION;
-- INSERT INTO seq_ai VALUES (NULL);
-- SELECT LAST_INSERT_ID(); -- 結果: 2
-- ROLLBACK;
-- INSERT INTO seq_ai VALUES (NULL);
-- SELECT LAST_INSERT_ID(); -- 結果: 3
-- 排他ロックがかかりません
-- 例
-- UPDATE seq_ai SET id = id + 1 WHERE id = 1;
-- SELECT LAST_INSERT_ID(); -- 待機なし
方法3：方法1 + ストレージエンジンをMyISAMにする
-- 採番用のテーブルを用意
CREATE TABLE sequence (
id INT NOT NULL
) ENGINE=MyISAM;
-- シーケンスの次の値を取得
UPDATE sequence SET id=LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();
-- 例
-- UPDATE sequence SET id=LAST_INSERT_ID(id+1);
-- SELECT LAST_INSERT_ID(); -- 結果: 1
-- ロールバックしても、同一のIDが再度発行されることはない
-- 例
-- START TRANSACTION;
-- UPDATE sequence SET id=LAST_INSERT_ID(id+1);
-- SELECT LAST_INSERT_ID(); -- 結果: 2
-- ROLLBACK;
-- UPDATE sequence SET id=LAST_INSERT_ID(id+1);
-- SELECT LAST_INSERT_ID(); -- 結果: 3
-- 排他ロックがかかりません
-- 例
-- UPDATE sequence SET id = id + 1 WHERE id = 1;
-- SELECT LAST_INSERT_ID(); -- 待機なし
-- MyISAMはInnoDBに比べて機能が限定されている
-- 例
-- 外部キー制約を設定できない
-- トランザクションのACID特性が一部制限される
方法4：SEQUENCEデータ型を使用する (MySQL 8.0以降)
MySQL 8.0以降では、SEQUENCEデータ型を使用して、シーケンスを生成することができます。
-- 採番用のシーケンスを作成
CREATE SEQUENCE seq_no START WITH 1;
-- シーケンスの次の値を取得
SELECT nextval('seq_no');
-- 例
-- SELECT nextval('seq_no'); -- 結果: 1
-- ロールバックしても、同一のIDが再度発行されることはない
-- 例
-- START TRANSACTION;
-- SELECT nextval('seq_no'); -- 結果: 2
-- ROLLBACK;
-- SELECT nextval('seq_no'); -- 結果: 3
-- 排他ロックがかかりません
-- 例
-- SELECT nextval('seq_no');
-- SELECT nextval('seq_no'); -- 待機なし
-- MySQL 8.0以降でのみ使用可能
方法5：トリガーを使用する
トリガーを使用して、レコード挿入時に自動的にシーケンス値を生成することができます。
-- 採番用のテーブルを用意
CREATE TABLE sequence (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
seq_no INT NOT NULL
);
-- トリガーを作成
CREATE TRIGGER before_insert_sequence
BEFORE INSERT ON sequence
FOR EACH ROW
BEGIN
SET NEW.seq_no = LAST_INSERT_ID() + 1;
END;
-- シーケンスの次の値を取得
INSERT INTO sequence VALUES (NULL);
SELECT seq_no FROM sequence ORDER BY id DESC LIMIT 1;
-- 例
-- INSERT INTO sequence VALUES (NULL);
-- SELECT seq_no FROM sequence ORDER BY id DESC LIMIT 1; -- 結果: 1
-- ロールバックしても、同一のIDが再度発行されることはない
-- 例
-- START TRANSACTION;
-- INSERT INTO sequence VALUES (NULL);
-- SELECT seq_no FROM sequence ORDER BY id DESC LIMIT 1; -- 結果: 2
-- ROLLBACK;
-- INSERT INTO sequence VALUES (NULL);
-- SELECT seq_no FROM sequence ORDER BY id DESC LIMIT 1; -- 結果: 3
-- 排他ロックがかかりません
-- 例
-- INSERT INTO sequence VALUES (NULL);
-- INSERT INTO sequence VALUES (NULL); -- 待機なし
-- トリガーの管理が複雑になる
方法6：アプリケーション側で生成する
アプリケーション側でシーケンス値を生成する方法もあります。
# Python の例
def generate_sequence_no():
return int(time.time() * 1000)
# シーケンスの次の値を取得
sequence_no = generate_sequence_no()
# 例
# sequence_no = generate_sequence_no()
# print(sequence_no) -- 結果: 1586745678912
# ロールバックの影響を受けない
# 例
# sequence_no = generate_sequence_no()
# print(sequence_no)
# 排他ロックがかかりません
# 例
# sequence_no_1 = generate_sequence_no()
# sequence_no_2 = generate_sequence_no()
# アプリケーション側の処理が複雑になる
mysql
