ERROR 1062 (23000):&nb…

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 |
+----+----------+------+
5rows in set (0.00 sec)

测试通过一条命令将id设为自增主键，命令alter table yoon add constraint auto_incrementprimary 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   |      |
+-------+-------------+------+-----+---------+-------+
3rows 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 |
+----+----------+------+
5rows in set (0.00 sec)

mysql> show index from yoon;
Emptyset (0.00 sec)

mysql> alter table yoonadd constraint auto_increment primarykey yoon(id);
QueryOK, 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     |        |              |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1row in set (0.01 sec)

mysql> insert intoyoon(name,user) values('z','HHH'); 
QueryOK, 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 |
+----+----------+------+
6rows 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;
QueryOK, 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     |        |              |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1row 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 |
+----+----------+------+
5rows in set (0.00 sec)

mysql> altertable yoon modify column id intauto_increment;
QueryOK, 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  |
+----+----------+------+
6rows in set (0.00 sec)

总结：主要原因alter语法使用不正确，有时候不报错，并不代表命令正确。具体语法如下：
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html