Is there a way to change the id auto-increment attribute to auto_random?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 请问有办法修改id自增属性为 auto_random吗?

| username: TiDBer_NIpp5a2i

[Test Environment for TiDB]
[TiDB Version] v7.5
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
Want to change the auto-increment attribute to auto_random
[Resource Configuration]

| username: TiDBer_jYQINSnf | Original post link

You can’t directly alter it; you have to rebuild the table.

| username: zhanggame1 | Original post link

The primary key in TiDB cannot be changed, it needs to be rebuilt.

| username: Jiawei | Original post link

Rebuild

| username: forever | Original post link

First, set the parameter tidb_allow_remove_auto_inc=1;
Then make the change, provided that the table is a clustered table, as non-clustered tables cannot be modified.
It is recommended to create a test table first for testing.

| username: Jiawei | Original post link

What the person above said is really worth trying.

| username: tidb菜鸟一只 | Original post link

I haven’t tried directly modifying it. I suggest recreating the table and then inserting the original data into it…

| username: 这里介绍不了我 | Original post link

It is best to rebuild the table.

| username: TIDB-Learner | Original post link

Rebuilding the table is simple and safe.

| username: forever | Original post link

How large is the data volume? If the data volume is small, rebuilding the table is feasible. If the data volume is large, rebuilding the table is a major project. I tested it, and the generated ID after modification meets expectations.

| username: Kongdom | Original post link

According to the prompt, it should be possible to achieve this by modifying the system variables. You can try setting the variables.

| username: yiduoyunQ | Original post link

Rebuild table

| username: TiDBer_jYQINSnf | Original post link

This parameter allows the removal of auto_increment. However, in addition to removing auto_increment, it also requires adding auto_random, which TiDB does not support, so setting it is useless.

| username: forever | Original post link

Sure.

| username: YuchongXU | Original post link

You can only rebuild it.

| username: Kongdom | Original post link

:+1: :+1: :+1: Pragmatist~

| username: zhaokede | Original post link

Rebuild the table. Remember, the documentation says that modifying to auto_random is not supported.

| username: Soysauce520 | Original post link

Recreate the table, split it according to _tidb_rowid, and reinsert the data.

| username: TiDBer_jYQINSnf | Original post link

The image is not visible. Please provide the text you need translated.

| username: forever | Original post link

Take a look at the sentence below it.