MySQL「auto_increment」と「on duplicate key update」

　こんにちは、Hiroです。
　早速ですが、既にレコードが存在していればUPDATEをし、存在していなければINSERTをするという処理は、開発をしていると時々必要になってきますよね。
　MySQLの場合、そのような時に「on duplicate key update」を使うと便利ですが、ちょっとした注意点がありますので、今回はそのことをブログにしました。

「on duplicate key update」とは

MySQLの公式リファレンスより引用します。

ON DUPLICATE KEY UPDATE を指定したとき、UNIQUEインデックスまたはPRIMARY KEYに重複した値を発生させる行が
挿入された場合は、MySQLによって古い行のUPDATEが実行されます。

また、MySQLの固有の構文であり、PostgreSQLやOracleではまた別の構文で実現させる必要があります。

注意点！「auto_increment」の増加

上記の記述だけを読むと重複した行の場合は、UPDATEが実行されるとあるので、PRIMARY KEYが「auto_increment」であっても、UPDATE時は増加しないと思えますが、直感に反して、「on duplicate key update」を使った場合は、UPDATE（レコード数は増加しない）でも、auto_increment値が増加してしまします。
実行結果をもとに説明していきます。


まず、テーブルの説明つについて。
あるスポットのいいね数を管理（カウントアップ/ダウン）するためのテーブルを例にしたいと思います。idカラムがPRIMARY KEYで「auto_increment」として定義しています。mst_spot_idがスポットを特定するためのidで、UNIQUEインデックスとなっています。

mysql> desc spot_like_counts;
+----------------------------+-----------+------+-----+---------+----------------+
| Field                                | Type      | Null   | Key | Default | Extra             |
+----------------------------+-----------+------+-----+---------+----------------+
| id                                    | int(11)   | NO    | PRI  | NULL    | auto_increment |
| mst_spot_id                 | int(11)   | NO    | UNI | NULL    |                        |
| like_count                     | int(11)   | NO    |         | 0          |                       |
| created_at                    | datetime | NO   |         | NULL   |                       |
| updated_at                   | datetime | NO   |         | NULL   |                       |
+----------------------------+----------+-------+-----+---------+----------------+

何もINSERTされていな空の状態では、当然「auto_increment」値は「1」となっています。

mysql> SHOW TABLE STATUS LIKE 'spot_like_counts'\G
*************************** 1. row ***************************
                    Name: spot_like_counts
                   Engine: InnoDB
                 Version: 10
         Row_format: Dynamic
                     Rows: 0
 Avg_row_length: 0
         Data_length: 16384
Max_data_length: 0
        Index_length: 0
             Data_free: 0
  Auto_increment: 1                     ← ここです。
         Create_time: 2016-12-16 12:03:21
        Update_time: NULL
          Check_time: NULL
                Collation: utf8mb4_general_ci
             Checksum: NULL
    Create_options: 
              Comment: 
1 row in set (0.00 sec)

もう一度「INSERT...ON DUPLICATE KEY UPDATE」をするとmst_spot_idがUNIQUEインデックスとなっているため、既存のレコードが更新され、like_countが「2」となります。

mysql> INSERT INTO spot_like_counts(mst_spot_id, like_count, created_at, updated_at) values(1, 1, NOW(), NOW()) ON DUPLICATE KEY UPDATE like_count=like_count+1;
Query OK, 2 rows affected (0.02 sec)

mysql> SELECT * FROM spot_like_counts;
+----+----------------------------+-------------+---------------------+---------------------+
| id   | mst_spot_id                 | like_count | created_at          | updated_at          |
+----+----------------------------+-------------+---------------------+---------------------+
|  1   |                                     1 |                2 | 2016-12-16 12:08:03 | 2016-12-16 12:08:03 |
+----+----------------------------+-------------+---------------------+---------------------+
1 row in set (0.00 sec)

ここで、再度「auto_increment」値を確認するとレコードがINSERTされておらず、UPDATEされているにもかかわらず、 「auto_increment」値は1増加して、「3」となっています。

mysql> SHOW TABLE STATUS LIKE 'spot_like_counts'\G
*************************** 1. row ***************************
          　　　　　　　　　 Name: spot_like_counts
        　 　　　　　　　　Engine: InnoDB
      　　　　　　　　　  Version: 10
   　  　　　Row_format: Dynamic
  　      　　　　　　　　　   Rows: 1
 　Avg_row_length: 16384
    　　　　　Data_length: 16384
　Max_data_length: 0
 　　　　　  Index_length: 0
      　　　　　　　Data_free: 0
 　　Auto_increment: 3                     ← ここです。
   　　　　　 Create_time: 2016-12-16 12:03:21
    　　　　Update_time: 2016-12-16 12:21:50
    　　　　　 Check_time: NULL
     　　　　　　　　　 Collation: utf8mb4_general_ci
       　　　　　　Checksum: NULL
 　　　Create_options: 
    　　　　　　    Comment: 
1 row in set (0.00 sec)

ここで、mst_spot_idを1ではなく、2にしてINSERTを行うとidは「2」を飛ばして、「3」としてレコードがINSERTされます。

mysql> INSERT INTO spot_like_counts(mst_spot_id, like_count, created_at, updated_at) values(2, 1, NOW(), NOW()) ON DUPLICATE KEY UPDATE like_count=like_count+1;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM spot_like_counts;
+----+----------------------------+--------------+---------------------+---------------------+
| id 　　| mst_spot_id   　             | like_count  | created_at          | updated_at          |
+----+----------------------------+--------------+---------------------+---------------------+
|  1 　　|                          　　　　　　　　　　1 |  　　　　　　         2 | 2016-12-16 12:08:03 | 2016-12-16 12:08:03 |
|  3 　　|                         　　　　　　　　　　 2 |                 1 | 2016-12-16 12:22:45 | 2016-12-16 12:22:45 |
+----+----------------------------+--------------+---------------------+---------------------+
2 rows in set (0.00 sec)

 SHOW TABLE STATUS LIKE 'spot_like_counts'\G
*************************** 1. row ***************************
          　　　　　　　　　 Name: spot_like_counts
        　 　　　　　　　　Engine: InnoDB
      　　　　　　　　　  Version: 10
   　  　　　Row_format: Dynamic
  　      　　　　　　　　　   Rows: 2
 　Avg_row_length: 8192
    　　　　　Data_length: 16384
　Max_data_length: 0
 　　　　　  Index_length: 16384
      　　　　　　　Data_free: 0
 　　Auto_increment: 4                     ← ここです。
   　　　　　 Create_time: 2016-12-16 12:03:21
    　　　　Update_time: 2016-12-16 12:22:45
    　　　　　 Check_time: NULL
     　　　　　　　　　 Collation: utf8mb4_general_ci
       　　　　　　Checksum: NULL
 　　　Create_options: 
    　　　　　　    Comment: 
1 row in set (0.00 sec)

まとめ

「on duplicate key update」を使うと「既にレコードが存在していればUPDATEをし、存在していなければINSERTをするという処理」が簡単に実現することができます。ただし、注意点としてはUPDATEであっても「auto_increment」値が増加し続けるということです。そのため、UPDATEが大量に発生するテーブルに対して利用すると「auto_increment」値が増加し続けてしまい、最大値まで達してしまう可能性があるということを認識して利用すべきでしょう。

次回は、Ruby on Railsで「on duplicate key update」を利用せずに、同じような機能を実現する方法を紹介したいと思います。