mysql - MySQL 5.7,使用存储过程中的变量按列名对表进行排序
问题描述
我有一个包含一些数据的简单表格:
DROP TABLE IF EXISTS `MY_TABLE`;
CREATE TABLE IF NOT EXISTS `MY_TABLE` (
`id` CHAR(40) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `MY_TABLE` (`id`) VALUES (1);
INSERT INTO `MY_TABLE` (`id`) VALUES (2);
INSERT INTO `MY_TABLE` (`id`) VALUES (3);
现在我有一个从列中检索此数据排序的过程。这按预期工作:
DROP PROCEDURE IF EXISTS test_procedure;
DELIMITER $$
CREATE PROCEDURE test_procedure()
BEGIN
SELECT id AS columnAlias FROM `MY_TABLE`
ORDER BY columnAlias;
END $$
DELIMITER ;
CALL test_procedure(); /* Returns 1, 2, 3, 4, 5, 6, 7, 8, 9 */
还有这个:
DROP PROCEDURE IF EXISTS test_procedure;
DELIMITER $$
CREATE PROCEDURE test_procedure()
BEGIN
SELECT id AS columnAlias FROM `MY_TABLE`
ORDER BY -columnAlias;
END $$
DELIMITER ;
CALL test_procedure(); /* Should return 9, 8, 7, 6, 5, 4, 3, 2, 1 */
现在,我的情况:必须从过程中传递列名,所以我这样做:
DROP PROCEDURE IF EXISTS test_procedure;
DELIMITER $$
CREATE PROCEDURE test_procedure(IN data JSON)
BEGIN
SET @sortBy = JSON_UNQUOTE(JSON_EXTRACT(DATA, '$.sortBy'));
SELECT id AS columnAlias FROM `MY_TABLE`
ORDER BY @sortBy;
END $$
DELIMITER ;
CALL test_procedure('{"sortBy": "columnAlias"}'); /* Should return 1, 2, 3, 4, 5, 6, 7, 8, 9 */
CALL test_procedure('{"sortBy": "-columnAlias"}'); /* Should return 9, 8, 7, 6, 5, 4, 3, 2, 1 */
但不能让它工作。欢迎任何帮助。
编辑:
鉴于@bill_karwin 的解决方案,我尝试使用 CASE,测试我的变量:
DROP PROCEDURE IF EXISTS test_procedure;
DELIMITER $$
CREATE PROCEDURE test_procedure(IN data JSON)
BEGIN
SET @sortBy = JSON_UNQUOTE(JSON_EXTRACT(DATA, '$.sortBy'));
SELECT id AS columnAlias FROM `MY_TABLE`
ORDER BY
CASE @sortBy
WHEN "columnAlias" THEN columnAlias
WHEN "-columnAlias" THEN -columnAlias
END
;
END $$
DELIMITER ;
CALL test_procedure('{"sortBy": "-columnAlias"}');
虽然不起作用1, 2, 3
,但它应该被反转,因为我正在传递带有减号前缀的字符串:-columnAlias
。
解决方案
您可以使用 CASE 表达式:
SELECT id AS columnAlias FROM `MY_TABLE`
ORDER BY CASE @sortBy WHEN 'updated_at' THEN updated_at
WHEN 'user_id' THEN user_id
ELSE id END;
有些人使用动态 SQL,但 CASE 表达式解决方案很好,因为它会根据一组固定的列自动检查输入,因此您不会意外导致 SQL 注入漏洞。
推荐阅读
- javascript - 简单代码的 Mocha 测试在没有明显原因的情况下失败
- .htaccess - 是否可以为 404 错误页面发送 2 个 http 状态代码并重定向到主页
- javascript - 如何在传单地图上添加和删除标记簇?
- bash - Gitlab 挂钩以强制开发人员在其提交消息中使用 JIRA 问题
- codenameone - 只看到组件的一半字母 - cn1
- python - 用上面的数据填充空白单元格
- android - Flutter - 另一个 Listview 中的 Listview.builder
- c# - 如何从异步方法打开 WPF 窗口
- r - 在 R 的循环中创建一个变量并添加到数据框中
- automotive - 如何为AGL制作构建环境?