MySQL多表选择

Question

我被一个结合MySQL查询卡住了一点。它是多语言，多目标站点的一部分，因此我需要从多个表中检索站点的一些文本部分，并使用备用。可能命中的顺序：

纠正@ local_tbl>英语@ local_tbl>修正@ global_tbl> 英语@ global_tbl。

因为它会被高度使用，我想保持它的快速和低连接数，但只返回1行。我尝试了几种方法，但我确信有更好的解决方案。

---

FROM子句中的子查询：如果子查询中的任何一个给出0行，它就会死亡。即使它的工作原理，需要一些PHP解释：

SELECT * FROM 

(SELECT `sp_content` AS sp_local FROM `loc-ae_siteparts` WHERE `sp_name` = 'name_of_some_sitepart' AND `sp_lang` = 'de' AND `sp_corrected`='1' LIMIT 1) as local, 
(SELECT `sp_content` AS sp_local_en FROM `loc-ae_siteparts` WHERE `sp_name` = 'name_of_some_sitepart' AND `sp_lang` = 'en' LIMIT 1) as local_en, 
(SELECT `sp_content` AS sp_global, `sp_corrected` as sp_global_corrected FROM `loc-global_siteparts` WHERE `sp_name` = 'name_of_some_sitepart' AND `sp_lang` = 'de' LIMIT 1) as global, 
(SELECT `sp_content` AS sp_global_en FROM `loc-global_siteparts` WHERE `sp_name` = 'name_of_some_sitepart' AND `sp_lang` = 'en' LIMIT 1) as global_en 

---

TEMP表：在这里，我关心的性能，因为它涉及到一个文本字段不能使用内存引擎。小鸟浪费了核弹吗？

CREATE TEMPORARY TABLE IF NOT EXISTS `random_tbl_name` AS (SELECT `sp_content` FROM `loc-global_siteparts` LIMIT 0); 

INSERT INTO `random_tbl_name` SELECT `sp_content` FROM `loc-ae_siteparts` WHERE `sp_name` = 'name_of_some_sitepart' AND `sp_lang` = 'de' AND `sp_corrected` = '1' LIMIT 1; 
INSERT INTO `random_tbl_name` SELECT `sp_content` FROM `loc-ae_siteparts` WHERE `sp_name` = 'name_of_some_sitepart' AND `sp_lang` = 'en' LIMIT 1; 
INSERT INTO `random_tbl_name` SELECT `sp_content` FROM `loc-global_siteparts` WHERE `sp_name` = 'name_of_some_sitepart' AND `sp_lang` = 'de' AND `sp_corrected` = '1' LIMIT 1; 
INSERT INTO `random_tbl_name` SELECT `sp_content` FROM `loc-global_siteparts` WHERE `sp_name` = 'name_of_some_sitepart' AND `sp_lang` = 'en' LIMIT 1; 

SELECT * FROM `random_tbl_name` LIMIT 1; 

编辑：感谢所有的答案，他们是真正的帮助。

Answer 1

SELECT * FROM 
    ((SELECT 1 precedence, `sp_content` 
    FROM `loc-ae_siteparts` 
    WHERE `sp_name` = 'name_of_some_sitepart' AND `sp_lang` = 'de' AND `sp_corrected`='1' 
    LIMIT 1) 
    UNION 
    (SELECT 2 precedence, `sp_content` 
    FROM `loc-ae_siteparts` 
    WHERE `sp_name` = 'name_of_some_sitepart' AND `sp_lang` = 'en' 
    LIMIT 1) 
    UNION 
    ... 
    ) x 
ORDER BY precedence 
LIMIT 1 

Answer 2

你没有结果时，子查询的一个是空的原因是，与

select * from 
s1, s2, s3, s4 

你有一个隐含的加入。当其中一个子结果为空时，生成的连接也是空的。

要解决这个问题，可以使用子查询作为列。这会给你同样的结果，但与NULL值，其中子查询不返回行

SELECT (SELECT `sp_content` AS sp_local FROM `loc-ae_siteparts` WHERE `sp_name` = 'name_of_some_sitepart' AND `sp_lang` = 'de' AND `sp_corrected`='1' LIMIT 1) as local, 
     (SELECT `sp_content` AS sp_local_en FROM `loc-ae_siteparts` WHERE `sp_name` = 'name_of_some_sitepart' AND `sp_lang` = 'en' LIMIT 1) as local_en, 
     (SELECT `sp_content` AS sp_global, `sp_corrected` as sp_global_corrected FROM `loc-global_siteparts` WHERE `sp_name` = 'name_of_some_sitepart' AND `sp_lang` = 'de' LIMIT 1) as global, 
     (SELECT `sp_content` AS sp_global_en FROM `loc-global_siteparts` WHERE `sp_name` = 'name_of_some_sitepart' AND `sp_lang` = 'en' LIMIT 1) as global_en 

Answer 3

我已经做过类似的事情在过去是这样的：

SELECT COALESCE(ls1.sp_content, ls2.sp_content) AS sp_content 
FROM (SELECT 1) a 
LEFT JOIN loc-ae_siteparts ls1 
    ON ls1.sp_name = 'name_of_some_sitepart' AND ls1.sp_lang = 'de' AND ls1.sp_corrected = '1' 
LEFT JOIN loc-ae_siteparts ls2 
    ON ls2.sp_name = 'name_of_some_sitepart' and ls2.sp_lang = 'en' 
LIMIT 1