Generating Unique Order Numbers in High‑Concurrency Scenarios Using MySQL Stored Procedures

When multiple users create orders simultaneously in an ERP or similar system, duplicate order numbers can occur, leading to serious business issues.

To ensure each order receives a unique identifier, the solution combines a dedicated table (with the order number as a primary key) and a MySQL stored procedure that handles generation and uniqueness checks.

Step 1: Create the order‑number table – define a table where the order number column is the primary key, guaranteeing uniqueness.

Step 2: Create the stored procedure that receives a prefix (BILL_TYPE) and outputs the full order number (BILL_NOP).

Procedure code:

<code>CREATE DEFINER = CURRENT_USER PROCEDURE `getbillno`(in BILL_TYPE VARCHAR(3), out BILL_NOP varchar(25)) BEGIN</code>

Inside the procedure, the current date is obtained and a suffix counter is initialized:

<code>DECLARE currentDate varCHAR(15); DECLARE lastno INT DEFAULT 0; SELECT DATE_FORMAT(NOW(), '%Y%m%d') INTO currentDate;</code>

The latest existing order number for the given prefix and date is queried:

<code>SELECT IFNULL(BILL_NO, 'notnull') INTO BILL_NOP FROM temp_bill WHERE SUBSTRING(BILL_NO,1,3)=BILL_TYPE AND SUBSTRING(BILL_NO,4,8)=currentDate ORDER BY BILL_NO DESC LIMIT 1;</code>

If a previous number exists, its numeric suffix is incremented; otherwise the suffix starts at 001 :

<code>IF BILL_NOP !='' THEN SET lastno = CONVERT(SUBSTRING(BILL_NOP, -3), DECIMAL); SELECT CONCAT(BILL_TYPE,currentDate,LPAD((lastno + 1),3,'0')) INTO BILL_NOP; ELSE SELECT CONCAT(BILL_TYPE,currentDate,LPAD((lastno + 1),3,'0')) INTO BILL_NOP; END IF;</code>

The newly generated order number is then inserted into the table, which will fail automatically if a duplicate primary key is detected:

<code>INSERT INTO temp_bill (BILL_NO, BILL_TYPE) VALUES (BILL_NOP, BILL_TYPE);</code>

Finally, the procedure returns the unique order number:

<code>SELECT BILL_NOP;</code>

Running results : The first call (with an empty table) produces UIE20200611001 ; the second call generates UIE20200611002 , demonstrating correct sequential incrementation.

The article also provides a download link for the full mysql_getbillno.sql script and encourages readers to use the code directly.