MySQL high concurrency method to generate unique order number

Preface

After this blog post was published, some friends asked if there is a SQL server version . Now there is ==》 Portal

1. Scene Reproduction

In an ERP inventory management system or other systems such as 0A, if multiple people generate order numbers at the same time, it is easy for multiple people to obtain the same order number, causing irreparable losses to the company's business.

2. How to avoid non-unique order numbers in high concurrency situations

We can use stored procedures and data tables to create a table and a stored procedure. The stored procedure is responsible for generating the order number, and the table is responsible for handling the uniqueness problem.

When a stored procedure generates an order number, the order number is first written into the table, and then the order number result is displayed. There are two situations when the generated order number is written into the table. Why? Because our table has a primary key (primary key uniqueness)

• Can be written into: When there is no identical order number in the table, the generated order number is written into the table
• Cannot be written: When the same order number exists in the table, the generated order number cannot be written into the table, and the order number cannot be obtained, thus ensuring that a unique order number is generated under high concurrency

3. The process of generating unique order numbers under high concurrency

The following will explain the process of generating a unique order number using code and actual operations.

Step 1: Create a data table and set the order number field as the primary key (the key to the unique order number)

Step 2: Create a stored procedure to generate the order number

The format of the generated order number is: custom prefix + year, month, day + suffix (001, 002, 003)
1. First create a stored procedure <br /> The input is BILL_TYPE (prefix), and the output is BILL_NOP (order number)

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

2. Generate year, month, day and suffix <br /> Year, month, day is the current system time, and the initial value of the suffix is ​​0

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

3. Query the table and get the order number of the table <br /> Query the table and get the latest order number related to the prefix and custom content

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;

4. Generate order number

If the order number obtained in the previous step is not empty, the newly generated order number is +1 on the original order number.

Example: Obtained order number: UIE20200611015
The generated order number is: UIE20200611016

If the order number obtained in the previous step is empty, the suffix of the newly generated order number is 001
Example: Generated order number: UIE20200611001

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;

5. Insert the generated order number into the table <br /> If the same order number exists in the table, the insertion into the table fails. If the same order number does not exist in the table, the insertion into the table succeeds.

INSERT INTO temp_bill (BILL_NO, BILL_TYPE) VALUES (BILL_NOP,BILL_TYPE);

6. Return unique order number <br /> When the order is successfully inserted into the table, the unique order number will be returned (if the previous step is unsuccessful, this step will not be executed)

SELECT BILL_NOP;

4. Operation Results

1. First, there is no data in my table, so a prefix (I entered: UIE) + year, month, and day (
20200611)+001 (the first data, so 001)
That is: UIE20200611001

2 When I enter the second time, because there is data in the table, the suffix of the latest order number will be increased by 1
That is: UIE20200611002

5. A summary that is not a summary

Code download link: mysql_getbillno.sql

Code screenshot:

This is the end of this article about how to implement MySQL high concurrency to generate unique order numbers. For more information about MySQL high concurrency to generate unique order numbers, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!