MYSQLのIF文を書こうとしたらドハマりした

IF文書き始めたら夢中になっちゃった★
という話ではございません。

先日、直接DBを書き換える作業がありまして。
削除前後で行数確認して問題なければコミットにしてほしいというオーダーだったので、軽い気持ちでIF文に手を出したら大火傷。。。
syntax error祭り開催。。。

結果的には私がSQLファイルを流して自動で判断させなきゃいけないと思い込んでいただけで、手順とクエリを用意して目視で確認でよかったので事なきを得たのですが。

できないままだとアレなので、何とか動くものを書いてみました。

1 そもそも
2 実行環境
3 完成形
4 やりたいこと
5 ハマったアレコレ
6 実行するとこんな感じ
7 結論

そもそも

SQLのIF文なんて存在すらググって知ったわ。。。
普段もSQLをゴリゴリ書くことってあまりないんですが、書くとしてもPHPなど他の言語から使うことが多いのでIF文を必要としたことがありませんでした。

そんなわけで今回は

動けばOK

としてます。
最適な書き方は他にもっとあるかもしんない。

ここが大変参考になりました。

Qiita

ストアドプロシージャの基本的ななにか - Qiita

https://qiita.com/setsuna82001/items/e742338eb93e3a48ba46

あんまり知識ないけどがんばるSQLにも存在する様だけど, MySQLでしか経験がないからMySQLで書くよ対象以下に該当する方が対象です。ある程度, SQLクエリが読めるある程度, DBの知識がある背景資料を...

実行環境

mysql Ver 8.0.22 for Linux on x86_64

完成形

適当にテーブルを作って確認。
賞味期限ってexpiration dateって言うらしいですが、スペル覚えてなくて打つのが大変だるい。
サンプルなんだからdateとかlimitとかにしておけばよかった。

-- テーブル作成
CREATE TABLE dessert (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
name VARCHAR(20),
expirationDate DATE);
-- テーブル確認
DESCRIBE dessert;
-- 適当にデータを追加
INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-04-27');
INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-04-28');
INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-04-29');
INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-04-30');
INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-05-01');
INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-05-02');
INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-05-03');
INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-05-04');
INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-05-05');
INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-04-27');
INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-04-28');
INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-04-29');
INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-04-30');
INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-05-01');
INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-05-02');
INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-05-03');
INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-05-04');
INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-05-05');
INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-04-27');
INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-04-28');
INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-04-29');
INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-04-30');
INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-05-01');
INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-05-02');
INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-05-03');
INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-05-04');
INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-05-05');
-- 削除用のストアドプロシージャを作成
DELIMITER //
CREATE PROCEDURE DoDelete( IN target_month CHAR(2), IN p_target INT, IN p_all INT )
BEGIN
-- トランザクション開始
START TRANSACTION;
-- 対象レコードを削除
DELETE FROM dessert WHERE DATE_FORMAT(expirationDate, '%m') = target_month;
-- 削除結果確認
SELECT
@cnt_target := SUM((CASE WHEN DATE_FORMAT(expirationDate, '%m') = target_month THEN 1 ELSE 0 END)) AS 'Target',
@cnt_other := SUM((CASE WHEN DATE_FORMAT(expirationDate, '%m') != target_month THEN 1 ELSE 0 END)) AS 'Other',
@cnt_all := COUNT(*) AS 'All'
FROM
dessert;
-- 計算結果と実際に残ったレコード数が合っているか確認してコミットorロールバック
IF p_all - p_target = CAST(@cnt_other AS SIGNED) THEN
COMMIT;
SELECT '削除をコミットしました';
ELSE
ROLLBACK;
SELECT '削除をロールバックしました';
END IF;
END;
//
DELIMITER ;
-- 削除前の件数を取得
-- 4月のデータだけ消す
SELECT
@cnt_april := SUM((CASE WHEN DATE_FORMAT(expirationDate, '%m') = '04' THEN 1 ELSE 0 END)) AS 'April',
@cnt_other := SUM((CASE WHEN DATE_FORMAT(expirationDate, '%m') != '04' THEN 1 ELSE 0 END)) AS 'Other',
@cnt_all := COUNT(*) AS 'All'
FROM
dessert;
-- 作成したストアドプロシージャを呼び出して削除実行
CALL DoDelete('04', CAST(@cnt_april AS SIGNED), CAST(@cnt_all AS SIGNED));
-- 作成したストアドプロシージャを削除
DROP PROCEDURE DoDelete;

