重複エラー時のシーケンスIDの取得方法について

質問

いつも参考にさせて頂いております。
重複エラー時のシーケンスIDの取得方法についてご質問させて頂きます。

ユニークキー設定してあるテーブルにデータを挿入し、入っている行のAUTO_INCREMENTの値を取得したい場合、
挿入できるものに関しては挿入後LAST_INSERT_ID()で取得できますが、
重複エラーにより挿入されない場合の取得方法で悩んでおります。

挿入するデータには限りがあるので、挿入されたデータ量が増えるほど（時間が経てば経つほど）、
エラーが返る可能性が高くなり、その分の遅延がもったいないなぁと思っておりますが、
何かよい方法はありませんでしょうか？

よろしくお願い致します。

回答No.9

chukenkenkou · ベストアンサー

>重複値を挿入しようとした場合の、挿入試行からエラーが返るまでのコストはどのようなものでしょうか？

正常ケースならまだしも、エラーケースの情報を持っている企業・団体・個人は極めて少ないのではないでしょうか？
私もそういった情報は持ち合わせていませんし、実測するにも適切でない環境のため、残念ながらこの件に関しては回答できません。

一番いいのは、質問者さんの環境で実測してみることだと思います。
1回だけ重複エラーを発生させたのでは、時間が短すぎ実測できないかも知れません。数千回くらいエラーを発生させるといったことが必要かも知れませんが、この場合、データベースのI/Oバッファに、前回の情報が残っていることが考えられます。また、MySQLはどうかは詳しくないですが、商用RDBMSには、「これまでに前処理したSQLとまったく同じようなSQLの場合、前処理のオーバヘッドを抑止できる」といった機能を実装している場合もあります。
そのため、値を変えながら重複エラーを発生させるといった工夫をしないと、良い数値を拾ってしまうことになるかも知れません。

お礼

ご回答ありがとうございます。
今回は、時間との兼ね合いにより、以前に提示させて頂いた以下の方法で実装することにします。
---------------------------------------
先にSELECTでIDを取得し、無ければ挿入、
その後挿入に失敗すれば再度SELECT
---------------------------------------
この箇所がボトルネックとなるようであれば、ご提示頂いたストアドプロシージャ、エラーケースを検証してみたいと思います。
最後までお付き合いくださいまして、大変感謝です。
ありがとうございました。

回答No.8

chukenkenkou

#7の説明に一部誤りがありました。

(1)～(3)は、

あるキー値で検索し、存在したらその行のauto_increment列の値を得る。存在しなかったら、insertし、そのLAST_INSERT_ID()を得る。

の誤りでした。

リンク先は、上記内容に合致しています。

お礼

ご回答ありがとうございます。
確かにストアドプロシージャもありですね。
ただ、以前簡単に検証したところ、mysqlではネットワークのオーバーヘッドを考えないで速度を計測したところ、
アプリ側で処理するのと比べて、期待した速度が出ませんでした。
これを機に、また検証してみたいと思います。

最後に、一つだけ気になっていることがあります。
前回も質問させて頂きましたが、ユニークキー設定されているカラムに、
重複値を挿入しようとした場合の、挿入試行からエラーが返るまでのコストはどのようなものでしょうか？
それ次第では、先に挿入する方法もありなのかな、と思いまして。
よろしくお願いいたします。

回答No.7

chukenkenkou

以前、別の方の質問で、

(1)あるキー値で検索
→存在したら(3)へ
(2)(1)のキー値で追加
(3)LAST_INSERT_ID()を得る

といった操作を、「1回のクエリでやれないか？」という質問がありました。
「Perlでは、そういうメソッドがあるらしい（？）が、phpでやれないか？」とのことだったので、ストアド・プロシジャで実装する例を提示しました。

今回の質問も、やりたいことは同じですよね？

ストアド・プロシジャにすれば、サーバ側での処理になるので、複数クエリをクライアントから実行する場合に比べ、往復でのオーバーヘッドは軽減できると思います。

参考URL：

http://oshiete1.goo.ne.jp/qa3201668.html

回答No.6

chukenkenkou

>最終的に、データの管理番号（idの値）を得たいので、IDが必要なのですが

#3回答にて、以下を回答済です。

「重複エラーでは、auto_incrementの値は更新されない（あるいは値が戻される）ようなので、既に格納済の値しか得られません。」

また、重複エラー時のauto_incrementの値は、#4回答のSQLで推測はできます。

「select max(id)+1 from testtable」

ただし、他ユーザからの追加があった場合は、既にその値は使用されている可能性があります。

>最終的に、データの管理番号（idの値）を得たいので、IDが必要なのですが。

＝＝＝＝＝質問に対する直接の回答（ここから）＝＝＝＝＝

こういう要件があるなら、「auto_incrementは使えない。自前で最大値を拾って+1するしかない」ということになります。

