mysql json数据处理

根据您提供的JSON结构，我将创建两个表和一个存储过程。以下是完整的SQL代码：

1. 创建两张表

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    isActive BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建产品表
CREATE TABLE products (
    productId VARCHAR(20) PRIMARY KEY,
    productName VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 创建存储过程

DELIMITER $$

CREATE PROCEDURE InsertFromJSON(
    IN p_json_data TEXT,
    OUT p_result INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = 0;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- 清空表数据（如果需要保留历史数据，请删除这两行）
    DELETE FROM products;
    DELETE FROM users;
    
    -- 插入用户数据
    INSERT INTO users (id, name, email, isActive)
    SELECT 
        JSON_EXTRACT(user_obj, '$.id') AS id,
        JSON_UNQUOTE(JSON_EXTRACT(user_obj, '$.name')) AS name,
        JSON_UNQUOTE(JSON_EXTRACT(user_obj, '$.email')) AS email,
        JSON_EXTRACT(user_obj, '$.isActive') AS isActive
    FROM JSON_TABLE(
        JSON_EXTRACT(p_json_data, '$.users'),
        '$[*]' COLUMNS (
            user_obj JSON PATH '$'
        )
    ) AS users;
    
    -- 插入产品数据
    INSERT INTO products (productId, productName, price, category)
    SELECT 
        JSON_UNQUOTE(JSON_EXTRACT(product_obj, '$.productId')) AS productId,
        JSON_UNQUOTE(JSON_EXTRACT(product_obj, '$.productName')) AS productName,
        JSON_EXTRACT(product_obj, '$.price') AS price,
        JSON_UNQUOTE(JSON_EXTRACT(product_obj, '$.category')) AS category
    FROM JSON_TABLE(
        JSON_EXTRACT(p_json_data, '$.products'),
        '$[*]' COLUMNS (
            product_obj JSON PATH '$'
        )
    ) AS products;
    
    COMMIT;
    SET p_result = 1;
    
END
$$

DELIMITER ;

3. 使用示例

-- 调用存储过程
SET @json_data = '{
  "users": [
    {
      "id": 1,
      "name": "张三",
      "email": "zhangsan@example.com",
      "isActive": true
    },
    {
      "id": 2,
      "name": "李四",
      "email": "lisi@example.com",
      "isActive": false
    },
    {
      "id": 3,
      "name": "王五",
      "email": "wangwu@example.com",
      "isActive": true
    }
  ],
  "products": [
    {
      "productId": "P001",
      "productName": "笔记本电脑",
      "price": 5999.99,
      "category": "电子产品"
    },
    {
      "productId": "P002",
      "productName": "办公椅",
      "price": 899.5,
      "category": "家具"
    }
  ]
}';

SET @result = 0;
CALL InsertFromJSON(@json_data, @result);
SELECT @result AS 执行结果;

4. 验证插入结果

-- 查看用户表数据
SELECT * FROM users;

-- 查看产品表数据
SELECT * FROM products;

存储过程特点说明：
事务处理：使用事务确保数据一致性，要么全部成功，要么全部回滚
错误处理：通过异常处理器捕获错误，失败时返回0
JSON解析：使用MySQL的JSON函数解析JSON字符串
数据清理：存储过程会先清空表数据，如需保留历史数据请删除DELETE语句
返回值：成功返回1，失败返回0
这个存储过程可以正确处理您提供的JSON格式，并将数据分别插入到对应的表中。