像 MySQL 中区分大小写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8083455/
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 Case Sensitive in MySQL
提问by Pmc Machinery
I have a MySQL query:
我有一个 MySQL 查询：
SELECT concat_ws(title,description) as concatenated HAVING concatenated LIKE '%SearchTerm%';
And my table is encoded utf8_general_ci with MyISAM.
我的表是用 MyISAM 编码的 utf8_general_ci 。
Searches seem to be case sensitive.
搜索似乎区分大小写。
I can't figure out how to fix it. What's going wrong and/or how do I fix it?
我不知道如何修复它。出了什么问题和/或我该如何解决？
采纳答案by Marco
Try this:
尝试这个：
SELECT LOWER(CONCAT_WS(title,description)) AS concatenated
WHERE concatenated LIKE '%searchterm%'
or (to let you see the difference)
或（让您看到差异）
SELECT LOWER(CONCAT_WS(title,description)) AS concatenated
WHERE concatenated LIKE LOWER('%SearchTerm%')
回答by kolypto
A much better solution in terms of performance:
在性能方面更好的解决方案：
SELECT .... FROM .... WHERE `concatenated` LIKE BINARY '%SearchTerm%';
String comparision is case-sensitive when any of the operands is a binary string.
当任何操作数是二进制字符串时，字符串比较区分大小写。
Another alternative is to use COLLATE,
另一种选择是使用COLLATE，
SELECT ....
FROM ....
WHERE `concatenated` like '%SearchTerm%' COLLATE utf8_bin;
回答by Malkocoglu
In this method, you do not have to select the searched field:
在此方法中，您不必选择搜索字段：
SELECT table.id
FROM table
WHERE LOWER(table.aTextField) LIKE LOWER('%SearchAnything%')
回答by Cédric Fran?oys
Just for completion, in case it helps:
只是为了完成，以防万一：
As stated on https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html, for default character sets, nonbinary string comparisons are case insensitive by default.
如https://dev.mysql.com/doc/refman/5.7/en/case-sensitive.html 所述，对于默认字符集，默认情况下非二进制字符串比较不区分大小写。
Therefore, an easy way to perform case-insensitive comparisons is to cast the field to CHAR, VARCHAR or TEXT type.
因此，执行不区分大小写比较的一种简单方法是将字段强制转换为 CHAR、VARCHAR 或 TEXT 类型。
Here is an example with a check against a single field:
这是对单个字段进行检查的示例：
SELECT * FROM table1 WHERE CAST(`field1` AS CHAR) LIKE '%needle%';
回答by minhas23
Check CHARSET mentioned in the table schema:
检查表架构中提到的CHARSET：
show create table xyz;
Based on CHARSET, you can try the following.
基于CHARSET，您可以尝试以下操作。
select name from xyz where name like '%Man%' COLLATE latin1_bin;
select name from xyz where name like '%Man%' COLLATE utf8_bin;
Following are the cases which worked for me, CHARSET=latin1, MySQL version = 5.6.
以下是对我有用的案例，CHARSET=latin1，MySQL version = 5.6。
mysql> select installsrc from appuser where installsrc IS NOT NULL and installsrc like 'Promo%' collate latin1_bin limit 1;
+-----------------------+
| installsrc |
+-----------------------+
| PromoBalance_SMS,null |
+-----------------------+
1 row in set (0.01 sec)
mysql>
mysql> select installsrc from appuser where installsrc IS NOT NULL and installsrc like 'PROMO%' collate latin1_bin limit 1;
+---------------------------+
| installsrc |
+---------------------------+
| PROMO_SMS_MISSEDCALL,null |
+---------------------------+
1 row in set (0.00 sec)
mysql> select installsrc from appuser where installsrc IS NOT NULL and installsrc like 'PROMO%' limit 1;
+-----------------------+
| installsrc |
+-----------------------+
| PromoBalance_SMS,null |
+-----------------------+
1 row in set (0.01 sec)
回答by neophyte
This problem is occurring in this case because of the collation used in the table. You have used utf8_general_cias collation. If the collation is changed to utf8_general_cithen the searches will not be case sensitive.
So, one possible solution is to change the collation.
由于表中使用的排序规则，在这种情况下会出现此问题。您已用作utf8_general_ci整理。如果排序规则更改为，utf8_general_ci则搜索将不区分大小写。因此，一种可能的解决方案是更改排序规则。
回答by Andrey Soldatov
This is the working code:
这是工作代码：
SELECT title,description
FROM (
SELECT title,description, LOWER(CONCAT_WS(title,description)) AS concatenated
FROM table1
) AS Q
WHERE concatenated LIKE LOWER('%search%')
回答by david
This works also:
这也有效：
SELECT LOWER(DisplayName) as DN
FROM Bidders
WHERE OrgID=45
HAVING DN like "cbbautos%"
LIMIT 10;
