Modify AUTO_INCREMENT Value in TiDB

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

Original topic: tidb修改AUTO_INCREMENT 值

| username: zhanggame1

[Test Environment for TiDB] Testing/
[TiDB Version] v7.1
[Encountered Problem: Problem Description and Impact]
In MySQL, you can modify the current auto-increment value using the statement ALTER TABLE table_name AUTO_INCREMENT = new_value;
I tested it in TiDB and it seems it cannot be modified. Is there any explanation for this?

| username: TiDBer_oHSwKxOH | Original post link

Defined on columns of type INTEGER, FLOAT, or DOUBLE.
Not supported to specify on the same column with the column’s default value DEFAULT.
Not supported to add AUTO_INCREMENT attribute using ALTER TABLE.
Supported to remove AUTO_INCREMENT attribute using ALTER TABLE. However, starting from TiDB versions 2.1.18 and 3.0.4, TiDB controls whether to allow removing the AUTO_INCREMENT attribute from a column using ALTER TABLE MODIFY or ALTER TABLE CHANGE through the session variable @@tidb_allow_remove_auto_inc, and by default, it is not allowed to remove.
ALTER TABLE requires the FORCE option to set AUTO_INCREMENT to a smaller value.

| username: TiDBer_oHSwKxOH | Original post link

Mandatory keywords need to be strengthened.

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

You can modify it, but you can only increase it, not decrease it. You can first use SHOW CREATE TABLE t; to see what the AUTO_INCREMENT is, then use ALTER TABLE t AUTO_INCREMENT = 999999; to increase it, and then check again.

| username: zhanggame1 | Original post link

According to the previous statement, adding force can reduce it, ALTER TABLE t FORCE AUTO_INCREMENT=100;

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

Can it still be forcibly reduced? Wouldn’t that cause data conflicts?

| username: zhanggame1 | Original post link

Of course, it was only changed when the data was deleted.

| username: system | Original post link

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