Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 如何高效修改大表id列？
|
username: 江湖故人
Is there a good way to modify the originally unordered id column into incremental numbers for a 5kw test table?
Export the dumpling backup, manually modify the table structure to be auto-increment, and then import it into TiDB using lighting. The table is essentially rebuilt.
If the data rows are not long for 5000KW, directly create a new table, use auto-increment for the ID, and then insert into select from the old table. The tidb_mem_quota_query can be adjusted to 10G at the session level, which should be sufficient.
First delete the ID column, then try adding the ID column with auto-increment.
Is it changing the id column to an auto-increment column, or changing the existing id column values to incrementing numbers?
If it is a clustered table, the auto-increment ID must be the primary key column, and the primary key column is not allowed to be modified.
You can try the following approach:
Delete the id column from the original table.
Use dumpling to export the entire table. Since the id column is removed, the exported data will not contain the id column.
Modify the schema file in the exported files to add an auto-increment id column to the new table. Note that you should use MySQL compatibility mode here; otherwise, the obtained id might be unique but not strictly incremental.
Use lightning to import the data.
This is fine because the id column is gone, so all data does not contain the id column.
Create a table with an auto-incrementing ID, and the data is reversed? 5 million is not much.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: