mysql 添加autoincrement,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`;