OS: centos 6.3
DB: 5.5.14测试创建yoon测试表，没有主键，没有索引，基础数据内容如下：mysql> select * from yoon;
+----+----------+------+| id | name | user |+----+----------+------+| 1 | \""##!aa | NULL || 2 | z2 | NULL || 3 | z3 | NULL || 4 | z4 | NULL || 5 | z5 | NULL |+----+----------+------+5 rows in set (0.00 sec) 测试通过一条命令将id设为自增主键，命令alter table yoon add constraint auto_increment primary key yoon(id);创建成功，但是插入2条数据发现报错，场景如下：mysql> desc yoon;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | NO | | 0 | || name | varchar(20) | YES | | NULL | || user | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+3 rows in set (0.01 sec) mysql> select * from yoon;+----+----------+------+| id | name | user |+----+----------+------+| 1 | \""##!aa | NULL || 2 | z2 | NULL || 3 | z3 | NULL || 4 | z4 | NULL || 5 | z5 | NULL |+----+----------+------+5 rows in set (0.00 sec) mysql> show index from yoon;Empty set (0.00 sec) mysql> alter table yoon add constraint auto_increment primary key yoon(id);Query OK, 0 rows affected (0.29 sec)Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from yoon;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| yoon | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.01 sec) mysql> insert into yoon(name,user) values ('z','HHH'); Query OK, 1 row affected (0.02 sec) mysql> select * from yoon;+----+----------+------+| id | name | user |+----+----------+------+| 0 | z | HHH || 1 | \""##!aa | NULL || 2 | z2 | NULL || 3 | z3 | NULL || 4 | z4 | NULL || 5 | z5 | NULL |+----+----------+------+6 rows in set (0.01 sec) mysql> insert into yoon(name,user) values ('z6','HHH'); ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY' mysql> delete from yoon where id=0;Query OK, 1 row affected (0.01 sec) mysql> show index from yoon;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| yoon | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)mysql> select * from yoon;
+----+----------+------+| id | name | user |+----+----------+------+| 1 | \""##!aa | NULL || 2 | z2 | NULL || 3 | z3 | NULL || 4 | z4 | NULL || 5 | z5 | NULL |+----+----------+------+5 rows in set (0.00 sec) mysql> alter table yoon modify column id int auto_increment;Query OK, 5 rows affected (0.03 sec)Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from yoon;+----+----------+------+| id | name | user |+----+----------+------+| 1 | \""##!aa | NULL || 2 | z2 | NULL || 3 | z3 | NULL || 4 | z4 | NULL || 5 | z5 | NULL || 6 | z6 | HHH |+----+----------+------+6 rows in set (0.00 sec)总结：主要原因alter语法使用不正确，有时候不报错，并不代表命令正确。具体语法如下：