mogulla3.tech

2021-07-17

ON DUPLICATE KEY UPDATE構文を activerecord-import で使う

目次

MySQLが提供する INSERT .. ON DUPLICATE KEY UPDATE 構文の使い方について、activerecord-import というRubyのライブラリを例に整理します。

また、この構文をMySQLで使うときの注意点についても後半に書きます。

INSERT .. ON DUPLICATE KEY UPDATE構文とはなにか

登録しようとしたレコードが存在しなければINSERT、既に存在する場合はUPDATEする構文です。

MySQLの場合、存在するかレコードかどうかは PRIMARY KEYもしくはUNIQUEインデックスに重複する値が発生するか否か で判定されます。

使い方の例

次のような、会社情報を管理するcompaniesテーブルがあるとします。

Field Type Key NULL
id BIGINT PRIMARY NOT NULL
name VARCHAR NOT NULL
employee_count INT NOT NULL

PRIMARY KEYであるidに加えて会社名と従業員数を持ちます。
次のSQLでテーブルを作成できます。

CREATE TABLE `companies` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `employee_count` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CSV形式で定義された会社のマスタデータを渡され、それらをデータベースに取り込むかんたんなお仕事をしている、という状況を想定しましょう。

1社目の登録依頼

最初に、foo株式会社なるところの登録依頼がきました。

# CSV
foo株式会社,10人

新規登録かつ1件しかないので、シンプルに #create で取り込んでしまいます。

Company.create(name: 'foo株式会社', employee_count: 10)
# => INSERT INTO `companies` (`name`, `employee_count`) VALUES ('foo株式会社', 10)

テーブルはこのようになりました。

テーブルの状態 その1
テーブルの状態 その1

2社の追加と1社の修正

次に2件の会社情報の追加依頼がきました。また、登録済みのfoo株式会社の従業員数が1人増えたようです。

foo株式会社,11人
bar株式会社,10人
baz株式会社,10人

foo株式会社は従業員数の更新のみ行い、bar株式会社とbaz株式会社は新規登録としたいですね。このようなケースで ON DUPLICATE KEY UPDATE 構文が役に立ちます。
以下は activerecord-import を利用してこの構文を利用するサンプルコードです。

# CSVはパース済みとします
csv = [
  { name: 'foo株式会社', employee_count: 11 },
  { name: 'bar株式会社', employee_count: 10 },
  { name: 'baz株式会社', employee_count: 10 },
]

companies = csv.map do |row|
  company = Company.find_or_initialize_by(name: row[:name])
  company.employee_count = row[:employee_count]
  company
end

Company.import(companies, on_duplicate_key_update: %i[employee_count])
# => INSERT INTO `companies` (`id`, `name`, `employee_count`)
#    VALUES
#      (1, 'foo株式会社', 11),
#      (NULL, 'bar株式会社', 10),
#      (NULL, 'baz株式会社', 10)
#    ON DUPLICATE KEY UPDATE `companies`.`employee_count` = VALUES (`employee_count`)

コードの実行後、テーブルは次のようになりました。

テーブルの状態 その2
テーブルの状態 その2

ここで抑えておきたいポイントは以下です。

  • ON DUPLICATE KEY UPDATE 構文は、PRIMARY KEYもしくはUNIQUEインデックスに重複する値が発生した場合に更新となる。companiesテーブルの場合はidがPRIMARY KEYとなっているため、idが重複する場合は更新処理になる。
  • #import メソッドに on_duplicate_key_update オプションを指定することで ON DUPLICATE KEY UPDATE 構文が有効になる
  • on_duplicate_key_update オプションの値として、更新をしたい列を指定する
    • 今回の例だと employee_count だけ修正したいので1つ指定

3社全ての修正

その後、3つの会社全てで従業員数が倍増したようです。

foo株式会社,22人
bar株式会社,20人
baz株式会社,20人

今回は新規で増えた会社は存在しないので更新のみでよいわけですが、このようなケースでも ON DUPLICATE KEY UPDATE 構文は利用できます。

csv = [
  { name: 'foo株式会社', employee_count: 22 },
  { name: 'bar株式会社', employee_count: 20 },
  { name: 'baz株式会社', employee_count: 20 },
]

companies = csv.map do |row|
  # 全て存在する会社であることはわかっているため find_by を利用
  company = Company.find_by(name: row[:name])
  company.employee_count = row[:employee_count]
  company
end

Company.import(companies, on_duplicate_key_update: %i[employee_count])
# => INSERT INTO `companies` (`id`, `name`, `employee_count`)
#    VALUES
#      (1, 'foo株式会社', 22),
#      (2, 'bar株式会社', 20),
#      (3, 'baz株式会社', 20)
#    ON DUPLICATE KEY UPDATE `companies`.`employee_count` = VALUES (`employee_count`)

先ほどは #find_or_initialize_by でDBに存在しない会社の場合は初期化していましたが、今回は全て存在することが事前にわかっているので #find_by で参照のみしています。

このコードの実行後、テーブルは次のようになりました。

テーブルの状態 その3
テーブルの状態 その3

テーブルに一意のインデックスが複数存在する場合は要注意

MySQLのドキュメント には次のように書かれています。

一般に、一意のインデックスが複数含まれているテーブルに対して ON DUPLICATE KEY UPDATE 句を使用することは避けるようにしてください。

ON DUPLICATE KEY UPDATE 構文を、一意のインデックスが複数存在するテーブルに対して使うことは非推奨のようです。

これはなぜか？ (ここからはドキュメントからは確証を得られなかった部分もあるため、筆者の解釈も含みます)

companiesテーブルを再び例として使います。 既にid=1でfoo株式会社が登録されている場合、次の2つのクエリは同等となります。

INSERT INTO companies (id, name, employee_count) VALUES (1, 'foo株式会社', 10) ON DUPLICATE KEY UPDATE employee_count=VALUES(`employee_count`);

UPDATE companies SET employee_count=10 WHERE id = 1;

ここでもし name もUNIQUEインデックスが貼ってあったとすると、次のようなクエリに変わります。

UPDATE companies SET employee_count=10 WHERE id = 1 OR name = 'foo株式会社';

ORでそれぞれをつなげて対象を絞り込んで更新するようになります。さらに、もし複数レコードが条件に一致しても1つのレコードだけが更新されるようです。つまり、複数の一意インデックスが存在する場合 ON DUPLICATE KEY UPDATE 構文で更新する場合にどのレコードが更新対象になるかが不明瞭になるということです。

companiesテーブルの例で言えば id = 1 OR name = 'foo株式会社' の条件に一致するレコードは現時点では1つしか存在しないので実質問題は発生しません。しかし、テーブル構造によってはORでつながって探索された結果、複数のレコードがヒットして期待しないレコードが更新対象となることは考えられるでしょう。

特にRailsを使っているケースでは通常 id がPRIMARY KEYとなるので、他に1つでもUNIQUEインデックスが貼られたカラムを追加した場合はこの条件に当てはまってしまうので、上記の仕様を理解した上で使うようにするのが良さそうです。

参考