MySQL:トリガーでデータ操作をコピーする

Copy data manipulation with triggers in MySQL.

トリガーを使ったデータ連携。

PKが張られているテーブルを前提にしています。
PKがないテーブルって追記されるだけですよね、多分updateもdeleteもないはず（いやまずPKをだな...）

事前準備

データ連携をする場合、連携先が必要です。作ってみましょう。
連携先は別データベースでも構いません。

create table new_table like original_table;
insert into new_table select * from original_table;

定義取得

こんな感じのSQLで必要な定義情報を取得しましょう。
group_concat_max_lenが足りないテーブルもあると思われますので、そこは確認、必要に応じて上限を上げましょう。(SESSION単位で変更可能です)

values_phrase

select
  table_name,
  group_concat(concat('new.', column_name) order by ordinal_position separator ', ') as values_phrase
from
  information_schema.columns
where
  table_schema = 'database_name'
  and table_name = 'original_table_name'
group by
  table_name;

set_phrase

select
  table_name,
  group_concat(concat(column_name, ' = new.', column_name) order by ordinal_position separator ', ') as set_phrase
from
  information_schema.columns
where
  table_schema = 'database_name'
  and table_name = 'original_table_name'
group by
  table_name;

where_phrase

select
  table_name,
  group_concat(concat(column_name, ' = old.', column_name) order by seq_in_index separator 'and ')
from
  information_schema.statistics
where
  table_schema = 'database_name' and
  table_name = 'original_table_name' and
  index_name = 'PRIMARY'
group by
  table_name;

トリガー作成スクリプト

ここまで取得した情報を以下のtrigger作成文の{}に代入して実行すれば完了です。
triggerはonされるテーブルと別データベースでは作成できませんので、useしているデータベースに気をつけるか、trigger作成文中でデータベース名まで指定するようにしましょう。

create_triggers

delimiter |

create trigger insert_{table_name}
after insert
on {table_name} for each row
begin
  insert into {new_table_name}
  values({values_phrase});
end;
|

create trigger update_{table_name}
after update
on {table_name} for each row
begin
  update {new_tabel_name}
    set {set_phrase}
  where
    {where_phrase};
end;
|

create trigger delete_{table_name}
after delete
on {table_name} for each row
begin
  delete from {new_table_name}
  where
    {where_phrase};
end;
|

delimiter ;

なんらかの事情がある場合、例えばコピー先のエンコーディングが変更され照合順序も変わっている場合、insertとupdateのtrigger操作をreplace into に切り替えてみるといいかもしれません。

定義情報を取得しているクエリをもっとCONCATしてTRIGGER文すべて作るもよし、コマンドラインからmysql、awk、sedなどを組み合わせて長ったらしいワンライナーで作るもよし、こまめにファイル出力しておいてjoinして一括生成するもよし、好きな言語・方法で生成しましょう。

既存のトリガーとの兼ね合いについてはバージョンに応じて対応が変わると思いますので、マニュアルを確認しましょう。

当然ですが、DDLは連携できません。
トリガーの操作中で参照しているオブジェクトがなくなったりすればエラーになります。
レプリケーションのスレーブではRBRの場合、トリガーが発火しません。(Mariaはできるらしい)
またパフォーマンスへの影響もあるので、そこはしっかり計測しましょう。