mysql - 在表中插入多行而不插入重复行

Question

我想合并一个数据来自两个不同数据库的表。我的操作如下:

1 – 我已经完成了源数据库表的转储，并得到以下插入查询:

INSERT INTO `t_vaccination` VALUES     (242,NULL,NULL,53,1,'20030528','0','W5770-2',0,'DTP - REVAXIS','A 20130521170623','2013-05-21 17:06:23'),
(243,NULL,NULL,53,1,'20130525','0','',1,'DTP - ','A 20130521170623','2013-05-21 17:06:23'),
(1830,NULL,NULL,50,1,'20080502','3','',0,'DTP - REVAXIS','A 20130521170623','2013-05-21 17:06:23'),
(1831,NULL,NULL,50,1,'20130501','4','',1,'DTP - ','A 20130521170623','2013-05-21 17:06:23'),
(1832,NULL,NULL,50,1,'20080502','3','',0,'PAPILLOMAVIRUS - Gardasil','A 20130521170623','2013-05-21 17:06:23')

t_vaccination表的结构为:

CREATE TABLE `t_vaccination` (
`nIdVaccination` INT(10) UNSIGNED NOT NULL,
`nIdVaccin` INT(10) UNSIGNED NULL DEFAULT NULL,
`nIdVacProtocole` INT(10) UNSIGNED NULL DEFAULT NULL,
`nIdPatient` INT(10) UNSIGNED NOT NULL,
`nIdUtilisateur` INT(10) UNSIGNED NULL DEFAULT NULL,
`sDateInjection` VARCHAR(8) NOT NULL DEFAULT '',
`nNumInjection` VARCHAR(45) NOT NULL DEFAULT '0',
`sNumLot` VARCHAR(45) NOT NULL DEFAULT '',
`nRappel` TINYINT(4) NOT NULL DEFAULT '0',
`sLibelle` VARCHAR(255) NOT NULL DEFAULT '',
`sAction` VARCHAR(16) NOT NULL DEFAULT 'A 20080101000000',
`sDH_REPLIC` DATETIME NULL DEFAULT '2010-01-01 00:00:00',
PRIMARY KEY (`nIdVaccination`),
INDEX `NDX_t_vaccination_nIdUtilisateur` (`nIdUtilisateur`),
INDEX `NDX_t_vaccination_nIdVaccin` (`nIdVaccin`),
INDEX `NDX_t_vaccination_nIdVacProtocole` (`nIdVacProtocole`),
INDEX `NDX_t_vaccination_nIdPatient` (`nIdPatient`),
CONSTRAINT `FK_vaccination_nIdUtilisateur_utilisateur` FOREIGN KEY (`nIdUtilisateur`) REFERENCES `t_utilisateur` (`nIdUtilisateur`),
CONSTRAINT `FK_vaccination_nIdVaccin_vaccin` FOREIGN KEY (`nIdVaccin`) REFERENCES `t_vaccin` (`nIdVaccin`)
)

2 - 我想插入最终数据库的 t_vaccination 表中的所有行，而不插入重复行。通过插入一行来运行新查询:

INSERT INTO t_vaccination (nIdVaccination, nIdVaccin, nIdVacProtocole, nIdPatient, nIdUtilisateur, sDateInjection, nNumInjection, sNumLot, nRappel, sLibelle, sAction, sDH_REPLIC) 
SELECT 251,41,4,53,1,'20030528','0','W5770-2',0,'DTP - REVAXIS','A 20130521170623','2013-05-21 17:06:23' FROM t_vaccination  WHERE NOT EXISTS (SELECT nIdVaccin, nIdVacProtocole, nIdPatient, nIdUtilisateur FROM t_vaccination WHERE nIdVaccin = 41 and nIdVacProtocole = 4 and nIdPatient = 53 and nIdUtilisateur =1 ) LIMIT 1

3 - 是否可以通过使用不存在的插入来按组插入行，因为我所做的尝试失败了。以下是插入失败的示例:

INSERT INTO t_vaccination (nIdVaccination, nIdVaccin, nIdVacProtocole, nIdPatient, nIdUtilisateur, sDateInjection, nNumInjection, sNumLot, nRappel, sLibelle, sAction, sDH_REPLIC) 
SELECT 251,41,4,53,1,'20030528','0','W5770-2',0,'DTP - REVAXIS','A 20130521170623','2013-05-21 17:06:23' FROM t_vaccination  WHERE NOT EXISTS (SELECT nIdVaccin, nIdVacProtocole, nIdPatient, nIdUtilisateur FROM t_vaccination WHERE nIdVaccin = 41 and nIdVacProtocole = 4 and nIdPatient = 53 and nIdUtilisateur =1 ) LIMIT 1,
SELECT 243,NULL,NULL,53,1,'20130525','0','',1,'DTP - ','A 20130521170623','2013-05-21 17:06:23' FROM t_vaccination  WHERE NOT EXISTS (SELECT nIdVaccin, nIdVacProtocole, nIdPatient, nIdUtilisateur FROM t_vaccination WHERE nIdVaccin = NULL and nIdVacProtocole = NULL and nIdPatient = 53 and nIdUtilisateur =1 ) LIMIT 1

希望得到您的帮助。

问候

莫蒂

Answer 1

在我看来，执行您想要的操作的最简单方法是删除唯一键/索引并删除重复项或创建一个没有这些键的临时表。假设您创建了一个 temp_t_vaccination 表并导入所有行，然后您只需执行以下操作:

INSERT INTO t_vaccination (field1, field2 ...) SELECT DISTINCT field1, fields2 ... FROM temp_vaccination

引用:http://dev.mysql.com/doc/refman/5.0/en/insert-select.html?ff=nopfpls