MySQL 如何使用 LIKE 通配符在列中搜索（不区分大小写）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2876789/
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
How can I search (case-insensitive) in a column using LIKE wildcard?
提问by David Morrow
I looked around some and didn't find what I was after so here goes.
我环顾四周，没有找到我想要的东西，所以就这样了。
SELECT * FROM trees WHERE trees.`title` LIKE '%elm%'
This works fine, but not if the tree is named Elm or ELM etc...
这工作正常，但如果树被命名为 Elm 或 ELM 等，则不行...
How do I make SQL case insensitive for this wild-card search?
如何使此通配符搜索的 SQL 不区分大小写？
I'm using MySQL 5 and Apache.
我正在使用 MySQL 5 和 Apache。
采纳答案by Quassnoi
SELECT *
FROM trees
WHERE trees.`title` COLLATE UTF8_GENERAL_CI LIKE '%elm%'
Actually, if you add COLLATE UTF8_GENERAL_CIto your column's definition, you can just omit all these tricks: it will work automatically.
实际上，如果您添加COLLATE UTF8_GENERAL_CI到列的定义中，您可以省略所有这些技巧：它会自动工作。
ALTER TABLE trees
MODIFY COLUMN title VARCHAR(…) CHARACTER
SET UTF8 COLLATE UTF8_GENERAL_CI.
This will also rebuild any indexes on this column so that they could be used for the queries without leading '%'
这也将重建此列上的所有索引，以便它们可以用于查询而无需前导“%”
回答by cwallenpoole
I've always solved this using lower:
我总是使用lower解决这个问题：
SELECT * FROM trees WHERE LOWER( trees.title ) LIKE '%elm%'
回答by aioobe
The case sensitivity is defined in the columns / tables / database collation settings. You can do the query under a specific collation in the following way:
区分大小写是在列/表/数据库整理设置中定义的。您可以通过以下方式在特定排序规则下进行查询：
SELECT *
FROM trees
WHERE trees.`title` LIKE '%elm%' COLLATE utf8_general_ci
for instance.
例如。
(Replace utf8_general_ciwith whatever collation you find useful). The _cistands for case insensitive.
（替换utf8_general_ci为您认为有用的任何排序规则）。该_ci代表不区分大小写。
回答by Federico Piragua
This is the example of a simple LIKE query:
这是一个简单的 LIKE 查询示例：
SELECT * FROM <table> WHERE <key> LIKE '%<searchpattern>%'
Now, case-insensitive using LOWER() func:
现在，使用 LOWER() 函数不区分大小写：
SELECT * FROM <table> WHERE LOWER(<key>) LIKE LOWER('%<searchpattern>%')
回答by Vi8L
Simply use :
只需使用：
"SELECT * FROM `trees` WHERE LOWER(trees.`title`) LIKE '%elm%'";
Or Use
或使用
"SELECT * FROM `trees` WHERE LCASE(trees.`title`) LIKE '%elm%'";
Both functions works same
两个功能的工作原理相同
回答by Dave Doga Oz
I'm doing something like that.
我正在做类似的事情。
Getting the values in lowercase and MySQL does the rest
以小写形式获取值，剩下的由 MySQL 完成
$string = $_GET['string'];
mysqli_query($con,"SELECT *
FROM table_name
WHERE LOWER(column_name)
LIKE LOWER('%$string%')");
And For MySQL PDO Alternative:
对于 MySQL PDO 替代方案：
$string = $_GET['string'];
$q = "SELECT *
FROM table_name
WHERE LOWER(column_name)
LIKE LOWER(?);";
$query = $dbConnection->prepare($q);
$query->bindValue(1, "%$string%", PDO::PARAM_STR);
$query->execute();
回答by cgupta
I think this query will do a case insensitive search:
我认为此查询将进行不区分大小写的搜索：
SELECT * FROM trees WHERE trees.`title` ILIKE '%elm%';
回答by Lopofsky
You don't need to ALTERany table. Just use the following queries, prior to the actual SELECTquery that you want to use the wildcard:
你不需要ALTER任何桌子。只需在SELECT要使用通配符的实际查询之前使用以下查询：
set names `utf8`;
SET COLLATION_CONNECTION=utf8_general_ci;
SET CHARACTER_SET_CLIENT=utf8;
SET CHARACTER_SET_RESULTS=utf8;
回答by ABS zarzis
use ILIKE
用 ILIKE
SELECT * FROM trees WHERE trees.`title` ILIKE '%elm%';
it worked for me !!
它对我有用！！
回答by user21546
well in mysql 5.5 , like operator is insensitive...so if your vale is elm or ELM or Elm or eLM or any other , and you use like '%elm%' , it will list all the matching values.
在 mysql 5.5 中，像操作符是不敏感的......所以如果你的 vale 是 elm 或 ELM 或 Elm 或 eLM 或任何其他，并且你使用 like '%elm%' ，它会列出所有匹配的值。
I cant say about earlier versions of mysql.
我不能说早期版本的 mysql。
If you go in Oracle , like work as case-sensitive , so if you type like '%elm%' , it will go only for this and ignore uppercases..
如果你进入 Oracle ，就像区分大小写一样，所以如果你输入 '%elm%' ，它只会为此而忽略大写..
Strange , but this is how it is :)
奇怪，但事实就是这样:)
