在 Rails 迁移中将一列更新为另一列的值

我在一个 Rails 应用程序中有一个包含数十万条记录的表，它们只有一个 created_at时间戳。我添加了编辑这些记录的能力，所以我想向表中添加一个 updated_at时间戳。在添加列的迁移中，我希望更新所有行，使新的 updated_at与旧的 created_at匹配，因为这是 Rails 中新创建的行的默认值。我可以执行 find(:all)并循环遍历记录，但由于表的大小，这将花费数小时。我真正想做的是:

UPDATE table_name SET updated_at = created_at;

在使用 ActiveRecord 而不是执行原始 SQL 的 Rails 迁移中，是否有更好的方法来做到这一点？

58040 次浏览
小开

You can use update_all which works very similar to raw SQL. That's all options you have.

BTW personally I do not pay that much attention to migrations. Sometimes raw SQL is really best solution. Generally migrations code isn't reused. This is one time action so I don't bother about code purity.

小开

As a one time operation, I would just do it in the rails console. Will it really take hours? Maybe if there are millions of records…

records = ModelName.all; records do |r|; r.update_attributes(:updated_at => r.created_at); r.save!; end;`
小开
最佳答案

I would create a migration 

rails g migration set_updated_at_values

and inside it write something like:

class SetUpdatedAt < ActiveRecord::Migration
def self.up
Yourmodel.update_all("updated_at=created_at")
end


def self.down
end
end

This way you achieve two things

  • this is a repeatable process, with each possible deploy (where needed) it is executed
  • this is efficient. I can't think of a more rubyesque solution (that is as efficient).

Note: you could also run raw sql inside a migration, if the query gets too hard to write using activerecord. Just write the following: 

Yourmodel.connection.execute("update your_models set ... <complicated query> ...")
小开

As gregdan wrote, you can use update_all. You can do something like this: 

Model.where(...).update_all('updated_at = created_at')

The first portion is your typical set of conditions. The last portion says how to make the assignments. This will yield an UPDATE statement, at least in Rails 4.

小开

This is a General way of solving, without the need for Writing Query, as queries are subjected to risk.

  class Demo < ActiveRecord::Migration
def change
add_column :events, :time_zone, :string
Test.all.each do |p|
p.update_attributes(time_zone: p.check.last.time_zone)
end
remove_column :sessions, :time_zone
end
end
小开

You can directly run following command to your rails console ActiveRecord::Base.connection.execute("UPDATE TABLE_NAME SET COL2 = COL1")

For example: I want to update sku of my items table with remote_id of items tables. the command will be as following:
ActiveRecord::Base.connection.execute("UPDATE items SET sku = remote_id")

小开

Do not use application models in migrations unless you redefine them inside migration. If you use application model tahat you later change or delete your migration might fail.

Of course you can also use full power of SQL inside migrations.

Read https://makandracards.com/makandra/15575-how-to-write-complex-migrations-in-rails

小开

You can also add updated_at column and update its values in one migration:

class AddUpdatedAtToTableName < ActiveRecord::Migration
def change
add_column :table_name, :updated_at, :datetime


reversible do |dir|
dir.up do
update "UPDATE table_name SET updated_at=created_at"
end
end
end
end

提交答案