MySQL LEFT JOIN使用PHP变量$qs作为关联条件无返回值但WHERE条件正常问题

问题描述 #

当我把PHP变量$qs放在WHERE条件中时查询运行正常，但将其放在LEFT JOIN关联条件中时，查询无任何数据返回。
问题代码行：AND e.terms_id_user =

可正常运行的查询语句 #

SELECT
    *,
    'users_staff_driving_license' AS driving_license_link,
    DATE_FORMAT(terms_date_created,'%a %d-%M-%Y %r') AS udate_created,
    DATE_FORMAT(terms_date_updated,'%a %d-%M-%Y %r') AS udate_updated
FROM users AS a
LEFT JOIN users_staff_details AS b
    ON a.user_id = b.users_staff_id_user
LEFT JOIN user_access AS c
    ON a.user_id_access_level = c.user_access_id
LEFT JOIN businesses AS d
    ON a.user_id_client = d.business_id
LEFT JOIN terms AS e
    ON e.terms_id_client = 'AA0000001'
    AND e.terms_id_store = '1'
    AND e.terms_id_user = 1001
    AND e.terms_datatype = 'users_staff_driving_license'
WHERE
    a.user_id = '$qs'

无法正常运行的查询语句 #

SELECT
    *,
    'users_staff_driving_license' AS driving_license_link,
    DATE_FORMAT(terms_date_created,'%a %d-%M-%Y %r') AS udate_created,
    DATE_FORMAT(terms_date_updated,'%a %d-%M-%Y %r') AS udate_updated
FROM users AS a
LEFT JOIN users_staff_details AS b
    ON a.user_id = b.users_staff_id_user
LEFT JOIN user_access AS c
    ON a.user_id_access_level = c.user_access_id
LEFT JOIN businesses AS d
    ON a.user_id_client = d.business_id
LEFT JOIN terms AS e
    ON e.terms_id_client = 'AA0000001'
    AND e.terms_id_store = '1'
    AND e.terms_id_user = '$qs'
    AND e.terms_datatype = 'users_staff_driving_license'
WHERE
    a.user_id = '$qs'

原因说明 #

两个查询的核心差异是terms表关联条件中e.terms_id_user的取值：

• 正常查询中该值是固定值1001，只要主表users存在user_id = $qs的记录就会返回结果，terms表匹配不到只会让对应字段返回NULL，不会导致整行数据消失
• 异常查询中该值改为和$qs匹配，此时只有terms表同时存在符合terms_id_user = $qs、terms_id_client = 'AA0000001'、terms_id_store = '1'、terms_datatype = 'users_staff_driving_license'的记录，才能关联到terms表的数据
• 无数据返回的常见原因有两个：
  1. 对应$qs的用户ID在terms表中没有符合全部关联条件的记录，且后续业务代码判断了terms表相关字段非空才返回，过滤掉了所有行
  2. e.terms_id_user是数值类型，你给$qs加了单引号转为字符串，隐式类型转换失败导致关联匹配不到

修复方案 #

1. 先查询terms表确认是否存在符合关联条件的记录，没有的话要么调整关联条件，要么移除后续代码中对terms表字段的非空过滤
2. 如果e.terms_id_user是数值类型，去掉$qs两侧的单引号，改成AND e.terms_id_user = $qs避免类型转换问题

内容的提问来源于stack exchange，提问作者R. Moshiur