blocks|key|867347|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|867348|SELECT+LOWER(CONCAT_WS(title,description))+AS+concatenated+
WHERE+concatenated+LIKE+'%25searchterm%25'|code-block|syntax|javascript|867349|或者(让你看看有什么不同)|867350|SELECT+LOWER(CONCAT_WS(title,description))+AS+concatenated+
WHERE+concatenated+LIKE+LOWER('%25SearchTerm%25')|867351|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Try this:

<pre><code>SELECT LOWER(CONCAT_WS(title,description)) AS concatenated 
WHERE concatenated LIKE '%searchterm%'
</code></pre>

or (to let you see the difference)

<pre><code>SELECT LOWER(CONCAT_WS(title,description)) AS concatenated 
WHERE concatenated LIKE LOWER('%SearchTerm%')
</code></pre>

blocks|key|5174467|text|在性能方面更好的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5174468|SELECT+....+FROM+....+WHERE+`concatenated`+LIKE+BINARY+'%25SearchTerm%25';|code-block|syntax|javascript|5174469|当任何操作数是二进制字符串时，字符串比较区分大小写。|5174470|另一种选择是使用COLLATE，|offset|length|style|CODE|5174471|SELECT+....
FROM+....
WHERE+`concatenated`+like+'%25SearchTerm%25'+COLLATE+utf8_bin;|5174472|entityMap^0|0|0|0|8|7|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|V|8|@$K|W|L|X|M|N]]|9|@]|A|$]]|$1|O|3|P|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

A much better solution in terms of performance:

<pre><code>SELECT .... FROM .... WHERE `concatenated` LIKE BINARY '%SearchTerm%';
</code></pre>

String comparision is case-sensitive when any of the operands is a binary string.

Another alternative is to use <code>COLLATE</code>,

<pre><code>SELECT ....
FROM ....
WHERE `concatenated` like '%SearchTerm%' COLLATE utf8_bin;
</code></pre>

blocks|key|4890955|text|在此方法中，您不必选择搜索字段：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4890956|SELECT+table.id+
FROM+table
WHERE+LOWER(table.aTextField)+LIKE+LOWER('%25SearchAnything%25')|code-block|syntax|javascript|4890957|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In this method, you do not have to select the searched field:

<pre><code>SELECT table.id 
FROM table
WHERE LOWER(table.aTextField) LIKE LOWER('%SearchAnything%')
</code></pre>

blocks|key|4891000|text|查询表模式中提到的字符集：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4891001|show+create+table+xyz;|code-block|syntax|javascript|4891002|根据字符集，您可以尝试以下操作。|4891003|select+name+from+xyz+where+name+like+'%25Man%25'+COLLATE+latin1_bin;
select+name+from+xyz+where+name+like+'%25Man%25'+COLLATE+utf8_bin;|4891004|以下是适用于我的情况，CHARSET=latin1，MySQL版本=+5.6。|4891005|mysql>+select+installsrc+from+appuser+where+installsrc+IS+NOT+NULL+and+installsrc+like+'Promo%25'+collate+latin1_bin+limit+1;
%2B-----------------------%2B
%7C+installsrc++++++++++++%7C
%2B-----------------------%2B
%7C+PromoBalance_SMS,null+%7C
%2B-----------------------%2B
1+row+in+set+(0.01+sec)

mysql>
mysql>+select+installsrc+from+appuser+where+installsrc+IS+NOT+NULL+and+installsrc+like+'PROMO%25'+collate+latin1_bin+limit+1;
%2B---------------------------%2B
%7C+installsrc++++++++++++++++%7C
%2B---------------------------%2B
%7C+PROMO_SMS_MISSEDCALL,null+%7C
%2B---------------------------%2B
1+row+in+set+(0.00+sec)

mysql>+select+installsrc+from+appuser+where+installsrc+IS+NOT+NULL+and+installsrc+like+'PROMO%25'+limit+1;
%2B-----------------------%2B
%7C+installsrc++++++++++++%7C
%2B-----------------------%2B
%7C+PromoBalance_SMS,null+%7C
%2B-----------------------%2B
1+row+in+set+(0.01+sec)|4891006|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Check CHARSET mentioned in the table schema:

