mysql 单号_MySQL生成订单号

生成订单号有很多种方式，下面介绍一种自定义函数的方式生产订单号的方式。

表设计:

CREATE TABLE `t_bizserial` (

`tagid` int(11) NOT NULL COMMENT '识别号',

`tradeday` varchar(6) NOT NULL COMMENT '当前交易日',

`maxbizno` int(11) NOT NULL COMMENT '当前业务号',

`startno` int(11) NOT NULL COMMENT '当前业务号',

`prefix` varchar(2) NOT NULL COMMENT '前缀',

PRIMARY KEY (`tagid`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

函数:

DELIMITER $$

USE `db_shoping`$$

DROP FUNCTION IF EXISTS `f_getorderno`$$

CREATE DEFINER=`db_shoping`@`%` FUNCTION `f_getorderno`(p_tagid TINYINT) RETURNS VARCHAR(20) CHARSET utf8

MODIFIES SQL DATA

BEGIN

DECLARE v_tradeday VARCHAR(6);

DECLARE v_curday VARCHAR(6);

DECLARE v_prefix VARCHAR(2);

SET v_curday = RIGHT(DATE_FORMAT(CURDATE(),'%Y%m%d'),6);

SELECT `tradeday`,prefix INTO v_tradeday,v_prefix FROM t_bizserial WHERE tagid = p_tagid;

IF (v_curday <> v_tradeday) THEN

UPDATE t_bizserial SET

`tradeday` = v_curday,

`maxbizno` = `startno`

WHERE tagid = p_tagid;

END IF;

UPDATE t_bizserial SET maxbizno=1+@mno:=maxbizno

WHERE tagid=p_tagid;

RETURN CONCAT(v_prefix,RIGHT(DATE_FORMAT(CURDATE(),'%Y%m%d'),6),LPAD(@mno,8,'0'));

END$$

DELIMITER ;