MySQL使用IFNULL()搭配SELECT子查询报错1583的解决方案咨询

• MySQL的IFNULL(expr1, expr2)函数仅接受两个参数，分别为待判断是否为空的表达式、为空时的返回值，不支持在参数内部给表达式设置别名
• 你当前的写法中，在两个翻译子查询后面都加了AS subcategory_name别名声明，同时外层多套了一层不必要的SELECT，违反了IFNULL的语法规则，因此触发参数错误。

SELECT seller_job_invites.idseller_job_invites,
seller_job_invites.idjob,
seller_job_invites.iduser as seller_id,
seller_job_invites.idjob_status,
user.iduser AS buyer_id,
user.first_name AS buyer_first_name,
user.last_name as buyer_last_name,
user.profile_picture AS buyer_profile_picture,
job.idjob_subcategories,
job.posted_date,
job.start_date,
job.location_lat,
job.location_long,
IFNULL(
  (SELECT translations.word FROM translations WHERE translations.dtype="JOB_SUB_CATEGORY" AND translations.language_code="FR" AND translations.fkey=job.idjob_subcategories),
  (SELECT translations.word FROM translations WHERE translations.dtype="JOB_SUB_CATEGORY" AND translations.language_code="EN" AND translations.fkey=job.idjob_subcategories)
) AS subcategory_name,
job_status.name AS 'job_status'
FROM peresia.seller_job_invites
INNER JOIN job ON seller_job_invites.idjob = job.idjob
INNER JOIN user ON job.iduser = user.iduser 
INNER JOIN job_status ON seller_job_invites.idjob_status = job_status.idjob_status
WHERE seller_job_invites.iduser=52 AND (job_status.name = 'SELLER_SCREENING' OR job_status.name ='SELLER_INVITED')

如果查询数据量较大，每行执行两次子查询的性能较差，可以改为提前关联翻译表的方式实现同样逻辑，修改参考片段如下：

-- 在主查询的JOIN部分添加两个翻译表关联
LEFT JOIN translations fr_trans 
  ON fr_trans.dtype="JOB_SUB_CATEGORY" 
  AND fr_trans.language_code="FR" 
  AND fr_trans.fkey=job.idjob_subcategories
LEFT JOIN translations en_trans 
  ON en_trans.dtype="JOB_SUB_CATEGORY" 
  AND en_trans.language_code="EN" 
  AND en_trans.fkey=job.idjob_subcategories

-- 字段查询部分替换为如下写法即可
IFNULL(fr_trans.word, en_trans.word) AS subcategory_name

内容的提问来源于stack exchange，提问作者PeakGen