MySQL基于逻辑在存储过程中动态构建查询字符串 [英] Mysql dynamically build query string in a stored procedure based on logic

问题描述

目标是根据输入变量在Mysql存储过程中更改查询字符串.

The objective is to alter a query string within a Mysql stored procedure based on input variables.

类似这样的东西:

CREATE DEFINER=`root`@`localhost` PROCEDURE `func`(type VARCHAR(15))
BEGIN
    SET @type = type;

    -- Check for the sort parameter
    if @type="asc" THEN
        SET @sort = " order by name asc";
    elseif @type="desc" THEN
        SET @sort = " order by name desc";
    else
        SET @sort ="";
    end if;

SELECT id, name from table @sort;

END    

推荐答案

解决方案是使用execute和concat:

The solution is to use execute, and concat:

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(input VARCHAR(15))
BEGIN
SET @input = input;

if @input="asc" then
    SET @sort = " order by ActivityLogKey asc";
elseif @input = "desc" then
    SET @sort = " order by ActivityLogKey desc";
else
    SET @sort ="";
end if;

SET @query = CONCAT('select * from activitylog ',@sort,' limit 0, 5');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

这篇关于MySQL基于逻辑在存储过程中动态构建查询字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！