Will modifying the primary key type using ALTER in MySQL reset the auto-increment value to 0?

Question

version=5.7.32-log

i use mysql statement like these to test.

    CREATE TABLE test_table (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255)
    );

    INSERT
        INTO
        test_table (name)
    VALUES ('Alice'),
    ('Bob');

table structure

table datas

and then execute

    ALTER TABLE test_table MODIFY id BIGINT(30);

changed table strucure

you can see auto increment become 0.

I try to search this in mysql manual and google,there do not exist related answers.

What's more,when i test this in 8.0.39-0ubuntu0.22.04.1,auto increment do not changed.

(wrong test,use 'SHOW TABLE STATUS LIKE 'test_table';' can't see the changed, we can use 'show create test_table;')

I want to know if this is a configuration item, a feature, or a bug.

Answer 1

When you did ALTER TABLE, you omitted the AUTO_INCREMENT option.

This makes the column just a BIGINT, with no AUTO_INCREMENT behavior.

In this case, the value of the next auto-increment is not relevant, and the metadata shows 0.

When you change the data type, you should make sure you include the AUTO_INCREMENT option:

ALTER TABLE test_table MODIFY id BIGINT AUTO_INCREMENT;

SHOW CREATE TABLE test_table;

Output:

CREATE TABLE `test_table` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
                              ^^^

P.S.: This is not related to your question, but I recommend just use INT or BIGINT. The "length" argument for integer types doesn't have any effect. In MySQL 8.0, adding a length to an integer is deprecated.