MySQL LEFT JOIN查询中ON子句多列匹配及NULL值处理问题求助

嘿，我来帮你搞定这个LEFT JOIN多列匹配的问题！先拆解下你遇到的两个核心问题：

• 用OR连接ON子句条件时，不是只有第一个条件生效，而是你的GROUP BY和HAVING逻辑拖了后腿。当一本书匹配多个分类时，GROUP BY b.book_name会只保留其中一条匹配记录，这时候HAVING里的cat.cats_name = '$search_data'可能刚好筛掉了你要的结果。
• 用AND的话，意味着一本书的所有cats_id列（从cats_id到cats_id6）都得等于同一个分类ID，这显然和你的需求不符，所以才会没有结果或者看起来“报错”。

下面给你几个可行的解决方案：

方案1：用IN简化多列匹配逻辑 #

把一堆OR的条件换成IN，代码更简洁，逻辑完全等价，可读性还更高：

SELECT 
    b.book_name, b.book_id, b.cats_id, b.cats_id1, b.cats_id2, 
    b.cats_id3, b.cats_id4, b.cats_id5, b.cats_id6, b.book_rating, 
    b.book_author, b.book_stock, b.book_publisher, b.book_front_img, 
    b.book_status, p.publisher_id, p.publisher_name, a.author_id, 
    a.author_name, cat.cats_id, cat.cats_name, cat.cats_status 
FROM `books` AS b 
LEFT JOIN `publisher` AS p ON b.book_publisher = p.publisher_id 
LEFT JOIN `author` AS a ON b.book_author = a.author_id 
LEFT JOIN categorys As cat ON cat.cats_id IN (b.cats_id, b.cats_id1, b.cats_id2, b.cats_id3, b.cats_id4, b.cats_id5, b.cats_id6)
WHERE b.book_status = 1 AND cat.cats_name = '$search_data'
GROUP BY b.book_id  -- 用book_id分组更靠谱，book_name可能重复
ORDER BY $sorting 
LIMIT $offset, $page_limit

这里做了几个关键调整：

• 把OR换成IN，简化条件，逻辑和原来的OR完全一致
• 把b.book_status = 1和cat.cats_name = '$search_data'从HAVING移到WHERE里，行级筛选比分组后筛选效率更高，逻辑也更清晰
• 用b.book_id分组，代替book_name——毕竟book_id是主键，能唯一标识一本书，避免book_name重复导致的分组错误

如果你的categorys表中存在cats_id为NULL的情况，且需要匹配books中那些为NULL的cats_id列，IN不会自动匹配NULL（因为NULL IN (...)结果是NULL），这时候要加额外条件：

LEFT JOIN categorys As cat ON 
    cat.cats_id IN (b.cats_id, b.cats_id1, b.cats_id2, b.cats_id3, b.cats_id4, b.cats_id5, b.cats_id6)
    OR (cat.cats_id IS NULL AND (b.cats_id IS NULL OR b.cats_id1 IS NULL OR b.cats_id2 IS NULL OR b.cats_id3 IS NULL OR b.cats_id4 IS NULL OR b.cats_id5 IS NULL OR b.cats_id6 IS NULL))

方案2：用<=>运算符处理NULL的精确匹配 #

如果你需要严格匹配NULL（也就是当books的某个cats_id列是NULL，且categorys的cats_id也是NULL时才算匹配），可以用MySQL专属的<=>运算符——它能安全比较NULL值，a <=> b会在a和b相等，或者两者都是NULL时返回true：

LEFT JOIN categorys As cat ON 
    cat.cats_id <=> b.cats_id 
    OR cat.cats_id <=> b.cats_id1 
    OR cat.cats_id <=> b.cats_id2 
    OR cat.cats_id <=> b.cats_id3 
    OR cat.cats_id <=> b.cats_id4 
    OR cat.cats_id <=> b.cats_id5 
    OR cat.cats_id <=> b.cats_id6

重要提醒：防范SQL注入 #

你的查询直接把$search_data、$sorting这类变量拼进SQL里，这存在严重的SQL注入风险！建议用预处理语句（比如PDO的prepare/execute，或者MySQLi的prepare/bind_param）来绑定参数，别直接拼接变量，不然很容易被攻击。

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