查看完整版本: SQL SERVER与MYSQL 的重复插入的区别

SQL SERVER与MYSQL 的重复插入的区别

[color=#000000]问题：[/color]
[color=#000000]如果一条记录存在，不插入，如果不存在则插入[/color]

SQL SERVER 中：
[color=#0080ff]create table b(id int)[/color]
[color=#0080ff]insert into b select 1[/color]
[color=#0080ff]union all[/color]
[color=#0080ff]select 2[/color]
[color=#0080ff]union all [/color]
[color=#0080ff]select 3[/color]
[color=#0080ff]union all[/color]
[color=#0080ff]select 4[/color]

[color=#0080ff]if not exists(select id from b where id=5)
insert into b(id) values(5)[/color]
===
MYSQL中没有简单的语句，只能用存储过程实现

MYSQL:
[color=#0080ff]create table b(id int);
insert into b(id) values(1),(2),(3),(4);[/color]
[color=#0080ff][/color]
[color=#0080ff]DELIMITER $$[/color]
[color=#0080ff]DROP PROCEDURE IF EXISTS `test`.`sp_b`$$[/color]
[color=#0080ff]CREATE PROCEDURE `test`.`sp_b`(in i_id int)[/color]
[color=#0080ff]    BEGIN[/color]
[color=#0080ff]    declare cnt int;
    select count(1) from b where id=i_id into cnt;
    if cnt = 0 then
       insert into b select i_id;
    end if;
    END$$[/color]
[color=#0080ff]DELIMITER ;[/color]