MySQL查询关于区分字母大小写问题

前段时间在工作中测试提出了一个BUG,让我把根据ID查询区分大小写的功能去掉,大小写都随便查,然后我在SQL的位置加上了UPPER(id) = UPPER(#{id})的写法,而同事知道这个问题后的反映是"MySQL查询不是本就不区分大小写吗",后来我找机会简单了解了一下才明白,MySQL区分大小写取决于数据库的排序规则,今天在这里记录一下

一般创建数据库都会使用utf8编码,对应两种常用的排序规则就是utf8_binutf8_general_ci,其中utf8_bin就是区分大小的排序规则,另一个就是不区分大小写的排序规则,接下来针对排序规则进行一些简单的测试

注意:本人使用的Navicat客户端工具,其他客户端工具可能会对测试结果造成影响(例如SQLyog)

先测试区分大小写的排序规则

-- 创建区分大小写的数据库
CREATE DATABASE test DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- 选择数据库
USE test;

-- 创建一张测试表
CREATE TABLE aaa (
  id VARCHAR(16),
  PRIMARY KEY(id)
);

-- 插入测试数据
INSERT INTO aaa VALUES('abc');

-- 正常查询,可以查询到结果
SELECT id FROM aaa WHERE id = 'abc';

-- 修改大小写后,查询不到结果
SELECT id FROM aaa WHERE id = 'abC';

可以看出排序规则为bin的数据库使用查询是区分大小写的,然后再测试一下不区分大小写的

-- 创建不区分大小写的数据库
CREATE DATABASE test2 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 选择数据库
USE test2;

-- 创建一张测试表
CREATE TABLE aaa (
  id VARCHAR(16),
  PRIMARY KEY(id)
);

-- 插入测试数据
INSERT INTO aaa VALUES('abc');

-- 正常查询,可以查询到结果
SELECT id FROM aaa WHERE id = 'abc';

-- 修改大小写后同样可以查询到结果
SELECT id FROM aaa WHERE id = 'abC';

可以看出general_ci是不区分大小写的,有了结论后我们把test库修改为不区分大小写试试看

-- 将数据库的排序规则修改为utf8_general_ci
ALTER DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 选择test数据库
USE test;

-- 再次查询,还是查询不到结果
SELECT id FROM aaa WHERE id = 'abC';

这里已经把数据库的排序规则修改了,怎么还是查不到数据呢?原因是因为直接改库只对以后创建的表生效,并不会对已存在的表造成影响,这里新建一张表来测试一下

CREATE TABLE bbb (
  id VARCHAR(16),
  PRIMARY KEY(id)
);

INSERT INTO bbb VALUES('bbb');

SELECT id FROM bbb WHERE id = 'bBb';

果然,后续创建的表是不区分大小写的,接下来需要让已经创建的表也不区分大小写,这里直接修改表的排序规则试试

-- 将表的排序规则修改为utf8_general_ci
ALTER TABLE aaa DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 再次查询,还是查询不到结果
SELECT id FROM aaa WHERE id = 'abC';

修改表的排序规则不行,那就修改字段(列)的排序规则

-- 将字段的排序规则修改为utf8_general_ci
ALTER TABLE aaa MODIFY id VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 再次查询,查询成功!
SELECT id FROM aaa WHERE id = 'abC';

通过测试我们可以知道,排序规则分别有三个生效的作用域,分别是 列、表、库,而他们之间生效的优先级为列 > 表 > 库,只要列的排序规则修改成功,表和库的排序规则对于列来说就不重要了


经过测试了解到如果想要某个字段不区分大小写查询,就执行如下SQL修改他的排序规则即可:

ALTER TABLE [表名] MODIFY [字段名] [字段类型] CHARACTER SET utf8 COLLATE utf8_general_ci;

如果表中有大批字段需要修改的话一个一个改很麻烦,可以调用这段SQL修改表以及表中所有字段

ALTER TABLE [表名] CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

扩展

其实除了utf8_general_ci排序规则之外,utf8_unicode_ci同样不区分大小写,只不过unicode相对于general效率较低,但是查询精准度更高一些,详情点击这里(其实我也不懂)

posted @ 2021-08-04 14:11  Java小学生丶  阅读(466)  评论(0编辑  收藏  举报