ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

最新推荐文章于 2023-09-05 11:32:44 发布

渔夫数据库笔记

最新推荐文章于 2023-09-05 11:32:44 发布

阅读量8.5k

点赞数 1

分类专栏： Mysql 文章标签： ERROR 1062 23000 Dup

本文链接：https://blog.csdn.net/shaochenshuo/article/details/49639221

版权

Mysql 专栏收录该内容

220 篇文章 9 订阅

订阅专栏

1.版本

1)操作系统

cat /etc/issue
CentOS release 6.6 (Final)
Kernel \r on an \m

cat /proc/version
Linux version 2.6.32-504.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC) ) #1 SMP Wed Oct 15 04:27:16 UTC 2014

2) mysql数据库版本

研发测试环境5.1.73

商用环境5.6.26

2.问题

研发的兄弟今天过来跟我反映说，在5.1.73的测试环境上下面的语句可以反复执行

##其中meclass_id 列是自增列

但是放到商用环境(5.6.26)上执行的时候执行第二次就会报如下错误(说实话，我是第一次看到研发这种写法，一般都是不指定auto_increment列，或者指定为null值)

ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

t_meclass 表定义如下：

CREATE TABLE `t_meclass` (
  `meclass_id` bigint(20) not NULL AUTO_INCREMENT COMMENT '品类编号',
  `meclass_name` varchar(100) DEFAULT NULL COMMENT '品类名称',
  `meclass_desc` varchar(255) DEFAULT NULL COMMENT '品类描述',
  `property` bigint(20) NOT NULL DEFAULT '0' COMMENT '品类性质',
  `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `last_update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '最后修改时间',
  `state` bigint(20) NOT NULL DEFAULT '0',
  `type` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`meclass_id`)
) ENGINE=InnoDB AUTO_INCREMENT=303903 DEFAULT CHARSET=utf8 COMMENT='品类表';

在5.6.26上反复执行情况如下：

mysql> insert into t_meclass set meclass_id = default ,meclass_name='XXXX APP(普通商品1100)',meclass_desc='',property=53453792,type=20000,create_time= unix_timestamp(now()),last_update_time=unix_timestamp(now()),state=0;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_meclass;
+------------+----------------------------+--------------+----------+-------------+------------------+-------+-------+
| meclass_id | meclass_name               | meclass_desc | property | create_time | last_update_time | state | type  |
+------------+----------------------------+--------------+----------+-------------+------------------+-------+-------+
|          0 | XXXX APP(普通商品1100)     |              | 53453792 |  1446625414 |       1446625414 |     0 | 20000 |
+------------+----------------------------+--------------+----------+-------------+------------------+-------+-------+
1 row in set (0.00 sec)

mysql> insert into t_meclass set meclass_id = default ,meclass_name='XXXX APP(普通商品1100)',meclass_desc='',property=53453792,type=20000,create_time= unix_timestamp(now()),last_update_time=unix_timestamp(now()),state=0;
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
mysql>

我们看到第一次执行后meclass_id列插入的值为0，第二次执行还是要插入0，所以报主键冲突

3.解决方案

在mysql(5.6.26)官方文档上找到关于auto_increment的部分，截取如下跟本问题相关部分：

 NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.<pre name="code" class="html"><span style="color:#ff6666;">>>NO_AUTO_VALUE_ON_ZERO参数会影响AUTO_INCREMENT列。通常情况下如果你一个自增列插入null值或者0时，会给你生成下一个自增列的下一个序列值。如果你的sql_mode中包含NO_AUTO_VALUE_ON_ZERO，那么当你在插入0值时就不会为你生成下一个自增序列值，而是直接插入0，只有当插入null值时才会插入下一个序列值。</span>

This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.) For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. mysqldump now automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.>>如果0值已经在自增列中存储，那么设置该sql_mode是有用的（顺便说一下，不建议在自增列中存储0值）比如，你使用mysqldump导出一个表，然后再导入到其他库中，在导入的时候如果遇到自增列为0值时会为它生成一个序列值，这样就导致了该表与原来的表内容不一致。可以在导入之前启用NO_AUTO_VALUE_ON_ZERO来避免该问题。现在mysqldump 的导出文件中会自动包含启用NO_AUTO_VALUE_ON_ZERO，来避免这个问题

