MYSQL检索条件区分大小写（CAST、BINARY）

 

1、查看MYSQL全局变量是否区分大小写（0区分，1不区分）

show Variables like '%table_names'

lower_case_table_names = 0 表名存储为给定的大小和比较是区分大小写的

lower_case_table_names = 1 表名存储在磁盘是小写的,但是比较的时候是不区分大小写

lower_case_table_names = 2 表名存储为给定的大小写但是比较的时候是小写

unix, linux下 lower_case_ table_ names默认值为0. Windows下默认值是1. Mac os X下默认值是2

更改方法：更改数据库参数文件 my.cnf在 mysqld下添加或修改 lower_case_table_names=1之后重启数据库

 

2、更新字段实现大小写（字段）

mysql对于类型为varchar数据默认不区分大小写，但如果该字段以“*_bin”编码的话会使mysql对其区分大小写。

utf8_general_ci --不区分大小写

utf8_bin--区分大小写

-- 使用SQL脚本将字段code转换成区分大小写(不推荐，高版本会提示后期弃用)
ALTER TABLE product_rm_type MODIFY COLUMN `code` VARCHAR(50) BINARY;

-- 使用SQL脚本将字段code的编码进行调整（推荐）
ALTER TABLE product_rm_type MODIFY COLUMN `code` VARCHAR(50) COLLATE utf8mb3_bin;

 

3、使用SQL实现字段大小写区分

1、CAST函数

CAST函数可以在MySQL中使用，其格式是CAST(VARIABLE_NAME AS DATA_TYPE) 。

VARIABLE_NAME参数表示要转换类型的表达式或变量，

DATA_TYPE参数表示将VARIABLE_NAME变量转换为的数据类型。

它支持包括MySQL提供的所有内置数据类型在内的大多数类型，如INT，CHAR，VARCHAR等。

-- 使用CAST(CODE AS BINARY)将CODE字段转换成BINARY数据类型，检索条件将区分字段大小写
SELECT * FROM product_rm_type WHERE CAST(CODE AS BINARY)  = 'st';

-- 效果一样，但高版本MYSQL会提示弃用
SELECT * FROM product_rm_type WHERE BINARY CODE = 'st'；

 

4、索引情况

-- 正常使用索引
explain SELECT * FROM product_rm_type WHERE CODE = 'st'

 SQL检索条件使用了CAST函数，索引会失效

-- 检索条件使用了函数，索引失效
explain SELECT * FROM product_rm_type WHERE CAST(CODE AS BINARY)  = 'st'；
-- 检索条件使用了函数，索引失效
explain SELECT * FROM product_rm_type WHERE BINARY CODE = 'st'；