The following error was reported when modifying the auto-grow value of the table's primary key:
mysql> ALTER TABLE YOON change COLUMN ID ID INT (one) not NULL auto_increment ADD PRIMARY KEY (ID);
Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' ADD-PRIMARY KEY (ID) ' at Line 1
Solve:
The record with ID value 0 or other data that is greater than 0 and is not duplicated;
To query for duplicate data:
Mysql> Select Id,count (*) as Count from Yoon the group by ID has 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)
The following error is still reported when adding the auto-grow value of the table as the primary key:
mysql> ALTER TABLE YOON change COLUMN ID ID INT (one) not NULL auto_increment ADD PRIMARY KEY (ID);
Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' ADD-PRIMARY KEY (ID) ' at Line 1
Reason:
Many people have ignored the null value:
Mysql> select * FROM Yoon where ID is null;
+------+------+
| ID | name |
+------+------+
| NULL | eeee |
+------+------+
1 row in Set (0.00 sec)
Mysql> Delete from Yoon the where ID is null;
Query OK, 1 row affected (0.01 sec)
Mysql> ALTER TABLE Yoon change column ID ID int (one) 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 (one) NOT NULL auto_increment primary key;
ALTER TABLE causes auto_increment resulting key ' PRIMARY '