使用重复密钥更新的 MySQL 错误

Question

我有一个项目，每周都会导入几个包含错误数据的大型数据集，例如不应该重复的重复员工 ID。为了标记重复项，我尝试以下代码：

ALTER TABLE AccountDuplicates
  ADD UNIQUE INDEX EmployeeID (EmployeeID);
INSERT INTO AccountDuplicates
  SELECT
    EmployeeID,
    FirstName,
    LastName
  FROM AccountsWork
  ON DUPLICATE KEY UPDATE
    EmployeeID = CONCAT(VALUES(EmployeeID), '*');

INSERT 语句给了我错误，我看不到我做错了什么：

[42000][1064] You have an error in your SQL syntax; check the manual that correspondsto your MySQL server version for the right syntax to use near 'FROM EAD_UserAccountsWork
           ON DUPLICATE KEY UPDATE EmployeeID = CONCAT(VALUES(E' at line 36

如果相关，我在OS X 10.11.4上运行MySQL 5.7.12，INNODB引擎和mysql_mode = ''。我在这里的目的是识别重复的 ID，以便我可以将它们转发给适当的 DBA 进行更正。

更新：我已将数据库默认值设置为如下：

[client]
default-character-set = utf8mb4
[mysqld]
sql_mode=''
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[mysql]
default-character-set = utf8mb4

Answer 1

你的语法看起来不错，也许有打字错误？这几乎有效：

-- drop table AccountsWork ;
-- drop table  AccountDuplicates;
CREATE TABLE AccountsWork (
    EmployeeID varchar(16),
    FirstName INT,
    LastName INT
);
CREATE TABLE AccountDuplicates (
    EmployeeID  varchar(16),
    FirstName INT,
    LastName INT
);
    alter table AccountDuplicates add unique index(EmployeeID); 
insert into AccountsWork values('a',2,3);
insert into AccountsWork values(1,2,3);
insert into AccountsWork values('b',2,3);
insert into AccountsWork values('c',2,3);
insert into AccountsWork values('c',2,3);
insert into AccountsWork values('c',2,3);
insert into AccountsWork values('c',2,3);
SELECT 
    *
FROM
    AccountsWork;
-- there is no syntax errors here (your original query):    
INSERT INTO AccountDuplicates
  SELECT
    EmployeeID,
    FirstName,
    LastName
  FROM AccountsWork
  ON DUPLICATE KEY UPDATE
    EmployeeID =  CONCAT(VALUES(EmployeeID), '*');
SELECT 
    *
FROM
    AccountDuplicates;    

使用重复键

的简单插入有效，但"在重复键更新时从表中选择"不起作用。

我认为您需要查看此问题：插入...从中选择 ...重复密钥更新时

看起来mysql解析器对这种查询很疯狂

Answer 2

我认为

这是因为您没有正确限定"源"EmployeeID

INSERT INTO AccountDuplicates
  SELECT
    EmployeeID,
    FirstName,
    LastName
  FROM AccountsWork t
  ON DUPLICATE KEY UPDATE
    EmployeeID = CONCAT(t.EmployeeID, '*');

位于=左侧的EmployeeID是指AccountDuplicates表，右侧的是指AccountsWork表