この場合のSQL構文を教えてください（MYSQL5.0)

質問

お世話になります。下記の場合どうしたらできるかどうか教えてください。
・商品リストテーブル
　商品ID（int primary key auto_increment　)
　商品名(char)
・受注リストテーブル
　受注ID（int primary key auto_increment　)
　商品ID（int)
　注文者（char)

上記のように二つのテーブルに分かれています。
(1)新商品の注文が入ると、商品リストテーブルにレコードがINSERTされ
受注リストにも同時にINSERTされるとします。
(2)すでに商品リストテーブルに登録済みの商品名の場合は受注リストテーブルのみINSERTされます。
この場合、商品リストの商品IDは自動インクリメントされるので問題ないですが、受注リストの商品IDはインクリメントされません。
商品リストに新レコードができると、このリストの商品IDは新しい番号がふられますが、この新しくできた番号を受注リストの商品IDに同時にいれたいのです。
○方法として
二つのテーブルをJOINしてレコードをINSERTできるのならしたいのですが、どうゆうSQL文を書けばいいのかわかりません。
またその方法以外にとりあえず商品リストテーブルにだけINSERTしてそのタイミングで受注リストテーブルにトリガでINSERTとかする方法もありなんでしょうか？
できればJOINした方にINSERTする方法でやりたいのですが、無理なら後者のほうでもいいんですが・・・。
SQL文わかる方どうかよろしくお願いいたします。

回答No.3

chukenkenkou · ベストアンサー

#1、#2回答者です。

商品リストの商品名に重複された値が格納される可能性がありますが、その場合はどう対処するのでしょうか？

(1)何らかの仕組みを作って、重複する商品名が登録されないようにする？
(2)同一の商品名が登録された場合は、もっとも新しく登録された商品名の商品IDを有効とする？

一連の処理をストアドプロシジャで実装した場合の例を、以下に示します。同等の処理を、アプリケーション側で実装することも、もちろん構いません。その場合は、ストアドプロシジャ中でのSQLの記述を参考にしてください。

１．テスト用表定義
create table 商品リスト
(商品ID int primary key auto_increment,
商品名 varchar(30));
create table 受注リスト
(受注ID int primary key auto_increment,
商品ID int,
注文者 varchar(10));

２．テスト用ストアドプロシジャの定義
drop procedure if exists sp_Juchu; -- 存在したら削除
delimiter // -- 終端記号の変更
create procedure sp_Juchu
(in p商品名 varchar(30),
in p注文者 varchar(10),
out rc int,
out msg varchar(30))
begin
declare v表名 varchar(30);
declare v行数 int;
declare ok int default 0;
declare exit handler for sqlexception
begin
set rc=16;
set msg=concat('表=',v表名,'の操作で例外が発生');
end;
--
set rc=ok,msg='';
select count(*) into v行数 from 商品リスト where 商品名=p商品名;
if v行数=0 then
select concat('商品名=(',p商品名,')は商品リストに存在しない') as trace_msg;
set v表名='商品リスト';
insert into 商品リスト values(null,p商品名);
select concat('商品名=(',p商品名,')を商品リストに追加した') as trace_msg;
elseif v行数>1 then
set rc=12,msg=concat('商品名=(',p商品名,')が商品リストに2件以上存在する');
end if;
if rc=ok then
set v表名='受注リスト';
insert into 受注リスト
select null,商品ID,p注文者 from 商品リスト where 商品名=p商品名;
select concat('商品名=(',p商品名,')、注文者=(',p注文者,')を受注リストに追加した') as trace_msg;
end if;
end;
//
delimiter ; -- 終端記号を元に戻す

３．テスト
(1)行数を0件にする
truncate table 商品リスト;
truncate table 受注リスト;

(2)テスト実施
MySQL Command Line Clientからの実行例です。@rcや@msgは、MySQL Command Line Clientで使用できる変数です。アプリケーションから呼び出す場合は、アプリケーション中の変数を使ってください。

