Mysql:表创建后添加AUTO_INCREMENT属性

mysql的自增属性用的很多，通常都是在创建的时候制定auto_increment属性。

CREATE TABLE `test` (
  `x` int(11) NOT NULL AUTO_INCREMENT,
  `d` datetime DEFAULT NULL,
  `a` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`x`);

如果在创建的表的时候忘了添加该属性，然后还插入了一定的数据。该如何处理，结果如何呢？

表结构如下

mysql> desc ttt;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| x     | int(11)     | NO   | PRI | NULL    |       |
| y     | datetime    | YES  |     | NULL    |       |
| z     | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

数据情况如下

mysql> select * from ttt;
+-----+---------------------+--------+
| x   | y                   | z      |
+-----+---------------------+--------+
|   1 | 2023-05-30 23:48:34 | 123    |
|   2 | 2023-04-26 17:58:18 | av3    |
|   3 | 2023-05-30 22:52:35 | at     |
|   4 | 2023-04-26 17:58:29 | attt   |
|   5 | 2023-05-30 22:52:55 | zxz    |
|  10 | 2023-06-07 21:10:31 | asdaqw |
|  50 | 2023-06-07 21:10:34 | asdaqw |
| 100 | 2023-06-07 21:10:38 | asdaqw |
+-----+---------------------+--------+
8 rows in set (0.00 sec)

为主键列增加auto_increment属性

mysql> alter table ttt modify column x int(11) AUTO_INCREMENT;
Query OK, 8 rows affected (0.07 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> show create table ttt;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ttt   | CREATE TABLE `ttt` (
  `x` int(11) NOT NULL AUTO_INCREMENT,
  `y` datetime DEFAULT NULL,
  `z` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`x`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

插入数据，查看

mysql> insert into ttt values(null,now(),'qqqq');
Query OK, 1 row affected (0.01 sec)

mysql> select * from ttt;
+-----+---------------------+--------+
| x   | y                   | z      |
+-----+---------------------+--------+
|   1 | 2023-05-30 23:48:34 | 123    |
|   2 | 2023-04-26 17:58:18 | av3    |
|   3 | 2023-05-30 22:52:35 | at     |
|   4 | 2023-04-26 17:58:29 | attt   |
|   5 | 2023-05-30 22:52:55 | zxz    |
|  10 | 2023-06-07 21:10:31 | asdaqw |
|  50 | 2023-06-07 21:10:34 | asdaqw |
| 100 | 2023-06-07 21:10:38 | asdaqw |
| 101 | 2023-06-07 21:17:18 | qqqq   |
+-----+---------------------+--------+
9 rows in set (0.00 sec)

mysql> 

总结：

1、表创建后加auto_increment属性需要用modify column来做，访问的行会很多，自然是数据越少的时候做越好，而且应当维持修改的列宽度一致。

2、加完这个属性，下个auto的值是当前表的该列max+1
 

学习原理，孵化思路。积累工具，下笔有道