带有动态子句 where 的 MySQL 存储过程

Question

我需要动态构造一个 SQL 子句，我看到一些仅使用 case when 的示例，但由于某种原因我的源代码无法工作。

有人可以帮助我吗？

create procedure sp_test(in iduser bigint, in name varchar(50), in company varchar(50), in city varchar(50), in profession varchar(50))
begin
    if not(name is null) then 
        begin   
            set name = '%' + lower(name) + '%'; 
        end;
    end if;

    if not(company is null) then 
        begin   
            set company = '%' + lower(company) + '%';
        end;
    end if;

    if not(city is null) then 
        begin   
            set city = '%' + lower(city) + '%'; 
        end;
    end if;

    if not(profession is null) then 
        begin   
            set profession = '%' + lower(profession) + '%';
        end;
    end if;

    select 
            usr.id_user,
            usr.ds_icon,
            usr.nm_user,
            usr.ds_slug,
            usr.ds_title,
            usr.nm_company
        from 
            tbl_user usr
            left join tbl_profession pro on (pro.id_profession = usr.id_profession)
            left join tbl_resume res on (res.id_user = usr.id_user)
        where
            (usr.ds_activation is null) and
            usr.id_user <> iduser and
            usr.id_user not in (select id_friend from tbl_user_friend where id_user = iduser) and
            usr.id_user not in (select id_user from tbl_user_friend where id_friend = iduser) and
            usr.id_user not in (select id_friend from tbl_user_friend_not_suggest where id_user = iduser) and
            case when not(name is null) then 
                lower (usr.nm_user) like lower(name) or 
            end
            case when not(company is null) then 
                lower (usr.nm_company) like lower(company) or 
            end
            case when not(profession is null) then 
                lower (pro.nm_profession) like lower(profession) or 
            end
            case when not(city is null) then 
                lower (res.ds_city) like lower(city) or 
            end
            1 = 1
        order by
            usr.nm_user
        limit
            0,20
        ;
end$$

我想这个想法是正确的，我使用 %value% 准备要过滤的字符串，以便在 SQL 命令上使用它，并在检查值是否为 null 后，我想将其添加到WHERE 子句。

Answer 1

SQL 是一种声明性语言。您告诉服务器您想要查找什么，而不是如何查找。查询优化器的任务是确定如何查找行。

简单的解决方案是让查询优化器负责优化掉不必要的条件，它会自动执行此操作

WHERE
... AND
(name IS NULL OR usr.nm_user LIKE CONCAT('%',name,'%') AND
(company IS NULL OR usr.nm_company LIKE CONCAT('%',company,'%') AND
... -- repeat for other variables

在准备查询计划时，优化器的工作是确定如何以尽可能少的工作实际找到所需的行。

由于 name 是一个在查询执行期间无法更改的变量，因此优化器将其解析为常量。由于 name 是一个常量，因此 name IS NULL 是一个常量表达式，可以在查询执行开始之前解析为 true 或 false。

如果为 true，则 OR 表达式始终为 true，因此无需解析表达式 CONCAT('%',name,'%')，因此已被优化掉。

如果为 false，则表达式 CONCAT('%',name,'%') 将解析为常量，并将每一行与其进行比较。不需要对每一行处理 CONCAT()，因为该值不会因行而异，因此无需提前执行此操作。

因此，无需重写您的查询。只需制定一个逻辑上有效的表达式，优化器就会完成剩下的工作。

此外，字符排序规则默认不区分大小写，因此除非您更改此设置，否则不需要 LOWER()。

并且，如上所述，您之前将 % 连接到变量的 block 也是不必要的，因为只有当我们需要时，我们才可以在 WHERE 中执行这些操作(当变量不为空时)。