Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: tidb auto_increment 改为auto_random
|
username: Raymond
May I ask if changing the table field in TiDB from auto_increment to auto_random cannot be done directly using the alter table command and requires recreating the table?
It is not supported to use ALTER TABLE to modify the AUTO_RANDOM attribute, including adding or removing this attribute.
So you can only rebuild the table.
Recently, I studied the PCTP video. For primary keys that are not of type int or bigint, you can use SHARD_ROW_ID_BITS and PRE_SPLIT_REGIONS to distribute the insert write hotspot. For primary keys of type bigint, you can use AUTO_RANDOM to distribute the insert write hotspot.
You can change it, you need to set tidb_allow_remove_auto_inc.
The field type of auto_increment is what? If it is int, you can first alter the table to change it to bigint, then allow the removal of auto_increment through the variable tidb_allow_remove_auto_inc. Finally, you can set auto_random through alter table.
The official documentation clearly states that using ALTER TABLE to modify the AUTO_RANDOM attribute, including adding or removing this attribute, is not supported. There might be an error in the documentation here, but it is also not recommended to change auto_increment to auto_random because if there are values in the table, it is equivalent to explicit assignment, which may cause the table to prematurely exhaust the values used for automatic allocation. If the business is already online, it is recommended to scatter hotspots through SHARD_ROW_ID_BITS and PRE_SPLIT_REGIONS. If it is not yet online, it is recommended to rebuild the table and import the data.
I tried it, and it works without specifying a clustered index. Learned something new.
Indeed, after consulting the official documentation, directly changing from auto_increment to auto_random may cause anomalies. Thank you for your response.
I haven’t done much of this before, but I just tested it and it indeed works. I also learned about the tidb_allow_remove_auto_inc parameter.
It seems that we can’t fully trust the official documentation; we still need to verify it ourselves. 
This topic was automatically closed 1 minute after the last reply. No new replies are allowed.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: