INSERTができない場合にUPDATE（DUPLICATE KEY UPDATE）

はじめに

データがなければ登録、データがあれば更新したい、
なんていうのは、よくある機能かと思います。
先日、機能改修でまさにこの対応を行い、プチはまりしたので残しておきます。

対応方針

目的は、INSERT時に該当レコードがある場合、UPDATEしたい。
DBはMySQLなので、DUPLICATE KEY UPDATE構文を使うことに。

公式　DUPLICATE KEY UPDATE 構文
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE 構文

最近では、Framework使っているとfindして、Entityに値詰めて、findの結果からinsertもしくはupdateすることが多いかなと思っています。
該当箇所はプレースホルダーは使っているものの、SQLをそのまま書いているような形式だったので、
元のSQLにDUPLICATE KEY UPDATEを追加することで対応。
実際に自分で使ってみるのは初だったりする。

実装・はまりポイント

正解パターン

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE b=b, c=c;

プログラム的にはバインドパラメータ使っているので、こんな感じに  
INSERT INTO table (a,b,c) VALUES (:a,:b,:c)
  ON DUPLICATE KEY UPDATE b=:b, c=:c;

間違いパターン
最初に飛ばし読みしすぎて、下記のような実装をしてエラーに。

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);

これはVALUESがよくないらしい。
Frameworkのせいかも？

ようやく動くようなSQLができたので、プログラムにて実装

$sql = "INSERT ～ ON DUPLICATE KEY UPDATE　～";
$command = Yii::$app->db->createCommand($sql)
　～パラメータのバインド～

if ($command->execute() < 1) {
    throw new Exception('更新失敗 [' . $command->getRawSql() . ']');
}

これで終わりかと思ったら、なぜか例外が発生
新規登録はできている。しかし更新するとエラー。

これじゃ前と一緒じゃん！
キャッシュが効いて修正前のソースのまま？
とも思いましたが、サーバ入ってみると確かにソースは変わっているし、
ログ的にも修正したSQLが動いている。

となるとやっぱり実装がおかしいということで、
DUPLICATE KEY UPDATEの使い方を見直してみる。

公式にちゃんと答えが書いてありました。

ON DUPLICATE KEY UPDATE を使用した場合、行ごとの影響を受けた行の値は、その行が新しい行として挿入された場合は 1、既存の行が更新された場合は 2、既存の行がその現在の値に設定された場合は 0 です。mysqld への接続時に CLIENT_FOUND_ROWS フラグを mysql_real_connect() に指定すると、既存の行がその現在の値に設定された場合の影響を受けた行の値は (0 ではなく) 1 になります。

整理すると以下
| パターン | SQL | 戻り値 | |---|---|---| | データがない | INSERT | 1 | | データがあり。更新あり | UPDATE | 2 | | データがあり。更新なし | UPDATE | 0 |

ということで、SQL実行後の戻り値を判定「execute() < 1」がよくなかったことが判明。
更新の場合、2が返され、if文の中に入って例外が発生ということでした。

しょうがないので、戻り値の判定は0より大きいことで簡易的に判断することにしました。

さいごに

今回は、アサインされた案件の改修箇所でとあるAPIの呼び出しを追加しましたが、
思いのほかハマってしまい、呼び出し側のデバッグ環境なども整えてようやく判明しました。
知っていれば何てことない判定だったので、ちょっと実装見てもらえれば良かったかなと反省。
その、ちょっとしたことに有識者の時間を使わせてしまう事への抵抗感は意外とあって、
新規参画＋リモートだとそれが普段より強くなるなぁと感じた出来事でした。