勉強したことのメモ

webプログラマが勉強したことのメモ。

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

   

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

 

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

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

 

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

INSERT INTO test_table 
	(name, score) 
VALUES 
	("tanaka",90), ("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

 

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

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

 

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

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

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

 

注意したい点

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

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

 - MySQL

  関連記事

MySQLでWhere句をつけてdumpする

MySQLであるテーブルからWhere句をつけて、絞った結果をダンプさせたかった ...

エラー管理まとめ

■エラーログをとる。 error_reporting(E_ALL ^ E_NOT ...

1回のSQL文で複数のUPDATE

INSERTはバルクインサートを使えばいいが、 UPDATEはあまり聞かないので ...

htmlspecialcharsを通してMySQLに格納したものを元に戻して出力

MySQLにHTMLタグを入れたくない場合にhtmlspecialcharsを使 ...

MySQLで1対多のデータ結合と条件検索

MySQLで飲食店舗テーブルと休業日テーブルがあり、1店舗に対して複数行の休業日 ...

MySQLで構文エラーが出た際は予約語も疑う

MySQLで構文エラーが発生しており、チェックしたところ、どう見ても構文的には間 ...

MySQLのREGEXPで半角記号を検索したい場合

MySQLのREGEXPで半角記号を検索したい場合 \\を付けてエスケープしない ...

MySQLでランダムに取得

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

MySQLで大文字小文字

半角英数字のみのフォームで入力した値を検索した際、 データに差異が発生。 [Aa ...

MySQLでWhere句を使ってカラム指定でデータコピー

MySQLで大量のデータを保存しているテーブルがあり、そのデータを絞った上で尚且 ...