#创建一个自动生成订单序号的函数20140103001
#作用:可以减少连接数据库的次数,减少数据库的负担,加快程序的的运行
drop function if exists create_sn;
delimiter $$
create function test_1.create_sn() returns bigint(15) #编写程序时要注意数据类型
begin
declare order_sn bigint(15);
declare prev bigint(15);
declare prevdatetime bigint(15);
declare sn bigint(15);
declare nowdate bigint(15);
select order_sn from userorder order by userorder_id desc limit 1 into prev; #赋值prev
select concat(year(prev),month(prev),dayofmonth(prev)) into prevdatetime;
select right(prev,4) into sn;
select concat(year(now()),month(now()),dayofmonth(now())) into nowdate;
#if isnull(prev) && nowdate = prevdatetime then mysql不支持这样写
if isnull(prev) then
select concat(nowdate,'0001') into order_sn;
return order_sn;
elseif nowdate = prevdatetime then
select concat(nowdate,'0001') into order_sn;
return order_sn;
else
select concat(prevdatetime,(sn+1)) into order_sn;
return order_sn;
end if;
end
$$
delimiter ;
select create_sn();