MySQL 出错。错误 : ER_DUP_FIELDNAME: Duplicate column

Question

我有一个关于 MySQL 的问题，它显示了这条错误消息。

Error: ER_DUP_FIELDNAME: Duplicate column name '1'

当我在下面使用这段代码时。

INSERT INTO execution (employee, task, report, accept_time)
SELECT * FROM (SELECT '1', '1', '123', NOW()) AS tmp
WHERE NOT EXISTS (SELECT * FROM execution WHERE employee = '1' AND task = '1') LIMIT 1

不知道为什么 "(SELECT '1', '1', '123', NOW())"有重复的问题？

这是原始的 SQL 表。

CREATE TABLE `science_cheer`.`execution` (
  `aid` INT NOT NULL AUTO_INCREMENT,
  `employee` INT NOT NULL,
  `task` INT NOT NULL,
  `report` VARCHAR(1000) NOT NULL,
  `accept_time` DATETIME NOT NULL,
  `audit` VARCHAR(45) NOT NULL DEFAULT 'unaudited',
  PRIMARY KEY (`aid`),
  INDEX `uid_idx` (`employee` ASC),
  INDEX `tid_idx` (`task` ASC),
  CONSTRAINT `employee`
    FOREIGN KEY (`employee`)
    REFERENCES `science_cheer`.`user` (`uid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `task`
    FOREIGN KEY (`task`)
    REFERENCES `science_cheer`.`task` (`tid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Answer 1

问题是列的名称，而不是值。您可以使用别名给它们任意或有意义的名称:

INSERT INTO execution(employee, task, report, accept_time)
    SELECT employee, task, report, accept_time
    FROM (SELECT '1' as employee, '1' as task, '123' as report, NOW() as accept_time
         ) AS tmp
    WHERE NOT EXISTS (SELECT * FROM execution WHERE employee = '1' AND task = '1');

不需要 limit 子句。

你也可以这样写:

INSERT INTO execution(employee, task, report, accept_time)
    SELECT '1', '1', '123', now()
    FROM dual
    WHERE NOT EXISTS (SELECT * FROM execution WHERE employee = '1' AND task = '1');

然而，如果你想避免表中的重复，你可以让数据库来完成这项工作。在 execution(employee, task) 上创建唯一索引。然后，您可以使用 on duplicate key update 进行插入，这样在尝试进行重复插入时它不会返回错误:

create index idx_execution_employee_task on execution(employee, task);

然后插入:

insert into execution(employee, task, report, accept_time)
    select '1', '1', '123', now()
    on duplicate key update set employee = values(employee);