想当然之MySQL的字符串大小写敏感

通常情况，想当然代表对知识理解不够，修行不够深，当我意识到自己是想当然的时候，非常汗颜，但同是也√了新的知识。无知并不可怕，可怕的是无知还不自知。

先描述理解，我司与某银行进行对接充值数据，主要是将用户在银行的充值记录（当天的转账流水号唯一），同步到我司自己的数据库中，某天查询银行网关的数据条目为140条，但是同步任务同步到我司自己的数据库中只有138条，少了两条，感觉很奇怪，查到这两条未同步数据有一个特点：这两条转账流水号若不区分大小写的话，已经存在于我司的数据库中，我第一反应是：怎么可能，难道mysql的varchar类型大小写不敏感？话不多说，这个一试便知

先看一下我本地的数据库版本

MariaDB [examples]> select @@version;
+---------------------+
| @@version           |
+---------------------+
| 10.3.10-MariaDB-log |
+---------------------+
1 row in set (0.000 sec)

表结构如下

CREATE TABLE `assume` (
  `id` bigint(64) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(12) NOT NULL,
  `trans_no` varchar(12) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_no_idx` (`name`,`trans_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我们试着插入一条数据

MariaDB [examples]> insert into assume (name, trans_no) values("sql", "asdf");
Query OK, 1 row affected (0.037 sec)

ok，数据插入成功，还是用上一条插入的数据, 只不过这次name字段首字段我们改成大写

MariaDB [examples]> insert into assume (name, trans_no) values("sql", "Asdf");
ERROR 1062 (23000): Duplicate entry 'sql-Asdf' for key 'name_no_idx'
MariaDB [examples]>

真的假的，sql-asdf与sql-Asdf明明就不一样啊，怎么会这样？但是从报错来看，mysql又似乎认为sql-asdf与sql-Asdf是重复记录？工作四年，印象中是第一次遇到这种情况，这就是之前的想当然。赶紧查一下mysql文档

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 will be case-sensitive.

秀一下我拙劣的翻译：

对于非二进制字符串(CHAR、VARCHAR、TEXT)，字符串搜索使用比较操作数的排序。对于二进制字符串(BINARY、VARBINARY、BLOB)，比较使用操作数中字节的数值;这意味着对于字母字符，比较是区分大小写的（？这句表示没看懂）。

非二进制字符串和二进制字符串之间的比较被视为二进制字符串之间的比较。

简单的比较操作(>=、>、=、<、<=、排序和分组)基于每个字符的“排序值”。具有相同排序值的字符将被视为相同字符。例如，如果e和é在给定的排序规则中具有相同的排序值，那么它们的比较就相等。

默认的字符集和排序规则是utf8mb4和utf8mb4_0900_ai_ci，因此默认情况下，非二进制字符串比较不区分大小写

查看一下当前的COLLATION(排序规则)版本

MariaDB [examples]> SELECT COLLATION(VERSION());
+----------------------+
| COLLATION(VERSION()) |
+----------------------+
| utf8_general_ci      |
+----------------------+

也就是说在utf8_general_ci的字符规则中，是不区分大小写的

MariaDB [examples]> select 'a' ='A';
+----------+
| 'a' ='A' |
+----------+
|        1 |
+----------+
1 row in set (0.024 sec)

MariaDB [examples]> select 'a' ='b';
+----------+
| 'a' ='b' |
+----------+
|        0 |
+----------+

回到assume这张表，我的本意是name跟trans_no是需要区分大小写的，可以修改字符排序规则属性为二进制类型

alter table `assume` MODIFY column name varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;
alter table `assume` MODIFY column trans_no varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;

此时便能

MariaDB [examples]> show create table assume \G;
*************************** 1. row ***************************
       Table: assume
Create Table: CREATE TABLE `assume` (
  `id` bigint(64) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `trans_no` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_no_idx` (`name`,`trans_no`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.001 sec)

ERROR: No query specified

MariaDB [examples]> insert into assume (name, trans_no) values("sql", "Asdf");
Query OK, 1 row affected (0.010 sec)

我感觉这样设计很奇怪，不明白为何如此设计，按之前的想当然，应该默认大小写敏感才是，google搜索无果，诸公若有线索，欢迎留言,不胜感激。

参考：

mysql case sensitivity
https://stackoverflow.com/questions/6448825/sql-unique-varchar-case-sensitivity-question

想当然之MySQL的字符串大小写敏感

More from this blog

2025: 祛魅灰度念头通达

大厂祛魅：破碎的专注力

Black Swan

2024年: 逐渐平静

企业软件之殇

Command Palette

More from this blog