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

合集 - SQL(16)

1.SQL中模糊查询（like、REGEXP）的用法2023-02-232.SQL中GROUP_CONCAT相关用法2023-02-093.MYSQL中ORDER BY使用2022-08-114.MYSQL中 UNION ALL 与 ORDER BY 联合使用2022-08-105.MYSQL语法2022-07-226.MYSQL中EXISTS的用法2022-07-217.MYSQL中JOIN的用法2022-07-218.SQL优化2022-07-219.xml使用foreach2023-03-1010.EXPLAIN 详解（SQL优化）2022-07-2111.mysql_存储过程_入门2022-05-0912.SQL中的case when then else end用法（数据库内容纵向变横向）2022-01-08

13.MYSQL检索条件区分大小写（CAST、BINARY）2023-09-07

14.sql_mode=only_full_group_by以及ANY_VALUE()2024-04-2615.异常com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction2022-09-0716.MYSQL中SHOW PROCESSLIST使用2024-07-24

收起

 

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'；