SELECT count(*) FROM XXX の件数と id が合わない

Question

MySQLでSELECT count(*) FROM XXX;で件数を確認しているのですが、AUTO_INCREMENTで振ったidと数字がかけ離れてしまっており、困っています。

対象の table への操作は INSERT と UPDATE のみで、DELETE は行っておりません。また、id が1から始まっていることも確認しています。

どうも根本的に何かを勘違いしている気がするのですが、上記状況に関してご説明いただける方はいますか？

よろしくお願いします。

Comments

[Mr_Roboto]

count(*)の数と、max(id)の数が違うということですか？

[退会済みユーザー]

ご迷惑をお掛けしました。

Answer 1

対象の table への操作は INSERT と UPDATE のみで、DELETE は行っておりません。また、id が1から始まっていることも確認しています。

外しているかもしれませんが、可能性の一つとして。
データ操作がアプリケーションから行われている場合、（システムエラー等）エラー発生時のロールバックで欠番が発生していることが考えられませんでしょうか？

参考URL
https://dev.mysql.com/doc/refman/5.6/ja/innodb-auto-increment-traditional.html

カウンタを使用して数値が生成されたトランザクションをロールバックすると、AUTO_INCREMENT カラムに割り当てられた一連の値でギャップが見つかることがあります。

Comments

[退会済みユーザー]

takyafumin さんの記述の意味がやっと少し理解できるレベルになりました。
INSERT ... ON DUPLICATE KEY UPDATE
の使用で、AUTO_INCREMENT カラムに飛び番が発生しておりました。
今更ですが、ありがとうございました。

[takyafumin]

状況の追記ありがとうございます。
原因解明まで進んだようで良かったです。
時間が経ってからでもこのように結果をコメントいただけると、回答者としてのモチベーションも上がり嬉しく思います。また、事例を検索して利用する方にも大変参考になる情報になるかと思います。

[退会済みユーザー]

正直、完全に理解できてるわけではないので、「こちらの回答が関係あったんだな」ってぐらいでしか評価できてないのが申し訳ないのですが、改めてコメントありがとうございます。

当時の理解としては、
「システム的なエラーの入る余地もない、当然ロールバックもないから事象と関係ないな！」って判断でした＾＾；理解不足であったため、コメントもできません。

今、リンク先見ると、かなり関連深いことが書かれていますね。
大変失礼しました。

Answer 2

すみません。まだ途中ですが、自己解決です。
id を1づつ増やして、確認したところ、欠落している箇所がありました。

欠落した理由は不明なのですが、お騒がせしてすみません。

Comments

[退会済みユーザー]

今更ですが、原因がわかりました。
INSERT ... ON DUPLICATE KEY UPDATE
を使用していたのですが、それが悪さをしていたようです。

http://ftp.nchu.edu.tw/MySQL/doc/refman/5.1-olh/ja/innodb-auto-increment-configurable.html

結果として、当時の私では結び付けられなかったのですが、takyafumin さんの指摘の一部が正しかったようです。
takyafumin さん今更ながら気が付きました。ありがとうございました。