mysql upper后还走索引

基础概念

MySQL中的UPPER函数用于将字符串转换为大写。索引是数据库管理系统中用于提高数据检索速度的数据结构。MySQL支持多种类型的索引，包括B-tree索引、哈希索引、全文索引等。

类型

MySQL中的主要索引类型包括：

B-tree索引：最常见的索引类型，适用于范围查询和排序操作。
哈希索引：适用于等值查询，但不支持范围查询。
全文索引：用于全文搜索。

应用场景

索引通常用于经常用于查询条件的列，例如主键、外键、频繁用于WHERE子句的列等。

问题分析

当在查询中使用UPPER函数时，MySQL可能无法直接使用索引，因为UPPER函数改变了数据的存储形式。MySQL在执行查询时，通常会尝试将查询条件转换为索引可以使用的形式，但UPPER函数会阻止这种优化。

原因

UPPER函数是非确定性的函数，因为它依赖于输入数据。MySQL的查询优化器通常不会将非确定性函数包含在索引查找中，因为这会使得索引查找变得复杂且效率低下。

解决方法

避免在查询条件中使用UPPER函数：如果可能，尽量避免在WHERE子句中使用UPPER函数。例如，如果有一个列name，可以这样查询：
避免在查询条件中使用UPPER函数：如果可能，尽量避免在WHERE子句中使用UPPER函数。例如，如果有一个列name，可以这样查询：
使用覆盖索引：如果必须使用UPPER函数，可以考虑创建一个包含原始列和其大写形式的复合索引。例如：
使用覆盖索引：如果必须使用UPPER函数，可以考虑创建一个包含原始列和其大写形式的复合索引。例如：
这样，查询可以同时利用name和UPPER(name)两个部分。
使用触发器或存储过程：在插入或更新数据时，使用触发器或存储过程将数据的大写形式存储在一个额外的列中，并在该列上创建索引。例如：
使用触发器或存储过程：在插入或更新数据时，使用触发器或存储过程将数据的大写形式存储在一个额外的列中，并在该列上创建索引。例如：
然后在name_upper列上创建索引：
然后在name_upper列上创建索引：

示例代码

假设我们有一个表users，其中有一个列username，我们希望在查询时使用大写形式：

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(255),
    username_upper VARCHAR(255)
);

-- 创建触发器
DELIMITER $$
CREATE TRIGGER before_insert_username
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.username_upper = UPPER(NEW.username);
END$$
DELIMITER ;

-- 创建索引
CREATE INDEX idx_username_upper ON users (username_upper);

-- 插入数据
INSERT INTO users (id, username) VALUES (1, 'john_doe');

-- 查询数据
SELECT * FROM users WHERE username_upper = 'JOHN_DOE';