How to Efficiently Modify the ID Column of a Large Table?

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?

| username: WalterWj | Original post link

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.

| username: 江湖故人 | Original post link

Unable to manually clear the data in the id column.

| username: zhanggame1 | Original post link

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.

| username: 小龙虾爱大龙虾 | Original post link

Deleting the ID will clear it out :joy_cat:

| username: dba远航 | Original post link

First delete the ID column, then try adding the ID column with auto-increment.

| username: Kongdom | Original post link

Is it changing the id column to an auto-increment column, or changing the existing id column values to incrementing numbers?

| username: zhanggame1 | Original post link

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.

| username: Jellybean | Original post link

You can try the following approach:

  1. Delete the id column from the original table.

  2. Use dumpling to export the entire table. Since the id column is removed, the exported data will not contain the id column.

    • There is an issue here: currently, deleting primary key columns or columns related to composite indexes is not supported. You need to delete the index first. If it is a clustered index, deletion is also not supported. If deletion is possible, proceed to the next steps.
    • If not, you may need to recreate the table and import the data.
  3. 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.

  4. Use lightning to import the data.

| username: 哈喽沃德 | Original post link

ETL can be temporarily handled, 50,000 records are not much.

| username: 双开门变频冰箱 | Original post link

Rebuild it.

| username: TiDBer_rvITcue9 | Original post link

Rebuild it.

| username: Kongdom | Original post link

This compatibility mode is really awesome~

| username: chris-zhang | Original post link

This is fine because the id column is gone, so all data does not contain the id column.

| username: 呢莫不爱吃鱼 | Original post link

Rebuild it.

| username: Hacker_QGgM2nks | Original post link

Create a table with an auto-incrementing ID, and the data is reversed? 5 million is not much.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.