如何在MySQL查询中将CONCAT拼接式存为full_name变量优化代码？

当然可以！这样的重构完全没问题，能让你的SQL代码简洁很多，可读性也更强。你有几种可行的实现方式：

方法1：使用派生表（子查询） #

通过内层子查询预先计算出full_name，然后在外层查询中直接使用这个别名，彻底避免重复写CONCAT(firstname, ' ', lastname)：

$query = "
SELECT user_id, firstname, lastname, picUrl, full_name
FROM (
    SELECT 
        user_id, 
        firstname, 
        lastname, 
        picUrl,
        CONCAT(firstname, ' ', lastname) AS full_name
    FROM user
) AS user_with_fullname
WHERE full_name LIKE '%" . $searchString . "%'
ORDER BY 
    CASE 
        WHEN full_name = '" . $searchString . "' THEN 0
        WHEN full_name LIKE '" . $searchString . "%' THEN 1
        WHEN full_name LIKE '%" . $searchString . "%' THEN 2
        WHEN full_name LIKE '%" . $searchString . "' THEN 3
        ELSE 4 
    END, 
    full_name ASC";

方法2：使用CTE（公共表表达式，MySQL 8.0+支持） #

如果你的MySQL版本是8.0及以上，用CTE会让代码结构更清晰，逻辑分层更直观：

$query = "
WITH user_with_fullname AS (
    SELECT 
        user_id, 
        firstname, 
        lastname, 
        picUrl,
        CONCAT(firstname, ' ', lastname) AS full_name
    FROM user
)
SELECT user_id, firstname, lastname, picUrl, full_name
FROM user_with_fullname
WHERE full_name LIKE '%" . $searchString . "%'
ORDER BY 
    CASE 
        WHEN full_name = '" . $searchString . "' THEN 0
        WHEN full_name LIKE '" . $searchString . "%' THEN 1
        WHEN full_name LIKE '%" . $searchString . "%' THEN 2
        WHEN full_name LIKE '%" . $searchString . "' THEN 3
        ELSE 4 
    END, 
    full_name ASC";

重要提醒 #

直接将用户输入的$searchString拼接进SQL语句存在严重的SQL注入风险，强烈建议使用预处理语句来绑定参数，比如用PDO的prepare()和execute()方法，或者mysqli的预处理功能，这样能有效避免安全漏洞。

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