読者です 読者をやめる 読者になる 読者になる

Database JUNKY

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

なければINSERT、あればUPDATE(ON DUPLICATE KEY UPDATE)

MySQL UPSERT SQL MySQL MariaDB UPDATE INSERT SQL構文 DUPLICATE KEY UPDATE ユニーク索引 主キー 複合キー UNIQUE

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

ON DUPLICATE KEY UPDATE

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

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

これが出来る前提条件は、

  • 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 [work_nakayama]> 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 オプションは有効!!!ってことなります!!

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