ActiveRecord/Rails 中的原子插入或增量 [英] atomic insert or increment in ActiveRecord/Rails

问题描述

在 Rails 中为带有计数器的模型实现原子插入/更新的最佳方法是什么?我试图解决的问题的一个很好的类比是具有两个字段的喜欢"计数器:

What's the best way to implement an atomic insert/update for a model with a counter in Rails? A good analogy for the problem I'm trying to solve is a "like" counter with two fields:

url   : string
count : integer

插入时，如果当前没有匹配 url 的记录，则应创建一个计数为 1 的新记录；否则现有记录的 count 字段应该增加.

Upon insert, if there is not currently a record with a matching url, a new record should be created with count 1; else the existing record's count field should be incremented.

最初我尝试了如下代码:

Initially I tried code like the following:

Like.find_or_create_by_url("http://example.com").increment!(:count)

但不出所料，结果 SQL 显示 SELECT 发生在 UPDATE 事务之外:

But unsurprisingly, the resulting SQL shows that the SELECT happens outside the UPDATE transaction:

Like Load (0.4ms)  SELECT `likes`.* FROM `likes` WHERE `likes`.`url` = 'http://example.com' LIMIT 1
(0.1ms)  BEGIN
(0.2ms)  UPDATE `likes` SET `count` = 4, `updated_at` = '2013-01-17 19:41:22' WHERE `likes`.`id` = 2
(1.6ms)  COMMIT

是否有处理这个问题的 Rails 习惯用法，或者我是否需要在 SQL 级别实现它(从而失去一些可移植性)?

Is there a Rails idiom for dealing with this, or do I need to implement this at the SQL level (and thus lose some portability)?

推荐答案

我不知道有任何 ActiveRecord 方法可以在单个查询中实现原子增量.你自己的答案是你所能得到的.

I am not aware of any ActiveRecord method that implemts atomic increments in a single query. Your own answer is a far as you can get.

所以您的问题可能无法使用 ActiveRecord 解决.请记住:ActiveRecord 只是一个映射器，用于简化某些事情，同时使其他事情复杂化.有些问题只能通过对数据库的普通 SQL 查询来解决.你会失去一些便携性.下面的示例适用于 MySQL，但据我所知，不适用于 SQLlite 和其他.

So your problem may not be solvable using ActiveRecord. Remember: ActiveRecord is just a mapper to simplify some things, while complicating others. Some problems just solvable by plain SQL queries to the database. You will loose some portability. The example below will work in MySQL, but as far as I know, not on SQLlite and others.

 quoted_url = ActiveRecord::Base.connection.quote(@your_url_here)
::ActiveRecord::Base.connection.execute("INSERT INTO likes SET `count` = 1, url = "#{quoted_url}" ON DUPLICATE KEY UPDATE count = count+1;");

这篇关于ActiveRecord/Rails 中的原子插入或增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！