windows7のmysqlでレコード単位の更新を行ないたいが良く分からない。

Question

table1のフィールドがsql1のみで内容はsqlのupdate文が格納されてい
ます。そして、このtable1のsql1を読んでその内容で更新クエリーを実
行するとした場合は、どの様にmysqlでは処理を記述すれば良いのかと
言うのが分かりません。

例えば、table1が下記の場合はどう言う記述になるのでしょうか。
update result1 set target1=7 where id between 1 and 13;
update result1 set target1=12 where id between 14 and 20;
update result1 set target1=20 where id between 21 and 31;

この場合は、table1を読んで、sql1の更新クエリーを実行したいのです
が。個別にレコード単位に処理をする所がプロシージャをどの様に使う
のかと言うのが良く分かりません。

済みません。sqlの例示をお願いします。

yambejp · Accepted Answer

＞SET @sql=CONCAT('update result1 set target1=? where id between ? and ?;');
＞上記で、?をブレースホルダーと言い、

まぁプレースホルダ自体は比較的一般的なIT用語ですね
http://e-words.jp/w/%E3%83%97%E3%83%AC%E3%83%BC%E3%82%B9%E3%83%9B%E3%83%AB%E3%83%80.html

＞EXECUTE stmt USING @a,@b,@c;
＞この時に実際の指定をするのがバインドと言う事でしょうか。

私もプロではないので、厳密な用語として正しいかどうかといわれると怪しいですが
そういう認識でよいかと思います

https://dev.mysql.com/doc/refman/5.6/ja/sql-syntax-prepared-statements.html

＞PREPARE stmt FROM @sql;
＞上記で、指定をするのは概観だけで実際の正確な指定は実行時に指定をする
＞と言う方法の事をここではブレースホルダーの指定と言うのでしょうか。

プリペア（もしくはプリペアド）の処理を指定してるという言い方の方がわかりやすいのでは？
実行自体はEXECUTEの構文になるので

https://dev.mysql.com/doc/refman/5.6/ja/prepare.html

yambejp · Answer

>',a,'とか、',b,'とか、',c,'とかは何故ここにコンマが前後に入らないといけないのでしょうか。

MYPROCEDURE2について
SELECT val,start_id,end_id FROM table2;
で得た、カーソルをそれぞれaにval、bにstart_id、cにend_idを代入してあり
@sql変数にCANCATでつなげて代入しているわけです。
その後stmtに反映して実行しています

MYPROCEDURE3について
＞SET @a=a,@b=b,@c=c;が@sql=concatの括弧の中に引数として展開をすると言う事でしょうか。
MYPROPCEDURE2と同様、a,b,cで受けたval,start_id,end_idをプリペアド処理するために
変数@a,@b,@cにいれておき、EXECUTEする際に、該当位置に展開しています
プレースホルダの基本的な表記方法です

MYPROCEDUREの2と3の違いは、プレースホルダを有効につかっているかです。
とくに3の書式であればプロシージャの外側から来た汚染されたインジェクションデータであっても
安全に運用することができます。
今回は結局table2から渡されたデータをつかっているだけなので、
セキュリティ的にもあまり大きな違いはありません。

yambejp · Answer

ちなみに・・・

CREATE TABLE table2(val int,start_id int,end_id int);
INSERT INTO table2 VALUES(7,1,13),(12,14,20),(20,21,31);
としておいて値を利用して更新する手もあります。

DROP PROCEDURE IF EXISTS MYPROCEDURE2;
DELIMITER //
CREATE PROCEDURE MYPROCEDURE2()
BEGIN
DECLARE a,b,c VARCHAR(100);
DECLARE done INT DEFAULT 0;
DECLARE CUR CURSOR FOR SELECT val,start_id,end_id FROM table2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN CUR;
REPEAT
FETCH CUR INTO a,b,c;
IF NOT done THEN
SET @sql=CONCAT('update result1 set target1=',a,' where id between ',b,' and ',c,';');
PREPARE stmt FROM @sql;
EXECUTE stmt;
END IF;
UNTIL done END REPEAT;
CLOSE CUR;
END
//
DELIMITER ;

