mysql - 将不存在的记录插入Mysql表中

Question

您好，在我们的系统中，我们丢失了一个 acc，我们需要找回它。幸运的是我们有数据库的备份。现在我正在尝试将记录从备份 mysql 数据库添加到当前数据库。但我有一个问题。当我尝试插入数据时，出现错误“ID 重复”。这就是我从备份导出表时得到的结果。

    INSERT INTO `anketu_perziuros_mine` (`id`, `anketa`, `kada`, `timemark`) VALUES
(955009, 498044, 1443021887, '2015-09-23 18:24:47'),
(147188, 498044, 1443018663, '2015-09-23 17:31:03'),
(948120, 498044, 1443017899, '2015-09-23 17:18:19'),
(958152, 498044, 1442954185, '2015-09-22 23:36:25'),
(888916, 498044, 1442863283, '2015-09-21 22:21:23'),
(782244, 498044, 1442839575, '2015-09-21 15:46:15'),
(827707, 498044, 1442746875, '2015-09-20 14:01:15'),
(869393, 498044, 1442683453, '2015-09-19 20:24:13');

我对 mysql 完全陌生。我尝试了很多方法(来自教程)但都失败了。IF NOT EXISTS如何实现，或者还有其他解决方案吗？

Answer 1

这里举例说明如何实现丢失数据的恢复。请参阅Demo on SQL Fiddle .

-- suppose you have a table 
create table test (
  id int not null primary key,
  val int not null
);

-- with these data
insert into test values
(1,1),
(2,2),
(3,4),
(4,90);

-- lets assume you lost these data from the test table
delete from test where id in (1,4);

-- now you want to restore the lost data from your backup
-- do the following.

-- create a temporal table with schema of test table
create table test2 as 
select * from test limit 0;

-- insert backup data into the temporal table
insert into test2 values
(1,1),
(2,2),
(3,4),
(4,90);

-- copy backup data from temporal table into the real table
insert into test
select * from test2 b
where not exists (select null from test a where a.id=b.id);

-- drop backup data
drop table test2;