-- テーブル作成

CREATE TABLE dessert (

id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

name VARCHAR(20),

expirationDate DATE);

-- テーブル確認

DESCRIBE dessert;

-- 適当にデータを追加

INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-04-27');

INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-04-28');

INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-04-29');

INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-04-30');

INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-05-01');

INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-05-02');

INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-05-03');

INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-05-04');

INSERT INTO dessert (name, expirationDate) VALUES ('pudding', '2021-05-05');

INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-04-27');

INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-04-28');

INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-04-29');

INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-04-30');

INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-05-01');

INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-05-02');

INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-05-03');

INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-05-04');

INSERT INTO dessert (name, expirationDate) VALUES ('cake', '2021-05-05');

INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-04-27');

INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-04-28');

INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-04-29');

INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-04-30');

INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-05-01');

INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-05-02');

INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-05-03');

INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-05-04');

INSERT INTO dessert (name, expirationDate) VALUES ('donut', '2021-05-05');

-- 削除用のストアドプロシージャを作成

DELIMITER //

CREATE PROCEDURE DoDelete( IN target_month CHAR(2), IN p_target INT, IN p_all INT )

BEGIN

-- トランザクション開始

START TRANSACTION;

-- 対象レコードを削除

DELETE FROM dessert WHERE DATE_FORMAT(expirationDate, '%m') = target_month;

-- 削除結果確認

SELECT

@cnt_target := SUM((CASE WHEN DATE_FORMAT(expirationDate, '%m') = target_month THEN 1 ELSE 0 END)) AS 'Target',

@cnt_other := SUM((CASE WHEN DATE_FORMAT(expirationDate, '%m') != target_month THEN 1 ELSE 0 END)) AS 'Other',

@cnt_all := COUNT(*) AS 'All'

FROM

dessert;

-- 計算結果と実際に残ったレコード数が合っているか確認してコミットorロールバック

IF p_all - p_target = CAST(@cnt_other AS SIGNED) THEN

COMMIT;

SELECT '削除をコミットしました';

ELSE

ROLLBACK;

SELECT '削除をロールバックしました';

END IF;

END;

DELIMITER ;

-- 削除前の件数を取得

-- 4月のデータだけ消す

SELECT

@cnt_april := SUM((CASE WHEN DATE_FORMAT(expirationDate, '%m') = '04' THEN 1 ELSE 0 END)) AS 'April',

@cnt_other := SUM((CASE WHEN DATE_FORMAT(expirationDate, '%m') != '04' THEN 1 ELSE 0 END)) AS 'Other',

@cnt_all := COUNT(*) AS 'All'

FROM

dessert;

-- 作成したストアドプロシージャを呼び出して削除実行

CALL DoDelete('04', CAST(@cnt_april AS SIGNED), CAST(@cnt_all AS SIGNED));

-- 作成したストアドプロシージャを削除

DROP PROCEDURE DoDelete;

やりたいこと

こんなテーブルがあったとして

expirationDateに4月～5月の日付が入ってます。

手作業でやるなら、こんな感じで月ごとの件数でも出して削除後に5月の15件だけ残ってる状態であればコミットしてやればいいんですが、これをSQLに全部やらせたい。

ハマったアレコレ

全部syntax errorにするのやめてもらっていいですか。

IF文書くとエラー

ググってIF文ってこうやるのかーと調べて書いてみたらいきなりエラー。

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near …

1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near …

え、どこがエラー？
見たところ書き方あってるし；のつけ忘れもないんですけど？

で約1日ハマりました。。。

結論としては、MYSQLではIF文はストアドプロシージャまたはストアドファンクションの中でしか使えないということでした。
MYSQLのIF文リファレンスにそれ書いておいてよ。。。

