Does the database automatically increment the primary key?

1 Should every table have an auto-incrementing primary key?

Not necessarily
Auto-incrementing primary keys can speed up row insertion, have advantages in table space utilization, and make fragmentation less obvious.

But for some content, such as queries based on uid that are very frequent and relatively concentrated, if you don’t use auto-incrementing the primary key, but use uid+id as the composite primary key, the query efficiency will go up, but the insertion and fragmentation will Increase. But if the storage type of the database is SSD, then this problem does not exist.

So, in most cases, it is correct for the table to have an auto-incrementing primary key.

2 Is the auto-incremented primary key business unique?

Not necessarily

Under the single table structure, yes.

In the case of multiple tables, it is not necessarily necessary. Certain strategies are required, such as setting different suffixes, the same interval, etc.

3 Can self-increasing primary keys affect business?

This is not recommended.

For example: a table can have an auto-incrementing primary key, which is unique within the table. When querying and updating based on id, the operation can be simplified. But generally speaking, when there is a relationship with the business and uniqueness is required, the business should maintain it independently, such as using formats or algorithms, hash generation, etc.

4 How to maintain the uniqueness of the primary key maintained by the business in the case of multiple tables?

Maintain the auto-increment key interval segment, the server takes one segment each time, and the optimistic lock is updated. This requires additional tables or strategies to maintain this field.

Based on algorithm A, fixed time prefix, such as: yyyyMMddHHmmss + table number mod value + random number, reducing the possibility of conflict by increasing the number of digits. There is a unique constraint on the table field (but sometimes this constraint is not reliable). If a duplicate field value exception is thrown during insertion, the insertion will be regenerated.

Based on algorithm B, fixed time prefix, such as: yyyyMMddHHmmss+fixed number of digits, collision auto-increment value N+random number. There is no need to increase the number of bits to reduce the possibility of conflicts. When an insertion throws a duplicate field value exception, N++, reinserts until there are no more conflicts. From now on, N is used as the infix, and N is cached on the server. This infix will continue to be used after restarting. If repeated exceptions occur, just perform the same operation with N++ again. There is no need to mention the mod value of N intentionally.

Based on infix management, that is, reporting infixes to the central server. It can be understood that the ID relationship of the server is cached somewhere, and infixes are dynamically allocated.

There are many other methods, but they have not been used before, so I won’t go into details.
Algorithm B is simple, has less communication, and has a limited number of collisions. Algorithm A, there is an infinite number of collisions, although the percentage is very, very low. But in the case of high concurrency, algorithm B will be more violent than algorithm A during initialization.

Interval segment and infix management both introduce the concept of central node, which is highly dependent but relatively reliable and is a more common implementation method in the industry.

The above is the detailed content of Does the database automatically increment the primary key?. For more information, please follow other related articles on the PHP Chinese website!