call sp_Juchu('テレビ','川口',@rc,@msg);
select @rc,@msg;
call sp_Juchu('ラジオ','宮本',@rc,@msg);
select @rc,@msg;
call sp_Juchu('エアコン','玉田',@rc,@msg);
select @rc,@msg;
call sp_Juchu('ラジオ','宮本',@rc,@msg);
select @rc,@msg;
call sp_Juchu('テレビ','宮本',@rc,@msg);
select @rc,@msg;

(3)結果確認
select * from 商品リスト
order by 商品ID;
select * from 受注リスト
order by 受注ID;

お礼

連休にて返事が遅れてしまいました。申し訳ありません。
プロシジャ構文、大変参考になりました。
ここまで詳細にご回答いただき感謝の言葉もありません。
本当にありがとうございました。

回答No.2

chukenkenkou

#1回答者です。

>商品リストテーブルにだけINSERTしてそのタイミングで受注リスト
>テーブルにトリガでINSERTとかする方法もありなんでしょうか

商品リストのinsert背景で動くトリガで受注リストにinsertしようとした場合、「注文者」列に格納する値を渡す仕組みを作らなければなりません。

一連の処理をストアドプロシジャで定義して、「call sp受注(商品名,注文者)」といったパラメタで呼び出し実行する方法もあります。

お礼

ANo.１のお礼の続きです。
>「注文者」列に格納する値を渡す仕組みを作らなければなりません
流れとしては、WEBページからFORMで
商品名と注文者がservletに送られ、その二つの値をMYSQLに送るのですが、その際にservlet側からMYSQLに命令するMYSQL文（二つのテーブルにレコードをINSERTする文）がわからず困っています。
LAST_INSERT_ID()が使えないとなると、商品リストテーブルに商品名のレコードINSERT⇒商品リスト.新商品IDを何らかの方法で取得⇒取得した新商品IDと注文者の名前を受注リストテーブルにINSERTとなると思うのですが・・・
>ストアドプロシジャで定義して、「call sp受注(商品名,注文者)」といったパラメタで呼び出し実行
この方法がベストなのでしょうか？
それとも地味に
商品リストテーブルに商品名のレコードINSERT⇒商品リスト.新商品IDを何らかの方法で取得⇒取得した新商品IDと注文者の名前を受注リストテーブルにINSERT
のほうが良いでしょうか？
どちらかよさそうな方のSQL文をどうかご教授願います。

回答No.1

chukenkenkou

>二つのテーブルをJOINしてレコードをINSERTできるのならしたい

対応するキー値が存在しない状態の話なので、結合相手が決められないのでは？

各接続ユーザ毎に、auto_incrementで生成した最新の値を、「LAST_INSERT_ID()」関数で拾えます。

(1)のケース
商品リストテーブルにinsertした直後に、以下のSQLを実行する。
insert into 受注リストテーブル value(null,LAST_INSERT_ID(),'xxxx')

(2)のケース
insert into 受注リストテーブル
select null,商品ID,'xxxx' from 商品リストテーブル
where 商品名='yyyy'

商品名や注文者は、charでなく、varcharの方がいいのでは？名称が固定長ということはないですよね？

お礼

回答ありがとうございます。レスが長くなりそうなのでANo.２の方と分けて書かせていただきます。
>対応するキー値が存在しない状態の話なので、結合相手が決められないのでは？
　よく考えるとそうですよね(；・∀・)
ということは別々にinsertするしかないですよね。
>各接続ユーザ毎に、auto_incrementで生成した最新の値を、「LAST_INSERT_ID()」関数で拾えます。
　実は今回のケースではservletからMYSQLに渡されるのですが、コネクションプールを使用しており、すでに張られているコネクションを再利用してMYSQLにアクセスする可能性が高いのでLAST_INSERT_ID()を使うと、問題が発生する可能性が高くなりそうです。（コネクションが再発行されていないためLAST_INSERT_ID()が不正確な値になりそうです）
恐縮なのですがLAST_INSERT_ID()を使わない方法を教えていただけないでしょうか？