mysql - 无法限制

Question

我有以下查询:

UPDATE pc
INNER JOIN cams 
   ON cams.cam_id = pc.camuid 
SET 
    timestamp = NOW(),
    uid = @out_param:=uid
WHERE zone = 1 
  AND (unable = '0' OR unable IS NULL)
  AND (corrected_plate = ''  OR corrected_plate IS NULL)
  AND (timestamp IS NULL OR timestamp < (NOW() - INTERVAL @interval MINUTE)) 
LIMIT 1; 
SELECT @out_param;

我无法运行该查询，因为我在连接查询中有一个 LIMIT 子句。我无法弄清楚如何在保持性能的同时将其扩展到子查询中。我需要这个查询尽可能快，而 MySQL 的优化器到目前为止没有提供太多帮助。

这是从 MySQL 得到的错误，因为上面是不允许的:Incorrect usage of UPDATE and LIMIT

Answer 1

只需使用子查询来决定更新哪个 uid。

UPDATE PC
SET timestamp = NOW(),
   uid = @out_param:=uid
WHERE uid = (SELECT uid
             FROM ( SELECT * FROM pc ) as pc2
             INNER JOIN cams 
                ON cams.cam_id = pc2.camuid 
             WHERE zone = 1 
               AND (unable = '0' OR unable IS NULL)
               AND (corrected_plate = ''  OR corrected_plate IS NULL)
               AND (timestamp IS NULL OR timestamp < (NOW() - INTERVAL @interval MINUTE)) 
             ORDER BY uid -- optional
             LIMIT 1)