MySQL Mysql基于逻辑在存储过程中动态构建查询字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8549619/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-31 22:05:00 来源:igfitidea点击:
Mysql dynamically build query string in a stored procedure based on logic
提问by Ben Guthrie
The objective is to alter a query string within a Mysql stored procedure based on input variables.
目标是根据输入变量更改 Mysql 存储过程中的查询字符串。
Something like this:
像这样的东西：
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
回答by Ben Guthrie
The solution is to use execute, and concat:
解决方法是使用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
