I am trying to create a simple procedure with parameters.
我试图用参数创建一个简单的过程。
CALL new_procedure('mode', 'ASC');
The first input is the column the second is the sort direction
第一个输入是列,第二个输入是排序方向
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(IN in_order_by_column varchar(20), in_order_by_direction char(4))
BEGIN
DECLARE order_by varchar(30);
SET @order_by = CONCAT('`', in_order_by_column, '` ', in_order_by_direction);
/*
SELECT * FROM `common_tags` ORDER BY @order_by LIMIT 5;
*/
SELECT @order_by as 'c';
END
In the above example I have it only outputting the 2 parameters so I can see what's happening.
在上面的例子中我只输出了2个参数,这样我就可以看到发生了什么。
Result:
"c"
`mode` ASC
.
When I run the procedure with it's intended code, below.
当我使用它的预期代码运行该程序时,如下所示。
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(IN in_order_by_column varchar(20), in_order_by_direction char(4))
BEGIN
DECLARE order_by varchar(30);
SET @order_by = CONCAT('`', in_order_by_column, '` ', in_order_by_direction);
SELECT * FROM `common_tags` ORDER BY @order_by LIMIT 5;
END
Results
tags_id data mode parent_id position
1 Wood 2 13 6
2 Trippy 0 0 0
4 Artists 1 0 1
6 "Newest Additions" 1 0 11
12 "Natural Elements" 2 5 8
As you can see the results are not sorted by mode
.
如您所见,结果未按模式排序。
Any help is appreciated.
任何帮助表示赞赏。
1 个解决方案
#1
10
Unfortunately, you need to PREPARE
entire query in this case:
不幸的是,在这种情况下你需要PREPARE整个查询:
DELIMITER $$
DROP PROCEDURE IF EXISTS `new_procedure`$$
CREATE PROCEDURE `new_procedure`(IN in_order_by_column varchar(20), in_order_by_direction char(4))
BEGIN
SET @buffer = CONCAT_WS('',
'SELECT * FROM `common_tags` ORDER BY `', in_order_by_column, '` ', in_order_by_direction, ' LIMIT 5'
);
PREPARE stmt FROM @buffer;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
NOTE: Described approach should be used very careful, because it is vulnurable to SQL Injection attacks, if used incorrectly.
注意:应该非常小心地使用所描述的方法,因为如果使用不正确,它对SQL注入攻击很难理解。
#1
10
Unfortunately, you need to PREPARE
entire query in this case:
不幸的是,在这种情况下你需要PREPARE整个查询:
DELIMITER $$
DROP PROCEDURE IF EXISTS `new_procedure`$$
CREATE PROCEDURE `new_procedure`(IN in_order_by_column varchar(20), in_order_by_direction char(4))
BEGIN
SET @buffer = CONCAT_WS('',
'SELECT * FROM `common_tags` ORDER BY `', in_order_by_column, '` ', in_order_by_direction, ' LIMIT 5'
);
PREPARE stmt FROM @buffer;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
NOTE: Described approach should be used very careful, because it is vulnurable to SQL Injection attacks, if used incorrectly.
注意:应该非常小心地使用所描述的方法,因为如果使用不正确,它对SQL注入攻击很难理解。