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 ;