mysql查询时字符串区分大小写问题

mysql默认情况下查询语句的where条件是不区分大小写的。

建表语句

CREATE TABLE `test_users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

初始化语句

INSERT INTO `test_users` (`id`, `name`)
VALUES
	(1, 'Abc'),
	(2, 'abc'),
	(3, 'sss');

默认不区分大小写

select * from test_users where  name = "abc";

输出结果

id	name
1	Abc
2	abc

索引情况

explain select * from test_users where name = "abc";

索引结果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	test_users	NULL	ref	idx_name	idx_name	122	const	2	100	Using index

命中了索引index_name，并且type = const

区分大小写的方法

区分大小写的方法有3种：

• 字段前添加关键字: binary
• 建表时字段加入关键字：binary
• 修改字段字符集:

方案一：查询条件字段前添加关键字：binary

查询语句

select * from test_users where binary name = "abc";

输出结果

id	name
2	abc

查询语句索引情况

explain select * from test_users where binary name = "abc";

输出结果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	test_users	NULL	index	NULL	idx_name	122	NULL	3	100	Using where; Using index

可以看出来命中了索引 idx_name，并且 type = index

方案二：建表时字段加入关键字：binary

CREATE TABLE `test_users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) binary NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

查询语句

select * from test_users where name = "abc";

输出结果

id	name
2	abc

查询语句索引情况

explain select * from test_users where name = "abc";

输出结果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	test_users	NULL	ref	idx_name	idx_name	122	const	1	100	Using index

可以看出命中了 idx_name索引，并且type = ref

方案三：修改字段的字符序(collation)

字符集(character set)和字符序(collation)是两个东西, 一个字符集对应1个或者多个字符序。

可以通过SHOW COLLATION查看数据库支持的字符序，通过SHOW CHARACTER SET字符集

SHOW COLLATION where collation like 'utf8mb4_%'命令

Collation	Charset	Id	Default	Compiled	Sortlen
utf8mb4_general_ci	utf8mb4	45	Yes	Yes	1
utf8mb4_bin	utf8mb4	46		Yes	1
utf8mb4_unicode_ci	utf8mb4	224		Yes	8
utf8mb4_icelandic_ci	utf8mb4	225		Yes	8
utf8mb4_latvian_ci	utf8mb4	226		Yes	8
utf8mb4_romanian_ci	utf8mb4	227		Yes	8
utf8mb4_slovenian_ci	utf8mb4	228		Yes	8
utf8mb4_polish_ci	utf8mb4	229		Yes	8
utf8mb4_estonian_ci	utf8mb4	230		Yes	8
utf8mb4_spanish_ci	utf8mb4	231		Yes	8
utf8mb4_swedish_ci	utf8mb4	232		Yes	8
utf8mb4_turkish_ci	utf8mb4	233		Yes	8
utf8mb4_czech_ci	utf8mb4	234		Yes	8
utf8mb4_danish_ci	utf8mb4	235		Yes	8
utf8mb4_lithuanian_ci	utf8mb4	236		Yes	8
utf8mb4_slovak_ci	utf8mb4	237		Yes	8
utf8mb4_spanish2_ci	utf8mb4	238		Yes	8
utf8mb4_roman_ci	utf8mb4	239		Yes	8
utf8mb4_persian_ci	utf8mb4	240		Yes	8
utf8mb4_esperanto_ci	utf8mb4	241		Yes	8
utf8mb4_hungarian_ci	utf8mb4	242		Yes	8
utf8mb4_sinhala_ci	utf8mb4	243		Yes	8
utf8mb4_german2_ci	utf8mb4	244		Yes	8
utf8mb4_croatian_ci	utf8mb4	245		Yes	8
utf8mb4_unicode_520_ci	utf8mb4	246		Yes	8
utf8mb4_vietnamese_ci	utf8mb4	247		Yes	8

常用的字符序：

• utf8_general_ci & utf8mb4_general_ci: 不区分大小写
• utf8_bin & utf8mb4_bin: 区分大小写

建表语句：

CREATE TABLE `test_users3` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

查询语句：

select * from test_users where name = "abc";

输出结果

id	name
2	abc

查询语句索引情况

explain select * from test_users where name = "abc";

输出结果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	test_users	NULL	index	NULL	idx_name	122	NULL	3	100	Using where; Using index

命中了索引idx_name，并且 type = index