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

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:

insert image description here

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:

insert image description here

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');

insert image description here

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

insert image description here

Then execute the insert statement:

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

Can insert normally again:

insert image description here

(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;

insert image description here

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(),'-',''),'老王');

insert image description here

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

insert image description here

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.

You may also be interested in:
  • Solution to running out of MySQL's auto-increment ID (primary key)
  • The pitfall of MySQL numeric type auto-increment
  • Troubleshooting and solutions for MySQL auto-increment ID oversize problem
  • How to modify the starting value of mysql auto-increment ID
  • Summary of some small issues about MySQL auto-increment ID

<<:  How to display div on object without being blocked by object animation

>>:  How to set up Spring Boot using Docker layered packaging

Recommend

Docker uses root to enter the container

First run the docker container Run the command as...

Using CSS to implement image frame animation and curve motion

The basic principle of all animations is to displ...

Summary of Node.js service Docker container application practice

This article will not explain the use and install...

Common attacks on web front-ends and ways to prevent them

The security issues encountered in website front-...

Example method of viewing IP in Linux

Knowing the IP address of a device is important w...

How to extract string elements from non-fixed positions in MySQL

Preface Note: The test database version is MySQL ...

Node.js returns different data according to different request paths.

Table of contents 1. Learn to return different da...

How to use map to allow multiple domain names to cross domains in Nginx

Common Nginx configuration allows cross-domain se...

Axios secondary encapsulation example Demo in the project

1. Why do packaging? Facilitates overall code cal...

How to use Navicat to export and import mysql database

MySql is a data source we use frequently. It is v...

How to import txt into mysql in Linux

Preface When I was writing a small project yester...

How to use CocosCreator for sound processing in game development

Table of contents 1. Basics of audio playback in ...

How to completely uninstall Docker Toolbox

Docker Toolbox is a solution for installing Docke...