如何解决MySQL不区分大小写

如何解决MySQL不区分大小写

binary关键字

where子句的字符串比较是不区分大小写的，但是可以使用binary关键字设定where子句区分大小写

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `mobile` varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT '手机',
  `name` varchar(32) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `udxMobile` (`mobile`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC COMMENT='用户表'

SELECT id,mobile,name FROM user WHERE mobile = 'AS' 

SELECT id,mobile,name FROM user WHERE binary  mobile = 'AS'

设置字段类型

ALTER TABLE user MODIFY COLUMN mobile varchar(32)  COLLATE utf8_bin ; #设置字段类型