MySQL错误Duplicate-entry-for—key-group-key

发表于
今生无悔入华夏，来生还做中国人！

问题背景

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

1
2
3
4
5
6
7
8
9
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

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

1
2
3
4
5
6
7
8
9
+----------+-------------+------------------+----------------------+------------+-----------------+
| 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：

1
2
3
4
#先建立一张空表
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插入时都报错，只是零星地报几个。

解决办法

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=536870912和tmp_table_size=536870912添加到/etc/my.cnf中

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

1
2
3
4
5
6
mysql> show variables like "tmpdir";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp/ | 
+---------------+-------+

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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。

https://cloud.tencent.com/developer/article/1176358