Insert On Duplicate Key Update構文の使い方
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構文の基本構文です。
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構文についての具体例
まず、実験用のテーブルを用意します。
————————-
1 2 3 4 5 6 7 8 9 |
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 ; |
————————-
データも入っていた方がいいので、必要であれば下記のサンプルデータを使ってみてください。
————————-
1 2 3 4 5 6 7 8 9 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'); |
————————-
なぜ、こんな状況かと言いますと、「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するときは、指定の値に更新するような場合の記述の仕方
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するときは、元ある値に対し何かしらの処理をするような場合の記述の仕方
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を入れようとするとエラーになります。
ついでに Insert文に関する話
Insert文を記述するときに他の値を利用する場合のコツ
On Duplicate Keyのことを調べている中で、下記の MySQLの公式サイトで Insertに関する情報も確認しました。
http://dev.mysql.com/doc/refman/4.1/ja/insert.html
その際「Insertするときに先に設定されたカラムであれば参照が可能」という記述を見かけ、それの実験もしてみています。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
・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() ) |
GoogleAdwords
GoogleAdwords
この記事が参考になったと思いましたらソーシャルメディアで共有していただけると嬉しいです!
関連記事
-
MySQLの設定ファイル my.cnfがある場所と読み込み順序
MySQLの設定はmy.cnfファイルに記述されています。my.cnfを設置する場所は複数あり予め決められた順番で読み込み、その順番通りに設定内容が反映されます。
-
MySQLの「tinyint(1)」の悲劇 Boolean型になるとは...
MySQLで tinyint(1)を指定すると Boolean型を指定したことになります。意外な誤解があります。
-
MySQLで SQLSTATE[42000] Row size too large(> 8126)のエラーが出たときの対処方法
MySQLで入力する文字数が多い場合「SQLSTATE[42000] Row size too large(> 8126)」のエラーが発生することがある。その対処方法の解説を解説。
-
MySQLのタイムゾーン(mysql_tzinfo_to_sql)の設定方法・XAMPP環境の解説も
MySQLのタイムゾーンテーブル(mysql.time_zone)の中身を確認し、からの場合は追加する方法を解説。LinuxとWindows・XAMPPとで異なる対応方法についてそれぞれ解説。
-
MySQL、CakePHP 2.3で「tinyint(1)」の Boolean型の動作を再確認
MySQL+CakePHPの環境で「tinyint(1)」を利用する際の動作を検証。「tinyint(1)」の Boolean型について CakePHPでは自動処理が実施されていることを確認しました。
-
MySQLの数値型(int、tinyint、bigint、decimal、number、float)の解説
MySQLの数値型についてテーブル設計のたびに調べているような気がしたので、調べてまとめてみた。数値型には整数型、固定小数点型、浮動小数点型がある。
-
MySQLのInnoDBでUniqueキーは最大767バイト
MySQLの Uniqueキーには InnoDBでは 767バイト、MyISAMでは 1000バイトの最大サイズの制限があります。
-
別サーバの MySQLにアクセスする設定・ファイアウォール設定、ポート設定、MySQL設定、テーブルへ権限付与の方法
外部サーバからアクセス方法解説。GRANTで権限付与。my.cnfのbind-addressで制限解除。3306ポートの開放、ファイアウォールの制限解除。確認方法と設定方法を詳細解説。