MySQL で採番テーブル


# LAST_INSERT_ID(expr) を使う方法

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

* MySQL 5.6 Reference Manual :: 12.14 Information Functions

<a href="http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id" target="_blank">LAST_INSERT_ID(), LAST_INSERT_ID(expr)</a>

> 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:

### テーブル準備

* 採番テーブル

```sql
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 のレコードを入れておく.

```sql
insert into seq values (0);
```


* データ用テーブル (採番された id を保存するだけ)

```sql
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
<?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
<?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 を使う方法

### テーブル準備

* 採番テーブル

```sql
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) パターンと同じ.

### 採番処理

```php
$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);
}
```

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

```php
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) パターンと同じ.

### 採番処理

```php
$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)
```