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种:
查询语句
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
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
字符集(character set)和字符序(collation)是两个东西, 一个字符集对应1个或者多个字符序。
可以通过SHOW COLLATION查看数据库支持的字符序,通过SHOW CHARACTER SET字符集
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 |
常用的字符序:
建表语句:
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
页面更新:2024-05-30
本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828
© CopyRight 2020-2024 All Rights Reserved. Powered By 71396.com 闽ICP备11008920号-4
闽公网安备35020302034903号