如何在 MySQL 上进行 SQL 区分大小写的字符串比较？

Question

我有一个函数返回五个大小写混合的字符。如果我对此字符串进行查询，无论大小写，它都会返回值。

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

Answer 1

使用它来进行区分大小写的查询：

SELECT *  FROM `table` WHERE BINARY `column` = 'value'

Answer 2

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

如果您希望始终以区分大小写的方式处理列，请使用区分大小写或二进制排序规则来声明它。

Answer 3

Craig White 发布的答案有很大的性能损失，

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 秒）

Answer 4

您可能希望使用 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”

Answer 5

在不更改所查询列的排序规则的情况下执行区分大小写的字符串比较的最正确方法是为值<显式指定字符集和排序规则 /strong> （这重要，请阅读下面的原因）与该列进行比较。

SELECT *
FROM `table`
WHERE `column` = CONVERT('value' USING utf8mb4) COLLATE utf8mb4_bin;

为什么不使用BINARY？

不建议使用 BINARY 运算符，因为它会比较编码字符串的实际字节。如果您比较使用不同字符集编码的两个字符串的实际字节，则两个应被视为相同的字符串可能不相等。例如，如果您有一个使用 latin1 字符集的列，并且您的服务器/会话字符集是 utf8mb4，那么当您将该列与包含重音符号的字符串进行比较时例如“café”，它不会匹配包含相同字符串的行！这是因为在 latin1 中，é 被编码为字节 0xE9，但在 utf8 中它是两个字节：0xC3A9。

为什么要使用CONVERT 以及COLLATE？

排序规则必须与字符集匹配。因此，如果您的服务器或会话设置为使用 latin1 字符集，则必须使用 collat​​e latin1_bin，但如果您的字符集是 utf8mb4 您必须使用整理utf8mb4_bin。因此，最可靠的解决方案是始终转换 将值转换为最灵活的字符集，并为该字符集使用二进制排序规则。

为什么将 CONVERT 和 COLLATE 应用于值而不是列？

当您在进行比较之前对列应用任何转换函数时，它会阻止查询引擎使用索引（如果该列存在索引），这可能会显着减慢查询速度。因此，在可能的情况下转换值总是更好。当在两个字符串值之间执行比较并且其中一个具有显式指定的排序规则时，查询引擎将使用显式排序规则，无论它应用于哪个值。

重音敏感度

值得注意的是，MySql 不仅对使用 _ci 排序规则（通常是默认值）的列不区分大小写，而且对重音也不敏感。这意味着'é' = 'e'。使用二进制排序规则（或BINARY 运算符）将使字符串比较区分重音以及区分大小写。

什么是 utf8mb4？

MySQL中的utf8字符集是utf8mb3的别名，它已在最近版本中已弃用，因为它不支持 4 字节字符（这对于编码像

Answer 6

要在使用 BINARY 之前使用索引，如果您有大型表，可以执行类似的操作。

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

子查询将产生一个非常小的不区分大小写的子集，然后您可以在其中选择唯一区分大小写的匹配项。

Answer 7

您可以像这样使用 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 |
+----+-------------+--------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+  

Answer 8

以下适用于 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”读为“区分大小写” - ???）。

Answer 9

无需在数据库级别进行任何更改，只需更改 SQL 查询即可工作。

示例 -

"SELECT * FROM where userId = '" + iv_userId + "' AND password = BINARY '" + iv_password + "'";

二进制关键字将区分大小写。

Answer 10

出色的！

我与您分享一个比较密码的函数的代码：

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;

Answer 11

对于那些希望使用 RLIKE 或 REGEXP 与正则表达式进行区分大小写比较的人，您可以使用 REGEXP_LIKE() 匹配类型 c 像这样:

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

Answer 12

mysql 默认不区分大小写，尝试将语言排序规则更改为 latin1_general_cs