mysql - 如何在存储过程的json字符串中添加变量？

Question

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure1`(
    out try varchar(50)
)
BEGIN
    select @var1 :=count(*) from book.books;
    select @var2 :=count(*) from book.authors;
    set try =  ({"Count1": @var1, {"Count2 : @var2});
END

预期输出 {"Count1": 79, {"Count2 : 50} 因为分别有 79 和 50 个条目

标签： mysqljson

Answer 1

您可以使用下一个存储过程来实现此目的：

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
    DECLARE a INT DEFAULT 0;
    DECLARE b INT DEFAULT 0;
    
    SELECT COUNT(*) INTO b FROM `books`;
    SELECT COUNT(*) INTO a FROM `authors`;

    SELECT JSON_OBJECT(
      'books', b,
      'authors', a
    ) AS 'Result';
    END$$

DELIMITER ;