char、varchar、text等属于字符串类型。
在创建表时,如果含有字符串类型的列,一定要指定字符集排序方式,否则可能导致查询不符合预期,比如,字符串判等忽略了大小写。
有问题的建表语句
插入 HELLO 和 hello:
查询 content 为 hello 的数据:
结果,查到了两条。查询时隐式的忽略了大小写,这是不符合预期的。
对我而言,默认查询是不忽略大小写的,要忽略大小写必须显式说明。所以这里MySQL的表现是不符合预期的,或者说我没有用对。
忽略大小写的坏影响不止体现在简单查询,还会在 distinct、group by 等地方导致不符合预期的结果。因为 HELLO 和 hello 被认为是相同的字符串。
解决方案1:使用 binary
在查询条件前加上
解决方案2:指定字符集排序方式
我们在创建表时为表指定字符集排序方式(也可以单独为列指定指定排序方式,但这样会增加复杂度,慎用):
测试:
符合预期。
背后的原因
在不给列单独指定字符集的情况下,每张表都会使用一个字符集,也会用一个排序方式。如果未指定,那就用默认的字符集和排序方式。
查看支持的所有字符集和排序方式:
每个字符集有多种排序方式,以utf8mb4为例:
结果是:
utf8mb4_general_ci是默认的排序方式。在它的规则下会忽略大小写,甚至不同语言的文字之间也会判等,具体可参考:MYSQL中的COLLATE是什么?。
utf8mb4_bin是将字符串看做二进制串进行比较,所以不会忽略大小写。
在一个不忽略大小写的表中如何忽略大小写
我们可以使用 MySQL 的lower 函数:
插入数据:
查询: