Insert On Duplicate Key Update構文の使い方

2014/05/07 2018/08/04

レコードがあれば Update、なければ INSERTする On Duplicate Key構文

On Duplicate Key構文とは？

　
On Duplicate Key構文は
特定のカラムをキーとして、そのカラムに重複するレコードがなければ Insertし、あれば Updateを実行するという構文です。
　
この構文を使うことで、わざわざ一度 Select文を実行してレコードの存在確認をすることなく、MySQL側でレコードの有無を確認して Insertか Updateか適切な方を実行してくれるという構文です。
　
特定のキーとは、Uniqueキーとして登録してあるか、Primary Keyとして登録してある必要がありますが、1つのカラムに限らず、複数のカラムでも一つの Uniqueキー（Primary Key）の設定を行うことで対応が可能です。
　
ただ、Uniqueキーの設定が必要ですので、設定するカラムで複数レコードが存在するような仕組みの場合は利用することはできません。
　
　
下記が On Duplicate Key構文の基本構文です。

insert into {テーブル名} ( id, post_id, sec_code, .... ) values ( 13, 2222, 1111, .... )
 on duplicate key update post_id = 2222, sec_code = 1111, list = "2222", ......

1 2	insert into {テーブル名} ( id, post_id, sec_code, .... ) values ( 13, 2222, 1111, .... ) on duplicate key update post_id = 2222, sec_code = 1111, list = "2222", ......

On Duplicate Key構文についての具体例

　
まず、実験用のテーブルを用意します。
　
————————-

CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `post_id` int(8) NOT NULL,
  `sec_code` char(4) DEFAULT NULL,
  `list` varchar(255) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

