mysql 生成随机订单

-- 创建商品表
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2)
);

-- 插入一些示例商品数据
INSERT INTO products (name, price) VALUES
('Product A', 10.99),
('Product B', 15.99),
('Product C', 20.99);

-- 创建订单表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(255) UNIQUE,
    customer_id INT
);

-- 生成随机订单
DELIMITER //
CREATE PROCEDURE generate_random_order()
BEGIN
    DECLARE order_number VARCHAR(255);
    DECLARE customer_id INT;
    DECLARE product_count INT DEFAULT 3;

    -- 生成随机订单号
    SET order_number = CONCAT('ORDER', FLOOR(RAND() * 1000000));

    -- 随机选择一个客户
    SET customer_id = FLOOR(RAND() * 100) + 1; -- 假设有100个客户

    -- 插入订单
    INSERT INTO orders (order_number, customer_id) VALUES (order_number, customer_id);

    -- 获取刚插入的订单ID
    DECLARE order_id INT;
    SET order_id = LAST_INSERT_ID();

    -- 随机选择商品并插入到订单详情表中
    INSERT INTO order_details (order_id, product_id)
    SELECT order_id, id FROM products ORDER BY RAND() LIMIT product_count;
END //
DELIMITER ;

-- 调用存储过程生成随机订单
CALL generate_random_order();