[MySQL查询]如何区分大小写?

hbghlyj

例如下图
SELECT firstname FROM `MyGuests` WHERE `firstname`='mary';
输出2行:
mary
Mary
如何让MySQL区分大小写(只输出Mary)?

hbghlyj

MySQL 8.0 Reference Manual  /  ...  /  Case Sensitivity in String Searches

For nonbinary strings (CHAR, VARCHAR, TEXT), string searches use the collation of the comparison operands. For binary strings (BINARY, VARBINARY, BLOB), comparisons use the numeric values of the bytes in the operands; this means that for alphabetic characters, comparisons are case-sensitive.

A comparison between a nonbinary string and binary string is treated as a comparison of binary strings.

Simple comparison operations (>=, >, =, <, <=, sorting, and grouping) are based on each character's “sort value.” Characters with the same sort value are treated as the same character. For example, if e and é have the same sort value in a given collation, they compare as equal.

所以需将collation(原先是utf8_general_ci)改成utf8_general_cs(这里ci/cs应为case insensitive/case sensitive的缩写)或者utf8mb4_bin(转换为binary,按上文所说,binary类型的数据是按数值排序的,所以总是可以分出先后)

hbghlyj

不改变原表结构的方法: 加上binary关键字
SELECT firstname FROM `MyGuests` WHERE binary `firstname`='mary';
参考:juejin.cn/post/6871780646939803655