在插入触发器之前,用于将重复的行插入到另一个表中 [英] 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
我有问题列表
-
这是停止重复并将其插入另一个的正确方法吗 表?
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:
- 插入语句后没有
;
-
IF
语句应以END IF
和分号结尾,而不仅仅是END
- 您必须使用
DELIMITER
命令更改定界符 - 使用
EXISTS()
而不是COUNT()
- you don't have
;
after insert statement IF
statement should end withEND IF
and a semicolon, not justEND
- you have to change a delimiter with
DELIMITER
command - use
EXISTS()
rather thenCOUNT()
话虽如此,您的触发器可能看起来像
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:
- 创建无约束且无索引的临时登台表
- 使用
LOAD DATA INFILE
填充登台表 - 具有
tblspmaster
和登台表,并使用INSERT ... SELECT
语法一次将所有重复项插入tblspduplicate
中 - 一次性将暂存表中不存在的行再次插入到
tblspmaster
中 -
TRUNCATE
或DROP
登台表
- create temporary staging table with no constraints and no indices
- use
LOAD DATA INFILE
to populate staging table - having
tblspmaster
and the staging table and usingINSERT ... SELECT
syntax insert all duplicates intblspduplicate
in one go - insert only non-existent rows from staging table into
tblspmaster
again in one go TRUNCATE
orDROP
staging table
这篇关于在插入触发器之前,用于将重复的行插入到另一个表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!