Os:centos 6.3
db:5.5.14
Test Create Yoon Test table, no primary key, no index, the underlying data content is as follows:
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)
The
Test uses a command to set the ID to the self-increment primary key, the command ALTER TABLE YOON Add constraint auto_increment primary key Yoon (ID), the creation succeeds, but inserts 2 data to find the error, the scene is as follows:
mysql> Desc Yoon;
+-------+-------------+------+-----+---------+-------+
| Field | type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int (one) | 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)
Summary: The main reason that alter syntax is used incorrectly, sometimes without error, does not mean that the command is correct. The specific syntax is as follows:
Http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
ERROR 1062 (23000): Duplicate entry ' 0 ' for key ' PRIMARY '