mysql - 在 JSON_EXTRACT 中返回空值

Question

MyJsonArray

[{"ID":"D29","PersonID":"23616639"},{"ID":"D30","PersonID":"22629626"}]

我想从 sql 函数将此数组设置到我的表中，但在变量中返回空值，而不是在我的数据库中设置记录我的功能:

    DELIMITER $$
CREATE DEFINER=`toshiari`@`localhost` FUNCTION `setTitleRecords`(`Title` VARCHAR(166) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `List` JSON) RETURNS int(4)
BEGIN
    DECLARE Item                INT;
    DECLARE HolderLENGTH        INT;
    DECLARE ValidJson           INT;
    DECLARE ID                  VARCHAR(166);
    DECLARE PersonID            VARCHAR(166);
    DECLARE S1                  VARCHAR(166);
    DECLARE S2                  VARCHAR(166);
    SET ValidJson = (SELECT JSON_VALID(List));
    IF ValidJson = 1 THEN 
        SET HolderLENGTH = (SELECT JSON_LENGTH(List));
        SET Item = 0;
        WHILE Item < HolderLENGTH DO
            SET S1 = CONCAT("'$[",Item, "].ID'");
            SET S2 = CONCAT("'$[",Item, "].PersonID'");
            SET ID       = (SELECT JSON_EXTRACT(List,S1));
            SET PersonID = (SELECT JSON_EXTRACT(List,S2));
            INSERT INTO `Titles`(`ID`,`PersonID`,`Title`) VALUES (ID, PersonID, Title);
            SET Item = Item + 1;
        END WHILE;
        RETURN 3;       
    ELSE
        RETURN 2;       
    END IF;
END$$
DELIMITER ;

当我在Sql命令中使用这个命令时没有问题并返回true值

SELECT JSON_EXTRACT('[{"ID":"D29","PersonID":"23616639"},{"ID":"D30","PersonID":"22629626"}]','$[0].ID')  return "D29"

返回 “D29”但在这段代码的运行函数中返回错误并说:

SET @p0='DR'; SET @p1='[{\"ID\":\"D29\",\"PersonID\":\"23616639\"},{\"ID\":\"D30\",\"PersonID\":\"22629626\"}]'; SELECT `setTitleRecords`(@p0, @p1) AS `setTitleRecords`;


#4042 - Syntax error in JSON path in argument 2 to function 'json_extract' at position 1 

Answer 1

我创建了一个小测试，以便重现您的问题。基本上你只需要重新声明 S1 和 S2，如下所示:

SET S1 = CONCAT('$[',Item,'].ID');
SET S2 = CONCAT('$[',Item,'].PersonID');

And that's it. You can check the test in the following url: https://www.db-fiddle.com/f/2TPgF868snjwcHN3uwoSEA/0