Help us understand the problem. What is going on with this article?

MySQL で採番テーブル

More than 3 years have passed since last update.

LAST_INSERT_ID(expr) を使う方法

公式ドキュメントで紹介されている方法.

  • MySQL 5.6 Reference Manual :: 12.14 Information Functions

LAST_INSERT_ID(), LAST_INSERT_ID(expr)

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is
returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID().
This can be used to simulate sequences:

テーブル準備

  • 採番テーブル
create table `seq` (
    `id` int(11) not null
) engine = InnoDB default charset = utf8;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

予め id = 0 のレコードを入れておく.

insert into seq values (0);
  • データ用テーブル (採番された id を保存するだけ)
create table `data` (
    `no` int(11) not null unique
) engine = InnoDB default charset = utf8;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| no    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

採番処理

<?php
/**
 * LAST_INSERT_ID に渡された引数は,
 * 次回の LAST_INSERT_ID() の返り値として記憶される.
 */
$res = $pdo->query('update seq set id = LAST_INSERT_ID(id + 1)');
if (!$res) {
    error_log('failed to update seq.', 3, $error_log_file);
    die();
}

list($no) = $pdo->query('select LAST_INSERT_ID()', PDO::FETCH_NUM)->fetch();
if (!is_numeric($no)) {
    error_log('failed to select LAST_INSERT_ID', 3, $error_log_file);
    die();
}

$stmt = $pdo->prepare('insert into data values (:no)');
$stmt->bindValue(':no', (int)$no, PDO::PARAM_INT);

$res = $stmt->execute();
if (!$res) {
    error_log(json_encode($stmt->errorInfo()) . PHP_EOL, 3, $error_log_file);
}

注意点

トランザクションの影響を受けるため、ロールバックした場合に同一のIDが採番される.

<?php
var_dump(currentSeq($pdo));
// "26"

if (!$pdo->beginTransaction()) {
    error_log('failed to begin transaction.', 3, $error_log_file);
    die();
}

// ...
// ... 採番処理 ...
// ...

if (!$pdo->rollBack()) {
    error_log('failed to rollback.', 3, $error_log_file);
    die();
}
var_dump(currentSeq($pdo));
// "26"

パフォーマンス確認

$ ab -n 5000 -c 50 http://{VM}/index.php
Time taken for tests:   39.566 seconds
Requests per second:    126.37 [#/sec] (mean)
Time per request:       395.656 [ms] (mean)

AUTO_INCREMENT を使う方法

テーブル準備

  • 採番テーブル
create table `seq_ai` (
    `id` int(11) not null auto_increment primary key
) engine = InnoDB default charset = utf8;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
  • データ用テーブルは LAST_INSERT_ID(expr) パターンと同じ.

採番処理

$res = $pdo->query('insert into seq_ai values (null)');
if (!$res) {
    error_log('failed to insert.', 3, $error_log_file);
    die();
}

list($no) = $pdo->query('select LAST_INSERT_ID()', PDO::FETCH_NUM)->fetch();

if (is_numeric($no)) {
    // 採番テーブルにたまるレコードを削除.
    $stmt = $pdo->prepare('delete from seq_ai where id < :no');
    $stmt->bindValue(':no', (int)$no, PDO::PARAM_INT);
    $stmt->execute();
} else {
    error_log('failed to select LAST_INSERT_ID', 3, $error_log_file);
    die();
}

$stmt = $pdo->prepare('insert into data values (:no)');
$stmt->bindValue(':no', (int)$no, PDO::PARAM_INT);

$res = $stmt->execute();
if (!$res) {
    error_log(json_encode($stmt->errorInfo()) . PHP_EOL, 3, $error_log_file);
}

トランザクションの影響を受けない

var_dump(currentAutoIncrement($pdo));
// "26"

if (!$pdo->beginTransaction()) {
    error_log('failed to begin transaction.', 3, $error_log_file);
    die();
}

// ...
// ... 採番処理 ...
// ...

if (!$pdo->rollBack()) {
    error_log('failed to rollback.', 3, $error_log_file);
    die();
}
var_dump(currentAutoIncrement($pdo));
// "27"

パフォーマンス確認

$ ab -n 5000 -c 50 http://{VM}/index_ai.php
Time taken for tests:   33.390 seconds
Requests per second:    149.75 [#/sec] (mean)
Time per request:       333.898 [ms] (mean)

アンチパターン(明示的なLOCK, UNLOCK)

  • 採番テーブル, データ用テーブルは LAST_INSERT_ID(expr) パターンと同じ.

採番処理

$res = $pdo->query('lock tables seq_anti write');

$res = $pdo->query('update seq_anti set id = id + 1');
if (!$res) {
    error_log('failed to update seq.', 3, $error_log_file);
    die();
}

list($no) = $pdo->query('select max(id) from seq_anti', PDO::FETCH_NUM)->fetch();
if (!is_numeric($no)) {
    error_log('failed to select id', 3, $error_log_file);
    die();
}

$res = $pdo->query('unlock tables');

$stmt = $pdo->prepare('insert into data values (:no)');
$stmt->bindValue(':no', (int)$no, PDO::PARAM_INT);

$res = $stmt->execute();
if (!$res) {
    error_log(json_encode($stmt->errorInfo()) . PHP_EOL, 3, $error_log_file);
}

パフォーマンス確認

$ ab -n 5000 -c 50 http://{VM}/index_anti.php
Time taken for tests:   90.504 seconds
Requests per second:    55.25 [#/sec] (mean)
Time per request:       905.045 [ms] (mean)
Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away