在插入触发器之前，用于将重复的行插入到另一个表中 [英] before insert trigger for insert duplicate rows into another table

问题描述

我有一个名为tblspmaster的表，其中sp列具有唯一索引，因此不会插入任何重复项，但是我想将重复的行插入到tblspduplicate中.所以我决定为此编写触发器. tblspmaster记录的IN主表将使用mysql

I have a table called tblspmaster in which sp column i have unique index so there will be no duplicates will be inserted, but i want to insert duplicate rows into tblspduplicate . so i decided to write trigger for this . IN master table which is tblspmaster records will be inserted using Load File of mysql

create trigger tblspmaster_noduplicate
before insert on tblspmaster
for each row
begin
  if ( select count(sp) from tblspmaster where sp=new.sp > 0 )then
    insert into tblspduplicate (sp,FileImported,AMZFileName)   values (NEW.sp,NEW.FileImported,NEW.AMZFileName)
  END
END

我有问题列表

1. 这是停止重复并将其插入另一个的正确方法吗 表?

1. Is this right approach to stop duplicates and insert into another table ?

我的触发器未执行，因为它显示了一些语法错误

My trigger is not executing as its showing some syntax errors

错误响应为Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END END' at line 7

** * ** * ** * ** * **** 已编辑 * ** * ** * ** * ****

****************EDITED**************

这是主表和重复表以及触发器和数据文件MySQL语句的表定义

here is table definition for master and duplicate table and trigger and load data file MySQL statements

CREATE TABLE IF NOT EXISTS `tblspmaster` (
  `CSN` bigint(20) NOT NULL AUTO_INCREMENT,
  `SP` varchar(10) NOT NULL,
  `FileImportedDate` date NOT NULL,
  `AMZFileName` varchar(50) NOT NULL,
  `CasperBatch` varchar(50) NOT NULL,
  `BatchProcessedDate` date NOT NULL,
  `ExpiryDate` date NOT NULL,
  `Region` varchar(50) NOT NULL,
  `FCCity` varchar(50) NOT NULL,
  `VendorID` int(11) NOT NULL,
  `LocationID` int(11) NOT NULL,
  PRIMARY KEY (`CSN`),
  UNIQUE KEY `SP` (`SP`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10000000000 ;




   CREATE TABLE IF NOT EXISTS `tblspduplicate` (
      `SP` varchar(50) NOT NULL,
      `FileImportedDate` date NOT NULL,
      `AMZFileName` varchar(50) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

use casper;
DELIMITER $$
create trigger tblspmaster_noduplicate
before insert on tblspmaster
for each row
begin
  if ( select count(sp) from tblspmaster where sp=new.sp > 0 ) then
    insert into tblspduplicate (sp,FileImportedDate,AMZFileName)   values (NEW.sp,NEW.FileImportedDate,NEW.AMZFileName);
  END IF;
END$$

DELIMITER ;






LOAD DATA local INFILE 'E://31october//SP//sp_files_sample1//400k sp00 6-19 E.csv'
INTO TABLE  tblspmaster
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(sp);

这是我从c#控制台应用程序执行此MySql命令的故事的一个转折点，但我认为它不会以任何方式影响与db相关的结构或程序. 我还需要删除IGNORE 1 LINES语句，因为将没有标题行.

Here is one twist in story that i am executing this MySql command from a c# console application but i dont think it will affect our db related structure or program in any way. I also need to remove IGNORE 1 LINES statement as there will be no header row.

推荐答案

就您所关注的问题而言，存在几个问题:

As far as you trigger concerned there are several problems:

1. 插入语句后没有;
2. IF语句应以END IF和分号结尾，而不仅仅是END
3. 您必须使用DELIMITER命令更改定界符
4. 使用EXISTS()而不是COUNT()

1. you don't have ; after insert statement
2. IF statement should end with END IF and a semicolon, not just END
3. you have to change a delimiter with DELIMITER command
4. use EXISTS() rather then COUNT()

话虽如此，您的触发器可能看起来像

That being said your trigger might look like

DELIMITER $$
CREATE TRIGGER tblspmaster_noduplicate
BEFORE INSERT ON tblspmaster
FOR EACH ROW
BEGIN
  IF (EXISTS(SELECT * FROM tblspmaster WHERE sp = NEW.sp)) THEN
    INSERT INTO tblspduplicate (sp,FileImported,AMZFileName)   
    VALUES (NEW.sp, NEW.FileImported, NEW.AMZFileName);
  END IF;
END$$
DELIMITER ;

这里是 SQLFiddle 演示

Here is SQLFiddle demo

在LOAD DATA INFILE语句中使用IGNORE子句. MySql将错误(违反唯一约束)作为警告有效地丢弃重复项.

Use IGNORE clause in your LOAD DATA INFILE statement. MySql will treat errors (violating unique constraint) as warnings effectively discarding duplicates.

加载数据文件
如果指定IGNORE，将跳过在唯一键值上复制现有行的输入行.

LOAD DATA INFILE
If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped.

LOAD DATA LOCAL INFILE 'E://31october//SP//sp_files_sample1//400k sp00 6-19 E.csv' 
IGNORE  
INTO TABLE tblspmaster 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' 
LINES TERMINATED BY '\n' 
-- IGNORE 1 LINES

注意::FYI重复行插入失败将在auto_increment SCN列的值中留下空白.

Note: FYI failed inserts for duplicate rows will leave gaps in values of auto_increment SCN column.

您可能会考虑另一种可能更可取的性能方法:

You may consider another approach which might be more preferable performance wise:

1. 创建无约束且无索引的临时登台表
2. 使用LOAD DATA INFILE填充登台表
3. 具有tblspmaster和登台表，并使用INSERT ... SELECT语法一次将所有重复项插入tblspduplicate中
4. 一次性将暂存表中不存在的行再次插入到tblspmaster中
5. TRUNCATE或DROP登台表

1. create temporary staging table with no constraints and no indices
2. use LOAD DATA INFILE to populate staging table
3. having tblspmaster and the staging table and using INSERT ... SELECT syntax insert all duplicates in tblspduplicate in one go
4. insert only non-existent rows from staging table into tblspmaster again in one go
5. TRUNCATE or DROP staging table

这篇关于在插入触发器之前，用于将重复的行插入到另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！