MySQL SQL SELECT LIKE（不区分大小写）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18853452/
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
SQL SELECT LIKE (Insensitive casing)
提问by user2583714
I am trying to execute the sql query:
我正在尝试执行 sql 查询：
select * from table where column like '%value%';
But the data is saved as 'Value' ( V is capital ).
但数据保存为“值”（ V 是大写）。
When I execute this query i don't get any rows. How do i make the call such that, it looks for 'value' irrespective of the casing of the characters ?
当我执行这个查询时，我没有得到任何行。我如何进行调用，无论字符的大小写如何，它都会查找“值”？
回答by JGutierrezC
use LOWER Function in both (column and search word(s)). Doing it so, you assure that the even if in the query is something like %VaLuE%, it wont matter
在（列和搜索词）中使用 LOWER 函数。这样做，您可以确保即使查询中的内容类似于 %VaLuE%，也没关系
select qt.*
from query_table qt
where LOWER(column_name) LIKE LOWER('%vAlUe%');
回答by kmas
If you want this column be case insensitive :
如果您希望此列不区分大小写：
ALTER TABLE `schema`.`table`
CHANGE COLUMN `column` `column` TEXT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
Thus, you don't have to change your query.
因此，您不必更改查询。
And the MySQL engine will process your query quicker than using lower() function or any other tricks.
并且 MySQL 引擎将比使用 lower() 函数或任何其他技巧更快地处理您的查询。
And I'm not sure that using lower function will be a good solution for index searching performance.
而且我不确定使用较低的函数将是索引搜索性能的一个很好的解决方案。
回答by Marc B
Either use a case-insensitive collationon your table, or force the valuesto be lower case, e.g.
在您的表上使用不区分大小写的排序规则，或强制将值设为小写，例如
WHERE lower(column) LIKE lower('%value%');
回答by Gordon Linoff
Use the lower()function:
使用lower()函数：
select t.*
from table t
where lower(column) like '%value%';
回答by juergen d
Try using a case insensitive collation
尝试使用不区分大小写的排序规则
select * from table
where column like '%value%' collate utf8_general_ci
回答by Govindu Surla
you should use either loweror upperfunction to ignore the case while you are searching for some field using like.
在使用 like 搜索某个字段时，您应该使用lower或upper函数来忽略大小写。
select * from student where upper(sname) like 'S%';
OR
或者
select * from student where lower(sname) like 'S%';
回答by Alter Lagos
If you are using PostgreSQL, a simpler solution is to use insensitive like (ILIKE):
如果您使用的是 PostgreSQL，一个更简单的解决方案是使用insensitive like (ILIKE)：
SELECT * FROM table WHERE column ILIKE '%value%'
回答by Marko Bonaci
I know this is a very old question, but I'm posting this for posterity:
Non-binary string comparisons (including LIKE) are case-insensitive by default in MySql:
https://dev.mysql.com/doc/refman/en/case-sensitivity.html
我知道这是一个非常古老的问题，但我将这个问题发布给后代：在 MySql 中，默认情况下，
非二进制字符串比较（包括LIKE）不区分大小写：https:
//dev.mysql.com/doc/refman/en /大小写敏感.html
