mysql 函数 生成订单号_mysql 函数根据日期生成订单号

#创建一个自动生成订单序号的函数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();