CREATE TABLE IF NOT EXISTS order_seq(
timestr INT NOT NULL,
order_sn INT NOT NULL);
建立過程
DROP PROCEDURE IF EXISTS `seq_no`;
CREATE DEFINER = `root`@`localhost` PROCEDURE `seq_no`()
BEGIN
DECLARE v_cnt INT; /*聲明變量*/
DECLARE v_timestr INT;
DECLARE rowcount BIGINT;
SET v_timestr=DATE_FORMAT(NOW(),'%Y%m%d'); /*當(dāng)天日期號*/
SELECT ROUND(RAND()*100,0)+1 INTO v_cnt;/*取隨機(jī)數(shù)*/
START TRANSACTION; /*啟動事務(wù)*/
UPDATE order_seq SET order_sn=order_sn+v_cnt WHERE timestr=v_timestr;/*再前面數(shù)據(jù)基礎(chǔ)上累加珊肃，不會出現(xiàn)重復(fù)單號*/
IF ROW_COUNT()=0 THEN
INSERT INTO order_seq(timestr,order_sn) VALUES(v_timestr,v_cnt);
END IF;/*行數(shù)為0插入數(shù)據(jù)*/
SELECT CONCAT(v_timestr,LPAD(order_sn,7,0)) AS order_sn /*日期與隨機(jī)數(shù)拼接*/
FROM order_seq WHERE timestr=v_timestr;
COMMIT;
END;
