MySQLの"INSERT ON DUPLICATE KEY UPDATE"を知らなかった

知らなかった。

ユニークな制約に違反した場合にUPDATE文に切り替えてくれるINSERTという事らしいですが、知りませんでした。さらに、ActiveRecord::Extensions.importが対応しているという事も知りませんでした。":on_duplicate_key_update"オプションで、UPDATE対象のフィールドを指定できるようです(Railsの拡張ライブラリの話)。

ひとまずは試しという事で、小さなモデルを使ってimportが作るSQLを見ていたわけですが、さっぱり"ON DUPLICATE"がつきません。コードを見ても、MySQL用アダプタは対応しているはずですが、さっぱりです。

requireの位置や作法が間違っているのかと、READMEやら何やらを見ていると、ありました。

I forgot to mention that ar-extensions no longer loads adapter specific functionality by itself. You need to tell it what you want. For example if you want to load import functionality for MySQL you’d have to require the right files, like so:

require 'ar-extensions/adapters/mysql'
require 'ar-extensions/import/mysql'

Continuous Thinking—

0.8.0ではアダプタの自動読み込みがされていないらしく、supports_on_duplicate_key_update?がfalseになる様です。そうすると、"ON DUPLICATE"なINSERTをサポートしない事になるので、当然SQLにもくっついてきません。ちなみに、上記のrequireはアダプタ読み込みのためのrequireの様で、別途事前に"ar-extensions"をrequireする必要があったはずです。

以下が、テストに使ったモデルのマイグレーションコードです。単純なテーブルです。

class CreateOnDuplicates < ActiveRecord::Migration
  def self.up
    create_table :on_duplicates do |t|
      t.string :name, :null => false
      t.string :value
      t.timestamps
    end
    add_index :on_duplicates, [:name], :unique => true, :name => 'uniq_key'
  end
 
  def self.down
    drop_table :on_duplicates
  end
end
 

準備ができたら、script/consoleからimportメソッドを実行してみます。

>> OnDuplicate.import [:name, :value], [['xxx', '123']], :on_duplicate_key_update => { :name => :name }
=> #<OpenStruct failed_instances=[], num_inserts=1>

上記に対応するログは以下の通りです。

INSERT INTO `on_duplicates` (`name`,`value`,`created_at`,`updated_at`)
  VALUES ('xxx','123','2008-08-25 12:55:43','2008-08-25 12:55:43')
  ON DUPLICATE KEY UPDATE
    `on_duplicates`.`name`=VALUES( `name` ),
    `on_duplicates`.`updated_at`=VALUES( `updated_at` )

実行してから気づきましたが、updated_atフィールドがあるので、くっついてくるようです。

"ON DUPLICATE"がActiveRecordから利用できる事はわかりましたが、もう一つ知りたい事があります。UPDATE時のコストです。今回は、「UPDATEしたくない」という隠し(?)要求があります。ユニークなキーの存在が確認できたら、「何もしない」というSQLが欲しいわけです("ON DUPLICATE"じゃない方法で良いものがあるのか知りませんが、このままいきます)。

UPDATEが実行されるかどうかは、実際にMySQLクライアントから実行してみればわかるはずです。"〜rows affected"という結果表示に見覚えがあります。あれの数を素直に愚直に信じる事にします。「変更されてないだけで、SQL(コマンド)は実行されてる」という事かもしれませんが、ひとまずは"0 rows affected"を見て安心したいと思います。

mysql@localhost> CREATE TABLE on_duplicate
    -> (
    ->   id int primary key not null auto_increment,
    ->   name varchar(255) unique key not null,
    ->   value varchar(255)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql@localhost> INSERT INTO on_duplicate (name, value) VALUES ('xxx', '123');
Query OK, 1 row affected (0.00 sec)

mysql@localhost> SELECT * FROM on_duplicate;
+----+------+-------+
| id | name | value |
+----+------+-------+
|  1 | xxx  | 123   |
+----+------+-------+
1 row in set (0.00 sec)

mysql@localhost> INSERT INTO on_duplicate (name, value) VALUES ('xxx', '123') ON DUPLICATE KEY UPDATE name=name;
Query OK, 0 rows affected (0.00 sec)

mysql@localhost> INSERT INTO on_duplicate (name, value) VALUES ('xxx', '123') ON DUPLICATE KEY UPDATE name=VALUES(name);
Query OK, 0 rows affected (0.00 sec)

ほっ。

というわけで、「"INSERT ON DUPLICATE KEY UPDATE"したい場合は、ActiveRecord::Extensions.importでもできる」というお話でした。


SELECTの場合、明らかにありえないWHERE句が与えられた場合は、オプティマイザか何かの段階で0件を返すというのはどこかで知りました。が、UPDATEに関して、明らかに変更がない場合にオプティマイザか何かが実行を回避する、という事はあるんでしょうか?あるとして、それはVALUES(field_name)を使う場合もありでしょうか?

プロフィール

このブログ記事について

このページは、koshigoeが2008年8月25日 22:25に書いたブログ記事です。

ひとつ前のブログ記事は「ひょっとしてTime Machineで接続解除?」です。

次のブログ記事は「MySQLのUNIQUE KEY制約とNULL値」です。

最近のコンテンツはインデックスページで見られます。過去に書かれたものはアーカイブのページで見られます。