MySQLでUPSERTとパーティショニングを設定したいとします。運悪くパーティショニングをしたい列は一意にはなれないとします。
少し具体的にいうと、こんなテーブル。データが挿入された月毎でテーブルを分け、古いパーティションは後日捨てたい。timeがパーティショニングキーになります。一方システム要件として一意なデータを指定するにはidで十分なので、行の選択はidのみで指定されます。
create table test(id int, datetime time default current_timestamp);
MySQLでパーティショニングキーになる列には以下の要求事項があります。Partitioning Keys, Primary Keys, and Unique Keys (MySQL documentation)
- そのテーブルに設定されている全てのUnique key/Primary keyに含まれていること
先にも言ったとおりtimeは一意性を保証できないので、idと組み合わせて一意性を確保することにします。
create table test(id int, datetime time, primary key(id, time))
partiotion by range columns(time)
(partition p201612 values less than ('2016-12-01 00:00:00'),
partition p201701 values less than ('2017-01-01 00:00:00'),
partition p201702 values less than ('2017-02-01 00:00:00'),
...
)
ここまでは問題なし。次にUPSERTとして有名なのは、INSERT ... ON DUPLICATE KEY UPDATE ...構文。が、Unique keyはidとtimeの二つで設定されているので、idだけ指定してtimeが適当ではINSERTしても重複と判定されずupdate文が実行されません。
重複判定させるためにid単独で別のUnique keyを作成しようとするとエラー。パーティションキーになる列は全てのUnique/primary keyに含まれるという制限に反するからです。
それならばとbefore updateのトリガーを指定して、更新しようとするidがテーブルになければidだけの行をinsertしておこうとする。
delimiter $$
create trigger test_updatetrigger
before update on test
for each row
begin
if not exists (select 1 from test where id=new.id) then
insert into test(id) values(new.id);
end if;
end$$
これはトリガー発動時にエラー。トリガー関数内ではテーブルに対する操作は禁止されている とのこと。
というわけで現在ここで行き詰っており、王道であるアプリケーション側でのselect→insert/update対応になりそうです。こんな案件珍しくないと思うんだけど、なぜパーティションキーにUnique key設定が必要なんて使いにくい仕様があるんだろ。以前もちょっと複雑なSQL書こうとしたらハマった(で、MySQL捨てた)とか、どうも私はMySQLと馬が合わないようです。
RSS2.0
Y:
Online: 
Comments