如何让Ruby on Rails处理MySQL数据库中的重复记录错误

Question

我在我的数据库上有多个字段的唯一索引。如果您尝试在重复记录上调用save，它将引发ActiveRecord :: StatementInvalid并显示mysql错误。是否有一种方法可以在Rails内处理此问题，无论是通过创建唯一约束条件还是以其他方式在出现此情况时返回相关错误消息？

这是跟踪信息：

ActiveRecord::StatementInvalid: Mysql::Error: Duplicate entry '2010-12-09-2-0-1-1' for key 2: INSERT INTO `entries` (`rejected_at`, `created_at`, `comments`, `overtime`, `submitted_at`, `updated_at`, `time`, `approved`, `day`, `user_id`, `approved_at`, `job_id`, `submitted`, `rejected`) VALUES(NULL, '2010-12-09 21:50:46', NULL, 0, NULL, '2010-12-09 21:50:46', 2.0, NULL, '2010-12-09', 1, NULL, 1, NULL, NULL)
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:219:in `log'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:319:in `execute'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:259:in `insert_sql'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:329:in `insert_sql'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:44:in `insert_without_query_dirty'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:18:in `insert'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/base.rb:2901:in `create_without_timestamps'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/timestamp.rb:53:in `create_without_callbacks'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/callbacks.rb:266:in `create'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/base.rb:2867:in `create_or_update_without_callbacks'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/callbacks.rb:250:in `create_or_update'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/base.rb:2538:in `save_without_validation'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/validations.rb:1078:in `save_without_dirty'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/dirty.rb:79:in `save_without_transactions'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/transactions.rb:229:in `send'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/transactions.rb:229:in `with_transaction_returning_status'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:136:in `transaction'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/transactions.rb:182:in `transaction'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/transactions.rb:228:in `with_transaction_returning_status'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/transactions.rb:196:in `save'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/transactions.rb:208:in `rollback_active_record_state!'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/transactions.rb:196:in `save'

- loosecannon

Answer 1

begin
  Event.create!(:name => 'Ironman Lanzarote 2011')
rescue ActiveRecord::RecordNotUnique => e
  # handle duplicate entry 
end

在Rails 3中，这对我有用。

- Michael Voigt

---

这违反了最少惊讶原则，并且不遵循Rails中的约定。 - maletor

当您使用主/从设置时，您将遇到漂移问题（以及由此产生的竞争条件），可以轻松地使用这种方法解决，而不是使用Rails的唯一验证，可能会在从服务器上进行检查。 - scones

这样做没问题，但我会将此行为放在一个具有描述性名称的模型方法中。 - Papipo

Answer 2

您可以在模型中添加唯一性约束，这样记录将返回错误并无效。

例如（Rails 2）：

class User < ActiveRecord::Base
  validates_uniqueness_of :email, :scope => [:name, :age]
end

或（Rails 3）

class User < ActiveRecord::Base
  validates :email, :uniqueness => {:scope => [:name, :age]}
end

这应该产生以下结果：

user1 = User.create :email => "[e-mail]", :name => "one", :age => 20
user2 = User.create :email => "[e-mail]", :name => "one", :age => 20
user2.valid? # false

- Pan Thomakos

---

我知道我可以为一个字段做到这一点，但我能否为多个字段做到这一点，例如，我可以让它检查它们是否具有相同的电子邮件、姓名、喜爱颜色和工作。可能会有5个人叫约翰，但只有一个人可以拥有[e-mail]、橙色和成为程序员。这样说您明白了吗？ - loosecannon

嘿，我更新了帖子，并提供了多个属性的答案。 - Pan Thomakos

虽然这基本涵盖了大多数使用情况，但validates_uniqueness_of中存在一种竞态条件 - 它不能完全阻止重复输入的方式导致了这种情况。请注意，我的翻译只包括原始意思和更易于理解的措辞，没有其他背景或解释信息。 - Omar Qureshi

没错，Omar。您仍应该在MySQL上拥有一个(name、age、email)的唯一索引，并且最好在更新操作周围加上begin..rescue..end语句以捕获这些情况，如果您认为这可能是您的应用程序的问题。 - Pan Thomakos

Answer 3

你能把查询改成

INSERT IGNORE INTO `entries` ...

或者，您可以考虑使用INSERT... ON DUPLICATE KEY UPDATE...语法。

INSERT的文档在这里。

- Riedsio

---

我认为这样可以在一定程度上解决问题，但是SQL查询是自动生成的。我可以修改它，但我希望它能引发一个错误，只有我能捕获并显示消息，否则服务器会显示500内部服务器错误。 - loosecannon