背景
カラム名に日付をつけたかったがはまったのでメモ
お題
ユーザーの日毎の体温を記録する user_body_temperature テーブルがある。
SELECT * FROM user_body_temperature
| id | user_id | date | temperature |
|---|---|---|---|
| 1 | 1 | 2022-09-01 | 36.9 |
| 2 | 2 | 2022-09-01 | 36.1 |
| 3 | 1 | 2022-09-02 | 36.6 |
| 4 | 2 | 2022-09-02 | 36.5 |
| 5 | 1 | 2022-09-03 | 36.1 |
| 6 | 2 | 2022-09-03 | 36.5 |
| ... | ... | ... | ... |
日付をカラムとして、user_id が 1 のユーザーが入力した当日の体温を抽出するクエリを作りたい。
SELECT文で動的にエイリアスを指定したいので、に当日の CURDATE() を指定すると・・・
SELECT temperature AS CURDATE() FROM `user_body_temperature` WHERE date = CURDATE() AND user_id = 1;
# => ERROR 1064 (42000) at line 4: 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 'AS CURDATE() from Test' at line 1
文法エラーとなってしまう・・・
これはエイリアスなどクエリー自体には動的な要素を指定することはできないため。
解決方法
CONCAT関数を使い、クエリー自体を文字列化して一旦変数に保存。
その後、EXECUTE文で実行。
IDE標準のフォーマッターや文法チェックは使えず可読性も落ち、デバッグもしづらいがこれで実行可能！
SET @query = CONCAT('SELECT temperature AS \'', CURDATE(), '\' FROM `user_body_temperature` WHERE date = CURDATE() AND user_id = 1;');
PREPARE dynamic_statement FROM @query;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;
定期的なデータ抽出などには便利かも。