HOW TO SOLVE ERROR 1062 (23000) ?
CAUSE
a)ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
b)ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
SOLUTION
a)ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment;
b)Primary key column values set 1 starting
1.create case table and insert into data
mysql> CREATE TABLE animals (
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO animals (name) VALUES
-> ('dog'),('cat'),('penguin'),
-> ('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM animals;
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)
1.1 show create table statement
mysql> show create table animals ;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| animals | CREATE TABLE `animals` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`name` char(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1.2 show next Auto_increment value
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
Name: animals
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 6
Avg_row_length: 2730
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 7
Create_time: 2016-03-24 11:19:03
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
2.DDL and change id column,table definition is changed.
mysql> ALTER TABLE `animals` CHANGE `id` `id` INT;
Query OK, 6 rows affected (0.09 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> show create table animals ;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| animals | CREATE TABLE `animals` (
`id` int(11) NOT NULL DEFAULT '0',
`name` char(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.1 DDL alter and Auto_increment is change to NULL
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
Name: animals
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 6
Avg_row_length: 2730
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2016-03-24 11:23:20
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES('doudou01');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM animals;
+----+----------+
| id | name |
+----+----------+
| 0 | doudou01 |
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+----------+
7 rows in set (0.00 sec)
3.ERROR 1062 (23000) and solve problem using 'UNSIGNED'
mysql> INSERT INTO animals (name) VALUES('doudou02');
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
mysql> ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment;
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
## id=0 is not accord with auto_increment resequencing,and auto_increment default 1 .
mysql> select LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM animals;
+----+----------+
| id | name |
+----+----------+
| 0 | doudou01 |
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+----------+
7 rows in set (0.00 sec)
mysql> INSERT INTO animals (id,name) VALUES(7,'doudou01');
Query OK, 1 row affected (0.01 sec)
3.1 set id column is accord with auto_increment resequencing
mysql> delete from animals where id=0;
Query OK, 1 row affected (0.01 sec)
3.2 solve 'ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY''
mysql> ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment;
Query OK, 7 rows affected (0.16 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM animals;
+----+----------+
| id | name |
+----+----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou01 |
+----+----------+
7 rows in set (0.01 sec)
mysql> show create table animals ;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| animals | CREATE TABLE `animals` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`name` char(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
Name: animals
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 7
Avg_row_length: 2340
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 8
Create_time: 2016-03-24 11:29:52
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> select LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES('doudou02');
Query OK, 1 row affected (0.00 sec)
mysql> select LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 8 |
+------------------+
1 row in set (0.00 sec)
mysql> select * from animals ;
+----+----------+
| id | name |
+----+----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou01 |
| 8 | doudou02 |
+----+----------+
8 rows in set (0.00 sec)
4.Problem is solved.
########################################################################################
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!【QQ交流群:53993419】
QQ:14040928 E-mail:dbadoudou@163.com
本文链接: http://blog.itpub.net/26442936/viewspace-2063150/
########################################################################################
--------------------------------------------------------------------------------------------------------------------------------------
5.case scripts
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
show create table animals ;
show table status like 'animals' \G
ALTER TABLE `animals` CHANGE `id` `id` INT;
show create table animals ;
show table status like 'animals' \G
INSERT INTO animals (name) VALUES('doudou01');
INSERT INTO animals (name) VALUES('doudou02');
ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment;
select LAST_INSERT_ID() ;
SELECT * FROM animals;
show create table animals ;
show table status like 'animals' \G
INSERT INTO animals (id,name) VALUES(7,'doudou01');
delete from animals where id=0;
ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment;
show create table animals ;
show table status like 'animals' \G
INSERT INTO animals (name) VALUES('doudou02');
select LAST_INSERT_ID() ;
INSERT INTO animals (name) VALUES('doudou02');
SELECT * FROM animals;
--------------------------------------------------------------------------------------------------------------------------------------------------
CAUSE
a)ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
b)ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
SOLUTION
a)ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment;
b)Primary key column values set 1 starting
1.create case table and insert into data
mysql> CREATE TABLE animals (
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO animals (name) VALUES
-> ('dog'),('cat'),('penguin'),
-> ('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM animals;
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)
1.1 show create table statement
mysql> show create table animals ;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| animals | CREATE TABLE `animals` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`name` char(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1.2 show next Auto_increment value
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
Name: animals
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 6
Avg_row_length: 2730
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 7
Create_time: 2016-03-24 11:19:03
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
2.DDL and change id column,table definition is changed.
mysql> ALTER TABLE `animals` CHANGE `id` `id` INT;
Query OK, 6 rows affected (0.09 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> show create table animals ;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| animals | CREATE TABLE `animals` (
`id` int(11) NOT NULL DEFAULT '0',
`name` char(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.1 DDL alter and Auto_increment is change to NULL
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
Name: animals
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 6
Avg_row_length: 2730
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2016-03-24 11:23:20
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES('doudou01');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM animals;
+----+----------+
| id | name |
+----+----------+
| 0 | doudou01 |
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+----------+
7 rows in set (0.00 sec)
3.ERROR 1062 (23000) and solve problem using 'UNSIGNED'
mysql> INSERT INTO animals (name) VALUES('doudou02');
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
mysql> ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment;
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
## id=0 is not accord with auto_increment resequencing,and auto_increment default 1 .
mysql> select LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM animals;
+----+----------+
| id | name |
+----+----------+
| 0 | doudou01 |
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+----------+
7 rows in set (0.00 sec)
mysql> INSERT INTO animals (id,name) VALUES(7,'doudou01');
Query OK, 1 row affected (0.01 sec)
3.1 set id column is accord with auto_increment resequencing
mysql> delete from animals where id=0;
Query OK, 1 row affected (0.01 sec)
3.2 solve 'ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY''
mysql> ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment;
Query OK, 7 rows affected (0.16 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM animals;
+----+----------+
| id | name |
+----+----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou01 |
+----+----------+
7 rows in set (0.01 sec)
mysql> show create table animals ;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| animals | CREATE TABLE `animals` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`name` char(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
Name: animals
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 7
Avg_row_length: 2340
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 8
Create_time: 2016-03-24 11:29:52
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> select LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES('doudou02');
Query OK, 1 row affected (0.00 sec)
mysql> select LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 8 |
+------------------+
1 row in set (0.00 sec)
mysql> select * from animals ;
+----+----------+
| id | name |
+----+----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou01 |
| 8 | doudou02 |
+----+----------+
8 rows in set (0.00 sec)
4.Problem is solved.
########################################################################################
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!【QQ交流群:53993419】
QQ:14040928 E-mail:dbadoudou@163.com
本文链接: http://blog.itpub.net/26442936/viewspace-2063150/
########################################################################################
--------------------------------------------------------------------------------------------------------------------------------------
5.case scripts
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
show create table animals ;
show table status like 'animals' \G
ALTER TABLE `animals` CHANGE `id` `id` INT;
show create table animals ;
show table status like 'animals' \G
INSERT INTO animals (name) VALUES('doudou01');
INSERT INTO animals (name) VALUES('doudou02');
ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment;
select LAST_INSERT_ID() ;
SELECT * FROM animals;
show create table animals ;
show table status like 'animals' \G
INSERT INTO animals (id,name) VALUES(7,'doudou01');
delete from animals where id=0;
ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment;
show create table animals ;
show table status like 'animals' \G
INSERT INTO animals (name) VALUES('doudou02');
select LAST_INSERT_ID() ;
INSERT INTO animals (name) VALUES('doudou02');
SELECT * FROM animals;
--------------------------------------------------------------------------------------------------------------------------------------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26442936/viewspace-2063150/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26442936/viewspace-2063150/