方法一,直接把 field1,field2 设置为 unique key, 这样,当你插入重复值时会报错。 方法二 insert into tab1 (id,field1,field2) select $id,a1,b1 from dual where not exists (select 1 from tab1 where field1=a1 and field2=b1)
IF not exists (select 1 from tab1 where field1=a1 and field2=b1) THEN insert into tab1 (id,field1,field2) values($id,a1,b1 ) ; END IF
->drop procedure if exists pro_tab1; ->delimiter // ->create procedure pro_tab1(in str1 varchar(100),in str2 varchar(100)) ->begin ->set @n=0; ->select count(*) into @n from tab1 where field1=str1 and field2=str2; ->if @n=0 then ->insert into tab1(field1,field2) values(str1,str2); ->end if; ->end// ->delimiter ; ->call pro_tab1('a','b'); 影响的数据栏: 1 时间: 0.000ms ->call pro_tab1('a','b'); 影响的数据栏: 0 时间: 0.000ms
不能用insert into 数据多了要load data local infile,要考虑到这一点!
方法二 insert into tab1 (id,field1,field2)
select $id,a1,b1 from dual where not exists (select 1 from tab1 where field1=a1 and field2=b1)
END IF
->drop procedure if exists pro_tab1;
->delimiter //
->create procedure pro_tab1(in str1 varchar(100),in str2 varchar(100))
->begin
->set @n=0;
->select count(*) into @n from tab1 where field1=str1 and field2=str2;
->if @n=0 then
->insert into tab1(field1,field2) values(str1,str2);
->end if;
->end//
->delimiter ;
->call pro_tab1('a','b');
影响的数据栏: 1
时间: 0.000ms
->call pro_tab1('a','b');
影响的数据栏: 0
时间: 0.000ms