なければINSERT、あればUPDATE（ON DUPLICATE KEY UPDATE）

レコードがなければINSERT、あればUPDATEなどどいう超便利なSQL構文があります

ON DUPLICATE KEY UPDATE

オプションがそれなのですが、

結構便利です、今回 MariaDBで試しておりますが、MySQLでもおそらく同様にいけると思います。

f:id:hit10231023:20180309104332j:plain

前提条件は、

primayキーがあること
ユニーク索引があること

と書いてあります..がどんな条件でどうなるかというのをはっきりさせたかったので調べてみました

primaryキーとユニーク索引がある場合　とか
ユニーク索引が複合キーだった　とか

だった場合、どういう動きになるのかを見てみようと思います。

対象テーブル

今回以下のようなテーブルで検証してみました

 CREATE TABLE `table_b` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `table_name` varchar(200) NOT NULL,
  `cnt` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix01_table_b` (`table_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

見ての通り、プライマリーキーが、idでユニークキーが、table_name ですね。

データの中身

今のところ1レコードのみになっております

SELECT * FROM table_b ;
+----+------------+-----+
| id | table_name | cnt |
+----+------------+-----+
|  1 | table_a    |   3 |
+----+------------+-----+

このデータをベースに細かく検証してみます

SQL

プライマリーキー、ユニークキーで検証

これが有効じゃなきゃ、そもそも何だったんだという話になってしまうのですが（笑）念のためやってみます、が同時にユニーク索引の部分も変更してみますね

INSERT INTO
       table_b (id,table_name, cnt)
   VALUES
       (1,'table_XY', 1)
   ON DUPLICATE KEY UPDATE
        table_name = 'table_XY',
        cnt = cnt + 1;
---------------------------------------------
Query OK, 2 rows affected (0.07 sec)

んんんん？？？2 rows affectedってなんだ？？1レコードしかないのに・・・

データを確認してみる気になるのデータを確認してみます

SELECT * FROM table_b ;
+----+------------+-----+
| id | table_name | cnt |
+----+------------+-----+
|  1 | table_XY   |   4 |
+----+------------+-----+

テーブル名も変わっているし、カウントもちゃんとあってます。

でわ、idは別でtable名がid=1と同じ場合はどうなっちゃうのか確認してみます

INSERT INTO
       table_b (id,table_name, cnt)
   VALUES
       (2,'table_XY', 1)
   ON DUPLICATE KEY UPDATE
        table_name = 'table_XY',
        cnt = cnt + 1;

id=2は存在しないのでOKですが、table_nameは、id=1同様table_XYです。この場合、どのような挙動になるでしょうか？

SELECT * FROM table_b ;
+----+------------+-----+
| id | table_name | cnt |
+----+------------+-----+
|  1 | table_XY   |   5 |
+----+------------+-----+

上記のような結果になりました。これはつまり、table_name のユニーク制約が優先されて、UPDATE処理になったということですね。INSERT 時に指定した、id=2には当然のことながら無効になりましたどのようなプライオリティ付けかはわかりませんが、すくなくとも、プライマリーキーとユニーク索引が混ざってもどちらか有効な方で適用されるというのがわかったかと思います

検証

念のため、INSERTも動くか確認してみます

INSERT INTO
       table_b (id,table_name, cnt)
   VALUES
       (2,'table_XY2', 1)
   ON DUPLICATE KEY UPDATE
        table_name = 'table_XY',
        cnt = cnt + 1;

今回は、idもユニークだし、table_nameもユニークです、当然ですが、レコードが新規追加される状態になりましたね

+----+------------+-----+                                                  
| id | table_name | cnt |                                                  
+----+------------+-----+
|  1 | table_XY   |   5 |
|  2 | table_XY2  |   1 |
+----+------------+-----+

複合索引の場合の動作検証

プライマリーキーはそのままとして、ユニークキーが複合だった場合は、どのような挙動にになるのか確認してみたいと思います

ちょっと、table_bをいじってこのような構成に変更してみました

 CREATE TABLE `table_b` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `table_name` varchar(200) NOT NULL,
  `cnt` int(11) NOT NULL DEFAULT '0',
  `kubun` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix01_table_b` (`table_name`,`kubun`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
;

table_name,kubun`でユニーク索引という構成です

さっそく試してみます

まずは、インサートされるか。。

INSERT INTO
       table_b (id,table_name, cnt,kubun)
   VALUES
       (3,'table_XY2', 1,2)
   ON DUPLICATE KEY UPDATE
        table_name = 'table_XY',
        kubun = 1,
        cnt = cnt + 1;

結果以下のようになりました

MariaDB> SELECT * FROM table_b ;                           
+----+------------+-----+-------+
| id | table_name | cnt | kubun |
+----+------------+-----+-------+
|  1 | table_XY   |   5 |  NULL |
|  2 | table_XY   |   2 |     1 |
|  3 | table_XY2  |   1 |     2 |
+----+------------+-----+-------+

では、このid=3が複合キーで有効になるか確認してみます。想定では、id=3のcntは2になるはずです

INSERT INTO
       table_b (id,table_name, cnt,kubun)
   VALUES
       (4,'table_XY2', 1,2)
   ON DUPLICATE KEY UPDATE
        table_name = 'table_XY2',
        kubun = 2,
        cnt = cnt + 1;

table_name と kubun に変化がないので、複合キーでも条件さえあれば、大丈夫なはずです

そして、結果は・・・

 SELECT * FROM table_b ;
+----+------------+-----+-------+
| id | table_name | cnt | kubun |
+----+------------+-----+-------+
|  1 | table_XY   |   5 |  NULL |
|  2 | table_XY   |   2 |     1 |
|  3 | table_XY2  |   2 |     2 |
+----+------------+-----+-------+

おぉお！！！ちゃんと複合キーでもいける！！！！id=3のcntが、2にカウントアップされていますね！！！

まとめると、

プライマリキー、ユニーク索引、および複合キーの構成でも、条件に合致するのであれば、ON DUPLICATE KEY UPDATE オプションは有効！！！ってことなります！！

すごく便利なので皆様も、ぜひご活用ください

補足

INSERT INTO 〜 SELECTで DUPLICATE KEY UPDATE する場合は、以下のようにサブクエリにして実行すれば実現できますよ！

INSERT INTO
       table_b (id,table_name, cnt,kubun)
       SELECT 
          X.id,
          X.table_name,
          X.cnt,
          X.kubun
       FROM
         (
          SELECT
            id,
            table_name,
            cnt,
            kubun
          FROM
           table_zzz
         ) X
   ON DUPLICATE KEY UPDATE
        table_name = 'table_XY',
        kubun = 1,
        cnt = cnt + 1;

Database JUNKY

MySQL,MariaDBを中心としたブログです

なければINSERT、あればUPDATE（ON DUPLICATE KEY UPDATE）

対象テーブル

データの中身

SQL

プライマリーキー、ユニークキーで検証

検証

複合索引の場合の動作検証

補足