MySQL多列多字符串搜索：现有方法效率及单次拼接优化问询

问题背景 #

需要在MySQL数据表的同一组多列中搜索多个不同字符串，现有两种写法，想了解第一种的效率，以及第二种仅拼接一次列的写法是否可行。

第一种写法（多次拼接列）：

SELECT * from some_table WHERE 
LOCATE('word1', (CONCAT(column_1, column_2, column_3))) > 0 OR 
LOCATE('word2', (CONCAT(column_1, column_2, column_3))) > 0 OR
LOCATE('word3', (CONCAT(column_1, column_2, column_3))) > 0;

第二种写法（仅拼接一次列的尝试）：

SELECT *, CONCAT(column_1, column_2, column_3) AS combined_columns WHERE
LOCATE ("word1", combined_columns) > 0 OR
LOCATE ("word2", combined_columns) > 0 OR
LOCATE ("word3", combined_columns) > 0;

第一种写法的效率分析 #

这种写法效率极低，核心问题有两个：

1. 每一次LOCATE调用都会重复执行CONCAT(column_1, column_2, column_3)，相当于对每行数据做3次列拼接操作，大量重复计算会消耗额外CPU资源。
2. 拼接后的字符串无法利用原列上的索引，查询只能走全表扫描，数据量越大，性能下降越明显。

仅拼接一次列的可行实现方式 #

你提出的第二种写法思路正确，但存在语法错误：MySQL的执行顺序是先处理WHERE子句，再处理SELECT子句，因此WHERE里无法直接引用SELECT中定义的别名combined_columns。

以下是几种正确实现“仅拼接一次列”的方案：

方案1：使用派生表/子查询 #

SELECT *
FROM (
    SELECT *, CONCAT(column_1, column_2, column_3) AS combined_columns
    FROM some_table
) AS temp_table
WHERE LOCATE('word1', combined_columns) > 0
   OR LOCATE('word2', combined_columns) > 0
   OR LOCATE('word3', combined_columns) > 0;

方案2：使用HAVING子句（MySQL 5.7+支持） #

SELECT *, CONCAT(column_1, column_2, column_3) AS combined_columns
FROM some_table
HAVING LOCATE('word1', combined_columns) > 0
   OR LOCATE('word2', combined_columns) > 0
   OR LOCATE('word3', combined_columns) > 0;

方案3：用正则表达式简化多条件 #

如果不需要精确匹配位置，可通过正则表达式一次性匹配多个关键词，代码更简洁：

SELECT *, CONCAT(column_1, column_2, column_3) AS combined_columns
FROM some_table
WHERE CONCAT(column_1, column_2, column_3) REGEXP 'word1|word2|word3';

注意：若关键词包含正则特殊字符（如.、*），需要提前转义。

更高效的替代方案 #

如果追求极致性能，不建议通过拼接列搜索，推荐两种优化方向：

1. 直接对单列做条件判断：可利用各列上的索引（若存在），避免全表扫描：

SELECT *
FROM some_table
WHERE column_1 LIKE '%word1%' OR column_1 LIKE '%word2%' OR column_1 LIKE '%word3%'
   OR column_2 LIKE '%word1%' OR column_2 LIKE '%word2%' OR column_2 LIKE '%word3%'
   OR column_3 LIKE '%word1%' OR column_3 LIKE '%word2%' OR column_3 LIKE '%word3%';

2. 创建生成列并加索引（MySQL 5.7+支持）：

-- 创建存储型生成列，自动维护拼接结果
ALTER TABLE some_table ADD COLUMN combined_columns VARCHAR(500) GENERATED ALWAYS AS (CONCAT(column_1, column_2, column_3)) STORED;
-- 给生成列添加索引
CREATE INDEX idx_combined ON some_table(combined_columns);

-- 查询时直接使用生成列
SELECT *
FROM some_table
WHERE combined_columns LIKE '%word1%' OR combined_columns LIKE '%word2%' OR combined_columns LIKE '%word3%';

生成列会自动同步原列的变更，查询时可直接利用索引，大幅提升性能。

内容的提问来源于stack exchange，提问作者rgg