SQL LIMIT 不返回结果 where no LIMIT returns results

Question

SELECT * FROM mm_tfs 
WHERE product_slug LIKE '%football%' 
    AND schoolid = '8' AND category_id ='21'
LIMIT 4

像我问的那样返回 4 个值，但以下语句返回 0 - 是否有我不熟悉的关于使用 OR 语句的规则？我的假设是它应该返回 1 中的所有值(如果不是限制，则返回更多)。

SELECT * FROM mm_tfs 
WHERE (product_slug LIKE '%football%' 
   AND schoolid = '8' AND category_id ='21') 
OR (product_description LIKE '%football%' 
   AND schoolid = '8' AND category_id ='21') 
LIMIT 4

_{NOTE by cyberkiwi Q2 的第一个 OR 部分与 Q1 的 WHERE 子句完全相同}

product_description LIKE '%football%' AND schoolid = '8' AND category_id ='21

没有 OR 语句本身确实会产生所需的结果，只要它没有限制。当 OR 语句也与 LIMIT 一起使用时，将返回 0 个值。

SELECT * FROM mm_tfs 
WHERE product_description LIKE '%football%' 
    AND schoolid = '8' AND category_id ='21'
LIMIT 4

^-- 产生 0 个结果

SELECT * FROM mm_tfs 
WHERE product_description LIKE '%football%' 
    AND schoolid = '8' AND category_id ='21'

^-- 产生结果

最奇怪的是，所有这些查询都在我的 PHPMYADMIN SQL 查询窗口中产生了正确的效果，但在应用程序本身却没有。

Answer 1

您重复了一些不必要的条件。试试这个:

SELECT * FROM mm_tfs 
WHERE 
   (product_slug LIKE '%football%' 
 OR product_description LIKE '%football%')
   AND schoolid = '8' 
   AND category_id ='21'
LIMIT 4

更新:

我创建了下表:

create table mm_tfs2 (schoolid varchar(2), categoryid varchar(2), 
                      description varchar(20), slug varchar(20));

5 次:

insert into mm_tfs2 values (8, 21, '', 'football');

最后是查询:

select * from mm_tfs2 where 
(slug like '%football%' and schoolid = 8 and categoryid = 21) 
or (description like '%football%' and schoolid = 8 and categoryid = 21) 
limit 4;
+----------+------------+-------------+----------+
| schoolid | categoryid | description | slug     |
+----------+------------+-------------+----------+
| 8        | 21         |             | football |
| 8        | 21         |             | football |
| 8        | 21         |             | football |
| 8        | 21         |             | football |
+----------+------------+-------------+----------+
4 rows in set (0.00 sec)

很抱歉，我无法重现问题。