mysql字段值如何区分大小写

https://www.cnblogs.com/baby123/p/11384492.html

1.查询时指定大小写敏感
在查询时指定大小写“敏感”，加关键字“BINARY”

drop table binary_test;
CREATE TABLE binary_test (
  `id` INT unsigned PRIMARY key NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into binary_test (name) values ('QWERTY');
insert into binary_test (name) values ('qwerty');

测试数据

select * from binary_test where name ='qwerty';

select * from binary_test where binary name ='QWERTY';

2.定义表结构时指定字段大小写敏感
关键字“BINARY”指定guid字段大小写敏感

drop table binary_test;
CREATE TABLE binary_test (
  `id` INT unsigned PRIMARY key NOT NULL AUTO_INCREMENT,
  `name` varchar(255) BINARY  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into binary_test (name) values ('QWERTY');
insert into binary_test (name) values ('qwerty');

注意 name字段使用binary修饰了。

select * from binary_test where name ='qwerty';

3.修改排序规则（COLLATION）
show variables like ‘collation_database’;
Collation以 “_ci"结尾的不区分大小写（ci——Case Ignore），以”_bin"或者"_cs"结尾的区分大小写

将Collation改为 utf8_bin（大小写敏感的）
可以为库、表、列指定Collation。
优先级为 列>表>库

CREATE DATABASE test COLLATE utf8_bin;

ALTER TABLE binary_test DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

ALTER TABLE binary_test MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL