Rails 创建新数据主键重复问题

lengcb · June 19, 2017 · Last by lengcb replied at June 19, 2017 · 1624 hits

我项目中有一部分数据是从其他数据库导过来的,导致我创建新数据时主键重复。每次遇到这种情况我都是从数据库中删除旧数据,感觉好麻烦。有什么好的处理方式吗?我想保留旧数据不变。

1 Floor has deleted

最简单的方法是、如果以后不再导数据了、把主键递增的起始值改大一些就行了吧

导入的时候别带主键不就好了

如果用的是 postgresql 数据库,导完数据,我一般会跑这么一个 migration,让表的 id sequence 值变成最大,就不会有主键重复的问题了:

def change
  ActiveRecord::Base.connection.execute("SELECT * FROM pg_catalog.pg_tables where schemaname='public'").each do |r|
    next if r["tablename"] == 'schema_migrations'
    sequence = "#{r["tablename"]}_id_seq"

    count = ActiveRecord::Base.connection.execute("SELECT count(*) FROM pg_class where relname='#{sequence}'")
    next if count[0]['count'].to_i == 0

    max = ActiveRecord::Base.connection.execute("SELECT MAX(id) FROM #{r["tablename"]}")
    start = max[0]['max'].to_i

    current = ActiveRecord::Base.connection.execute("SELECT nextval('#{sequence}')")
    curval = current[0]['nextval'].to_i

    if curval <= start
      ActiveRecord::Base.connection.execute("SELECT setval('#{sequence}', #{start + 1}, true)")
    end

  end
end
Reply to fan124

嗯嗯,谢谢。刚查到一个方法,我试了试没出错。 ActiveRecord::Base.connection.tables.each do |t| ActiveRecord::Base.connection.reset_pk_sequence!(t) end 重设一下这个 sequence

Reply to fan124

额,格式乱了,凑和着吧

Reply to dudu_zzzz

主键肯定要带啊

Reply to wootaw

谢谢,就是用的 postgresql

You need to Sign in before reply, if you don't have an account, please Sign up first.