Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: TIDB库的自增ID
[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]
Auto-increment ID, how to prevent conflicts with the fields in the auto-increment ID pool if manually inserting records with IDs into the database
Using AUTO_ID_CACHE=1 when creating a table and upgrading the TiDB version to 6.5.8 or 7.5.1 will not cause conflicts.
AUTO_RANDOM is an attribute applied to BIGINT type columns for automatic value allocation. The automatically allocated values satisfy both randomness and uniqueness.
AUTO_INCREMENT is a column attribute used for automatically filling default column values. When the INSERT statement does not specify a specific value for the AUTO_INCREMENT column, the system will automatically allocate a value for that column.
It is not recommended to manually specify values for auto-increment fields. If you are using auto-increment, why would you need to manually insert values?
The approach is incorrect. Either use auto-increment IDs or have the program distribute the IDs.
Manual insertion cannot be prevented.
Create a unique combination of a prefix + infix + suffix to prevent duplication.
I feel it should be unavoidable.
Not recommended to mix use.
When adding data using an auto-increment primary key, if a primary key conflict is encountered, catch the error and then reinsert it until it succeeds.
I think if you must mix manual and auto-increment, the best solution would be to consider using sequences.
Additionally, the table can only be modified to a non-clustered table with shard_row_id_bits, otherwise, there will definitely be hotspots.
The performance of TiDB sequences is very poor.
First, modify the value of the auto-increment ID to skip the ones you want to insert; AUTO_INCREMENT=value
I’ve always had a question: why do most applications prefer to use auto-increment IDs? Does this provide a significant performance boost in any specific aspect? I just can’t figure it out.
Do not use the built-in TiDB auto-increment feature, because many auto-increment IDs have specific uses in business, but the auto-increment IDs do not increase over time. The best approach is for developers to generate the auto-increment IDs themselves.
Use the Snowflake algorithm to generate auto-increment IDs.
It’s better to use auto-increment ID.
Why manually specify ID insertion, what is the purpose?
The unified auto-increment effect of TiDB is still good, and the performance impact is not significant. It is best to use the new version with the AUTO_ID_CACHE=1 parameter set for the table.
|
username: TiDBer_嘎嘣脆
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: