mysql - MySQL 中外连接逻辑表错误的 key 文件不正确

Question

我有一个很长的 MySQL 查询，我正在尝试使用 FULL OUTER JOIN，但 MySQL 中不存在该查询:

select c.cat_id,c.cat_name as cat_name, 
 c.cat_desc, c.cat_image, mi.filename, 
 l.link_id, l.user_id, l.address,l.city, 
 l.country,l.link_created,l.link_desc, 
 l.email,l.fax,l.link_hits, l.link_modified,
 l.link_name,l.postcode, l.price,l.link_rating, 
 l.state,l.telephone,l.link_votes,
 l.website, l.link_id, l.link_visited, cf.value
from j25_mt_cats as c,
j25_mt_links as l 
LEFT JOIN j25_mt_cfvalues AS cf ON (cf.link_id = l.link_id),
j25_mt_images AS mi,
j25_mt_cl as cl
where cf.cf_id = 40 and cl.link_id = l.link_id 
 AND mi.link_id = l.link_id AND mi.ordering < 2  
 AND c.cat_id = cl.cat_id and c.cat_published = 1 
 AND c.cat_approved = 1 and l.link_published = 1 and l.link_approved = 1
ORDER BY RAND() DESC;

上面的 LEFT JOIN 是我希望使用 FULL OUTER JOIN 逻辑的地方。我知道它必须使用多个 JOIN 和 UNION，但我的尝试导致了各种语法错误。

将上述内容更改为 FULL OUTER JOIN 逻辑的正确语法是什么？

我正在尝试这个:

select c.cat_id,c.cat_name as cat_name, 
 c.cat_desc, c.cat_image, mi.filename, 
 l.link_id, l.user_id, l.address,l.city, 
 l.country,l.link_created,l.link_desc, 
 l.email,l.fax,l.link_hits, l.link_modified,
 l.link_name,l.postcode, l.price,l.link_rating, 
 l.state,l.telephone,l.link_votes,
 l.website, l.link_id, l.link_visited, cf.value
from j25_mt_cats as c,
j25_mt_links as l 
LEFT OUTER JOIN j25_mt_cfvalues AS cf ON (cf.link_id = l.link_id),
j25_mt_images AS mi,
j25_mt_cl as cl
UNION ALL
select c.cat_id,c.cat_name as cat_name, 
 c.cat_desc, c.cat_image, mi.filename, 
 l.link_id, l.user_id, l.address,l.city, 
 l.country,l.link_created,l.link_desc, 
 l.email,l.fax,l.link_hits, l.link_modified,
 l.link_name,l.postcode, l.price,l.link_rating, 
 l.state,l.telephone,l.link_votes,
 l.website, l.link_id, l.link_visited, cf.value
FROM j25_mt_cats as c,
j25_mt_links as l
RIGHT OUTER JOIN j25_mt_cfvalues AS cf ON cf.link_id = l.link_id,
j25_mt_images AS mi,
j25_mt_cl as cl
where cf.cf_id = 40 and cl.link_id = l.link_id 
 AND mi.link_id = l.link_id AND mi.ordering < 2  
 AND c.cat_id = cl.cat_id and c.cat_published = 1 
 AND c.cat_approved = 1 and l.link_published = 1 and l.link_approved = 1
 AND cf.link_id IS NULL
ORDER BY RAND() DESC;

但运行需要几分钟，然后返回#126 - 表 '/tmp/#sql_4f82_0.MYI' 的 key 文件不正确；尝试修复它
当我认为我更接近正确的语法时。磁盘未满，甚至未接近。这也不是一个表，它只是我在 phpMyAdmin 中运行的一个查询。

Answer 1

通常，当您填充临时目录时，就会出现此问题，并且在您的情况下，您的查询可能会创建一个巨大的临时文件。

除了优化查询之外，请尝试在查询期间监视临时目录所在的分区显示“tmpdir”等变量；。