MySQL: REPLACEは未指定フィールドがNULLに置き換わるのでON DUPLICATE KEY UPDATEを使う

MySQLのREPLACE構文は未指定のフィールドがNULLに置き換わる仕様なので、全フィールドの値を指定するか、一部だけ指定したい場合は、ON DUPLICATE KEY UPDATEを使うといい。

検証用テーブル

Nullableなカラムを2つ以上持つテーブルを用意する。

CREATE TABLE `mytable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `foo` varchar(255) DEFAULT NULL,
  `bar` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

検証

片方ずつREPLACEしてみる。

-- fooのみセットしてINSERTまたはUPDATE
REPLACE INTO mytable (`id`, `foo`) VALUES (1, 'foo');

-- barのみセットしてINSERTまたはUPDATE
REPLACE INTO mytable (`id`, `bar`) VALUES (1, 'bar');

-- この段階でfooはNULLに変化してしまう…

今度はON DUPLICATE KEY UPDATEを使って片方ずつ更新してみる。

-- fooのみセットしてINSERTまたはUPDATE
INSERT INTO mytable (`id`, `foo`) VALUES (2, 'foo') ON DUPLICATE KEY UPDATE `foo` = 'foo';


-- barのみセットしてINSERTまたはUPDATE
INSERT INTO mytable (`id`, `bar`) VALUES (2, 'bar') ON DUPLICATE KEY UPDATE `bar` = 'bar';

-- fooもbarも値が入った状態になる