mysql存储过程解析json

CREATE TABLE users (
    id INT PRIMARY KEY,
    data JSON
);

DELIMITER //

CREATE PROCEDURE ParseUserJson()
BEGIN
    DECLARE userId INT;
    DECLARE userName VARCHAR(255);
    DECLARE userAge INT;
    
    -- 假设JSON数据格式为 {"id": 1, "name": "Alice", "age": 30}
    SELECT JSON_EXTRACT(data, '$.id') INTO userId,
           JSON_EXTRACT(data, '$.name') INTO userName,
           JSON_EXTRACT(data, '$.age') INTO userAge
    FROM users
    WHERE id = 1;
    
    -- 处理解析后的数据
    SELECT userId, userName, userAge;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE ParseUserJson()
BEGIN
    DECLARE userId INT;
    DECLARE userName VARCHAR(255);
    DECLARE userAge INT;
    
    -- 假设JSON数据格式为 {"id": 1, "name": "Alice", "age": 30}
    IF JSON_VALID(data) THEN
        SELECT JSON_EXTRACT(data, '$.id') INTO userId,
               JSON_EXTRACT(data, '$.name') INTO userName,
               JSON_EXTRACT(data, '$.age') INTO userAge
        FROM users
        WHERE id = 1;
        
        -- 处理解析后的数据
        SELECT userId, userName, userAge;
    ELSE
        SELECT 'Invalid JSON data';
    END IF;
END //

DELIMITER ;