MySQL high concurrency generates unique order numbers

##Preface

After this blog post was published, a friend asked if there was a

SQL server Version , now has "SQL server high concurrent generation of unique order number"

Recommended: "

mysql tutorial"

一, Scene reproduction

In an ERP purchase-sales-inventory system or other systems such as 0A, if multiple people perform the operation of generating order numbers at the same time, it is easy for multiple people to obtain the same order number, which will affect the company's business. Causing irreparable losses

2. How to avoid high concurrency situations where order numbers are not unique

We can use stored procedures and data tables to create a table and create a stored procedure. The stored procedure is responsible Generate order numbers, and the table is responsible for handling uniqueness issues

When the stored procedure generates an order number, first write the order number into the table, then display the result of the order number, and generate When the order number is written into the table, two situations will occur. Why? Because our table has a primary key (primary key uniqueness)

can be written: When there is no same 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, that is, the generated order number cannot be written to the table, and the order number cannot be obtained, thereby ensuring that a unique order number is generated under high concurrency

3. The process of generating a unique order number under high concurrency

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

The first step: Create a data table and set The order number field is 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: Customize the prefix year, month and day suffix (001, 002, 003)

1. First create a stored procedure 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 the year, month, day and suffix The year, month and 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 obtain The order number of the table Query the table and get the latest order number with the prefix related to the 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 the order number

• If the order number obtained in the previous step is not empty, the newly generated order number will be based on the original order number 1
  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 If the same
order number exists in the table, the insertion into the table will fail If in the table If the same
order number does not exist, the insertion into the table is successful

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

6. Return the unique order number

When the order is inserted into the table successfully, the unique order number can be returned (if the above If one step is unsuccessful, this step will not be run)

SELECT   BILL_NOP;

4. Running results

1. First of all, there is no data in my table, and the prefix will be generated (I entered: UIE) year and month Day (

20200611) 001 (the first data, so it is 001)

is:
UIE20200611001
2 When I enter it for the second time, because there is data in the table , the suffix of the latest order number will be 1

, that is:

UIE20200611002

# 5. Summary that is not a summary

The self-report of a humble rookie:

I hope this article can help everyoneIf you have enough points, just download it directly. If you don’t have points, just type on the picture. The amount of code is not big anywayIf you are a student and don’t have points, you can chat with me privately and I will send you the source code for free

Code download link: mysql_getbillno.sql
Code screenshot:

The above is the detailed content of MySQL high concurrency generates unique order numbers. For more information, please follow other related articles on the PHP Chinese website!