MySQL 写sql for循环插入三亿数据

原创

mob649e81563816 2024-09-27 05:56:25 ©著作权

文章标签 数据存储过程插入数据 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者mob649e81563816的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL批量插入与for循环：如何在数据库中插入三亿条数据

在很多应用场景中，特别是大数据分析、机器学习和数据挖掘等领域，我们需要处理大量的数据。这里，我们将讨论如何在MySQL中使用for循环批量插入三亿条数据。这将包括代码示例、性能优化和一些注意事项。

概述

MySQL是一种广泛使用的关系型数据库管理系统，支持复杂的查询和事务。批量插入数据是一项重要的技能，它可以显著提高插入数据的效率。我们将使用存储过程和循环来实现这一功能。

插入三亿条数据的准备

在插入数据之前，我们首先需要创建一个表。在本例中，我们将创建一个简单的表，用于存储用户数据。

CREATE TABLE user_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255),
    age INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

定义一个存储过程

接下来，我们将定义一个存储过程，利用for循环来批量插入数据。

DELIMITER //

CREATE PROCEDURE InsertUserData(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 0;

    WHILE i < num DO
        INSERT INTO user_data (username, age)
        VALUES (CONCAT('user_', i), FLOOR(18 + RAND() * 50)); -- 随机生成年龄
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

在上面的代码中，我们定义了一个名为InsertUserData的存储过程。该过程接受一个参数num，用于指定需要插入的数据条数。循环内我们使用INSERT语句，并通过CONCAT函数生成用户名称，同时使用RAND()生成一个随机年龄。

执行存储过程

接下来，我们可以调用这个存储过程，插入三亿条数据。为了避免长时间的数据库锁定，我们可以将数据分批次插入，比如每次插入百万条：

SET @batchSize = 1000000; -- 每次批量插入百万条
SET @totalData = 300000000; -- 总数据量三亿
SET @iterations = @totalData / @batchSize;

SET @i = 0;

WHILE @i < @iterations DO
    CALL InsertUserData(@batchSize);
    SET @i = @i + 1;
END WHILE;

确保性能

在插入大量数据时，性能是一个非常重要的因素。有以下几种优化方式：

禁用索引: 在大规模插入数据前禁用索引，插入完成后再重新启用，可显著提高插入速度。
```
ALTER TABLE user_data DISABLE KEYS;
-- 执行插入过程
ALTER TABLE user_data ENABLE KEYS;
```
使用事务: 将多次插入放在一个事务中，可以减少I/O操作。
```
START TRANSACTION;
CALL InsertUserData(@batchSize);
COMMIT;
```
合并插入: 如果数据能够合并插入，可以利用多值插入语句。

状态图：过程的执行状态

在执行插入过程时，我们可以使用状态图来展示它的执行过程：

stateDiagram
    direction LR
    [*] --> 开始
    开始 --> 初始化
    初始化 --> 循环开始
   循环开始 --> 插入数据
    插入数据 --> 检查条件
    检查条件 --> 循环结束 : 满足条件
    检查条件 --> 插入数据 : 不满足条件
    循环结束 --> 完成
    完成 --> [*]