##通过上面官方文档描述可以，在sql_mode中去掉NO_AUTO_VALUE_NO_ZERO应该就没有问题了，后来再商用环境上，调整sql_mode后问题解决(其实最根本的解决方法，还是需要研发修改他们的代码)

好了看完了官方文档再来看看我们研发遇到的问题：

1)查看重复执行报错的 5.6.26数据库的sql_mode

mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                |
+---------------+--------------------------------------------------------------------------------------+
| sql_mode      | NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------------------------------------------------+

2) 我们看到 sql_mode 中确实包含NO_AUTO_VALUE_ON_ZERO，这就说明当auto_increment列遇到0值时不会生成自增序列值，而是直接当0值插入

3) 那问题是我们插入的是default值，怎么变成0值了? 关于这个问题我做了如下实验：

1.sql_mode='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'时

mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                |
+---------------+--------------------------------------------------------------------------------------+
| sql_mode      | NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> drop table test3;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `test3` (
    ->   `meclass_id` bigint(20) not NULL COMMENT '品类编号',
    ->   `meclass_name` varchar(100) COMMENT '品类名称',
    ->   `meclass_desc` varchar(255) DEFAULT NULL COMMENT '品类描述',
    ->   `property` bigint(20) NOT NULL DEFAULT '0' COMMENT '品类性质',
    ->   `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    ->   `last_update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '最后修改时间',
    ->   `state` bigint(20) NOT NULL DEFAULT '0',
    ->   `type` bigint(20) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`meclass_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=303903 DEFAULT CHARSET=utf8 COMMENT='品类表'; 
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test3 set meclass_id = default ,meclass_name=default ,meclass_desc='',property=53453792,type=20000,create_time= unix_timestamp(now()),last_update_time=unix_timestamp(now()),state=0;
ERROR 1364 (HY000): Field 'meclass_id' doesn't have a default value  <span style="color:#ff0000;">>>在sql_mode包含STRICT_TRANS_TABLES时，定义列为not null，在插入时指定列的值为default，会报错</span>
mysql> drop table test3;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test3` (
    ->   `meclass_id` bigint(20) COMMENT '品类编号',
    ->   `meclass_name` varchar(100) COMMENT '品类名称',
    ->   `meclass_desc` varchar(255) DEFAULT NULL COMMENT '品类描述',
    ->   `property` bigint(20) NOT NULL DEFAULT '0' COMMENT '品类性质',
    ->   `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    ->   `last_update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '最后修改时间',
    ->   `state` bigint(20) NOT NULL DEFAULT '0',
    ->   `type` bigint(20) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`meclass_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=303903 DEFAULT CHARSET=utf8 COMMENT='品类表';  
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test3 set meclass_id = default ,meclass_name=default ,meclass_desc='',property=53453792,type=20000,create_time= unix_timestamp(now()),last_update_time=unix_timestamp(now()),state=0;
Query OK, 1 row affected (0.00 sec)  <span style="color:#ff0000;">>>在sql_mode包含STRICT_TRANS_TABLES时，定义列时不指定not null属性，插入时指定列的值为default，值能够正常插入，meclass_id(主键)插入的值为0，meclass_name插入的值为null</span>

mysql> select * from test3;
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
| meclass_id | meclass_name | meclass_desc | property | create_time | last_update_time | state | type  |
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
|          0 | NULL         |              | 53453792 |  1446630357 |       1446630357 |     0 | 20000 |
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
1 row in set (0.00 sec)

mysql> drop table test3;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE `test3` (
    ->   `meclass_id` bigint(20) COMMENT '品类编号',
    ->   `meclass_name` varchar(100) COMMENT '品类名称',
    ->   `meclass_desc` varchar(255) DEFAULT NULL COMMENT '品类描述',
    ->   `property` bigint(20) NOT NULL DEFAULT '0' COMMENT '品类性质',
    ->   `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    ->   `last_update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '最后修改时间',
    ->   `state` bigint(20) NOT NULL DEFAULT '0',
    ->   `type` bigint(20) NOT NULL DEFAULT '0'
    -> ) ENGINE=InnoDB AUTO_INCREMENT=303903 DEFAULT CHARSET=utf8 COMMENT='品类表';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test3 set meclass_id = default ,meclass_name=default ,meclass_desc='',property=53453792,type=20000,create_time= unix_timestamp(now()),last_update_time=unix_timestamp(now()),state=0;
Query OK, 1 row affected (0.03 sec) <span style="color: rgb(255, 0, 0); font-family: Arial, Helvetica, sans-serif;">>>在sql_mode包含STRICT_TRANS_TABLES时，定义列时不指定not null属性，插入时指定列的值为default，值能够正常插入，meclass_id(非主键)插入的值为null，meclass_name插入的值为null.(跟上一个实验唯一的区别就是此时meclass_id没有被定义为主键)</span>


mysql> select * from test3;
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
| meclass_id | meclass_name | meclass_desc | property | create_time | last_update_time | state | type  |
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
|       NULL | NULL         |              | 53453792 |  1446630887 |       1446630887 |     0 | 20000 |
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
1 row in set (0.00 sec)

mysql> drop table test3;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `test3` (
    ->   `meclass_id` bigint(20) not NULL AUTO_INCREMENT COMMENT '品类编号',
    ->   `meclass_name` varchar(100) COMMENT '品类名称',
    ->   `meclass_desc` varchar(255) DEFAULT NULL COMMENT '品类描述',
    ->   `property` bigint(20) NOT NULL DEFAULT '0' COMMENT '品类性质',
    ->   `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    ->   `last_update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '最后修改时间',
    ->   `state` bigint(20) NOT NULL DEFAULT '0',
    ->   `type` bigint(20) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`meclass_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=303903 DEFAULT CHARSET=utf8 COMMENT='品类表';
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test3 set meclass_id = default ,meclass_name=default ,meclass_desc='',property=53453792,type=20000,create_time= unix_timestamp(now()),last_update_time=unix_timestamp(now()),state=0;
Query OK, 1 row affected (0.01 sec) <span style="color: rgb(255, 0, 0); font-family: Arial, Helvetica, sans-serif;">>>在sql_mode包含STRICT_TRANS_TABLES时，定义列时指定not null属性，并且指定AUTO_INCREMENT，插入时指定列的值为default，值能够正常插入，meclass_id(主键)插入的值为null，meclass_name插入的值为null.(可以看到 给meclass_id指定auto_increment时即使指定not null属性，插入default值时也不会报错)</span>


mysql> select * from test3;
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
| meclass_id | meclass_name | meclass_desc | property | create_time | last_update_time | state | type  |
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
|          0 | NULL         |              | 53453792 |  1446631037 |       1446631037 |     0 | 20000 |
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
1 row in set (0.00 sec)

mysql> insert into test3 set meclass_id = default ,meclass_name=default ,meclass_desc='',property=53453792,type=20000,create_time= unix_timestamp(now()),last_update_time=unix_timestamp(now()),state=0;
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'  <span style="color:#ff0000;">>>第二次插入时因为在主键列又一次插入0，所以报主键冲突</span>
mysql>

在sql_mode=为空时，测试如下：

mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |   <span style="color:#ff0000;">>>sql_mode为空</span>
+---------------+-------+
1 row in set (0.00 sec)

mysql> drop table test5;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `test5` (
    ->   `meclass_id` bigint(20) not null COMMENT '品类编号',
    ->   `meclass_name` varchar(100) not NULL COMMENT '品类名称',
    ->   `meclass_desc` varchar(255) DEFAULT NULL COMMENT '品类描述',
    ->   `property` bigint(20) NOT NULL DEFAULT '0' COMMENT '品类性质',
    ->   `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    ->   `last_update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '最后修改时间',
    ->   `state` bigint(20) NOT NULL DEFAULT '0',
    ->   `type` bigint(20) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`meclass_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=303903 DEFAULT CHARSET=utf8 COMMENT='品类表'; 
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test5 set meclass_id = default ,meclass_name=default,meclass_desc='',property=53453792,type=20000,create_time= unix_timestamp(now()),last_update_time=unix_timestamp(now()),state=0;
Query OK, 1 row affected, 2 warnings (0.00 sec)  <span style="color:#ff0000;">>>在sql_mode为空时，定义表时指定列为not null，插入时指定列值为default，插入后会有两个不存在default值的warning，此时meclass_id插入的值为0，meclass_name插入的值为空</span>

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1364 | Field 'meclass_id' doesn't have a default value   |
| Warning | 1364 | Field 'meclass_name' doesn't have a default value |
+---------+------+---------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from test5;
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
| meclass_id | meclass_name | meclass_desc | property | create_time | last_update_time | state | type  |
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
|          0 |              |              | 53453792 |  1446633043 |       1446633043 |     0 | 20000 |
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test5` (
    ->   `meclass_id` bigint(20) COMMENT '品类编号',
    ->   `meclass_name` varchar(100) COMMENT '品类名称',
    ->   `meclass_desc` varchar(255) DEFAULT NULL COMMENT '品类描述',
    ->   `property` bigint(20) NOT NULL DEFAULT '0' COMMENT '品类性质',
    ->   `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    ->   `last_update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '最后修改时间',
    ->   `state` bigint(20) NOT NULL DEFAULT '0',
    ->   `type` bigint(20) NOT NULL DEFAULT '0'
    -> ) ENGINE=InnoDB AUTO_INCREMENT=303903 DEFAULT CHARSET=utf8 COMMENT='品类表'; 
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test5 set meclass_id = default ,meclass_name=default,meclass_desc='',property=53453792,type=20000,create_time= unix_timestamp(now()),last_update_time=unix_timestamp(now()),state=0;
Query OK, 1 row affected (0.00 sec)  <span style="color:#ff0000;">>>sql_mode为空时，不给列指定not null属性，不设置default值，在插入的时候设置列的值为default，插入成功，插入后meclass_id值为null，meclass_name值为null(跟上一个实验的区别是，两个列都未指定not null，并且meclass_name列没有被指定为主键)##所以没有not null约束时插入的是默认值时null</span>

mysql> 
mysql> select * from test5;
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
| meclass_id | meclass_name | meclass_desc | property | create_time | last_update_time | state | type  |
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
|       NULL | NULL         |              | 53453792 |  1446633290 |       1446633290 |     0 | 20000 |
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
1 row in set (0.00 sec)

mysql> drop table test5;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test5` (
    ->   `meclass_id` bigint(20) not NULL AUTO_INCREMENT COMMENT '品类编号',
    ->   `meclass_name` varchar(100) COMMENT '品类名称',
    ->   `meclass_desc` varchar(255) DEFAULT NULL COMMENT '品类描述',
    ->   `property` bigint(20) NOT NULL DEFAULT '0' COMMENT '品类性质',
    ->   `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    ->   `last_update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '最后修改时间',
    ->   `state` bigint(20) NOT NULL DEFAULT '0',
    ->   `type` bigint(20) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`meclass_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=303903 DEFAULT CHARSET=utf8 COMMENT='品类表';
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test5 set meclass_id = default ,meclass_name=default,meclass_desc='',property=53453792,type=20000,create_time= unix_timestamp(now()),last_update_time=unix_timestamp(now()),state=0;
Query OK, 1 row affected (0.00 sec) <span style="color:#ff0000;">>>sql_mode为0时，定义表时给列指定not null 和auto_increment，插入时指定列值为default，插入成功，meclass_id插入的值为auto_increment序列值，meclass_name插入的值为null</span>

mysql> 
mysql> select * from test5;
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
| meclass_id | meclass_name | meclass_desc | property | create_time | last_update_time | state | type  |
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
|     303903 | NULL         |              | 53453792 |  1446633360 |       1446633360 |     0 | 20000 |
+------------+--------------+--------------+----------+-------------+------------------+-------+-------+
1 row in set (0.00 sec)