MySql“在重复键更新上插入..."仍在插入重复的记录.我想念什么? [英] MySql "INSERT … ON DUPLICATE KEY UPDATE" still inserting duplicate records. What am I missing?

问题描述

我有一个简单的表，该表设置了两列，每一列都是一个键值.存储在每个字段中的值是表示电子邮件地址和关键字的varchar(45).所收集的信息可能会重复自身，因为它与站点浏览数据收集有关.为了避免重复输入，我曾经尝试使用INSERT IGNORE into，REPLACE into，最后尝试以下操作:

I have a simple table set up with two columns, each column is a key value. the values stored in each field are varchar(45) representing an email address and a keyword. It is possible that the information collected may duplicate itself as it is related to site browsing data collection. To avoid duplicate entries, I used tried to use INSERT IGNORE into, REPLACE into, and finally I'm trying the following:

insert into <table name> (user_email, key_token) values ('<email>@<this>.com', 'discountsupplies') on duplicate key update user_email='<email>@<this>.com',key_token='discountsupplies';

但是我仍然看到重复的记录被插入到表中.生成表的SQL:

but I am still seeing duplicate records being inserted into the table. The SQL that generated the table:

DROP TABLE IF EXISTS `<database name>`.`<table name>` ;

CREATE  TABLE IF NOT EXISTS `<database name>`.`<table name>` (
  `user_email` VARCHAR(45) NOT NULL ,
  `key_token` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`user_email`, `key_token`) )
ENGINE = InnoDB;

虽然我看到了几个与此问题很接近的问题，但我没有看到任何有关为什么可能会发生此问题的问题，我想弄清楚我对这种行为不了解的地方.感谢您的帮助.

While I saw several questions that were close to this one, I did not see any that addressed why this might be happening, and I'd like to figure out what I'm not understanding about this behavior. Any help is appreciated.

作为附录，在添加UNIQUE KEY语句之后，我回过头来尝试了REPLACE和INSERT IGNORE来实现我的目标，并且这些选项都没有排除重复的条目.

As an addendum, After adding the UNIQUE KEY statements, I went back and tried both REPLACE and INSERT IGNORE to achieve my goal, and none of these options is excluding duplicate entries.

还添加:唯一索引( user_email ， key_token )似乎也无济于事.

Also adding: UNIQUE INDEX (user_email, key_token) doesn't seem to help either.

我将通过手动查找例程进行此检查，直到我能弄清楚为止.如果找到答案，我将很乐意更新该帖子.

I'm going to do this check via a manual look-up routine until I can figure this out. If I find an answer I'll be happy to update the post.

在原始create table语句下方添加了唯一索引行-

Added Unique Index lines below the original create table statement -

-- -----------------------------------------------------
-- Table `<db name>`.`<table name>`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `<db name>`.`<table name>` ;

CREATE  TABLE IF NOT EXISTS `<db name>`.`<table name>` (
  `user_email` VARCHAR(45) NOT NULL ,
  `key_token` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`user_email`, `key_token`),
  UNIQUE KEY (user_email),
  UNIQUE KEY (key_token)
  )

ENGINE = InnoDB;

CREATE UNIQUE INDEX ix_<table name>_useremail on `<db name>`.`<table name>`(user_email);
CREATE UNIQUE INDEX ix_<table name>_keytoken on `<db name>`.`<table name>`(key_token);

似乎还可以(在源代码步骤中创建表时没有错误)，但是在重复查询上运行时，我仍然出现重复.

it seems to be ok (no errors when creating tables during the source step), but I'm still getting duplicates when running the on duplicate query.

推荐答案

目前的最终解决方案:查询表以通过user_email获取key_token列表，针对列表条目测试当前key_token，如果未插入.虽然不是最佳或漂亮，但是可以....

final solution for now: query table to get list of key_tokens by user_email, test current key_token against list entries, if found don't insert. Not optimal or pretty, but it works....

这篇关于MySql“在重复键更新上插入..."仍在插入重复的记录.我想念什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！