MySQL通配符表示“=”-是否有

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,

谢谢,

8 个解决方案

#1

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 Manual for specifics on how it works, as there are situations where it's not the same (such as language sets).

编辑:我应该澄清，LIKE和=仅在普通的、单调的字符串比较使用中是相似的。您应该检查MySQL手册以了解它是如何工作的，因为在某些情况下它是不相同的(比如语言集)。

#2

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子句呢?只是有条件地保留它，而不是放入通配符。

#3

The reason for using LIKE is because the = does not offer wildcard support. Otherwise there would be no reason for LIKE

使用LIKE的原因是=不提供通配符支持。否则就没有理由喜欢

#4

SELECT * FROM table WHERE col RLIKE '.*'

i.e. regular-expression LIKE.

例如,正则表达式。

#5

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.

宗巴特的回答很好，但我只在他的回答中注意到你在选择整数。他提到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);

#6

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 @myvariable is either null if you dont want a value otherwise it would use the integer value you pass in.

如果您不想要一个值，那么@myvariable是null，否则它将使用传入的整数值。

#7

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
;

#8

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

从>=5、col<=5的表中选择*;//这个等于col=5

select * from table where col>=0 and col<=12; //this is equal to col=any value

从col>=0、col<=12的表中选择*;//这个等于col=任何值

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.

通过适当地选择两个参数，同样的行可以产生这两种效果。当我需要一个准备好的语句时，我遇到了类似的问题，该语句应该以两种不同的方式工作，要么检查列中的某个值，要么完全忽略该列。

#1