Mysql generates a sequential number stored procedure Order Number

It is very common to use a stored procedure to generate a sequential number. Here we use the sequential number generated for the order number as an example. (The flow number for the new day starts from 1. For example, the order number for today is cd20130000000014, And the next order number is cd20130000000015. The Order Number for tomorrow will start from CD2013011000001: 2-digit prefix

It is very common to use a stored procedure to generate a sequential number. Here we use the sequential number generated for the order number as an example. (The serial number of the new day starts from 1. For example, the Order Number of today is CD2013 0109 00014, And the next order number is CD 2013 0109 00015; tomorrow's order number will start from CD 2013 0110 00001) generation rule: 2-digit prefix

It is very common to use a stored procedure to generate a sequential number. Here we use the sequential number generated for the order number as an example. (The flow number for the new day starts from 1. For example, the order number for today is cd20130000000014, And the next order number is cd20130000000015. The Order Number for tomorrow will start from CD2013011000001)

Generation rules: 2-digit prefix + year, month, and day + 5-digit sequential number or 2-digit prefix + year, month, and day + 5-digit sequential number or 2-digit prefix + year, month, day, minute, and second + 5-digit sequential number.

Test order table (test_orders ):

CREATE TABLE `test_orders` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `orderNo` varchar(25) NOT NULL DEFAULT '',    `orderName` char(10) NOT NULL DEFAULT '',    PRIMARY KEY (`id`)  ) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8

Generate_orderNo ):

Create definer = PROCEDURE 'generate _ orderno' (in orderNamePre char (2), in num int, out newOrderNo varchar (25) begin declare currentDate varCHAR (15); -- current date, it may contain time, minute, and second DECLARE maxNo int default 0; -- the last five bits of the sequential number that meet the condition closest to the current time, such: maxNo = 2 -- DECLARE l_orderNo varCHAR (25); -- generated order number -- DECLARE oldDate DATE; -- DECLARE oldOrderNo VARCHAR (25) DEFAULT '', the date closest to the current condition that meets the condition Order No. if num = 8 then -- generate Order No. SELECT DATE_FORMAT (NOW (), '% Y % m % D') INTO currentDate based on the year, month, and day; -- Order No. Form: prefix + year, month, and day + flow number, for example, sh201301110802 elseif num = 14 then -- generate the order number SELECT DATE_FORMAT (NOW () based on the year, month, and day (), '% Y % m % d % H % I % s') INTO currentDate; -- Order Number Format: prefix + year month day hour minute second + flow number, for example: SH2013011010050700001, this method is not recommended for individuals to generate the flow number else -- generate the order number SELECT DATE_FORMAT (NOW (), '% Y % m % d % H % I') INTO currentDate based on the time of year, month, and day; -- order form: prefix + year, month, day + flow number, for example 0130110100900005 end if; select ifnull (orderNo, '') INTO oldOrderNo FROM test_orders where substring (orderNo, 3, num) = currentDate and substring (orderNo, 1, 2) = orderNamePre and length (orderNo) = 7 + num order by id desc limit 1; -- IF there are multiple entries, only the one closest to the current is displayed. IF oldOrderNo! = ''Then SET maxNo = CONVERT (SUBSTRING (oldOrderNo,-5), DECIMAL); -- SUBSTRING (oldOrderNo,-5 ): IF the order number is not '', the last five end if of the Order is truncated; select concat (orderNamePre, currentDate, LPAD (maxNo + 1), 5, '0 ')) INTO newOrderNo; -- LPAD (maxNo + 1), 5, '0'): if less than five digits exist, insert into test_orders (orderNo, orderName) on the left will be filled with 0) VALUES (newOrderNo, 'testno'); -- insert data to the order table -- set newOrderNo = l_orderNo; SELECT newOrderNo; END

Parameter description: orderNamePre: (input) prefix of the order number, which is set to two characters
Num: (input) based on which rules will be used to generate the serial number (generation rules include year, month, day, year, month, hour, minute, second, and year, month, day, And hour). Optional num values include 8, 12, and 14.
NewOrderNo: (output) New Order Number

Some of the descriptions in the generation have been clearly written in the stored procedure and are not repeated here.

Call the stored procedure to insert data into the table:

SET @orderNo = '';  CALL `generate_orderNo`('SH', 12, @orderNo);  SELECT @orderNo;

View the generated data (I called it many times here, so there are a lot of generated data ):

In the actual project, you only need to modify some of the generation rules, so far, the generation of the serial number is done, is it very easy?

For more information about mysql stored procedures, see my article: MySQL stored procedures