在一个 mysql 查询中喜欢和不喜欢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/995971/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
like and not like in one mysql query
提问by Maurice Kroon
I want to do a query containing 'like' and 'not like'.
我想做一个包含“喜欢”和“不喜欢”的查询。
Current example: i want everything starting with '1|%' but not with '1|6|199|%' or '1|6|200|%'.
当前示例：我想要以 '1|%' 开头的所有内容，但不以 '1|6|199|%' 或 '1|6|200|%' 开头。
Current query:
当前查询：
'SELECT * FROM `links` WHERE `category` LIKE '1|%' NOT LIKE '1|6|199|%','1|6|200|%' ORDER BY `score` DESC LIMIT 9'.
But that doesn't work. Any tips? thx
但这不起作用。有小费吗？谢谢
回答by Michael Haren
Just add "and category"...
只需添加“和类别”...
SELECT * FROM links
WHERE category LIKE '1|%'
AND category NOT LIKE '1|6|199|%','1|6|200|%'
ORDER BY score DESC LIMIT 9
Actually, the comma separated condition is not a syntax I'm familiar with. If that's not working, try this instead:
实际上，逗号分隔的条件不是我熟悉的语法。如果这不起作用，请尝试以下操作：
SELECT * FROM links
WHERE category LIKE '1|%'
AND category NOT LIKE '1|6|199|%'
AND category NOT LIKE '1|6|200|%'
ORDER BY score DESC LIMIT 9
回答by Quassnoi
You can use regexps:
您可以使用regexps：
SELECT *
FROM links
WHERE category LIKE '1|%'
AND category NOT REGEXP '^1\|6\|(199|200)\|'
ORDER BY
score DESC
LIMIT 9
Note that REGEXP's don't use indexes, while LIKEdoes.
请注意，REGEXP's 不使用索引，而LIKE使用。
In this query, LIKE '1|%'will serve as a coarse filter using the index on categoryif any, while REGEXP's will fine filter the results.
在此查询中，LIKE '1|%'将使用索引作为粗过滤器（category如果有），而REGEXP's 将精细过滤结果。
回答by longneck
I think a bigger problem is that you have de-normalized tables. The correct answer would be to normalize your tables.
我认为一个更大的问题是你有非规范化的表。正确的答案是标准化你的表格。
But if you can't do that, you should use commas as separators and FIND_IN_SET()instead:
但如果你不能这样做，你应该使用逗号作为分隔符，FIND_IN_SET()而不是：
WHERE FIND_IN_SET('1', category) > 1
AND FIND_IN_SET('6', category) > 1
AND FIND_IN_SET('199', category) = 0
AND FIND_IN_SET('200', category) = 0
回答by Ash
It is also possible to use two inner join, probably not the best solution for this query, but could still be useful.
也可以使用两个内部联接，这可能不是此查询的最佳解决方案，但仍然有用。
SELECT * FROM links
INNER JOIN (SELECT * FROM links WHERE category NOT LIKE '1|6|199|%') AS escl1 ON (links.category=escl1.category)
INNER JOIN (SELECT * FROM links WHERE category NOT LIKE '1|6|200|%') AS escl2 ON (links.category=escl2.category)
WHERE category LIKE '1|%'
ORDER BY
scoreDESC LIMIT 9
选择 * 从链接
INNER JOIN (SELECT * FROM links WHERE category NOT LIKE '1|6|199|%') AS escl1 ON (links.category=escl1.category)
INNER JOIN (SELECT * FROM links WHERE category NOT LIKE '1|6|200|%') AS escl2 ON (links.category=escl2.category)
WHERE 类别 LIKE '1|%'
按
scoreDESC 限制订购9
