MySQLでUPSERTとパーティショニングの両立方法が分からない

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)Link

  • そのテーブルに設定されている全ての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$$

これはトリガー発動時にエラー。トリガー関数内ではテーブルに対する操作は禁止されているLink とのこと。

というわけで現在ここで行き詰っており、王道であるアプリケーション側でのselect→insert/update対応になりそうです。こんな案件珍しくないと思うんだけど、なぜパーティションキーにUnique key設定が必要なんて使いにくい仕様があるんだろ。以前もちょっと複雑なSQL書こうとしたらハマった(で、MySQL捨てた)とか、どうも私はMySQLと馬が合わないようです。

— posted by mu at 07:03 pm   commentComment [0]  pingTrackBack [0]

この記事に対する TrackBack URL:

設定によりTB元のページに、こちらの記事への言及(この記事へのリンク)がなければ、TB受付不可となりますのであらかじめご了承下さい。

コメントをどうぞ。 名前(ペンネーム)と画像認証のひらがな4文字は必須で、ウェブサイトURLはオプションです。

ウェブサイト (U):

タグは使えません。http://・・・ は自動的にリンク表示となります

:) :D 8-) ;-) :P :E :o :( (TT) ):T (--) (++!) ?;w) (-o-) (**!) ;v) f(--; :B l_P~ (QQ)

     

[X] [Top ↑]

T: Y: ALL: Online:
ThemeSwitch
  • Basic
Created in 0.0690 sec.
prev
2016.12
next
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31