一:表结构及数据如下
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;username=fyl的id值 由0变成了1
+----+----------+----------+
| 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)
三:删除主键时,有可能报错
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`;