MySQL 如何修改一个列为自增列?


摘要:
下文讲述MySQL中使用sql脚本一个将一个已存在列修改为自增列的方法分享,如下所示:

例:

mysql> create table maomao(keyId int);
Query OK, 0 rows affected (1.37 sec)

mysql> insert into maomao(keyId)values (20),(19),(18),(17),(16);
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select keyId from maomao;
+------+
| keyId   |
+------+
|   20 |
|   19 |
|   18 |
|   17 |
|   16 |
+------+
rows in set (0.00 sec)

mysql> ALTER TABLE maomao add autoId int;
Query OK, 0 rows affected (2.84 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from maomao;
+------+------+
| keyId   | autoId  |
+------+------+
|   20 | NULL |
|   19 | NULL |
|   18 | NULL |
|    17 | NULL |
|    16 | NULL |
+------+------+
rows in set (0.00 sec)

mysql> alter table maomao change autoId  autoId int NOT NULL AUTO_INCREMENT; ---修改autoId为自增列
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
 
mysql> select * from maomao;
+------+-----+
| keyId   | autoId |
+------+-----+
|   20 |   1 |
|    19 |   2 |
|    18 |   3 |
|    17 |   4 |
|    16 |   5 |
+------+-----+
rows in set (0.00 sec)