mysql一条数据拆分多条

-- 创建原订单表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    items JSON
);

-- 插入示例数据
INSERT INTO orders (id, items) VALUES
(1, '[{"product_id": 101, "quantity": 2}, {"product_id": 102, "quantity": 1}]');

-- 创建拆分后的商品表
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- 拆分数据
INSERT INTO order_items (order_id, product_id, quantity)
SELECT id, JSON_UNQUOTE(JSON_EXTRACT(items, CONCAT('$[', idx, '].product_id')), JSON_UNQUOTE(JSON_EXTRACT(items, CONCAT('$[', idx, '].quantity')))
FROM orders, (SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS indexes
WHERE JSON_LENGTH(items) > idx;

START TRANSACTION;

-- 拆分数据的SQL语句
INSERT INTO order_items (order_id, product_id, quantity)
SELECT id, JSON_UNQUOTE(JSON_EXTRACT(items, CONCAT('$[', idx, '].product_id')), JSON_UNQUOTE(JSON_EXTRACT(items, CONCAT('$[', idx, '].quantity')))
FROM orders, (SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS indexes
WHERE JSON_LENGTH(items) > idx;

COMMIT;