＝＝＝＝＝質問に対する直接の回答（ここまで）＝＝＝＝＝

>冗長をなくすため、データ量を減らすため、またJOINなどのキーになることも想定しているためにID化する必要があると思った次第です。

発想を変えてですが、重複エラーを起こさせなければならない理由があるのでしょうか？
例えば下記のように、auto_incrementの列を2番目のキーの構成要素とし、単語毎に通番を付けるというのはどうでしょうか？

create table 表名
(単語 varchar(n),
通番 int auto_increment,
primary key(単語,通番))

これなら重複エラーは発生しませんし、単語＋通番で一意に管理もできます。ジョインする場合も、単語でグループ化（group by）すれば問題ないはずです。
冗長にはなりますが、一定期間毎に「通番が2以上の行」を削除すれば、その問題も解決します。

いずれにしても、「他の列値で重複エラー時、そのときのauto_increment列の値を知りたい」という形では、前に進めないと思います。

お礼

ご回答ありがとうございます。
ご提案も、とても参考になりました。
ただ、今回はデータの管理番号（INT）を得るということを前提とさせて頂きますので
最後に、もう一度仕様とこの質問をさせて頂いた経緯を（単純化するためにデータを英単語に代えて）
説明させて頂きます。

[仕様]
英単語をユーザーに入力してもらい、それを逐一ログに記入する仕組みを作成。
記入する内容は、（英単語の管理番号（INT）、時間）

[経緯]
はじめに、
１．先にSELECTで単語のIDを取得し、あればそのIDを取得、無ければ挿入
という方法でやる方針でしたが、せっかくのユニークキーなので、その特性を生かすように
２．先に挿入し、挿入できればそのIDを取得し、できなければ（すでに挿入されている）SELECTでIDを取得する
という方法を思いつき、それではデータ量が増えるほど挿入できないことが多くなるので、最終的に自分の中で
３．先にSELECTでIDを取得し、無ければ挿入、その後挿入に失敗すれば再度SELECT
という方法にまとまりました。しかし、IDを得るまでのプロセスが長いような気もしたため、
もう少し簡単に取得できないかと思い、投稿しました次第です。

２の、挿入できない場合に、挿入試行からエラーが返るまでのオーバーヘッドがほとんどかからないようでしたら
２でも問題ないかと思うのですが、実際のところいかがでしょうか？
それを踏まえて、最終的には２、３番どちらがよいでしょうか？
または、他の方法でもっとよいと思われる方法がありますでしょうか？

今まで遠回りに質問したために、大変ご迷惑をおかけしており申し訳ありません。
以上、何卒よろしくお願いいたします。

回答No.5

yambejp

#2です

>この様なことをしたい場合は多いような気がしますが、一般にはどのように
>すべきでしょうか？

前回の書き込みでも書いたとおりエラーを発生させず、
そのデータを活かして更新するかそのデータを無視して捨てるのが一般的でしょう。
「IDを得る」必要はありません。

IDを得たからといて、結局無視するか捨てるしかないのですから。
無視した場合にエラーかどうかを判断するには、処理プログラムの方で
やればすむことですから。（たとえばPHPならmysql_affect_rows()など
の値を検証するなど）

お礼

ご回答ありがとうございます。
>前回の書き込みでも書いたとおりエラーを発生させず、
>そのデータを活かして更新するかそのデータを無視して捨てるのが一般的でしょう。
>「IDを得る」必要はありません。
最終的に、データの管理番号（idの値）を得たいので、IDが必要なのですが。

申し訳ありませんが、あまり理解できませんでしたので、
大変お手数ですが#3の回答へのお礼で書かせて頂いた例に沿って
ご返答頂けますと幸いです。

回答No.4

chukenkenkou

#1、#3回答者です。

#2回答でも書きましたが、auto_incrementの値は、重複エラーでは更新されないようです。
auto_incrementの値が他ユーザの追加により更新されている可能性がありますが、その時点での最大値は、
「select max(id) from testtable」
で得られますし、次に使用される値は、
「select max(id)+1 from testtable」
で得られます。

#3でも書いたように、何をやりたいのか、具体的にかいてもらえると、回答者側も具体的に回答できます。

お礼

ご回答ありがとうございます。
重複した場合に、最大値ではなく、重複した際の、その行の主キー（以前の例ですと「id」）が取得したいのです。
もう少し分かりやすいと思われる例を#3の回答のお礼に記入させて頂きました。

尚、遅れましたが、環境は以下です。
mysql5.0.44
ストレージエンジンは全てMyIsam

以上、何度もお手数かけておりますが、よろしくお願い致します。

回答No.3

chukenkenkou

#1回答者です。

num列の役割が分かりません。
insertするnum列の値は、どうやって決めているのでしょうか？それが分からなければ、「データ量が増えるほど、重複エラーが返る可能性が高くなる」という理由も分かりません。

