MySQL アップサートで更新の時のレコードの値カウントUP

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

ということだ。