InnoDBのオートインクリメント

2016/06/25 2016/07/01

MySQLのInnoDBでIDというカラムにオートインクリメント設定していたが、「1.2.3.4.5」と順番にならずに「1.5.9.10.13」と飛び飛びになっている現象があり、それが原因で障害が発生した。

見出し

1 飛び飛びになっていた理由
2 実験
3 注意したい点

飛び飛びになっていた理由

INSERT ~ ON DUPLICATE KEY UPDATEを使った場合、次回のインサート時はIDが飛んでしまう模様。以下実験を行った。

実験

まず以下のようなテーブルを作った。テーブル名はtest_table。IDと名前と何らかの得点的なテーブル。

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   | UNI | NULL    |                |
| score | int(11)      | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

+-------+--------------+------+-----+---------+----------------+

+-------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| score | int(11) | NO | | NULL | |

+-------+--------------+------+-----+---------+----------------+

データを2行入れてみる。

INSERT INTO test_table 
	(name, score) 
VALUES 
	("tanaka",90), ("yamada",88)

INSERT INTO test_table

(name, score)

VALUES

("tanaka",90), ("yamada",88)

この時点でのデータは以下の通り。

+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | tanaka |    90 |
|  2 | yamada |    88 |
+----+--------+-------+

+----+--------+-------+

| id | name | score |

+----+--------+-------+

| 1 | tanaka | 90 |

| 2 | yamada | 88 |

+----+--------+-------+

INSERT ~ ON DUPLICATE KEY UPDATEで更新をかけてみる。

INSERT INTO test_table 
	(name, score)
VALUES
	("tanaka", 99)
ON DUPLICATE KEY UPDATE
	score = 99

INSERT INTO test_table

(name, score)

VALUES

("tanaka", 99)

ON DUPLICATE KEY UPDATE

score = 99

この時点でのデータは以下の通り。

+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | tanaka |    99 |
|  2 | yamada |    88 |
+----+--------+-------+

+----+--------+-------+

| id | name | score |

+----+--------+-------+

| 1 | tanaka | 99 |

| 2 | yamada | 88 |

+----+--------+-------+

先ほどは更新だったけど、インサートになるようにINSERT ~ ON DUPLICATE KEY UPDATEを使う。

INSERT INTO test_table 
	(name, score)
VALUES
	("suzuki", 15)
ON DUPLICATE KEY UPDATE
	score = 15

INSERT INTO test_table

(name, score)

VALUES

("suzuki", 15)

ON DUPLICATE KEY UPDATE

score = 15

すると以下の通りIDが飛んでしまう。

+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | tanaka |    99 |
|  2 | yamada |    88 |
|  4 | suzuki |    15 |
+----+--------+-------+

+----+--------+-------+

| id | name | score |

+----+--------+-------+

| 1 | tanaka | 99 |

| 2 | yamada | 88 |

| 4 | suzuki | 15 |

+----+--------+-------+

尚、MyISAMで同様のテストをしたところちゃんと連番になった。

注意したい点

データが飛ぶのは問題なかったんだけど、頻繁に更新するプログラムだった為、IDがint型の上限を超えてしまうということが発生してしまった。しかも常時更新タイプのものだったのでその時点で障害が発生。

InnoDBでINSERT ~ ON DUPLICATE KEY UPDATEを使うときの挙動は覚えておく。

- MySQL

: MySQLで実行結果の行数取得

DBにデータがあるか調べる際にしばしば使用してたのでメモ。 $sql = " S ...

: MySQLのtinyintのカラムにbool値を入れた場合

他所のシステムに携わることがあり中身を色々見ているとMySQLでtinyint( ...

: MySQLでランダムに取得

SELECT カラム名 FROM テーブル名 ORDER BY RAND() L ...

: MySQLでテーブル構造とデータをコピーする

MySQLで既にあるテーブルとデータに対して実験を行う必要があった。本番テーブル ...

: 直近のクエリで発行されたAUTO_INCREMENTの値を取得する

やりたかったことは、新規登録(INSERT)なら、登録時のAUTO_INCRE ...

: MySQLで開始から終了日付内を調べたいとき

等号不等号でもできるけど、BETWEENというのがよさそう。 ■参考サイト ht ...

: プリペアドステートメント

個人的に得た内容として、SQL文だけ先にDBに送って、変数は後で送る。だからS ...

: お名前SDサーバにMySQLデータのインポート

お名前.comのレンタルサーバにそこそこ大きいサイズのMySQLデータを移行した ...

: 文字コードのutf8_general_ciとutf8_unicode_ciの違い

MySQLで設定する時に使っているサーバーによってデフォルトの文字コードがutf ...

: PHPからMySQLデータを取得した際に一括で配列に入れる方法

MySQLからデータを取得して、PHP側で配列に入れる際に今まで一個ずつ書いてい ...

PREV: WordpressでNo URL was～というエラー
NEXT: linuxのシェルスクリプト

InnoDBのオートインクリメント

飛び飛びになっていた理由

実験

注意したい点

関連記事