質問: 現在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する場合のベストパターンを確立できていないのか。"
回答: 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; ```