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で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で大量のデータを保存しているテーブルがあり、そのデータを絞った上で尚且 ...
- PREV
- WordpressでNo URL was~というエラー
- NEXT
- linuxのシェルスクリプト