mysql 一对多批量插入

-- 创建用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- 创建订单表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 批量插入用户
INSERT INTO users (name) VALUES
('Alice'),
('Bob'),
('Charlie');

-- 批量插入订单
INSERT INTO orders (user_id, amount) VALUES
(1, 100.00),
(1, 200.00),
(2, 150.00),
(3, 75.00);

-- 创建存储过程批量插入用户和订单
DELIMITER //

CREATE PROCEDURE BatchInsertUsersAndOrders()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 3 DO
        INSERT INTO users (name) VALUES (CONCAT('User', i));
        INSERT INTO orders (user_id, amount) VALUES (i, i * 50.00);
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

-- 调用存储过程
CALL BatchInsertUsersAndOrders();

INSERT IGNORE INTO users (name) VALUES ('Alice');

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO orders (user_id, amount) VALUES (LAST_INSERT_ID(), 100.00);

START TRANSACTION;

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO orders (user_id, amount) VALUES (LAST_INSERT_ID(), 100.00);

COMMIT;