mysql订单编号是什么类型_mysql生成订单编号函数

标签：

DROP TABLE IF EXISTS `order_seq`;

CREATE TABLE `order_seq` (

`timestr` int(11) NOT NULL,

`order_sn` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP FUNCTION order_seq;

create function order_seq()

returns integer

begin

declare v_cnt integer;

declare v_timestr integer;

declare v_value integer ;

declare rowcount BIGINT;

set v_timestr =DATE_FORMAT(now(),‘%Y%m%d‘);

select ROUND(RAND()*100,0) + 1 INTO v_cnt;

UPDATE order_seq set order_sn = order_sn + v_cnt where timestr = v_timestr;

if ROW_COUNT() = 0 THEN

INSERT INTO oder_seq values(v_timestr,v_cnt);

end if;

select CONCAT(v_timestr,LPAD(order_sn,7,0)) INTO v_value from order_seq where timestr = v_timestr;

return v_value;

end;

--运行结果

SELECT order_seq();

标签：