MySQL、UNION ALL 与 SQL_CALC_FOUND_ROWS 有时不起作用

Question

我在 MySQL、InnoDB 中使用 SQL_CALC_FOUND_ROWS 时遇到一些问题。首先让我展示一个简单的数据库架构来解释正在发生的事情......

Database Schema

媒体表

media_id    title
-------------------------------------
1           Empire of the Sun

流派模式表

genre_id    name
-------------------------------------
1           Action
2           Adventure

区域设置表

locale_id    code
-------------------------------------
1            en_US
2            pt_BR
3            fr_FR

类型翻译表

genre_id    locale    translation
-------------------------------------
1           1         Action
1           2         Ação
2           1         Adventure
2           2         Aventura

媒体类型表

media_id    genre_id
-------------------------------------
1           1
1           2

现在很简单:我需要返回与用户区域设置兼容的流派名称。假设用户区域设置为 pt_BR。为了获得结果，我使用以下 SQL:

select gt.name
from media_genres mg
inner join genre_translation gt on (mg.genre_id = gt.genre_id)
where (mg.media_id = 1 and gt.locale_id = 2)

它返回:

name
-------------------------------------
Ação
Aventura

但是，现在，我们假设用户区域设置是 fr_FR。 fr_FR 没有翻译，对吗？我找到的解决方案是使用 UNION ALL 和 SQL_CALC_FOUND_ROWS，然后我使用以下 SQL:

select SQL_CALC_FOUND_ROWS gt.name
from media_genres mg
inner join genre_translation gt on (mg.genre_id = gt.genre_id)
where (mg.media_id = 1 and gt.locale_id = 3)

UNION ALL

select  gt.name
from media_genres mg
inner join genre_translation gt on (mg.genre_id = gt.genre_id)
where FOUND_ROWS() = 0 and (mg.media_id = 1 and gt.locale_id = 1)

如果没有找到 locale_id = 3 和 media_id = 1 的行，那么我将获得 locale_id = 1(默认)和 media_id = 1 的结果。

问题是:该语句有时返回正确的数据，有时不返回数据。

我的主要问题:为什么会发生这种情况？

Answer 1

实现此目的的方法是使用 LEFT JOIN 连接转换表，然后在没有匹配项时使用 IFNULL 提供默认值。

SELECT mg.genre_id, IFNULL(gt2.name, gt1.name) AS name
FROM media_genres AS mg
JOIN genre_translation AS gt1 ON mg.genre_id = gt.genre_id AND gt.locale_id = 1
LEFT JOIN genre_translation AS gt2 ON mg.genre_id = gt.genre_id AND gt.locale_id = 3
WHERE mg.media_id = 1