ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
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 |
+----+----------+------+
5 rows in set (0.00 sec)
測試透過一條命令將id設為自增主鍵,命令alter table yoon add constraint auto_increment primary 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 | |
+-------+-------------+------+-----+---------+-------+
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)
總結:主要原因alter語法使用不正確,有時候不報錯,並不代表命令正確。具體語法如下:
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28939273/viewspace-1251949/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ERROR 1062 (23000): Duplicate entry for key 'PRIMARY'Error
- 從ERROR 1062 (23000) at line Duplicate entry 'R01' for key 'PRIMARY' 能看出什麼Error
- mysql主從複製錯誤:Last_SQL_Error: Error 'Duplicate entry '327' for key 'PRIMARY'' on query. Default databa...MySqlASTError
- HOW TO SOLVE ERROR 1062 (23000) ?Error
- ERROR 1130: Host ***.***.***.*** is not allowed to connect to this MySQL serverERROR 1062 (23000):ErrorMySqlServer
- vue報錯之Duplicate keys detected: '0'. This may cause an update error.VueError
- DUMP-CX_SY_OPEN_SQL_DB-DBSQL_DUPLICATE_KEY_ERRORSQLError
- sql primary key procedureSQL
- Q:[Vue warn]: Duplicate keys detected: ‘PAYACT‘. This may cause an update error.VueError
- yum error - package is a duplicate withErrorPackage
- SQL Server Primary Key ConstraintsSQLServerAI
- mysql INSERT ... ON DUPLICATE KEY UPDATEMySql
- oracle資料庫primary key和unique key的異同Oracle資料庫
- unique index與primary key的區別Index
- ERROR in Entry module not found Error Can't resolve 'babel' in ' UseErrorBabel
- 【Mysql】Slave_SQL_Running: No:Last_Error: Error :1032/1062MySqlASTError
- MySQL_插入更新 ON DUPLICATE KEY UPDATEMySql
- 簡單分析MySQL中的primary key功能MySql
- 關於primary key和foreign key的問題處理
- MySQL insert on duplicate key update 死鎖MySql
- REPLACE與INSERT INTO ... ON DUPLICATE KEY總結
- ALTER TABLE causes auto_increment resulting key 'PRIMARY'REM
- opatch apply 時碰到 'duplicate entry: META-INF/LICENSE.txt'APP
- MySQL pt-duplicate-key-checker工具使用初探MySql
- MySQL批量Insert應用ON DUPLICATE KEY UPDATEMySql
- SAP MRKO Error - For document type RE, an entry is required in field Reference -ErrorUI
- ORA-02429: cannot drop index used for enforcement of unique/primary keyIndex
- Creating Physical Standby using RMAN Duplicate Without Shutting Primary_789370.1
- 如何證明INNODB輔助索引葉子結點KEY值相同的按照PRIMARY KEY排序索引排序
- Java中List集合轉Map集合報錯:Duplicate keyJava
- MySQL 5.5 INSERT ... ON DUPLICATE KEY UPDATE語句說明MySql
- MIGO Error:Account determination for entry LGCC WRX not possibleGoErrorGC
- Creating Physical Standby using RMAN Duplicate Without Shutting down The Primary [ID 789370.1]
- error: unknown error 22 setting key 'kernel.shmmax'ErrorHMM
- Exception_android_java.util.zip.ZipException: duplicate entry:android/support/multidex/MultiDexExceptionAndroidJavaIDE
- python -- 解決字典【KEY ERROR】PythonError
- OGG How to handle / replicate tables with no (without) primary key (PK) or unique index (UI) (UPI) [IndexUI
- vipca報錯 Error 0PCAError