避免MySQL中的隐式转换的方法小结

create table users (
    user_id varchar(20)  -- 本应为 int 类型，却用字符串存储数字
);
-- 查询时需将字符串转换为数字，可能触发隐式转换
select * from users where user_id = 123; 

create table users (
    user_id int  -- 直接使用数字类型，避免类型不匹配
);

create table orders (
    order_date varchar(10)  -- 本应为 date 类型
);
-- 查询时字符串与日期比较，触发隐式转换
select * from orders where order_date >= '2024-01-01'; 

create table orders (
    order_date date  -- 直接使用日期类型
);

create table products (
    name varchar(50) character set utf8mb4  -- 与表级字符集一致
) character set utf8mb4 collate utf8mb4_general_ci;

select * from users where user_id = '123';  -- user_id 是 int 类型，传入字符串

select * from users where user_id = 123;  -- 直接使用数字，类型匹配

select * from products where sku = 12345;  -- sku 是 varchar 类型，传入数字

select * from products where sku = '12345';  -- 传入字符串，类型匹配

select * from orders where order_date = '20240101';  -- 格式不规范，可能转换失败

select * from orders where order_date = str_to_date('20240101', '%y%m%d');  -- 显式转换为日期

-- 将字符串转换为数字（显式转换，避免隐式转换）
select * from users where user_id = cast('123' as signed);  
-- 将数字转换为字符串
select concat('user id: ', convert(user_id, char)) from users;

select * from users where date_format(create_time, '%y') = '2024';  -- create_time 是索引字段，对字段用函数

select * from users where create_time >= '2024-01-01' and create_time < '2025-01-01'; 

create index idx_user_id on users(user_id);  -- user_id 是 int 类型索引
select * from users where user_id = '123';  -- 传入字符串，触发隐式转换，索引失效

select * from users where user_id = 123;  -- 传入数字，命中索引

create index idx_name_age on users(name varchar(50), age int);  -- 索引字段为字符串+数字
-- 正确：查询条件类型与索引顺序一致（字符串+数字）
select * from users where name = 'alice' and age = 30;  
-- 错误：age 传入字符串，触发隐式转换，可能导致索引部分失效
select * from users where name = 'alice' and age = '30';  

-- 临时启用（当前会话有效）
set session sql_mode = 'strict_trans_tables,no_auto_create_user,no_engine_substitution';  
-- 永久启用（修改 my.cnf/my.ini）
[mysqld]
sql_mode = strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_engine_substitution

cursor.execute("select * from users where user_id = %s", (123,))  -- 传入数字参数，驱动自动处理类型

explain select * from users where user_id = '123';  -- 查看是否触发全表扫描

-- 配置慢查询日志（修改 my.cnf）
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1