MySQLでupsertは標準機能で出来た(ON DUPLICATE KEY UPDATE)。mysql 4.1.0以降からあったのか…。知らんかった！！

admin

8年前

本の情報を管理するテーブル作成

create table books(
  id int primary key auto_increment,
  title varchar(255) not null,
  isbn varchar(255) not null unique, -- 更新キーに使う
  imgurl_large varchar(255),
  price int
);

create table books(

id int primary key auto_increment,

title varchar(255) not null,

isbn varchar(255) not null unique, -- 更新キーに使う

imgurl_large varchar(255),

price int

);

最初に存在チェックをした後に、INSERTするかUPDATEするかしないとダメ

insert books(title, isbn)values('title1', '01234567890');
update books set title = 'title2' where isbn = '01234567890';

1 2	insert books(title, isbn)values('title1', '01234567890'); update books set title = 'title2' where isbn = '01234567890';

ON DUPLICATE KEY UPDATEを使えば、SQL１回発行するだけでUPSERT出来る(前半はINSERT文で、キーが重複していたら値を更新する)

INSERT INTO books (title, isbn) VALUES ('title1', '01234567890')
 ON DUPLICATE KEY UPDATE title = 'title3', price=1000;

1 2	INSERT INTO books (title, isbn) VALUES ('title1', '01234567890') ON DUPLICATE KEY UPDATE title = 'title3', price=1000;

※あまりないと思うけど、INSERTする時にユニークキーを含まないと複数行ヒットしてしまう。
UPDATEみたいに全更新はされずに、１行だけ更新らしい。

今まではPHPとかで処理分けしていたけど、こんな単純なSQLで実現できたんだ…。

【追記】
upsertの更新キーは、暗黙的に決まるらしい。
1, プライマリキーが同一である
2, ユニークキー(複合ユニークでも可)が同一である
片方or両方が一緒なら、同じレコードとmysqlが勝手に判断するらしい。

関連記事