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と馬が合わないようです。