MySQL uses process storage to generate a unique order number

Database tables:

/*Navicat MySQL Data transfersource server:localsource Server version:50640source host:localhost         : 3306Source database:springsecuritytarget Server type:mysqltarget server Version:50640file Encoding : 65001date:2018-08-25 22:10:57*/SETForeign_key_checks=0;-- ------------------------------Table structure for orders-- ----------------------------DROP TABLE IF EXISTS' orders ';CREATE TABLE' orders ' (' OrderNo ' )varchar( -) not NULLCOMMENT'Order Number', ' Producttypes 'varchar( One) not NULLCOMMENT'Product Type', ' OrderNumber 'int( One)DEFAULT NULLCOMMENT'Number of goods', ' UnitPrice 'decimal(Ten,2) unsigned ZerofillDEFAULT NULLCOMMENT'Unit Price', ' Totalprice 'decimal(Ten,2) unsigned ZerofillDEFAULT NULLCOMMENT'Total Price',  PRIMARY KEY(' OrderNo ') USING BTREE) ENGINE=InnoDBDEFAULTCHARSET=UTF8 Row_format=COMPACT;

To create a process store:

CREATE PROCEDURE' Generate ' (inchOrdernamepreChar(2),inchProducttypesVARCHAR( -),inchOrderNumberINT(Ten),inchUnitPricedecimal(Ten,2))  --Enter the current order name and the length of time to output the order numberBEGIN    DECLARENewordernovarchar( -); DECLAREcurrentdateVarCHAR( the) ;--current date, may contain time division seconds  DECLAREMaxnoINT DEFAULT 0;--The last 5 digits of the serial number of the order number of the nearest satisfying condition, such as: SH2013011000002 's maxno=2  DECLAREOldordernoVARCHAR( -)DEFAULT "';--The most recent order number that satisfies the condition    --SELECT Date_format (now (), '%y%m%d%h%i%s ') into currentdate;--14 digits in minutes and seconds    SELECTDate_format (now (),'%y%m%d%h%i') intocurrentdate;--A total of 14 digits at the date of the month            --SUBSTRING () string intercept function  SELECTIfnull (OrderNo,"') intoOldorderno fromordersWHERE SUBSTRING(OrderNo,3, A)=currentdate--Intercept time period     and SUBSTRING(OrderNo,1,2)=Ordernamepre--intercept the current order name  ORDER  byOrderNoDESCLIMIT1;--There are multiple lines that show only the nearest        --determine if you have an order for the current time period  IFOldorderno!= "'  Then       SETMaxno= SUBSTRING(Oldorderno,-5); END IF ; SELECTCONCAT (Ordernamepre, currentdate, Lpad (maxno+ 1),5,'0')) intoNeworderno; --Lpad ((maxno + 1), 5, ' 0 '): if less than 5 bits, the left is filled with 0  INSERT  intoOrders (Orderno,producttypes,ordernumber,unitprice,totalprice)VALUES(Neworderno,producttypes,ordernumber,unitprice,format (OrderNumber*UnitPrice,2)) ;--inserting data into the order tableEND

Call Procedure Store:

Call Generate ("TT", "slacks",2,198.60)

Finally, the data is inserted into the table successfully:

MySQL uses process storage to generate a unique order number