MySQLでオートインクリメントの値が意図した通りにならない件の解決法について

Question

現在MySQL 5.5.45、InnoDBで使っています。
様々な場面で、AUTO_INCREMENTを設定した主キーの値が意図したとおりの動作にならずに困ることがあります。

特に多いのが、

• INSERT...ON DUPLICATE KEY UPDATE
• INSERT IGNORE
• REPLACE

などを使った場合のインクリメントの仕方。

「SELECTしてその値が無かったらINSERT」は多くの人が望んでいる、「よくある動作」かと思われます。
一般的な人間の思考でいくと綺麗な連番になっていてほしいものですが、実際は値が飛び飛びになっていたりすることが多いですよね。

REPLACEなどは挙動をよく理解しておかないと致命的なことになりかねません。特にオートインクリメントしたidをサロゲートキーとして使っている場合は。「マニュアル読め」と言われればそれまでですが、一般的な人間の感覚からはだいぶズレた「MySQL（DB全般？）特有の動作」と言っていいのではないでしょうか。

---

参考：【MySQL】AUTO_INCREMENTの値が増える、飛ぶ、欠番が発生する at softelメモ

参考：insert duplicate インクリメント - Google 検索

参考：サロゲートキー - Google 検索

---

たとえば
id AUTO_INCREMENT 主キー
name unique
みたいなカラムがあるテーブルに「田中、鈴木、田中、山田」というデータを入れるために

INSERT IGNORE INTO `member` (`name`) VALUES(:name);

を投げると、id値は「1,2,4」となります。
実用上はそれでも問題ないのかもしれませんが、人間の心情的にはidを連続した値にしてほしいところです。

こういうとき、地道にSELECTして値の存在を確認してからINSERT・・・などと回りくどいことをせずに一発でスマートに（連番で）解決する方法は存在するのでしょうか？
それともデータベースとはそういうものなのだ、と諦めるしかないのでしょうか？

---

参考：kamipo TRADITIONALでは防げないINSERT IGNOREという名の化け物 | おそらくはそれさえも平凡な日々

参考：songmuさんのツイート: "我々は何故2015年にもなってSELECTして無かったらINSERTする場合のベストパターンを確立できていないのか。"

Answer 1

それともデータベースとはそういうものなのだ、と諦めるしかないのでしょうか？

諦めましょう。
同じような質問はteratailにいくつかありますので、参考にしてください。

• 【MySQL】auto_incrementの罠
• データベースのテーブルのIDを削除した時に空きを詰めたい。

Comments

[zico_teratail]

やはりそういうものですかね。

Answer 2

auto_incrementに対するそもそもの考え方が間違えていると思います
auto_incrementはレコードをconstに特定するためだけに利用すべきものです
したがって連番であるということは不要ですし、むしろ忘れてください
原則SQLにはレコード同士の間に相関関係はありません。
したがってデータが連続しているという考え方がSQL的発想ではありません。

なんらかの連番処理が必要であればSQL側でランキングを計算する手法が
幾つか用意されています。（ただし効率は悪いです）

sample

create table tbl (id int not null unique key,val varchar(20));
insert into tbl values(1,'aaa'),(2,'bbb'),(4,'ccc'),(50,'ddd'),(999,'eee');
create view vw as select id,(select count(*)+1 from tbl as t2 where t1.id>t2.id) as renban,val from tbl as t1;

表示

select * from vw;

Comments

[zico_teratail]

データベースの理論的にはそうであるのは承知しているのですが、「人間の感情・心理的に気持ち悪いので」という理由です。

phpMyAdminなどでざっと眺めたときに欠番があるとどうも気分が悪くて・・・

[yambejp]

> 欠番があるとどうも気分が悪くて・・・

という発想が間違っていると言いたいのですがうまく伝わってないようで残念です。
どうしても連番を表示したいならviewをつくっておけばいいでしょう。
（サンプルつけました、前述の通り効率は悪いですけどね）

それでも納得出来ないならデータが投入・変更・削除される度に毎回UPDATE構文でidを振り直すことです。
（SQLの運用方法としては最悪ですけどね）

[zico_teratail]

>という発想が間違っていると言いたいのですがうまく伝わってないようで残念です。

心情的な問題なので理論的に間違っているかどうかは関係ないと言いたいのですがうまく伝わってないようで残念です。

[yambejp]

了解、回答を削除依頼しておきました

[Panzer_vor]

超絶な横槍ですが。

プログラムの世界は理論から成り立ってるので、
直感には反することがあるのは仕方ないと受け止めるのが吉です。

直感通りに動かないとどうしても気に入らない、
要件を満たせないのであれば、結局自作するほかありません。

個人的にはデータベースのオートインクリメントやシーケンスの仕組みは、
アクセスのあったかという事実をトリガーに動作するものという認識なので、
言うほど違和感はないかなと思ってます。

考えようによれば、
キー重複による試行失敗分が記録として分かるため、
それはそれはありかなと思いますし。

[zico_teratail]

＞Panzer_vorさん
＞キー重複による試行失敗分が記録として分かる

なるほど、それは確かにメリットともいえますね。

[Panzer_vor]

> zico_teratailさん
当方のDBのオートインクリメントに考え方についは上記の通りですが、
オートインクリメント絡みはDBを跨いだデータ移行なとで、ちょくちょくトラブルを生むことがあるのでご注意を。

それもあり移植性と連番の正確性を重視すると、
テーブル操作が１つ余計には増えますが、採番テーブルを用意するのが堅実かもしれませんね。

採番テーブルでの発番から、
本テーブル登録時の採番までを１つのトランザクションとしたストアドを作れば使い回しも効きますし。

Answer 3

簡単なサンプルをストアドで。
考慮すべきことはまだあるし、不十分ですが。

#テーブル定義

CREATE TABLE `Sample` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#ストアド

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateOrInsert`(IN id INT, IN txt VARCHAR(16))
BEGIN

UPDATE Sample SET `name` = txt WHERE id = id;
IF (0 = ROW_COUNT()) THEN
	INSERT INTO Sample (id, `name`) VALUES (id, txt);
END IF;

END;;
DELIMITER ;

#実行

CALL updateOrInsert(1, 'aaa');

Comments

[zico_teratail]

ありがとうございます。
参考にさせていただきます。