我有一个MySQL查询:
SELECT concat_ws(title,description) as concatenated HAVING concatenated LIKE '%SearchTerm%';
我的表使用MyISAM编码为utf8_general_ci。
搜索似乎区分大小写。
我无法解决这个问题。出了什么问题,我该怎么修复它?
我有一个MySQL查询:
SELECT concat_ws(title,description) as concatenated HAVING concatenated LIKE '%SearchTerm%';
我的表使用MyISAM编码为utf8_general_ci。
搜索似乎区分大小写。
我无法解决这个问题。出了什么问题,我该怎么修复它?
在性能方面更好的解决方案:
SELECT .... FROM .... WHERE `concatenated` LIKE BINARY '%SearchTerm%';
如果任何操作数是二进制字符串,则字符串比较区分大小写。
另一种选择是使用COLLATE
。
SELECT ....
FROM ....
WHERE `concatenated` like '%SearchTerm%' COLLATE utf8_bin;
SELECT 'a' LIKE 'A'; -- 1
; SELECT 'a' LIKE BINARY 'A'; -- 0
。所以二进制搜索是不区分大小写的。有异议吗? - kolypto试试这个:
SELECT LOWER(CONCAT_WS(title,description)) AS concatenated
WHERE concatenated LIKE '%searchterm%'
或者(让你看到区别)
SELECT LOWER(CONCAT_WS(title,description)) AS concatenated
WHERE concatenated LIKE LOWER('%SearchTerm%')
SELECT table.id
FROM table
WHERE LOWER(table.aTextField) LIKE LOWER('%SearchAnything%')
仅供参考,如果有帮助的话:
如https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html所述,对于默认字符集,非二进制字符串比较默认不区分大小写。
因此,执行不区分大小写比较的简单方法是将字段转换为CHAR、VARCHAR或TEXT类型。
这里有一个针对单个字段的检查示例:
SELECT * FROM table1 WHERE CAST(`field1` AS CHAR) LIKE '%needle%';
检查表模式中提到的CHARSET:
show create table xyz;
select name from xyz where name like '%Man%' COLLATE latin1_bin;
select name from xyz where name like '%Man%' COLLATE utf8_bin;
以下是对我有效的情况,字符集为latin1,MySQL版本为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)
utf8_general_ci
作为排序规则。如果将排序规则更改为utf8_general_ci
,则搜索将不区分大小写。
因此,一个可能的解决方案是更改排序规则。这是可用的代码:
SELECT title,description
FROM (
SELECT title,description, LOWER(CONCAT_WS(title,description)) AS concatenated
FROM table1
) AS Q
WHERE concatenated LIKE LOWER('%search%')
这个也可以运行:
SELECT LOWER(DisplayName) as DN
FROM Bidders
WHERE OrgID=45
HAVING DN like "cbbautos%"
LIMIT 10;