ERROR 1062 (23000) at line 1: Duplicate entry '1332883220' for key 'group_key'

1.问题背景

我有一个数据表,记录一个QQ号加好友的活跃天数、加好友次数、加好友的toUin数等信息。数据表的建表语句如下:

 echo "drop table if exists uinPortrait"|mysql -proot@mysql 
 echo "CREATE TABLE IF NOT EXISTS uinPortrait(
           uin int(10) unsigned NOT NULL DEFAULT 0,
           active_days int(10) unsigned NOT NULL DEFAULT 0,
           add_friend_count int(10) unsigned NOT NULL DEFAULT 0,
           add_friend_uin_count int(10) unsigned NOT NULL DEFAULT 0,
           black_count int(10) unsigned NOT NULL DEFAULT 0,
           black_uin_count int(10) unsigned NOT NULL DEFAULT 0
       )ENGINE=MyISAM DEFAULT CHARSET=utf8" |mysql -proot@mysql 

由于数据表中的数据存放形式如下:

+----------+-------------+------------------+----------------------+------------+-----------------+
| uin      | active_days | add_friend_count | add_friend_uin_count |black_count | black_uin_count |
+----------+-------------+------------------+----------------------+------------+-----------------+
|10000     |1            |2                 |2                     |0           |0                |
|10000     |0            |0                 |0                     |4           |3                |
|10001     |1            |3                 |2                     |0           |0                |
|10001     |0            |0                 |0                     |5           |5                |
....
+----------+-------------+------------------+----------------------+------------+-----------------+

现在需要将相同的UIN数据归并为一条数据,于是使用了如下SQL:

#先建立一张空表
mysql>create table if not exists blankUinPortrait like uinPortrait;

mysql>insert into blankUinPortrait select uin,sum(active_days),sum(add_friend_count),sum(add_friend_uin_count),sum(black_count),sum(black_uin_count) from uinPortrait group by uin;

在执行insert into时,错误如下ERROR 1062 (23000) at line 1: Duplicate entry '1332883220' for key 'group_key'。并非每一个uin插入时都报错,只是零星地报几个。

2.解决办法

MySQL版本5.1.61。很疑惑,blankUinPortrait并没有设置主键和唯一索引,不知道为什么会出现值冲突,百思不得其解,在网上各种google和baidu也没有找到原因。于是我尝试了重启mysql、将中间数据写到磁盘,再load到数据表,以及将insert into改为replace into都不行。不抛弃,不放弃,黄天不负有心人,终于在stack overflow社区上找到了解决方法,具体参见Duplicate entry for key ‘group_key’

具体做法是修改mysql的配置文件,一般在/etc/my.cnf,将max_heap_table_size=536870912tmp_table_size=536870912添加到/etc/my.cnf中,如下图:

先说一下tmp_table_size 在做GROUP BY操作时会生成临时表,它规定了临时表大小的最大值(实际起限制作用的是tmp_table_sizemax_heap_table_size的最小值。)。如果内存临时表超出了限制,MySQL就会自动地把它转化为基于磁盘的MyISAM表,存储在指定的tmpdir目录下。默认:

mysql> show variables like "tmpdir";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp/ | 
+---------------+-------+

如果调高该值,MySQL同时将增加heap表的大小,可达到提高联接查询速度的效果,建议尽量优化查询,要确保查询过程中生成的临时表在内存中,避免临时表过大导致生成基于硬盘的MyISAM表 。

mysql> show global status like ‘created_tmp%‘;

+——————————–+———+

| Variable_name             | Value |

+———————————-+———+

| Created_tmp_disk_tables | 21197  |

| Created_tmp_files   | 58  |

| Created_tmp_tables  | 1771587 |

+——————————–+———–+

每次创建临时表,Created_tmp_tables增加,如果临时表大小超过tmp_table_size,则是在磁盘上创建临时表,Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数,比较理想的配置是: Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% =1.20%,应该比较合适。

show variables like 'max_table_size'可以查看大小,默认是16MB,可调到64-256MB最佳,线程独占,太大可能导致内存不够,I/O堵塞。

关于max_heap_table_size 这个变量定义了用户可以创建的内存表(memory table)的大小,可用来计算内存表的最大行数值。这个变量支持动态改变,即set @max_heap_table_size=#,但对于已经存在的内存表就没有什么用了,除非这个表被重新创建(create table)或者修改(alter table)或者truncate table。服务重启也会设置已经存在的内存表为全局max_heap_table_size的值。

这个变量和tmp_table_size一起限制了内部内存临时表的大小。具体可参见 Section 8.4.4, “Internal Temporary Table Use in MySQL

show variables like 'max_heap_table_size'可以查看大小,默认是16MB。


参考文献

[1]tmp_table_size & max_heap_table_size [2]MySQL 配置优化 [3]mysql的tmp_table_size和max_heap_table_size [4]Duplicate entry for key ‘group_key’

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏李家的小酒馆

数据库事务和索引

事务的性质:   原子性:同一个事务中的所有操作要不然全部成功要不然全部失败   一致性:一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态,,也...

20300
来自专栏我的博客

PHP Mysql函数汇总表

mysql_affected_rows — 取得前一次 MySQL 操作所影响的记录行数mysql_change_user —  改变活动连接中登录的用户mys...

40050
来自专栏Java3y

Oracle总结【视图、索引、事务、用户权限、批量操作】

前言 在Oracle总结的第一篇中,我们已经总结了一些常用的SQL相关的知识点了…那么本篇主要总结关于Oralce视图、序列、事务的一些内容… 在数据库中,我们...

42540
来自专栏web编程技术分享

第四节 -自己 DIY 一个数据库管理工具1.编写php服务器代码

42640
来自专栏淡定的博客

sql注入入门学习(数字型)(连载中)

在MySQL中,表名存放在information_schema数据库下tables表table_name字段中、查表名我们主要用到的是TABLES表

38940
来自专栏木头编程 - moTzxx

后台 配置页面功能设计

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u011415782/article/de...

73540
来自专栏MySQL实战分享

MongoDB第四期:SQL 与 MongoDB 映射(入门)

传统的关系数据库一般由数据库、表、记录三个层次概念组成,MongoDB是由数据库、集合、文档对象三个层次组成。MongoDB对于关系型数据库里的表,但是集合中没...

40620
来自专栏IT技术精选文摘

Mysql锁机制分析

1.1K40
来自专栏编程之路

羊皮书APP(Android版)开发系列(二十二)10分钟秒懂单例模式

10030
来自专栏python成长之路

mysql在ubuntu中的操作笔记(详)

17840

扫码关注云+社区

领取腾讯云代金券