MySQLのストアドプロシージャ書きなれてないので、それも含めて自分向けのメモ。
あるテーブルの列の値をランダムにアップデートしたかったのだが、更新したいテーブルと同一のテーブルをサブクエリに指定できなかったので、ストアドプロシージャを使用することにしました。

目次

  1. やりたいこと
  2. 考えられる方法
  3. ストアドプロシージャ
    1. ストアド本体
    2. ストアドの実行と結果確認
    3. ストアドの削除
    4. エラー1175で実行できないとき

やりたいこと


10,000行あるpersonテーブルのhobby列の値を1,000行ランダムにpianoにアップデートしたい。

最初に書いたのは下記

1
2
3
4
5
update person set hobby = 'piano'
where person.id = (select id
from person
order by rand() limit 0,1000
);

できません。できないよね。
MySQLでは下記の通り同一テーブルupdateでのサブクエリは使用できないと書いていました。

You cannot update a table and select from the same table in a subquery.

他のDBはどうなんだろう…

考えられる方法


  • ストアドプロシージャを使う
  • 一時テーブルを作る

今回はストアドプロシージャを使いました。

ストアドプロシージャ


以下、自分向けのメモなのでコードと雑な解説だけ。
ざっくり書くと、カーソルでランダムに1,000件分のidを取得してそいつをグルグル回してアップデートしてます。

ストアド本体

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
/*
SQL区切り文字は通常;だが、ストアドだと複数のSQLを実行するので途中で;を使用することになる。
しかし、途中で;を記述するとそこでストアドが終了することになる。
そのためストアドの最後を;ではなく//と認識させるために初めにdelimiterを書く。
*/

delimiter //

/*
プロシージャ
out updated はupdateした件数確認用。(後記述)
*/

create procedure RandomUpdate(out updated int)
begin
declare targetId int;

declare done int default 0;

/*
カーソル宣言と1,000件のidをランダムに取得。
MySQLではたしかカーソル宣言時にしか取得できなかったはず。(以前調べ)
*/

declare update_target_cur cursor for
select id
from person
order by rand() limit 0,1000
;


#コード'02000'(レコード無し)になるまで続行するようにする。
declare continue handler for sqlstate '02000' set done = 1;

set updated = 0;

#カーソルオープン
open update_target_cur;

#以下グルグル
repeat
/*
fetchで取得した列の値を変数に格納する。
最初のカーソルで複数列取得した場合はintoの後に格納先変数をカンマ区切りで順番に複数書く。
*/

fetch update_target_cur into targetId;

update preson set hobby = "piano"
where preson.id = targetId;


set updated = updated + 1;

until done end repeat;

#カーソルクローズ
close update_target_cur;
end
//

ストアドの実行と結果確認

実行結果を確認したい場合はプロシージャの引数にoutパラメータを設けて、ストアド実行後にselectを実行すればよい。

1
2
3
call RandomUpdate(@updated);
#上記実行後に下記実行でupdateの件数を確認できる。
select @updated;

ストアドの削除

ORACLEでいうcreate or replace的なのはないので、変更した場合は毎回dropする。めんどくせ~

1
drop procedure RandomUpdate;

エラー1175で実行できないとき

MySQLでは主キーを使用しない更新は標準で使用できないようになっているので一時的にはずす。

1
2
#1175でupdateできないときとか一時的に
SET SQL_SAFE_UPDATES = 0;

ま~ストアドってめったに使わないよね。前職ではクソほど使ってたけど(ORACLEだったけど)