MySQL では、更新時に対象レコードの列の値をカウントUP更新は以下のようにできる。
UPDATE uranus SET icount = icount + 1 WHERE id = 1
- 更新するレコードが存在しなければ カウント初期値=0をINSERT
- 更新するレコードが存在すればカウントUP
としたい時に、 SELECT 文を流して実行した結果をプログラムが判定して
INSERT か UPDATEのSQLを流すようなことをしたくない!ので、
アップサート実行したいわけだが、REPLACEでは一旦削除されてしまうのでダメで
INSERT ~ ON DUPLICATE KEY UPDATEを使うことになる。
しかし、INSEERT 文 VALUE には工夫が必要でサブクエリ結果を判定して、カウンタとして
0から入るか、カウントUP値をセットするものを書かなければならない。
サンプル、テーブルの構造
DELIMITER // DROP TABLE IF EXISTS uranus // CREATE TABLE uranus ( id INT NOT NULL, uname CHAR(3) NOT NULL, icount INT DEFAULT 0, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci // DELIMITER ;
アップサートの実行、id = 3 , uname = 'ccc' のレコードが存在しなければ
INSERT で カウンタ icount = 0
存在すれば、UPDATE でカウンタ icount を更新
INSERT INTO uranus (id, uname, icount )VALUES(3, 'ccc' , COALESCE( (SELECT icount FROM (SELECT id, icount FROM uranus WHERE id = 3) x), -1 ) + 1 ) ON DUPLICATE KEY UPDATE icount = VALUES(icount)
このように、サブクエリでカウンタ(icount)を問い合わせた結果を
COALESCE で抽出して、+1するのである。
NULL なら、-1 に対して +1 で、0にする。
カウンタが0始まりからとする場合だ。
1始まりなら、
COALESCE( サブクエリ結果, 0) + 1
0始まりなら、
COALESCE( サブクエリ結果, -1) + 1
ということだ。