Rails方式来重置id字段上的种子 [英] Rails way to reset seed on id field

问题描述

我找到了这个问题的纯SQL答案。是否有方法 在Rails 中重置特定表的id字段？

为什么要这样做？因为我有不断移动的数据 - 很少超过100行，但总是不同的表。它现在高达25k，而且没有什么意义。我打算使用Rails应用程序内部的调度程序（rufus-scheduler）每月运行一次id字段。

I have found the "pure SQL" answers to this question. Is there a way, in Rails, to reset the id field for a specific table?
Why do I want to do this? Because I have tables with constantly moving data - rarely more than 100 rows, but always different. It is up to 25k now, and there's just no point in that. I intend on using a scheduler internal to the Rails app (rufus-scheduler) to run the id field reset monthly or so.

推荐答案

我发现了一个基于hgimenez的答案和这一个的解决方案。

I came out with a solution based on hgimenez's answer and this other one.

由于我通常使用Sqlite或PostgreSQL，我只为那些开发;但是扩展它，比如MySQL，不应该太麻烦了。

Since I usually work with either Sqlite or PostgreSQL, I've only developed for those; but extending it to, say MySQL, shouldn't be too troublesome.

把这里面放在lib /中，并且在初始化器上需要它：

Put this inside lib/ and require it on an initializer:

# lib/active_record/add_reset_pk_sequence_to_base.rb
module ActiveRecord
  class Base
    def self.reset_pk_sequence
      case ActiveRecord::Base.connection.adapter_name
      when 'SQLite'
        new_max = maximum(primary_key) || 0
        update_seq_sql = "update sqlite_sequence set seq = #{new_max} where name = '#{table_name}';"
        ActiveRecord::Base.connection.execute(update_seq_sql)
      when 'PostgreSQL'
        ActiveRecord::Base.connection.reset_pk_sequence!(table_name)
      else
        raise "Task not implemented for this DB adapter"
      end
    end     
  end
end

用法：

Client.count # 10
Client.destroy_all
Client.reset_pk_sequence
Client.create(:name => 'Peter') # this client will have id=1

b $ b

编辑：因为最常见的情况下，你会想要这样做是清除数据库表后，我建议看看 database_cleaner 。它自动处理ID重置。您可以指定只删除所选的表格，如下所示：

Since the most usual case in which you will want to do this is after clearing a database table, I recommend giving a look to database_cleaner. It handles the ID resetting automatically. You can tell it to delete just selected tables like this:

DatabaseCleaner.clean_with(:truncation, :only => %w[clients employees])

这篇关于Rails方式来重置id字段上的种子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！