如何在MySQL上进行SQL区分大小写的字符串比较？
我有一个函数返回五个字符混合大小写。 如果我对这个字符串进行查询，它将返回值而不管大小写。
如何使MySQL字符串查询区分大小写？
10个解决方案
605 votes
好消息是，如果您需要进行区分大小写的查询，则很容易做到：
SELECT * FROM `table` WHERE BINARY `column` = 'value'
Craig White answered 2019-02-18T03:09:27Z
125 votes
[http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html]
默认字符集和排序规则是latin1和latin1_swedish_ci，因此非二进制字符串比较默认情况下不区分大小写。 这意味着如果使用col_name LIKE'a％'进行搜索，则会获得以A或a开头的所有列值。 要使此搜索区分大小写，请确保其中一个操作数具有区分大小写或二进制排序规则。 例如，如果要比较具有latin1字符集的列和字符串，则可以使用COLLATE运算符使任一操作数具有latin1_general_cs或latin1_bin排序规则：
col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin
如果希望始终以区分大小写的方式处理列，请使用区分大小写或二进制排序规则来声明它。
drudge answered 2019-02-18T03:09:02Z
33 votes
您可能希望使用LIKE或LIKE BINARY，而不是使用=运算符
// this returns 1 (true)
select 'A' like 'a'
// this returns 0 (false)
select 'A' like binary 'a'
select * from user where username like binary 'a'
它的状态需要'a'而不是'A'
insoftservice answered 2019-02-18T03:09:59Z
24 votes
克雷格怀特发布的答案，性能损失很大
SELECT * FROM `table` WHERE BINARY `column` = 'value'
因为它不使用索引。 因此，您需要更改表格排序，如此处提及[https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html。]
要么
最简单的修复，您应该使用值的BINARY。
SELECT * FROM `table` WHERE `column` = BINARY 'value'
例如。
mysql> EXPLAIN SELECT * FROM temp1 WHERE BINARY col1 = "ABC" AND col2 = "DEF" ;
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | temp1 | ALL | NULL | NULL | NULL | NULL | 190543 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
VS
mysql> EXPLAIN SELECT * FROM temp1 WHERE col1 = BINARY "ABC" AND col2 = "DEF" ;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
| 1 | SIMPLE | temp1 | range | col1_2e9e898e | col1_2e9e898e | 93 | NULL | 2 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
enter code here
1排(0.00秒)
Nitesh answered 2019-02-18T03:10:50Z
13 votes
要在使用BINARY之前使用索引，如果你有大表，你可以这样做。
SELECT
*
FROM
(SELECT * FROM `table` WHERE `column` = 'value') as firstresult
WHERE
BINARY `column` = 'value'
子查询将导致一个非常小的不区分大小写的子集，然后您可以选择唯一区分大小写的匹配。
Eric answered 2019-02-18T03:11:22Z
7 votes
以下是MySQL版本等于或高于5.5。
添加到/etc/mysql/my.cnf
[mysqld]
...
character-set-server=utf8
collation-server=utf8_bin
...
我尝试的所有其他排序规则似乎都不区分大小写，只有“utf8_bin”才有效。
不要忘记在此之后重启mysql：
sudo service mysql restart
根据[http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html]，还有一个“latin1_bin”。
mysql启动不接受“utf8_general_cs”。 (我将“_cs”视为“区分大小写” - ???)。
fritzthecat answered 2019-02-18T03:12:21Z
4 votes
你可以像这样使用BINARY来区分大小写
select * from tb_app where BINARY android_package='com.Mtime';
不幸的是，这个sql无法使用索引，您将在依赖于该索引的查询中遭受性能损失
mysql> explain select * from tb_app where BINARY android_package='com.Mtime';
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | tb_app | NULL | ALL | NULL | NULL | NULL | NULL | 1590351 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
幸运的是，我有一些技巧来解决这个问题
mysql> explain select * from tb_app where android_package='com.Mtime' and BINARY android_package='com.Mtime';
+----+-------------+--------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | tb_app | NULL | ref | idx_android_pkg | idx_android_pkg | 771 | const | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
xiezefan answered 2019-02-18T03:12:59Z
1 votes
优秀！
我与您分享来自比较密码的函数的代码：
SET pSignal =
(SELECT DECODE(r.usignal,'YOURSTRINGKEY') FROM rsw_uds r WHERE r.uname =
in_usdname AND r.uvige = 1);
SET pSuccess =(SELECT in_usdsignal LIKE BINARY pSignal);
IF pSuccess = 1 THEN
/*Your code if match*/
ELSE
/*Your code if don't match*/
END IF;
Victor Enrique answered 2019-02-18T03:13:30Z
1 votes
无需在数据库级别上进行任何更改，只需在SQL查询中进行更改即可。
示例 -
"SELECT * FROM
二进制关键字将区分大小写。
Pappu Mehta answered 2019-02-18T03:14:10Z
0 votes
默认情况下，mysql不区分大小写，请尝试将语言排序规则更改为latin1_general_cs
ohmusama answered 2019-02-18T03:14:35Z
