如何在 MySQL 上进行 SQL 区分大小写的字符串比较? [英] How can I make SQL case sensitive string comparison on MySQL?

问题描述

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

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

解决方案

http://dev.mysql.com/doc/refman/5.0/en/case-sensitive.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_cscol_name COLLATE latin1_bin LIKE 'a%'col_name LIKE 'a%' COLLATE latin1_bin

<块引用>

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

I have a function that returns five characters with mixed case. If I do a query on this string it will return the value regardless of case.

How can I make MySQL string queries case sensitive?

解决方案

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation:

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

If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation.

这篇关于如何在 MySQL 上进行 SQL 区分大小写的字符串比较?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！