エス技研

WordPress、CakePHP、PHP、baserCMSなどの Web系システムを中心に情報を提供します!


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構文の基本構文です。

 
 

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

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

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

————————-
 
なぜ、こんな状況かと言いますと、「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するときは、指定の値に更新するような場合の記述の仕方

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

 ※もちろん、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するときに先に設定されたカラムであれば参照が可能」という記述を見かけ、それの実験もしてみています。
 

 - MySQL

GoogleAdwords

GoogleAdwords

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

Message

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

下記の空欄を埋めてください。 * Time limit is exhausted. Please reload CAPTCHA.

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)

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

  関連記事

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

MySQLのタイムゾーン(mysql_tzinfo_to_sql)の設定方法・XAMPP環境の解説も
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の数値型(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ポートの開放、ファイアウォールの制限解除。確認方法と設定方法を詳細解説。