ちなみにSQLServerは通常処理の中でも使用可能。
PostgreSQLは無名ブロックの中であれば使えるそうです。

結果を入れた変数の型が謎

IF文で比較する用の件数を事前に取得するところ。
SELECTした結果が複数行の場合、変数に持たせるのはものすごく面倒っぽかったので、それぞれSUMして1行ずつ取得しました。
CASE文も初めて使った。

-- 削除前の件数を取得
SELECT
@cnt_april := SUM((CASE WHEN DATE_FORMAT(expirationDate, '%m') = '04' THEN 1 ELSE 0 END)) AS 'April',
@cnt_other := SUM((CASE WHEN DATE_FORMAT(expirationDate, '%m') != '04' THEN 1 ELSE 0 END)) AS 'Other',
@cnt_all := COUNT(*) AS 'All'
FROM
dessert;

-- 削除前の件数を取得

SELECT

@cnt_april := SUM((CASE WHEN DATE_FORMAT(expirationDate, '%m') = '04' THEN 1 ELSE 0 END)) AS 'April',

@cnt_other := SUM((CASE WHEN DATE_FORMAT(expirationDate, '%m') != '04' THEN 1 ELSE 0 END)) AS 'Other',

@cnt_all := COUNT(*) AS 'All'

FROM

dessert;

この結果をもとにフラグでも立てようかなぁと試してみたところ

SELECT
@cnt_april,
(CASE WHEN @cnt_aplil = '12' THEN true ELSE false END) AS 'type_string',
(CASE WHEN @cnt_aplil = 12 THEN true ELSE false END) AS 'type_int',
(CASE WHEN CAST(@cnt_april AS SIGNED) = '12' THEN true ELSE false END) AS 'cast_string',
(CASE WHEN CAST(@cnt_april AS SIGNED) = 12 THEN true ELSE false END) AS 'cast_int';

SELECT

@cnt_april,

(CASE WHEN @cnt_aplil = '12' THEN true ELSE false END) AS 'type_string',

(CASE WHEN @cnt_aplil = 12 THEN true ELSE false END) AS 'type_int',

(CASE WHEN CAST(@cnt_april AS SIGNED) = '12' THEN true ELSE false END) AS 'cast_string',

(CASE WHEN CAST(@cnt_april AS SIGNED) = 12 THEN true ELSE false END) AS 'cast_int';

そのままstringやintと比較しようとするとfalseに。
どうもCastしてやらないといけないみたいです。

変数の型の調べ方が結局わからなかったので、とりあえず今後は比較前に全部Castするようにしよ…。

ストアドプロシージャのパラメータにallを指定するとエラー

ストアドプロシージャを作ろうとしたらまたエラー。

CREATE PROCEDURE DoDelete( IN target_month CHAR(2), IN target INT, IN all INT )

1	CREATE PROCEDURE DoDelete( IN target_month CHAR(2), IN target INT, IN all INT )

結果としては、allがMYSQLの予約語と被ってるからだめってことでした。
いやそれsyntax errorじゃなくちゃんと言ってくれよ。。。

その他単純な書き間違いも当然syntax errorなので、原因特定がなかなか大変でした。

実行するとこんな感じ

試しに削除対象を5月、削除予定の行に4月を指定して実行。
ちゃんとロールバックされてます。

今度は両方4月を指定してみると、ちゃんと削除が反映されてました。

結論

条件分岐なんて他の言語にやらせればよくない！？

なんてね。
他のRDBMSはともかく、MYSQLの場合ストアドプロシージャorファンクションの中でないとIF文が使えないので、書き捨てたい処理には向いてない気がします。
使った後に削除するの忘れてゴミプロシージャが大量生産されそう。

どうせその環境、メインシステム用の言語がインストールされてるんでしょ？
ならその言語でSQL繋いで好きなだけIF文書けばいいじゃん。

CASEは結構よさそうですが、SELECTやWHEREでしか使えないようなので今回のように後続処理を実行するかどうかの判断はできないし。
あまり複雑な処理をSQLにさせるとデータ量によっては遅くなるので、プログラム側で判断してシンプルなクエリにしたほうがいいような気がします。

ってことで、今回調べた内容は活用されることはないのでした…。