修改表为主键的自动增长值时，报出以下错误：
mysql> ALTER TABLE YOON CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT ADD PRIMARY KEY (id);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ADD PRIMARY KEY (id)' at line 1

解决：
将ID值为0的那条记录或其他大于0且不重复的数据；

查询重复数据：
mysql> select id,count(*) as count from yoon group by id having count > 1;
+------+-------+
| id | count |
+------+-------+
| 1 | 2 |
+------+-------+
1 row in set (0.00 sec)

mysql> select * from yoon where id =1;
+------+------+
| id | name |
+------+------+
| 1 | AAAA |
| 1 | DDDD |
+------+------+
2 rows in set (0.00 sec)

mysql> delete from yoon where name='DDDD';
Query OK, 1 row affected (0.00 sec)

添加表为主键的自动增长值时，依旧报出以下错误：
mysql> ALTER TABLE YOON CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT ADD PRIMARY KEY (id);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ADD PRIMARY KEY (id)' at line 1

原因：
很多人都忽略了NULL值：
mysql> select * from yoon where id is null;
+------+------+
| id | name |
+------+------+
| NULL | EEEE |
+------+------+
1 row in set (0.00 sec)

mysql> delete from yoon where id is null;
Query OK, 1 row affected (0.01 sec)

mysql> alter table yoon change column id id int(11) not null auto_increment,add primary key(id);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0

or

alter table yoon modify id int(11) not null auto_increment primary key;

ITPub博客

ALTER TABLE causes auto_increment resulting key 'PRIMARY'

最新文章