Solve the problem of mysql's int primary key self-increment

Introduction

When we use the MySQL database, we are accustomed to using int as the primary key and setting it to auto-increment. This can ensure uniqueness and is convenient to use. However, the length of the int type is limited. What if the length is exceeded?

Exposing the problem

Let's create a test table first. The creation statement is as follows:

CREATE TABLE test1 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
)

Then we insert two pieces of data:

INSERT INTO test1 VALUES(NULL,'Calf');
INSERT INTO test1 VALUES(NULL,'大牛');

The query table displays normally:

The range of the signed int type is 231 -1 = 2147483647. We directly insert a data with id 2147483647 as follows:

INSERT INTO test1 VALUES(2147483647 ,'Xiaohua')

The results show normal:

At this point the auto-increment ID has reached the upper limit of the int type. If I insert more data, an error will be reported:

INSERT INTO test1 VALUES(NULL,'cow');

At this time, the primary key can no longer be incremented, and the inserted id is still 2147483647, which violates the unique condition of the primary key, so an error is reported.

Solving the problem

(1) Use a larger data type, bigint

The range of bigint is 263-1, so-called exponential explosion, the size at this time reaches the terrible magnitude of 9,223,372,036,854,775,807. In simple terms, if you use bigint to store 1 million pieces of data a day, it will take 20 billion years for it to explode. So in the current scenario, there is almost no need to worry about bigint auto-incrementing.

We change the data type to bigint, as shown in the figure

Then execute the insert statement:

INSERT INTO test1 VALUES(NULL,'cow');

Can insert normally again:

(2) Use UUID as the primary key

We all know that UUID will be calculated based on a series of parameters such as current system performance and timestamp to obtain a unique string in the world, and MySQL provides a method to generate UUID. Using it as a primary key can ensure the uniqueness of the data.

The following code can be used to generate a 32-bit UUID:

-- Generate 32-bit UUID
SELECT REPLACE(UUID(),'-','') AS UUID;

Then let's create a test table:

CREATE TABLE test2(
  id VARCHAR(50) PRIMARY KEY,
  NAME VARCHAR(20) NOT NULL
)

Insert a piece of data:

-- Insert UUID
INSERT INTO test2 VALUES(REPLACE(UUID(),'-',''),'老王');

But it seems a bit too troublesome to write the UUID function manually every time you write an insert statement. We can write a trigger to let the trigger automatically set the ID for us:

-- Create trigger DELIMITER $$
CREATE
TRIGGER auto_id -- name BEFORE INSERT -- trigger time ON test2 FOR EACH ROW -- acts on the test2 table and takes effect on each row of data BEGIN
IF new.id = '' THEN -- set UUID if id is an empty string
  SET new.id = REPLACE(UUID(),'-','');
END IF;
END$$

Insert a piece of data:

-- Insert a data INSERT INTO test2 VALUES('','Xiao Wang');

The result can be added normally

Summarize

(1) The int and bigInt types are faster to add, delete, modify and query than UUID and save more space.

(2) Using UUID is more convenient.

Why use auto-increment int as primary key?

I believe everyone knows that you should use unsigned auto-incrementing int as the data type of the primary key, but do you know why you should use auto-incrementing int instead of varchar, text, varchar and other types?

Everyone can also point out some advantages: transparent to upper-level business, no need to explicitly specify when inserting data; simple data types, easier to store and maintain table structure

In fact, there are many benefits to using auto-increment int as the primary key. Let's learn about it today and strongly recommend that you use auto-increment int as the primary key in actual development.

advantage:

1. Int uses less storage space than varchar, char, and text, and has a simple data type, which can save CPU overhead and is easier to maintain the table structure.

2. By default, a primary key index will be created on the primary key. Using an integer as the primary key can load more indexes into memory and improve query performance

3. For the InnoDB storage engine, each secondary index will use the primary key as the suffix of the index value. Using an auto-incrementing primary key can reduce the length (size) of the index and facilitate loading more index data into memory.

4. It can make index data more compact. When inserting, deleting, and updating data, it can move and split pages of index data as little as possible, reduce the generation of fragments (you can rebuild the table through optimize table), and reduce maintenance costs.

5. When inserting data, it can ensure that logically adjacent elements are also physically adjacent, which is convenient for range search

Of course, using an auto-incrementing int as the primary key is not without its own disadvantages. It may also cause lock contention problems in high concurrency situations.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.