Duplicate entry 'xxx' for key 'xx'

🪐 TiDB 技术问题 备份&数据迁移
1

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

  • 【TiDB 版本】：v4.0.0-rc.1
  • 【问题描述】：insert表里面并没有select表中的索引，但是在写入的时候会报索引重复的异常
    image
    image1619×715 42.6 KB

    若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。
2

麻烦使用 ignore index ，尝试不走索引看下能够查出数据吗？多谢

https://pingcap.com/docs-cn/stable/optimizer-hints/#ignore_indext1_name-idx1_name--idx2_name-

3

:rofl:我把索引都给删了，查询插入都带上全字段就没问题了，其实两个表字段完全一样，只是顺序不同

insert into ent_advertise_data_t_toutiao_creative_day(id,account_id,keyword_id,keyword_name,convert,deep_convert,attribution_convert,attribution_deep_convert,total_play,valid_play,play_one_quarter_feed_break,play_two_quarter_feed_break,play_three_quarter_feed_break,play_four_quarter_feed_break,play_over,wifi_play,play_duration_sum,share,comment,like,follow,home_visited,ies_challenge_click,ies_music_click,location_click,date,execute_time,device_put,data_deadline_time,play_over_rate) select id,account_id,keyword_id,keyword_name,convert,deep_convert,attribution_convert,attribution_deep_convert,total_play,valid_play,play_one_quarter_feed_break,play_two_quarter_feed_break,play_three_quarter_feed_break,play_four_quarter_feed_break,play_over,wifi_play,play_duration_sum,share,comment,like,follow,home_visited,ies_challenge_click,ies_music_click,location_click,date,execute_time,device_put,data_deadline_time,play_over_rate from ent_advertise_data_t_toutiao_creative_day_tmp_tmp;

4

可以在 insert 和 select 的时候加上全字段，保留索引的情况下试下吗？

另外如果两张表字段完全一样，只是顺序不同的话，insert 和 select 的时候建议是要带上全字段的，不然插入的数据可能会有问题

MySQL [gangshen]> create table t1(a varchar(20),b varchar(20),c varchar(20));
Query OK, 0 rows affected (0.11 sec)

MySQL [gangshen]> create table t2(c varchar(20),a varchar(20),b varchar(20));
Query OK, 0 rows affected (0.12 sec)

MySQL [gangshen]> insert into t1 values('a','b','c');
Query OK, 1 row affected (0.03 sec)

MySQL [gangshen]> select * from t1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
| a    | b    | c    |
+------+------+------+
1 row in set (0.00 sec)

MySQL [gangshen]> insert into t2 select * from t1;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

MySQL [gangshen]> select * from t2;
+------+------+------+
| c    | a    | b    |
+------+------+------+
| a    | b    | c    |
+------+------+------+
1 row in set (0.01 sec)
5

:rofl:肯定是没写全字段，导致串字段了。

6

嗯嗯，应该是的