我有一些问题与UNION ALL使用SQL_CALC_FOUND_ROWS在MySQL, InnoDB。首先,让我展示一个简单的数据库模式来解释发生了什么…
数据库模式媒体表
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没有翻译,对吧?我发现的解决方案是使用SQL_CALC_FOUND_ROWS的UNION ALL,然后使用以下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。
问题是:语句有时返回正确的数据,有时不返回数据。
我的主要问题是:为什么会发生这种情况?
实现此目的的方法是使用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