mysql存储过程遇到问题，谁来看看怎么解决？

Question

DELIMITER //
DROP PROCEDURE IF EXISTS `replacename`//
create PROCEDURE replacename()

BEGIN 
declare c INT DEFAULT 0;
DECLARE r JSON ;
DECLARE id INT DEFAULT 0;
declare i int DEFAULT 0;

DECLARE result CURSOR for select id,slider_image from `eb_store_product`;

select count(*) into c from eb_store_product;
OPEN  result;

REPEAT 
    set i = i + 1;
    fetch result into id,r;
    
    set @t = replace(json_extract(r,'$[0]'),'ceshi','chenggong');

    until i >= c
end REPEAT; 
CLOSE result;
select @t;

END//

call replacename();//
DELIMITER ;

上述代码，就是想实现取出数组内文本并替换部分内容，然后显示替换后的文本。结果提示显示失败。提示大字段信息不存在。

哪位大佬来看看怎么解决 ...= =....

Answer 1

你的代码里在存储过程的时候没有更新 eb_store_product 表中的 slider_image 字段。

DELIMITER //
DROP PROCEDURE IF EXISTS `replacename`//
CREATE PROCEDURE replacename()
BEGIN
    DECLARE c INT DEFAULT 0;
    DECLARE r JSON;
    DECLARE id INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;

    DECLARE result CURSOR FOR SELECT id, slider_image FROM `eb_store_product`;

    SELECT COUNT(*) INTO c FROM eb_store_product;
    OPEN result;

    REPEAT
        SET i = i + 1;
        FETCH result INTO id, r;

        SET @t = REPLACE(JSON_EXTRACT(r, '$[0]'), 'ceshi', 'chenggong');

        -- 更新 eb_store_product 表中的 slider_image 字段
        UPDATE eb_store_product SET slider_image = JSON_REPLACE(r, '$[0]', @t) WHERE id = id;

    UNTIL i >= c
    END REPEAT;

    CLOSE result;
    SELECT @t;
END//
DELIMITER ;

CALL replacename();