MySQL:给原有表主键 添加 AUTO_INCREMENT

时间:2021-02-05 09:07:13

一:表结构及数据如下

 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`;