MySQL 字段值大小写敏感：BINARY 与 COLLATE 怎么选

10次阅读

MySQL 里大小写敏感这个问题，经常会被混在一起讨论：数据库名、表名、列名、别名是一类规则，字段里真正存进去的字符串又是另一类规则。

这篇只聚焦一个问题：当 username 里同时存在 user、User、USER 时，为什么普通查询会一起查出来，以及需要区分大小写时可以怎么处理。

MySQL 在 Linux 下，数据库名、表名、列名和别名的大小写规则大致是：

数据库名与表名严格区分大小写。
表的别名严格区分大小写。
列名与列的别名在所有情况下均忽略大小写。
字段值默认情况下通常不区分大小写。

很多问题就出在第 4 点。开发时我们以为查的是精确字符串，实际比较规则却由字符集和校对规则决定。

先建一张用户表：

CREATE TABLE `tb_user` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` VARCHAR(50) NOT NULL COMMENT '用户名',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT = '用户表';

INSERT INTO `tb_user` (`id`, `username`) VALUES ('1', 'user');
INSERT INTO `tb_user` (`id`, `username`) VALUES ('2', 'User');
INSERT INTO `tb_user` (`id`, `username`) VALUES ('3', 'USER');

使用普通查询查全部小写的 user：

mysql> SELECT username FROM tb_user WHERE username = 'user';
+----------+
| username |
+----------+
| user     |
| User     |
| USER     |
+----------+
3 rows in set

如果当前字段使用的是大小写不敏感的校对规则，user、User、USER 在比较时会被认为相等，所以三条记录都会被查出来。

最直接的办法是在查询里加 BINARY：

mysql> SELECT * FROM tb_user WHERE BINARY username = 'user';
+----+----------+
| id | username |
+----+----------+
|  1 | user     |
+----+----------+
1 row in set

这种方式的优点是改动小，不需要调整表结构。缺点也很明显：每个需要大小写敏感比较的查询都要记得加，一旦漏掉，行为又会回到原来的校对规则。

所以它更适合临时排查、少量查询，或者只在个别业务点需要精确比较的场景。

如果某个字段天然就应该区分大小写，可以在建表时直接指定。

例如给字段加 BINARY：

CREATE TABLE `tb_user1` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` VARCHAR(50) BINARY NOT NULL COMMENT '用户名',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT = '用户表';

查看建表结果时，可以看到 MySQL 会把它转换成字段级的二进制校对规则：

mysql> SHOW CREATE TABLE tb_user1;
tb_user1 | CREATE TABLE `tb_user1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用户名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'
1 row in set

也可以在表级别指定：

CREATE TABLE `tb_user2` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` VARCHAR(50) NOT NULL COMMENT '用户名',
  `info` VARCHAR(100) NOT NULL COMMENT '详情描述',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_bin COMMENT = '用户表';

查看表结构时，VARCHAR 字段会继承表级的 utf8_bin：

mysql> SHOW CREATE TABLE tb_user2;
tb_user2 | CREATE TABLE `tb_user2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '用户名',
  `info` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '详情描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户表'

这两种方式本质上都是把字段比较规则切到 utf8_bin，让字符串按二进制方式比较。

可以简单按这个思路判断：