“=”的 MySQL 通配符 - 有没有
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/804381/
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
MySQL Wildcard for "=" - is there one
提问by Dan
So,
所以，
SELECT * FROM table WHERE col LIKE '%'
will return everything. Is there a wildcard for the query
将返回一切。查询是否有通配符
SELECT * FROM table WHERE col = '*'
Clearly *doesn't work, I just put it there to indicate where I'd like a wildcard. The column I'm selecting from contains an integer between 1 and 12, and I want to be able to select either all records with a particular number, or all records with a wildcard.
显然*不起作用，我只是把它放在那里以表明我想要通配符的位置。我从中选择的列包含 1 到 12 之间的整数，并且我希望能够选择具有特定数字的所有记录，或具有通配符的所有记录。
Thanks,
谢谢，
采纳答案by zombat
LIKE is basically the same as =, except LIKE lets you use wildcards.
LIKE 与 = 基本相同，但 LIKE 允许您使用通配符。
These two queries will return the same results:
这两个查询将返回相同的结果：
SELECT * FROM table WHERE col LIKE 'xyz';
SELECT * FROM table WHERE col='xyz';
Without a '%' in the LIKE query, it is effectively the same as '='.
LIKE 查询中没有“%”，它实际上与“=”相同。
If you're doing a selection on an integer column, you should consider using the IN() or BETWEEN operators. It sounds like you have two separate conditions that should be handled in your code however, rather than in the query, as your conditions dictate that you need at least two different kinds of queries.
如果您在整数列上进行选择，则应考虑使用 IN() 或 BETWEEN 运算符。听起来您应该在代码中而不是在查询中处理两个单独的条件，因为您的条件要求您至少需要两种不同类型的查询。
Edit: I should clarify that LIKE and = are similar only in normal, humdrum string comparison usage. You should check the MySQL Manualfor specifics on how it works, as there are situations where it's not the same (such as language sets).
编辑：我应该澄清 LIKE 和 = 仅在正常、单调的字符串比较用法中相似。您应该查看MySQL 手册以了解其工作原理，因为有些情况下它不相同（例如语言集）。
回答by Chad Birch
If you want to select everything, why are you attaching the WHERE clause at all? Just leave it off conditionally instead of putting a wildcard into it.
如果要选择所有内容，为什么还要附加 WHERE 子句？只需有条件地将其关闭，而不是将通配符放入其中。
回答by Joe Phillips
The reason for using LIKE is because the = does not offer wildcard support. Otherwise there would be no reason for LIKE
使用 LIKE 的原因是 = 不提供通配符支持。否则就没有理由喜欢
回答by chaos
SELECT * FROM table WHERE col RLIKE '.*'
i.e. regular-expression LIKE.
即正则表达式 LIKE。
回答by u07ch
Assuming your query is parameter driven a case statement is probably appropriate
假设您的查询是参数驱动的 case 语句可能是合适的
select * from mytable
where col like case when @myvariable is null then % else myvariable end
Where @myvariableis either null if you dont want a value otherwise it would use the integer value you pass in.
@myvariable如果您不想要一个值，则where要么为 null，否则它将使用您传入的整数值。
回答by artlung
zombat's answer is great, but I only noticed in his answer that you are selecting integers. He mentioned IN() and BETWEEN(). Here's examples using those syntaxes, as well as some other options you have for an integer field.
zombat 的回答很棒，但我只在他的回答中注意到您正在选择整数。他提到了 IN() 和 BETWEEN()。下面是使用这些语法的示例，以及整数字段的一些其他选项。
SELECT * FROM table WHERE col = 1;
SELECT * FROM table WHERE col BETWEEN 1 AND 12;
SELECT * FROM table WHERE col BETWEEN 6 AND 12;
SELECT * FROM table WHERE col <= 6;
SELECT * FROM table WHERE col < 6;
SELECT * FROM table WHERE col >= 6;
SELECT * FROM table WHERE col > 6;
SELECT * FROM table WHERE col <> 6;
SELECT * FROM table WHERE col IN (1,2,5,6,10);
SELECT * FROM table WHERE col NOT IN (1,2,5,6,10);
回答by Izik
I have encountered such a case while building a stored procedure for a report Following is my solution, hope this is what you had in mind :)
我在为报表构建存储过程时遇到过这样的情况以下是我的解决方案，希望这是您的想法:)
set @p = "ALL";
Query:
询问：
select * from fact_orders
where
dim_country_id = if(@p is null or @p="ALL", dim_country_id, @p)
limit 10
;
回答by Planodios
If your values are in the the range (1,12) then:
如果您的值在 (1,12) 范围内，则：
select * from table where col>=5 and col<=5; //this is equal to col=5
select * from table where col>=5 and col<=5; //这等于col=5
select * from table where col>=0 and col<=12; //this is equal to col=any value
select * from table where col>=0 and col<=12; //这等于col=any值
The same line can produce both effects by choosing the 2 parameters appropriately. I faced a similar problem when I needed a single prepared statement which should work with 2 different ways , either checking for a particular value in a column or ignoring that column completely.
通过适当地选择 2 个参数，同一条线可以产生两种效果。当我需要一个准备好的语句时，我遇到了类似的问题，该语句应该以 2 种不同的方式工作，要么检查列中的特定值，要么完全忽略该列。
