重複したレコードのINSERTを防止する

前にやっていたブログから移行してきた記事です。

idとcreated_atだけが異なるレコードが重複して登録されることがあって困っていた。
同じレコードがないときはINSERT、同じレコードがある場合はUPDATEが実行されるようにしたい。
例として以下の様なテーブルを用いて説明する。

work_itemsテーブル

id created_at work item user
1 2015-08-25 14:59:00 1 ホチキス やまだ
2 2015-08-28 19:04:00 1 はさみ たなか
3 2015-08-28 19:35:00 2 はさみ やまだ
4 2015-08-28 21:15:00 2 はさみ たなか

※例のためのテーブルなので構成がテーブルとしておかしいです。

下記を参考に実装する。
MySQL で INSERT と UPDATE を1文で実現する ～ ON DUPLICATE KEY UPDATE 編～ | UB Lab.

MySQLのユニーク制約とは？
ユニークキー制約(UNIQUE) - テーブルの作成 - MySQLの使い方

今回はworkとitem両方が同じものは登録したくない。
→workとitemの2つでユニークになるようにしたい。

複数カラムにユニーク制約をつけたい！
ActiveRecord4のバリデーションで複数カラムにユニーク制約を付ける方法
Rails でユニーク制約 - @tmtms のメモ

元々存在するテーブルなので、ユニーク制約を追加する。

class AddUniqueToWorkItem < ActiveRecord::Migration
  def change
    add_index :work_items, [:work, :item], :unique=>true
  end
end


・・・ちょっとまった！！！
元々workとitemが重複しているレコードがあるのに後からユニーク制約つけようとしたらmigrateはどうなるんだ？！？！

実際にやってみた！

Mysql2::Error: Duplicate entry '21-3' for key 'index_chat_work_items_on_work_and_item': CREATE UNIQUE INDEX `index_chat_work_items_on_work_and_item`  ON `work_items` (`work`, `item`) /test/db/migrate/20150831000000_add_unique_to_work_items.rb:3:in `change'

わーい(∩´∀｀)∩ﾜｰｲ
落ちたー！

『ALTER IGNORE』を使えば大丈夫そうだ！
複数カラムでユニークキーを作る - 揮発性のメモ

過去データが書き換わってしまう(重複していた場合は先にあるほうが残る)がOKであるかはサービスやプロジェクトによると思います！

class AddUniqueToWorkItem < ActiveRecord::Migration
  def up
    sql = "ALTER IGNORE TABLE work_item ADD UNIQUE duplicate_prevent (work, item);"
    ActiveRecord::Base.connection.execute(sql)
  end

  def down
    sql = "ALTER TABLE work_item DROP INDEX duplicate_prevent;"
    ActiveRecord::Base.connection.execute(sql)
  end
end


これであとはINSERTするときにON DUPLICATE KEY UPDATEすればOKだ♪

now = Time.now.strftime("%Y-%m-%d %H:%M:%S")
query = "INSERT INTO `work_items` (`created_at`, `work`, `item`, `user`) VALUES ('%s', %d, %s, %s) ON DUPLICATE KEY UPDATE created_at = `%s`"
query = query % [
  now,
  [workの値],
  [itemの値],
  [userの値],
  now
]
ActiveRecord::Base.connection.execute(query)


・・・と思ったら、パーティションロックがかかるらしいorz
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE 構文

該当テーブルのINSERTでユニーク制約違反例外が発生したときに無視する、という方針に変更。

begin
  WorkItem.create(
    work:    [workの値],
    item:     [itemの値],
    user:     [userの値]
  )
rescue ActiveRecord::RecordNotUnique => exception
  # ユニーク制約違反は無視する
end

これで重複記録はなくなるはず。

メモ