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

#1 楼

http://dev.mysql.com/doc/refman/5.0/zh-CN/case-sensitiveivity.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

如果要始终以区分大小写的方式处理列，请使用区分大小写或二进制排序规则对其进行声明。

#2 楼

好消息是，如果您需要进行区分大小写的查询，则很容易做到：

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

#3 楼

克雷格·怀特（Craig White）发布的答案对性能造成了很大的影响

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

，因为它不使用索引。因此，您需要更改表排序规则，例如在这里提到https://dev.mysql.com/doc/refman/5.7/en/case-sensitiveivity.html。
OR
最简单的解决方法是，应该使用值的BINARY。

#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'

#5 楼

在不更改要查询的列的排序规则的情况下执行区分大小写的字符串比较的最正确方法是为要比较的列的值显式指定一个字符集和排序规则。

select * from `table` where `column` = convert('value' using utf8mb4) collate utf8mb4_bin;

为什么不使用binary？

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

为什么要同时使用convert和collate？

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

为什么将convert和collate应用于值而不是列？

在进行比较之前，对列应用任何转换功能时，如果该列存在索引，则查询引擎将无法使用索引，这可能会大大降低查询速度。因此，最好总是尽可能地转换值。当在两个字符串值之间执行比较并且其中一个具有显式指定的排序规则时，查询引擎将使用该显式排序规则，而不考虑将其应用于哪个值。

重音敏感度

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

utf8mb4是什么？

MySql中的utf8字符集是一个utf8mb3的别名，由于它不支持4字节字符（在对诸如🐈之类的字符串进行编码时很重要），因此在最近版本中已弃用。如果希望对MySql使用UTF8字符编码，则应使用utf8mb4字符集。

#6 楼

要在使用BINARY之前先使用索引，如果表很大，可以执行以下操作。

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

子查询将导致非常小的情况-不区分大小写的子集，然后选择唯一的区分大小写的匹配项。

#7 楼

以下是等于或高于5.5的MySQL版本。

添加到/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/zh-CN/case-sensitivity.html也有一个“ latin1_bin”。

mysql启动不接受“ utf8_general_cs” 。 （我将“ _cs”读为“区分大小写”-???）。

#8 楼

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

#9 楼

太好了！

我与您分享了比较密码功能的代码：

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;

#10 楼

无需在数据库级别上进行任何更改，只需更改SQL Query即可。

示例-

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

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

#11 楼

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