MySQL generate serial number stored procedure order number

Generating serial numbers with stored procedures is a common use, as an example of generating a serial number for an order number. (The new Day of the serial number starting from 1, such as: Today's order number is CD2013010900014, the next order number will be CD2013010900015; tomorrow's order number will start from CD2013011000001)

To generate a rule:
2-bit prefix + Month Day + 5-digit serial number
or 2-bit prefix + Month Day + 5-digit serial number
or 2-bit prefix + Month Day minute + 5-digit serial number.

Test order Form (test_orders):

 1  create TABLE ' test_orders ' ( 2  ' id ' int  (11   3  ' orderno ' varchar (25 ) not NULL DEFAULT  " ,  4  ' ordername ' char  (10 ) not NULL DEFAULT     PRIMARY KEY (' id ')  6 ) engine=in Nodb auto_increment=76  DEFAULT Charset=utf8 

Stored procedure for generating order numbers (Generate_orderno):

1CREATE definer=procedure ' Generate_orderno ' (inchOrdernamepreChar(2),inchNumint, outNeworderno varchar ( -))    2 BEGIN3DECLARE currentdate VarCHAR ( the) ;--current date, may contain time division seconds4DECLARE maxno INT DEFAULT0; --the last 5 digits of the serial number of the order number of the nearest satisfying condition, such as: SH2013011000002 's maxno=2     5--DECLARE L_orderno VarCHAR ( -) ;--the newly generated order number6--DECLARE olddate DATE;--date of the most recent order number that satisfies the condition7DECLARE Oldorderno VARCHAR ( -) DEFAULT"';--The most recent order number that satisfies the condition8           9       ifnum =8Then--generate order number based on year month dateTenSELECT Date_format (now (),'%y%m%d'into currentdate;--Order Number form: Prefix + Month Day +serial number, such as: SH2013011000002 OneElseIf num = -Then--The order number is generated according to the day of the month ASELECT Date_format (now (),'%y%m%d%h%i%s') into currentdate; --Order Number form: prefix + Month Day seconds +serial number, such as: SH2013011010050700001, personal do not recommend using this method to generate serial number -       Else--Generate order number according to date -SELECT Date_format (now (),'%y%m%d%h%i'into currentdate;--order form: prefix + Month Day +serial number, such as: SH20130110100900005 theEndif ;  -           -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 AORDER by ID DESC LIMIT1; --There are multiple lines that show only the nearest at                -IF Oldorderno! ="' Then -SET maxno = CONVERT (SUBSTRING (Oldorderno,-5), DECIMAL);--SUBSTRING (Oldorderno,-5): If the order number is not the last 5 digits of the "intercept order" - END IF;  - SELECT -CONCAT (Ordernamepre, currentdate, Lpad (Maxno +1),5,'0') into Neworderno; --Lpad ((Maxno +1),5,'0'): If less than 5 bits, the left side will be filled with 0 in                -INSERT into Test_orders (OrderNo, Ordername) VALUES (Neworderno,'Testno') ; --inserting data into the order table to--SetNeworderno =L_orderno;  + SELECT - Neworderno;  theEND

Parameter description:
Ordernamepre: (enter) The prefix of the order number, which is set to two characters
Num: (input) What rules will be generated by the serial number (the generation rules are: date, month, month, minute, day and date of three), optional num is: 8, 12, 14
Neworderno: (output) The newly generated order number

Call the stored procedure to insert data into the table:
SET @orderNo = ';
Call ' Generate_orderno ' (' SH ', @orderNo);
SELECT @orderNo;

To view the generated data:


In the actual project only need to modify some of these build rules, so far, the production of serial number is done.

Knowledge of MySQL stored procedures can be found in the following: MySQL stored procedures detailed

MySQL generate serial number stored procedure order number