UPDATE result1 SET target1=0;
CALL MYPROCEDURE2;

また、せっかくプリペアド処理をしているのですから、それらしく書いても良いでしょう
（外からデータを持ってくるわけではなく、データ型も分かっているのでプリペアドに処理する必要はないですが）
DROP PROCEDURE IF EXISTS MYPROCEDURE3;
DELIMITER //
CREATE PROCEDURE MYPROCEDURE3()
BEGIN
DECLARE a,b,c VARCHAR(100);
DECLARE done INT DEFAULT 0;
DECLARE CUR CURSOR FOR SELECT val,start_id,end_id FROM table2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN CUR;
REPEAT
FETCH CUR INTO a,b,c;
IF NOT done THEN
SET @a=a,@b=b,@c=c;
SET @sql=CONCAT('update result1 set target1=? where id between ? and ?;');
PREPARE stmt FROM @sql;
EXECUTE stmt USING @a,@b,@c;
END IF;
UNTIL done END REPEAT;
CLOSE CUR;
END
//
DELIMITER ;

UPDATE result1 SET target1=0;
CALL MYPROCEDURE3;

それと、今回はUPDATEを実行するデータが競合していませんが、
本来であれば競合を想定して、実行するSQLに順位をつけるべきです。
プロシージャ内でtable1（table2）からデータを抜き出す際に
何らかのキーをもとにORDER BYを指定して実行順位を確定してください。

yambejp · Answer

>sqlstateの値が'02000'

については、ここを参照
https://dev.mysql.com/doc/refman/5.6/ja/declare-handler.html

ようは普通にエラーを利用して制御する処理です
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
ってことで、データがなくなった終了フラグを立てる感じですかね
データを走査して処理するのであれば、この方法が一般的なようです

マニュアルの複合ステートメント構文全般をご一読されると理解が深まると思います
https://dev.mysql.com/doc/refman/5.6/ja/sql-syntax-compound-statements.html

yambejp · Answer

例えば・・・

CREATE TABLE table1 (sql1 varchar(100));
INSERT INTO table1 VALUES('update result1 set target1=7 where id between 1 and 13;'),('update result1 set target1=12 where id between 14 and 20;'),('update result1 set target1=20 where id between 21 and 31;');
CREATE TABLE result1 (id int not null,target1 int not null default 0);
INSERT INTO result1(id) VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32);

こうだったときに、table1のSQL文をつかってresult1のテーブルを更新したいということ？

今までの質問の流れからじきにこういった質問くるとは思ったけど、意外に早かった
カーソルとハンドラとプリペアド処理、慣れるまではちょっと面倒かも
以下サンプル

DROP PROCEDURE IF EXISTS MYPROCEDURE;
DELIMITER //
CREATE PROCEDURE MYPROCEDURE()
BEGIN
DECLARE a VARCHAR(100);
DECLARE done INT DEFAULT 0;
DECLARE CUR CURSOR FOR SELECT sql1 FROM table1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN CUR;
REPEAT
FETCH CUR INTO a;
IF NOT done THEN
SET @sql=a;
PREPARE stmt FROM @sql;
EXECUTE stmt;
END IF;
UNTIL done END REPEAT;
CLOSE CUR;
END
//
DELIMITER ;

→一度resul1を初期化
UPDATE result1 SET target1=0;

→プロシージャ実行
CALL MYPROCEDURE;

→確認
SELECT * FROM result1;

windows7のmysqlでレコード単位の更新を行ないたいが良く分からない。

＞SET @sql=CONCAT('update result1 set target1=? where id between ? and ?;');

>',a,'とか、',b,'とか、',c,'とかは何故ここにコンマが前後に入らないといけないのでしょうか。

ちなみに・・・

>sqlstateの値が'02000'

例えば・・・

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング