MySQL 5.7，使用存储过程中的变量按列名对表进行排序

  • Z时代
  • 2024-01-10
  • 分类：问答

我有一个简单的表，其中包含一些数据：

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注入漏洞。

以上是 MySQL 5.7，使用存储过程中的变量按列名对表进行排序 的全部内容， 来源链接： utcz.com/qa/417954.html

回到顶部