また、insertでnum列の値の重複エラーが発生した場合、「その時のid列の値を知りたい」という理由も分かりません。
isnertで重複エラーが発生したら、updateするという操作は一般的に多いですが、この表で何をしたいのでしょうか？

>その方法は自分では２通り思いつきました。
>１．重複エラーの後、SELECT id FROM testtable WHERE num = 1
>２．そもそもですが、先に SELECT id FROM testtable WHERE num = >1 実行後、
>　　なければ挿入、万が一重複エラーの場合はもう一度 SELECT id >FROM testtable WHERE num = 1
>このときの重複した num の id を取得したいのです

重複エラーでは、auto_incrementの値は更新されない（あるいは値が戻される）ようなので、既に格納済の値しか得られません。

2の場合は、検索後、他ユーザで更新される可能性はないのですか？

何をやりたいのか、具体的に示してもらった方が、解決への近道かも知れません。

お礼

例えが分かりにくく、何度も申し訳ありません。

>num列の役割が分かりません。
>insertするnum列の値は、どうやって決めているのでしょうか？それが分からなければ、「データ量が増えるほど、重複エラーが返る可能性が高くなる」という理由も分かりません。
数字だと分かりにくいので、例として「英単語マスター」というテーブルを作る、でお願いします。
（仕事上、実際のシステムの提示は控えさせて頂きます。ご了承下さい。）
挿入するデータ（英単語）には数が限られていると、また、更新することもないという前提でお願いします。

>また、insertでnum列の値の重複エラーが発生した場合、「その時のid列の値を知りたい」という理由も分かりません。
>isnertで重複エラーが発生したら、updateするという操作は一般的に多いですが、この表で何をしたいのでしょうか？
前述の英単語マスターに、ユーザーから英単語を入力してもらい、その英単語を登録する際に、
登録したものにはAUTO_INCREMENTのID（INT）がつき、そのIDを「ログテーブル」等に使用するといった感じです。
そもそもその英単語をそのまま入れればよいかもしれませんが、
冗長をなくすため、データ量を減らすため、またJOINなどのキーになることも想定しているために
ID化する必要があると思った次第です。

この様なことをしたい場合は多いような気がしますが、一般にはどのようにすべきでしょうか？
よろしくお願い致します。

回答No.2

yambejp

エラー後どうしたいかによるでしょう。
重複エラーを発生させるわけですから、単にINSERTはできないです。
エラーした行を有効にするのか無効にするのかによって処理は
異なるはずです。

単純にエラーを返さないには、INSERT IGNORE INTOしてやればすみます。
こうすると重複エラーをおこした行は無効になります。
そのあとUPDATEをかければ重複エラーをおこした行は有効になります。
（もとデータが更新されてよければ）

お礼

すいません、恐らく初めの質問では意味が不明かと思われますので
No1の回答のお礼にもう一度質問させて頂きました。
よろしければそちらをご覧頂ければと思います。
お手数ですがよろしくお願い致します。

回答No.1

chukenkenkou

何を言っているのか、分かりにくいのですが。。。

>入っている行のAUTO_INCREMENTの値を取得したい場合、挿入できるものに関しては挿入後LAST_INSERT_ID()で取得できます

「挿入できるもの」ではなく、「挿入したもの」ですよね？
しかも、表の最大値ではなく、自分が最後に挿入した値です。
他ユーザが最大値を挿入していた場合、LAST_INSERT_ID()では「表の最大値」は得られません。

>挿入するデータには限りがあるので、挿入されたデータ量が増えるほど（時間が経てば経つほど）、
>エラーが返る可能性が高くなり、その分の遅延がもったいないなぁと思っております

何を言いたいのか分かりません。具体的に説明してください。

お礼

すいません、例を挙げて質問からさせて頂きます。

まず、以下のようにテーブル作成します。
CREATE TABLE testtable (
id INT NOT NULL AUTO_INCREMENT,
num INT,
PRIMARY KEY(id),
UNIQUE INDEX unique_num(num)
);

そして、
INSERT INTO testtable (num) VALUES (1);
を実行。
LAST_INSERT_ID()で取得すれば、挿入された num の id を取得可能

続いて
INSERT INTO testtable (num) VALUES (1);
を実行した場合、エラーが返るが、
このときの重複した num の id を取得したいのです。

その方法は自分では２通り思いつきました。
１．重複エラーの後、SELECT id FROM testtable WHERE num = 1
２．そもそもですが、先に SELECT id FROM testtable WHERE num = 1 実行後、
　　なければ挿入、万が一重複エラーの場合はもう一度 SELECT id FROM testtable WHERE num = 1

１だと、データ量が増えるほど（時間が経つほど）エラーが返る可能性が高くなり、
その分の遅延が無駄かと思い、少しでも遅延を減らそうと思い、２を考えた次第です。

以上をご評価頂き、他に方法がありましたらご教授頂ければと思います。
よろしくお願い致します。