MySQL中的key ON DUPLICATE KEY UPDATE column是含糊不清的。

Question

有以下问题

INSERT INTO statistics_new (SELECT * FROM statistics)
    -> 
    -> ON DUPLICATE KEY UPDATE
    -> 
    -> end_date = IF(end_date < VALUES(end_date), VALUES(end_date), end_date);
ERROR 1052 (23000): Column 'end_date' in field list is ambiguous

当我尝试按照http://dev.mysql.com/doc/refman/5.0/en/insert-select.html的建议指定列和表别名时，结果相同。服务器版本：5.5.24-0ubuntu0.12.04.1（Ubuntu）。以下查询可以正常运行：

INSERT INTO statistics_new (SELECT * FROM statistics)
ON DUPLICATE KEY UPDATE
end_date = VALUES(end_date)

- user1802525

Answer 1

SQLFiddle示例

基本上我认为您想要这个：

INSERT INTO statistics_new (SELECT * FROM statistics)
ON DUPLICATE KEY UPDATE
statistics_new.end_date = if(statistics_new.end_date < statistics.end_date, 
                             statistics.end_date, statistics_new.end_date);

- juergen d

---

为什么在使用ON DUPLICATE KEY UPDATE时，如果没有“statistics_new.”前缀，会出现奇怪的行为？ - user1802525

因为数据库引擎不知道在两个表中具有相同名称的列应该采用哪一个，所以您必须命名表格。 - juergen d

这种行为并不清晰。因为1）我们无法将表别名添加到插入表中，需要使用完整的表名前缀字段；2）MySQL手册描述了仅通过VALUES()语句使用插入值的方法，但是我们可以使用“selection_table.field”形式，而且这些方式是否执行相同的操作并不清楚。也就是说，“ON DUPLICATE KEY UPDATE inserion.field = VALUES(selection.field)”和“ON DUPLICATE KEY UPDATE insertion.field = selection.field”是否相同？ - Alexander Pravdin

关于这个有趣的特性还有两个查询： 1） INSERT INTO statistics_new (SELECT * FROM statistics) ON DUPLICATE KEY UPDATE statistics_new.end_date = statistics.end_date, statistics_new.is_modified = if(statistics_new.end_date != statistics.end_date,1,0); 2） INSERT INTO statistics_new (SELECT * FROM statistics) ON DUPLICATE KEY UPDATE statistics_new.is_modified = if(statistics_new.end_date != statistics.end_date,1,0), statistics_new.end_date = statistics.end_date;订单已更改。 第一个查询每次将“is_modified”设置为0， 第二个查询将进行比较并设置为0或1。 - scrache

谢谢你，让我感到开心。由于某种原因，这个问题一直在给我“Insufficient index 64”的错误提示。看起来这与使用DB客户端（pycharm插件）的数据库会话有关。真是我的倒霉。 - M.Abulsoud

Answer 2

statistics_new和statistics都有一个end_date列，请在您的SQL中区分它们。

- Raptor

Answer 3

如果你试图在最后一行设置一个变量，应该是这样的：

SET @end_date = IF(end_date < VALUES(end_date), VALUES(end_date), end_date);

- Adam Bubela

Answer 4

如果您不想创建新表，可以使用子查询来重命名模糊字段。但是这需要列出所有字段，因此可能并不适用于所有用例：

INSERT INTO statistics(end_date, field2) 
SELECT * FROM (
  SELECT end_date as new_end_date, field2 
  FROM statistics
) q
ON DUPLICATE KEY UPDATE
  end_date = IF(end_date < VALUES(end_date), VALUES(end_date), end_date);

- kurkle