<pre><code>show create table xyz;
</code></pre>

Based on CHARSET, you can try the following.

<pre><code>select name from xyz where name like '%Man%' COLLATE latin1_bin;
select name from xyz where name like '%Man%' COLLATE utf8_bin;
</code></pre>

Following are the cases which worked for me, CHARSET=latin1, MySQL version = 5.6.

<pre><code>mysql&gt; 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&gt;
mysql&gt; 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&gt; 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)
</code></pre>

blocks|key|3277059|text|只是为了完成，以防它有帮助：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3277060|如https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html所述，对于缺省字符集，非二进制字符串比较缺省情况下不区分大小写。|offset|length|3277061|因此，执行不区分大小写的比较的一种简单方法是将字段强制转换为CHAR、VARCHAR或TEXT类型。|3277062|下面是一个对单个字段进行检查的示例：|3277063|SELECT+*+FROM+table1+WHERE+CAST(`field1`+AS+CHAR)+LIKE+'%25needle%25';|code-block|syntax|javascript|3277064|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html^0|0|1|1P|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@$D|Y|E|Z|1|10]]|A|$]]|$1|F|3|G|5|6|7|11|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|12|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|13|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

Just for completion, in case it helps: 

As stated on <a href="https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html</a>, for default character sets, nonbinary string comparisons are case insensitive by default.

Therefore, an easy way to perform case-insensitive comparisons is to cast the field to CHAR, VARCHAR or TEXT type.

Here is an example with a check against a single field:

<pre><code>SELECT * FROM table1 WHERE CAST(`field1` AS CHAR) LIKE '%needle%';
</code></pre>

blocks|key|3277011|text|由于表中使用的排序规则，在这种情况下会出现此问题。您已使用utf8_general_ci作为排序规则。如果排序规则更改为utf8_general_ci，则搜索将不区分大小写。因此，一种可能的解决方案是更改排序规则。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3277012|entityMap^0|T|F|1O|F|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

This problem is occurring in this case because of the collation used in the table. You have used <code>utf8_general_ci</code> as collation. If the collation is changed to <code>utf8_general_ci</code> then the searches will not be case sensitive.
So, one possible solution is to change the collation.

blocks|key|5174477|text|这是工作代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5174478|SELECT+title,description
FROM+(
+SELECT+title,description,+LOWER(CONCAT_WS(title,description))+AS+concatenated
+FROM+table1+
)+AS+Q
WHERE+concatenated+LIKE+LOWER('%25search%25')+|code-block|syntax|javascript|5174479|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This is the working code:

<pre><code>SELECT title,description
FROM (
 SELECT title,description, LOWER(CONCAT_WS(title,description)) AS concatenated
 FROM table1 
) AS Q
WHERE concatenated LIKE LOWER('%search%') 
</code></pre>

blocks|key|870415|text|这也是可行的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|870416|SELECT+LOWER(DisplayName)+as+DN
FROM+++Bidders
WHERE++OrgID=45
HAVING+DN+like+"cbbautos%25"
LIMIT++10;|code-block|syntax|javascript|870417|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This works also:

<pre><code>SELECT LOWER(DisplayName) as DN
FROM Bidders
WHERE OrgID=45
HAVING DN like "cbbautos%"
LIMIT 10;
</code></pre>

I have a MySQL query:

<pre><code>SELECT concat_ws(title,description) as concatenated HAVING concatenated LIKE '%SearchTerm%';
</code></pre>

And my table is encoded utf8_general_ci with MyISAM.

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?

Like Case Sensitive in MySQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个MySQL查询：SELECT concat_ws(title,description) as concatenated HAVING concatenated LIKE '%SearchTerm%';我的表是用MyISAM编码的utf8_general_ci。搜索似乎是区分大小写的。我想不出怎么修好它。哪里出了问题和/或我该如何修复它？

问就像MySQL中的区分大小写
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问就像MySQL中的区分大小写EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问就像MySQL中的区分大小写
EN