无法将用户从旧数据库还原到新数据库

Question

这是我第一次尝试的，

INSERT INTO db_after_deleted.users SELECT * FROM db_before_deleted.users where company_id = 30

这会引发错误：

#1062 - Duplicate entry '128' for key 'users.PRIMARY'

我认为可能是它不允许使用相同的id，所以我尝试了这个：

INSERT INTO db_after_deleted.users SELECT name,email,email_verified_at,password,remember_token, role_id,company_id,company_role, email_notification, email_subscription,first_time, created_at, updated_at, deleted_at FROM db_before_deleted.users where company_id = 30

这抛出：

#1136 - Column count doesn't match value count at row 1

我检查并确认两者确实有相同的列数。如何从company_id 30恢复用户。什么是最好和最安全的方法。

Answer 1

在第二个错误中，插入的表和源表列不对应。

我将使用显式指定在INSERT INTO之后插入的列名

INSERT INTO db_after_deleted.users
(NAME,
email,
email_verified_at,
password,
remember_token,
role_id,
company_id,
company_role,
email_notification,
email_subscription,
first_time,
created_at,
updated_at,
deleted_at)
SELECT NAME,
email,
email_verified_at,
password,
remember_token,
role_id,
company_id,
company_role,
email_notification,
email_subscription,
first_time,
created_at,
updated_at,
deleted_at
FROM   db_before_deleted.users
WHERE  company_id = 30 

第一个错误导致db_after_deleted.users中存在一些重复的数据，您可以尝试使用此查询来获取这些数据，然后在插入数据之前执行delete。

SELECT *
FROM   db_before_deleted.users b
INNER JOIN db_after_deleted.users a
ON a.id = b.id
WHERE b.company_id = 30