基于MySQL，使用存储过程，创建十张表，并每张表插入一百万条数据

步骤 1：创建十张表

-- 创建表 1 到 10
DELIMITER $$
CREATE PROCEDURE CreateTables()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 10 DO
        SET @sql = CONCAT('
            CREATE TABLE IF NOT EXISTS table_', i, ' (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255),
                email VARCHAR(255),
                created_at DATETIME,
                updated_at DATETIME,
                is_active BOOLEAN
            ) ENGINE=InnoDB;
        ');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

-- 执行创建表
CALL CreateTables();
DROP PROCEDURE CreateTables;

步骤 2：创建存储过程批量插入数据

DELIMITER $$
CREATE PROCEDURE BulkInsert(
    IN tableName VARCHAR(255),
    IN batchSize INT,        -- 每批插入数量（建议100-1000）
    IN totalRecords INT      -- 总插入数量（例如1000000）
)
BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE batches INT DEFAULT totalRecords / batchSize;
    DECLARE i INT DEFAULT 0;
    DECLARE j INT DEFAULT 0;
    DECLARE sql_text LONGTEXT;

    SET autocommit = 0;

    WHILE i < batches DO
        SET sql_text = '';
        SET j = 0;
        WHILE j < batchSize DO
		    -- 生成随机数据
            SET @name = CONCAT('User_', LEFT(UUID(), 8), '_', FLOOR(RAND() * 1000000));
            SET @email = CONCAT('miaoqiang', '@example.com');
            SET @created_at = NOW() - INTERVAL FLOOR(RAND() * 365 * 3) DAY;
            SET @updated_at = @created_at + INTERVAL FLOOR(RAND() * 30) DAY;
            SET @is_active = RAND() > 0.5;

            -- 拼接VALUES
            SET sql_text = CONCAT(sql_text,
                '("', @name, '","', @email, '","',
                DATE_FORMAT(@created_at, '%Y-%m-%d %H:%i:%s'), '","',
                DATE_FORMAT(@updated_at, '%Y-%m-%d %H:%i:%s'), '",',
                @is_active, '),');
            SET j = j + 1;
        END WHILE;

        -- 移除末尾逗号并构建完整SQL
        SET sql_text = LEFT(sql_text, LENGTH(sql_text) - 1);
        SET @sql = CONCAT('INSERT INTO ', tableName, 
            ' (name, email, created_at, updated_at, is_active) VALUES ', sql_text);

        -- 执行插入
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 每100批提交一次事务
        IF i % 100 = 0 THEN
            COMMIT;
            START TRANSACTION;
        END IF;

        SET i = i + 1;
        -- 输出进度（可选）
        IF i % 100 = 0 THEN
            SELECT CONCAT(tableName, ': ', (i * batchSize), ' inserted') AS Progress;
        END IF;
    END WHILE;

    -- 提交剩余事务并恢复设置
    COMMIT;
    SET autocommit = 1;
END$$
DELIMITER ;

步骤 3：执行存储过程插入数据

-- 插入数据到 table_1（其他表类似）
CALL BulkInsert('table_1', 1000, 1000000);
-- 重复调用以下命令，替换表名直到 table_10
-- CALL BulkInsert('table_2', 1000, 1000000);
-- ...

验证数据

插入完成后检查数据量：

SELECT COUNT(*) FROM table_1;