CREATE TABLE IF NOT EXISTS `comments` (

`id` int(8) NOT NULL AUTO_INCREMENT,

`post_id` int(8) NOT NULL,

`sec_code` char(4) DEFAULT NULL,

`list` varchar(255) DEFAULT NULL,

`created` datetime DEFAULT NULL,

`modified` datetime DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

————————-
　
　
データも入っていた方がいいので、必要であれば下記のサンプルデータを使ってみてください。
————————-

INSERT INTO `comments` (`id`, `post_id`, `sec_code`, `list`, `created`, `modified`) VALUES
(1, 60, '1113', '12-1231のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),
(2, 23, '2345', '23-2345のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),
(3, 34, '3456', '34-3456のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),
(4, 45, '4567', '45-4567のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),
(5, 56, '5678', '56-5678のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),
(6, 67, '6789', '67-6789のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),
(7, 78, '7890', '78-7890のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),
(8, 12, '1111', '12-1231のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),
(9, 13, '1111', '12-1231のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00');

INSERT INTO `comments` (`id`, `post_id`, `sec_code`, `list`, `created`, `modified`) VALUES

(1, 60, '1113', '12-1231のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),

(2, 23, '2345', '23-2345のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),

(3, 34, '3456', '34-3456のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),

(4, 45, '4567', '45-4567のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),

(5, 56, '5678', '56-5678のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),

(6, 67, '6789', '67-6789のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),

(7, 78, '7890', '78-7890のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),

(8, 12, '1111', '12-1231のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00'),

(9, 13, '1111', '12-1231のリスト', '2014-04-01 12:00:00', '2014-04-01 12:00:00');

————————-
　
なぜ、こんな状況かと言いますと、「CakePHP 2.3 主キー（ID）以外のキーで更新をする方法」の実験を行ったテーブルだからです。
　
そして、さらにこの実験の続きは、「CakePHP 2.3 で On Duplicate Key構文を実装」へと続いていきます。
　
ちなみに、CakePHP3で On Duplicate Key構文を実装するには「CakePHP3で生の SQLの実行はConnectionManagerを使う」を参考にしてください。
　
ただ、上記の記事では CakePHPで主（ID）キー以外のキーで Insert …. On Duplicate Key Update …. を使いたいと思ったのですが、それを実行する CakePHPの仕組みが分からず、結局は SQLを直接記述し、Queryメソッドで実行しています。
　
　
さて、SQL文の実験です。
　
・On Duplicate Keyの基本的な記述の仕方
　※Updateするときは、指定の値に更新するような場合の記述の仕方

insert into comments ( id, post_id, sec_code, list, created, modified ) values ( 13, 2222, 1111, "2222-1111のリストIns", now(), now() )
 on duplicate key update post_id = 2222, sec_code = 1111, list = "2222-1111のリストDup", created = now(), modified = now()

1 2	insert into comments ( id, post_id, sec_code, list, created, modified ) values ( 13, 2222, 1111, "2222-1111のリストIns", now(), now() ) on duplicate key update post_id = 2222, sec_code = 1111, list = "2222-1111のリストDup", created = now(), modified = now()

　
　※Updateするときは、元ある値に対し何かしらの処理をするような場合の記述の仕方

insert into comments ( id, post_id, sec_code, list, created, modified ) values ( 13, 2222, 1111, "2222-1111のリストIns", now(), now() )
 on duplicate key update post_id = post_id + 2222, sec_code = sec_code + 1111, list = "2222-1111のリストDup", created = now(), modified = now()

1 2	insert into comments ( id, post_id, sec_code, list, created, modified ) values ( 13, 2222, 1111, "2222-1111のリストIns", now(), now() ) on duplicate key update post_id = post_id + 2222, sec_code = sec_code + 1111, list = "2222-1111のリストDup", created = now(), modified = now()

　※もちろん、Updateの処理は、Updateする必要がある項目だけ指定すれば OK！
　
「ID」「post_id」を別々に Uniqueキーの設定を行った場合は、もちろん、どちらかが一致していると Insertになります。
ですが、「ID」「post_id」を合わせて Uniqueキーにする設定を行った場合は、両方が一致している場合に Insertになります。
　
　

Uniqueキーを設定する最大サイズ

　
Uniqueキーを設定する際の最大サイズが存在します。
　
　最大サイズ　　767 Bytes
　
上記は、ストレージエンジンが「InnoDB」の場合ですが、詳しくは「MySQLのInnoDBでUniqueキーは最大767バイト」に書きましたので参照ください。
　
　

On Duplicate Key　と　Replace Into　との違い

　
「On Duplicate Key」にはよく似た機能として「Replace Into」という機能があります。
　
それぞれの機能の違いは、キーとなる項目に重複する値がある場合、
　「On Duplicate Key」は、Updateを実行する
　「Replace Into」は、Deleteして Insertを実行する
という違いがあります。
　
同じ結果になる場合もありますが、AUTO_INCREMENT（オートインクリメント、自動付番）を設定しているテーブルに対して「Replace Into」の方を使ってしまいますと、新しい IDが割り振られてしまいますので、注意が必要です。
　
　

Unique　と　Primary Key　との違い

　
ついでに、「Unique」「Primary Key」の違いも見つけましたので書いておきます。
　
どちらも動きは同じようにふるまいますが、「Unique」「Primary Key」の違いは、nullの扱いが違います。
　
「Unique」は、nullは重複しないと見なします。
「Primary Key」は、nullは重複するとみなすため、nullを入れようとするとエラーになります。
　
　

理論から学ぶデータベース実践入門 ~リレーショナルモデルによる効率的なSQL (WEB+DB PRESS plus)

ついでに Insert文に関する話

Insert文を記述するときに他の値を利用する場合のコツ

　
On Duplicate Keyのことを調べている中で、下記の MySQLの公式サイトで Insertに関する情報も確認しました。
　http://dev.mysql.com/doc/refman/4.1/ja/insert.html
　
その際「Insertするときに先に設定されたカラムであれば参照が可能」という記述を見かけ、それの実験もしてみています。
　

・Insert文の基本形
insert into comments ( id, post_id, sec_code, list, created, modified )
 values ( 10, 89, 8901, "89-8901のリスト", now(), now() )

・IDは「AUTO_INCREMENT」なのでそれを省いた SQL
insert into comments ( post_id, sec_code, list, created, modified )
 values ( 89, 8901, "89-8901のリスト", now(), now() )

・IDを省いているので参照できない
　　※「post_id」には「0」が編集される
insert into comments ( post_id, sec_code, list, created, modified )
 values ( id*2, 8901, "89-8901のリスト", now(), now() )

・先に設定されている「post_id」を参照しているので問題なく「196」が編集される
insert into comments ( post_id, sec_code, list, created, modified )
 values ( 98, post_id*2, "89-8901のリスト", now(), now() )

・「sec_code」は後に設定されている項目なので参照できない
　　※SQLエラーになる
insert into comments ( post_id, sec_code, list, created, modified )
 values ( sec_code*2, 1111, "89-8901のリスト", now(), now() )

・「sec_code」と「post_id」の順番を入れ替えると
　先に設定されているカラムになるため問題なく「2222」が編集される
insert into comments ( sec_code, post_id, list, created, modified )
 values ( 1111, sec_code*2, "89-8901のリスト", now(), now() )

・Insert文の基本形

insert into comments ( id, post_id, sec_code, list, created, modified )

values ( 10, 89, 8901, "89-8901のリスト", now(), now() )

・IDは「AUTO_INCREMENT」なのでそれを省いた SQL

insert into comments ( post_id, sec_code, list, created, modified )

values ( 89, 8901, "89-8901のリスト", now(), now() )

・IDを省いているので参照できない

　　※「post_id」には「0」が編集される

insert into comments ( post_id, sec_code, list, created, modified )

values ( id*2, 8901, "89-8901のリスト", now(), now() )

・先に設定されている「post_id」を参照しているので問題なく「196」が編集される

insert into comments ( post_id, sec_code, list, created, modified )

values ( 98, post_id*2, "89-8901のリスト", now(), now() )

・「sec_code」は後に設定されている項目なので参照できない

　　※SQLエラーになる

insert into comments ( post_id, sec_code, list, created, modified )

values ( sec_code*2, 1111, "89-8901のリスト", now(), now() )

・「sec_code」と「post_id」の順番を入れ替えると

　先に設定されているカラムになるため問題なく「2222」が編集される

insert into comments ( sec_code, post_id, list, created, modified )

values ( 1111, sec_code*2, "89-8901のリスト", now(), now() )

- MySQL

最後までお読みいただきましてありがとうございます。
この記事が参考になったと思いましたらソーシャルメディアで共有していただけると嬉しいです！

Message コメントをキャンセル

※入力いただいたコメントは管理者の承認後に掲載されます。

: MySQLのInnoDBでUniqueキーは最大767バイト

MySQLの Uniqueキーには InnoDBでは 767バイト、MyISAMでは 1000バイトの最大サイズの制限があります。

: MySQLで SQLSTATE[42000] Row size too large(> 8126)のエラーが出たときの対処方法

MySQLで入力する文字数が多い場合「SQLSTATE[42000] Row size too large(> 8126)」のエラーが発生することがある。その対処方法の解説を解説。

: MySQLの数値型（int、tinyint、bigint、decimal、number、float）の解説

MySQLの数値型についてテーブル設計のたびに調べているような気がしたので、調べてまとめてみた。数値型には整数型、固定小数点型、浮動小数点型がある。

: MySQL、CakePHP 2.3で「tinyint(1)」の Boolean型の動作を再確認

MySQL＋CakePHPの環境で「tinyint(1)」を利用する際の動作を検証。「tinyint(1)」の Boolean型について CakePHPでは自動処理が実施されていることを確認しました。

: 別サーバの MySQLにアクセスする設定・ファイアウォール設定、ポート設定、MySQL設定、テーブルへ権限付与の方法

外部サーバからアクセス方法解説。GRANTで権限付与。my.cnfのbind-addressで制限解除。3306ポートの開放、ファイアウォールの制限解除。確認方法と設定方法を詳細解説。

: MySQLの設定ファイル my.cnfがある場所と読み込み順序

MySQLの設定はmy.cnfファイルに記述されています。my.cnfを設置する場所は複数あり予め決められた順番で読み込み、その順番通りに設定内容が反映されます。

: MySQLのタイムゾーン（mysql_tzinfo_to_sql）の設定方法・XAMPP環境の解説も

MySQLのタイムゾーンテーブル（mysql.time_zone）の中身を確認し、からの場合は追加する方法を解説。LinuxとWindows・XAMPPとで異なる対応方法についてそれぞれ解説。