Shulou(Shulou.com)06/01 Report--

This article mainly introduces what is the method of generating a unique order number with high concurrency in MySQL, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

I. scene reproduction

In an erp purchase, sale and storage system or other systems such as 0A, if multiple people generate the order number at the same time, it is easy for multiple people to get the same order number, which will cause irreparable losses to the company's business.

Second, how to avoid high concurrency when the order number is not unique

We can use the stored procedure and the data table to create a table and a stored procedure, which is responsible for generating the order number and the table is responsible for dealing with the uniqueness problem.

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 write the generated order number into the table. Why? Because our table has a primary key (primary key uniqueness)

Can be written into: when there is no same order number in the table, that is, the generated order number cannot be written in the table: when there is the same order number in the table, that is, the generated order number cannot be written into the table, the order number cannot be obtained. so as to ensure the process of generating a unique order number in high concurrency and generating a unique order number in high concurrency.

The following will explain the process of generating a unique order number with code and practice

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

Step 2: create a stored procedure that generates a subscription number

The generated order number format is: custom prefix + year, month and day + suffix (001, 002, 003)

1. First create a stored procedure

Input as BILL_TYPE (prefix) and output as BILL_NOP (order number)

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

two。 Generate year, month, day and suffix

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 form to get the order number of the form

Query the table to get the latest order number with the prefix related to 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: the order number obtained: UIE20200611015

The generated order number is: UIE20200611016

If the order number obtained in the previous step is empty, the newly generated order number is suffixed with 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

Insert table fails if the same order number exists in the table

Insert the table successfully if the same order number does not exist in the table

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

6. Returns the unique order number

When the order is successfully inserted into the table, the unique order number can be returned (if the previous step is not successful, this step will not be run)

SELECT BILL_NOP; IV. Running result

1. First of all, there is no data in my table, so I will generate a prefix (I typed: UIE) + year, month and day (

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

Namely: UIE20200611001

2 when I enter it for the second time, because there is data in the table, I will add 1 according to the suffix of the latest order number

Namely: UIE20200611002

5. Not a summary

The self-account of a humble chicken:

I hope this article can help you all.

If you have enough points, just download them directly. If you have no points, you can click on the picture. Anyway, the amount of code is not large.

If you are a student and have no points, you can talk to me in private and give you the source code for free.

Code download link: mysql_getbillno.sql

Code screenshot:

Thank you for reading this article carefully. I hope the editor will share what is the only way to generate the order number with high concurrency in MySQL. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.