修改表為主鍵的自動增長值時，報出以下錯誤：
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;

ALTER TABLE causes auto_increment resulting key 'PRIMARY'

相關文章