mysql - LOCK TABLES 表示表未锁定后在 UNION 查询上进行 SELECT

Question

我需要在代码的特定区域锁定一个表。

所以我用 :

锁定了我的表

LOCK TABLE christmas_game WRITE;

这很好。

现在我需要在此表上SELECT 东西，所以我这样做:

SELECT SQL_NO_CACHE prize
FROM christmas_game
WHERE user_id = {$this->user_id}
ORDER BY created_at DESC
LIMIT 2

这也很好用。但由于某种原因，这根本不起作用:

SELECT prize
FROM christmas_game
WHERE DATE(created_at) = CURDATE()
AND user_id != '{$uniqId}'
GROUP BY prize
UNION
SELECT prize
FROM christmas_game
WHERE user_id = {$user->user_id}
AND user_id != '{$uniqId}'
AND prize IN ('promo_1', 'promo_2')

我收到一条错误消息:Table 'christmas_game' was not locked with LOCK TABLES。显然 UNION 在这里有问题，因为没有它两个查询都可以正常工作。

这是为什么？

Answer 1

您必须同时锁定两个表，并且不能对同一个表锁定两次。

解决方案是为表添加一个别名:

LOCK TABLE christmas_game as table_locked_game_1 WRITE，christmas_game as table_locked_game_2 写入；

然后在你的联盟中添加相同的别名:

    SELECT prize 
    FROM christmas_game as table_locked_game_1 
    WHERE DATE(created_at) = CURDATE() GROUP BY prize 
UNION 
    SELECT prize FROM christmas_game as table_locked_game_2 
    WHERE user_id = 81 
    AND prize IN ('promo_1', 'promo_2')