在线删除或添加业务表的主键字段“AUTO_INCREMENT“属性

MySQL数据库版本：Server version: 8.0.25 MySQL Community Server - GPL

情景需要：去掉业务表主键字段的"AUTO_INCREMENT"属性。

下面是测试过程：

mysql> desc usertb1;
+-------------+-----------------+------+-----+---------+----------------+
| Field       | Type            | Null | Key | Default | Extra          |
+-------------+-----------------+------+-----+---------+----------------+
| id          | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| uname       | varchar(20)     | NO   | PRI | NULL    |                |
| ucreatetime | datetime        | YES  |     | NULL    |                |
| age         | int             | YES  |     | NULL    |                |
+-------------+-----------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> show index from usertb1;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| usertb1 |          0 | PRIMARY  |            1 | id          | A         |     9004787 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| usertb1 |          0 | PRIMARY  |            2 | uname       | A         |     9004787 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| usertb1 |          0 | id       |            1 | id          | A         |     9020102 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.04 sec)

mysql> alter table usertb1 modify id bigint not null;
Query OK, 10000000 rows affected (2 min 3.65 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> desc usertb1;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | bigint      | NO   | PRI | NULL    |       |
| uname       | varchar(20) | NO   | PRI | NULL    |       |
| ucreatetime | datetime    | YES  |     | NULL    |       |
| age         | int         | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

mysql>  show index from usertb1;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| usertb1 |          0 | PRIMARY  |            1 | id          | A         |     9004787 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| usertb1 |          0 | PRIMARY  |            2 | uname       | A         |     9004787 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| usertb1 |          0 | id       |            1 | id          | A         |     9020102 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

mysql> alter table usertb1 modify id bigint not null AUTO_INCREMENT;
Query OK, 10000000 rows affected (2 min 11.51 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> desc usertb1;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | bigint      | NO   | PRI | NULL    | auto_increment |
| uname       | varchar(20) | NO   | PRI | NULL    |                |
| ucreatetime | datetime    | YES  |     | NULL    |                |
| age         | int         | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

mysql> show index from usertb1;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| usertb1 |          0 | PRIMARY  |            1 | id          | A         |     9004787 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| usertb1 |          0 | PRIMARY  |            2 | uname       | A         |     9004787 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| usertb1 |          0 | id       |            1 | id          | A         |     9020102 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

结论：mysql8.0数据库可以在线添加或删除主键字段的"AUTO_INCREMENT"属性。

          需要注意的是：执行alter table ...语句时，不要对该表执行ddl、dml操作。