MySQL:给原有表主键 添加 AUTO_INCREMENT

一：表结构及数据如下

 CREATE TABLE `tt` (
  `id` int(10) NOT NULL ,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=innodb DEFAULT CHARSET=utf8;
 
insert into tt values(0,'fyl','123qwe'),(1,'fff','12qw'),(3,'qwe','3edcw');
 
(root:CS:3308) [test]> select * from tt;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  0 | fyl      | 123qwe   | 
|  1 | fff      | 12qw     | 
|  3 | qwe      | 3edcw    | 
+----+----------+----------+

现在要为表添加 AUTO_INCREMENT 
修改主键，需要先删除主键在添加
ALTER TABLE tt DROP PRIMARY KEY;
ALTER TABLE tt CHANGE COLUMN id id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY;
报错：
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
 
解决办法：
(root:CS:3308) [test]> delete from tt where id=0;
Query OK, 1 row affected (0.00 sec)
(root:CS:3308) [test]> ALTER TABLE tt CHANGE COLUMN id id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
(root:CS:3308) [test]> select * from tt;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | fff      | 12qw     | 
|  3 | qwe      | 3edcw    | 
+----+----------+----------+
2 rows in set (0.00 sec)

原因是：
NO_AUTO_VALUE_ON_ZERO影响AUTO_INCREMENT列的处理。一般情况，你可以向该列插入NULL或0生成下一个序列号。NO_AUTO_VALUE_ON_ZERO禁用0，因此只有NULL可以生成下一个序列号。原有数据id=0，添加自增属性会把id=0变为id=1.

二：实验如下：
(root:CS:3308) [test]> select * from tt;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  0 | fyl      | 123qwe   | 
|  3 | qwe      | 3edcw    | 
+----+----------+----------+
2 rows in set (0.00 sec)
 
(root:CS:3308) [test]> ALTER TABLE tt DROP PRIMARY KEY;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
(root:CS:3308) [test]> ALTER TABLE tt CHANGE COLUMN id id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
(root:CS:3308) [test]> select * from tt;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | fyl      | 123qwe   | 
|  3 | qwe      | 3edcw    | 
+----+----------+----------+
2 rows in set (0.00 sec)
username=fyl的id值  由0变成了1

三：删除主键时，有可能报错

ERROR 1025 (HY000): Error on rename of '.\course_design\#sql-898_3' to '.\course_design\course' (errno: 150)
[root@zp1 ~]# perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed
原因：有外键引用到该主键
 
解决办法：
根据以下sql找到引用该主键的外键
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME ='course';
找到外键后，可删除外键(不建议使用外键，具体情况具体分析)
ALTER TABLE course  DROP FOREIGN KEY `member_list_ibfk_1`;
--------------------- 
作者：Two手揣兜 
来源：CSDN 
原文：https://blog.csdn.net/u013820054/article/details/52298465 
版权声明：本文为博主原创文章，转载请附上博文链接！