SQL Rails 在 id 字段上重置种子的方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2097052/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails way to reset seed on id field
提问by Trevoke
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.
我找到了这个问题的“纯 SQL”答案。有没有办法在 Rails中重置特定表的 id 字段？
我为什么要这样做？因为我有包含不断移动数据的表 - 很少超过 100 行，但总是不同的。现在高达 25k，这没有意义。我打算使用 Rails 应用程序（rufus-scheduler）内部的调度程序来运行每个月左右重置的 id 字段。
回答by hgmnz
You never mentioned what DBMS you're using. If this is postgreSQL, the ActiveRecord postgres adapter has a reset_pk_sequences!method that you could use:
您从未提及您使用的是什么 DBMS。如果这是 postgreSQL，则 ActiveRecord postgres 适配器有一个reset_pk_sequences!您可以使用的方法：
ActiveRecord::Base.connection.reset_pk_sequence!('table_name')
回答by kikito
I came out with a solution based on hgimenez's answer and this other one.
我提出了一个基于 hgimenez 的答案和另一个的解决方案。
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.
因为我通常使用 Sqlite 或 PostgreSQL，所以我只为它们开发；但是将其扩展到例如 MySQL 应该不会太麻烦。
Put this inside lib/ and require it on an initializer:
把它放在 lib/ 中并要求它在初始化程序上：
# 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
Usage:
用法：
Client.count # 10
Client.destroy_all
Client.reset_pk_sequence
Client.create(:name => 'Peter') # this client will have id=1
EDIT: 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:
编辑：由于您想要执行此操作的最常见情况是在清除数据库表之后，我建议您查看database_cleaner。它会自动处理 ID 重置。您可以告诉它只删除选定的表，如下所示：
DatabaseCleaner.clean_with(:truncation, :only => %w[clients employees])
回答by cwninja
I assume you don't care about the data:
我假设您不关心数据：
def self.truncate!
connection.execute("truncate table #{quoted_table_name}")
end
Or if you do, but not too much (there is a slice of time where the data only exists in memory):
或者，如果您这样做，但不会太多（有一段时间数据仅存在于内存中）：
def self.truncate_preserving_data!
data = all.map(&:clone).each{|r| raise "Record would not be able to be saved" unless r.valid? }
connection.execute("truncate table #{quoted_table_name}")
data.each(&:save)
end
This will give new records, with the same attributes, but id's starting at 1.
这将提供具有相同属性的新记录，但 id 从 1 开始。
Anything belongs_toing this table could get screwy.
belongs_to这张桌子上的任何东西都可能变得棘手。
回答by AlexChaffee
Based on @hgmnz 's answer, I made this method that will set the sequence to any value you like... (Only tested with the Postgres adapter.)
根据@hgmnz 的回答，我创建了这个方法，可以将序列设置为您喜欢的任何值......（仅使用 Postgres 适配器进行测试。）
# change the database sequence to force the next record to have a given id
def set_next_id table_name, next_id
connection = ActiveRecord::Base.connection
def connection.set_next_id table, next_id
pk, sequence = pk_and_sequence_for(table)
quoted_sequence = quote_table_name(sequence)
select_value <<-end_sql, 'SCHEMA'
SELECT setval('#{quoted_sequence}', #{next_id}, false)
end_sql
end
connection.set_next_id(table_name, next_id)
end
回答by MattMcKnight
One problem is that these kinds of fields are implemented differently for different databases- sequences, auto-increments, etc.
一个问题是，对于不同的数据库，这些类型的字段的实现方式不同——序列、自动增量等。
You can always drop and re-add the table.
您可以随时删除并重新添加表。
回答by Jonas Elfstr?m
No there is no such thing in Rails. If you need a nice ids to show the users then store them in a separate table and reuse them.
不，Rails 中没有这样的东西。如果您需要一个很好的 id 来显示用户，那么将它们存储在一个单独的表中并重复使用它们。
回答by David Oneill
You could only do this in railsif the _ids are being set by rails. As long as the _ids are being set by your database, you won't be able to control them without using SQL.
如果 _ids 由 rails 设置，则只能在 rails 中执行此操作。只要 _ids 是由您的数据库设置的，您将无法在不使用 SQL 的情况下控制它们。
Side note: I guess using rails to regularly call a SQL procedure that resets or drops and recreates a sequence wouldn't be a purely SQL solution, but I don't think that is what you're asking...
旁注：我想使用 rails 定期调用重置或删除并重新创建序列的 SQL 过程不会是纯粹的 SQL 解决方案，但我认为这不是你要问的......
EDIT:
编辑：
Disclaimer: I don't know much about rails.
免责声明：我对 Rails 了解不多。
From the SQL perspective, if you have a table with columns id first_name last_nameand you usually insert into table (first_name, last_name) values ('bob', 'smith')you can just change your queries to insert into table (id, first_name, last_name) values ([variable set by rails], 'bob', 'smith')This way, the _id is set by a variable, instead of being automatically set by SQL. At that point, rails has entire control over what the _ids are (although if it is a PK you need to make sure you don't use the same value while it's still in there).
从 SQL 的角度来看，如果您有一个包含列的表，id first_name last_name并且您通常insert into table (first_name, last_name) values ('bob', 'smith')可以将查询更改为insert into table (id, first_name, last_name) values ([variable set by rails], 'bob', 'smith')This way，_id 由变量设置，而不是由 SQL 自动设置。那时，rails 可以完全控制 _id 是什么（尽管如果它是 PK，您需要确保在它仍在那里时不使用相同的值）。
If you are going to leave the assignment up to the database, you have to have rails run (on whatever time schedule) something like:
如果您要将分配留给数据库，则必须让 rails 运行（按任何时间表），例如：
DROP SEQUENCE MY_SEQ;
CREATE SEQUENCE MY_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1;
to whatever sequence controls the ids for your table. This will get rid of the current sequence, and create a new one. This is the simplest way I know of you 'reset' a sequence.
以任何顺序控制表的 id。这将摆脱当前序列，并创建一个新序列。这是我所知道的“重置”序列的最简单方法。
回答by ?tefan Barto?
Rails way for e.g. MySQL, but with lost all data in table users:
例如 MySQL 的 Rails 方式，但丢失了表中的所有数据users：
ActiveRecord::Base.connection.execute('TRUNCATE TABLE users;')
Maybe helps someone ;)
也许可以帮助某人;)
回答by tbushman
There are CounterCache methods:
https://www.rubydoc.info/docs/rails/4.1.7/ActiveRecord/CounterCache/ClassMethods
I used Article.reset_counters Article.all.length - 1and it seemed to work.
有 CounterCache 方法：
https://www.rubydoc.info/docs/rails/4.1.7/ActiveRecord/CounterCache/ClassMethods
我使用过Article.reset_counters Article.all.length - 1，它似乎有效。
