はじめに
この記事はCommune Advent Calendar 2024、シリーズ1の10日目の記事です
きっかけ
書籍「データベース・リファクタリング : データベースの体質改善テクニック」には、DBスキーマをリファクタリングするためのステップが色々とまとめられています
(複雑過ぎて棚上げした[Commune Advent Calendar 2024 シリーズ1の9日目]でも少し触れています)
先日いきなり複雑な事をしようとして失敗したので、今回はシンプルなシナリオを試してみます
想定
同一テーブルの列名を変更する
従業員番号の意味でemployees.number属性が存在するのだが、実際はアルファベットも含むので名称をemployees.codeに変更したい
移行中は現在動作するコードを考慮して、employees.numberに対するinsert, updateはemployees.codeにも同じ値を入れる
また、今後新しく作成されるコードを想定して、employees.codeに対するinsert, updateはemployees.numberにも同じ値を入れる
現在動作するコード全てのリファクタリングが終了し、employees.numberを参照しなくなった後に、列及び移行用トリガを削除する
準備
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31 |
+-----------+
元々あるテーブル(リファクタリング前)
DROP TABLE IF EXISTS employees;
CREATE TABLE `employees` (
`id` int NOT NULL AUTO_INCREMENT,
`number` varchar(45) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
移行中の状態を作る
まずは名称変更後の列を追加します
ALTER TABLE employees
ADD COLUMN `code` varchar(45) NOT NULL DEFAULT '';
トリガ
-- insert
DELIMITER //
DROP TRIGGER IF EXISTS before_insert;
CREATE DEFINER=`root`@`%` TRIGGER `before_insert` BEFORE INSERT ON `employees` FOR EACH ROW BEGIN
IF NEW.number <> "" AND NEW.code = "" THEN
SET NEW.code = NEW.number;
ELSEIF NEW.code <> "" AND NEW.number = "" THEN
SET NEW.number = NEW.code;
END IF;
END
//
DELIMITER ;
-- update
DELIMITER //
DROP TRIGGER IF EXISTS before_update;
CREATE DEFINER=`root`@`%` TRIGGER `before_update` BEFORE UPDATE ON `employees` FOR EACH ROW BEGIN
IF NEW.number <> OLD.number THEN
SET NEW.code = NEW.number;
ELSEIF NEW.code <> OLD.code THEN
SET NEW.number = NEW.code;
END IF;
END
//
DELIMITER ;
結果
employees.codeへの更新がemployees.numberにも、employees.numberへの更新がemployees.codeにも反映されました
mysql> insert into employees (number) values ("hoge");
Query OK, 1 row affected (0.02 sec)
mysql> select * from employees;
+----+--------+------+
| id | number | code |
+----+--------+------+
| 6 | hoge | hoge |
+----+--------+------+
1 row in set (0.01 sec)
mysql> insert into employees (code) values ("fuga");
Query OK, 1 row affected (0.02 sec)
mysql> select * from employees;
+----+--------+------+
| id | number | code |
+----+--------+------+
| 6 | hoge | hoge |
| 7 | fuga | fuga |
+----+--------+------+
2 rows in set (0.00 sec)
mysql> update employees set number="hogepiyo" where id = 6;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employees;
+----+----------+----------+
| id | number | code |
+----+----------+----------+
| 6 | hogepiyo | hogepiyo |
| 7 | fuga | fuga |
+----+----------+----------+
2 rows in set (0.01 sec)
mysql> update employees set code="piyohoge" where id = 7;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employees;
+----+----------+----------+
| id | number | code |
+----+----------+----------+
| 6 | hogepiyo | hogepiyo |
| 7 | piyohoge | piyohoge |
+----+----------+----------+
2 rows in set (0.01 sec)
まとめ
書籍「データベース・リファクタリング」では、トリガが循環しないように注意せよと書かれていますが、このシナリオではトリガでクエリを発行する必要が無く、同じトリガが呼ばれることはなさそうです
また今回、動作だけであればそれらしくなりましたが 上のコードは動作の確認で作成したものです。本番環境で動作させるといった実績のあるコードではありませんので、参考にされる場合はご自身の責任でお願いします
(トリガはdrop/createしか無いので置き換えるときに処理がどうなるかや、大量のトランザクションが到達したときに期待通り動作するかなど、気になる点も多いです)
Discussion