主键是整型自增: mysql> delimiter // mysql> drop procedure if exists insert_pro// mysql> create procedure insert_pro(uname varchar(100)) -> begin -> declare idx int; -> select id into idx from foo where memo = uname; -> if ifnull(idx,0) = 0 -> then -> #insert into foo(memo) values(uname);/*不存在插入*/ -> #select LAST_INSERT_ID() as not_find; /*插入后返回ID*/ -> select max(id)+1 as PK from foo; /*不存在返回主键*/ -> end if; -> if idx >0 -> then -> select * from foo where memo=uname;/*存在显示整行*/ -> end if; -> end // mysql> delimiter ; mysql> select * from foo; id memo 1 f 5 e 9 a 10 b 11 c mysql> call insert_pro('QQ'); PK 12mysql> call insert_pro('f'); id memo 1 f
create procedure sp_test(a int) begin if exists (select 1 from tb where num=a) then select id from tb where num=2; else insert into tb(num) values(a); select last_insert_id(); end if; end
mysql> delimiter //
mysql> drop procedure if exists insert_pro//
mysql> create procedure insert_pro(uname varchar(100))
-> begin
-> declare idx int;
-> select id into idx from foo where memo = uname;
-> if ifnull(idx,0) = 0
-> then
-> #insert into foo(memo) values(uname);/*不存在插入*/
-> #select LAST_INSERT_ID() as not_find; /*插入后返回ID*/
-> select max(id)+1 as PK from foo; /*不存在返回主键*/
-> end if;
-> if idx >0
-> then
-> select * from foo where memo=uname;/*存在显示整行*/
-> end if;
-> end //
mysql> delimiter ;
mysql> select * from foo;
id memo
1 f
5 e
9 a
10 b
11 c
mysql> call insert_pro('QQ');
PK
12mysql> call insert_pro('f');
id memo
1 f
begin
if exists (select 1 from tb where num=a)
then
select id from tb where num=2;
else
insert into tb(num) values(a);
select last_insert_id();
end if;
end