我有一个函数,返回5个混合大小写字符。如果我对这个字符串进行查询,不管大小写,它都会返回值。

如何使MySQL字符串查询区分大小写?


当前回答

您可以像这样使用二进制区分大小写

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 |
+----+-------------+--------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+  

其他回答

Mysql默认情况下不区分大小写,请尝试将语言排序规则更改为latin1_general_cs

要在使用BINARY之前使用索引,如果有大型表,可以这样做。

SELECT
   *
FROM
   (SELECT * FROM `table` WHERE `column` = 'value') as firstresult
WHERE
   BINARY `column` = 'value'

子查询将生成一个非常小的不区分大小写的子集,然后选择其中唯一区分大小写的匹配项。

对于那些希望使用RLIKE或REGEXP与正则表达式进行区分大小写的比较的人来说,你可以使用REGEXP_LIKE()来匹配匹配类型c,如下所示:

SELECT * FROM `table` WHERE REGEXP_LIKE(`column`, 'value', 'c');

您可以像这样使用二进制区分大小写

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 |
+----+-------------+--------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+  

不使用=运算符,您可能希望使用LIKE或LIKE二进制

// 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