mysql子查询插入

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

INSERT INTO order_items (item_id, order_id, product_id, quantity)
SELECT 1, 1, 101, 2
UNION ALL
SELECT 2, 1, 102, 3;

INSERT INTO order_items (item_id, order_id, product_id, quantity)
SELECT 1, 1, product_id, quantity
FROM (
    SELECT 101 AS product_id, 2 AS quantity
    UNION ALL
    SELECT 102 AS product_id, 3 AS quantity
) AS temp
LIMIT 1000; -- 分批插入

INSERT IGNORE INTO order_items (item_id, order_id, product_id, quantity)
SELECT 1, 1, product_id, quantity
FROM (
    SELECT 101 AS product_id, 2 AS quantity
    UNION ALL
    SELECT 102 AS product_id, 3 AS quantity
) AS temp;