Detailed explanation of the relationship between the primary key being 0 and the primary key self-selection constraint in MySQL (details)

Preface

This article mainly introduces the relationship between MySQL primary key 0 and primary key self-exclusion constraint, and shares it for your reference and learning. Let's take a look at the detailed introduction.

The design of the table without a primary key is as follows:

If there are several 0s in the id position: when setting the primary key and automatically sorting, 0 will increase from 1;

When inserting data with id = 0, the data will increase from the actual number of rows, which is different from changing from 0;

Now there is no 0 in the primary key. If you change an id to 0, 0 will not change! It will be sorted directly;

Insert another one with id=0 and see that it is still directly related to the number of rows as before!

Reset the automatic sorting again to see if this 0 will change. Cancel the automatic sorting first;

Adding self-sorting, it turned out to be just as expected, 0 was about to become 1, and the error message said that the primary key had a duplicate 1, so self-sorting was not allowed.

After the modification, it really works, 0 becomes 1,

summary

I think these are the only cases. The unsigned case should be no different. If there is anything else I haven't considered, I hope you can leave me a message and tell me what you think. I also want to know. Now I will write down my summary and thoughts:

To me, 0 is special in the database.

When using limit to view data in a specified range, the table will be sorted from 0 to the bottom. However, when inserting a row of data, it is related to the number of rows. At this time, it is sorted from 1 to the bottom. If there are 0s in the table before using the primary key self-sorting constraint, after setting the primary key self-sorting constraint, all 0s will not be sorted according to the number of rows, but directly sorted from 1 in top-down order. If the number of a primary key in the table is changed to 0, it will be directly sorted and placed in front of the positive numbers. In other words, the primary key self-sorting allows the existence of 0. Then why should the existing 0 be changed to an ascending sequence starting from 1? Even if there is only one 0 before adding the primary key self-sorting, it will become 1 after adding the primary key self-sorting.

It starts with 0, and when the primary key self-order constraint is added, 0 becomes 1, 2, 3, 4, etc.

There is no 0 at the beginning. Add a primary key self-order constraint. The newly added row with the primary key of 0 will change automatically according to the number of rows. Note that this is a newly added row, and insert is used.

There is no 0 at the beginning. If you change the number of a primary key to 0, this 0 will be directly displayed in the table after sorting.

To put it simply, after adding the primary key self-ranking constraint:

The value of the primary key: changed to 0, can exist, just for sorting.

Newly added 0 is not allowed to exist and must be changed according to the row number.

The existing 0 is not allowed to exist, and the change must start from 1 and increase incrementally.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.