mysql插入180万条交易记录

原创

mob64ca12d1a59e 2024-08-25 04:56:02 ©著作权

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

如何在 MySQL 中插入 180 万条交易记录

作为一名开发者，处理大量数据插入是我们经常面临的挑战。本文将引导您完成在 MySQL 数据库中插入 180 万条交易记录的过程。我们将一步步解析每一个环节，并提供相应的代码示例，以帮助您快速实现这一目标。

整体流程

首先，让我们概述一下整个过程的步骤。下表展示了从准备数据到插入数据的流程。

步骤	描述
1	准备数据库和表
2	生成测试交易数据
3	编写插入数据的 SQL 语句
4	执行插入操作
5	验证插入是否成功

各步骤详细介绍

步骤 1：准备数据库和表

在开始插入之前，您需要有一个数据库和一个表用于存储数据。我们将创建一个名为 transactions 的表，结构包括 id、amount、transaction_date 等字段。

CREATE DATABASE IF NOT EXISTS example_db;

USE example_db;

CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10, 2),
    transaction_date DATETIME
);

CREATE DATABASE IF NOT EXISTS example_db;：创建数据库 example_db，如果已经存在则不再创建。
USE example_db;：选择使用的数据库。
CREATE TABLE IF NOT EXISTS transactions (...)：创建 transactions 表，定义列及其属性。

步骤 2：生成测试交易数据

我们需要生成 180 万条数据。为了简单起见，我们可以使用 Python 脚本生成这些数据：

import random
import datetime

def generate_transaction_data(num_records):
    transactions = []
    for _ in range(num_records):
        amount = round(random.uniform(10, 1000), 2)  # 随机生成金额
        transaction_date = datetime.datetime.now()  # 使用当前时间
        transactions.append((amount, transaction_date))
    return transactions

transactions = generate_transaction_data(1800000)

random.uniform(10, 1000)：生成 10 到 1000 之间的随机金额。
datetime.datetime.now()：获取当前时间，就可作为交易日期。

步骤 3：编写插入数据的 SQL 语句

生成完数据后，我们需要编写 SQL 语句来插入数据。一次性插入大量数据时，可以使用 INSERT INTO ... VALUES 语法。

import mysql.connector

# 连接 MySQL 数据库
conn = mysql.connector.connect(user='your_username', password='your_password', host='localhost', database='example_db')
cursor = conn.cursor()

insert_query = "INSERT INTO transactions (amount, transaction_date) VALUES (%s, %s)"
transaction_count = 0

for transaction in transactions:
    cursor.execute(insert_query, transaction)
    transaction_count += 1
    if transaction_count % 10000 == 0:  # 每 10000 条提交一次
        conn.commit()
        print(f"Inserted {transaction_count} transactions.")

# 提交剩余的数据
conn.commit()
cursor.close()
conn.close()

INSERT INTO transactions (amount, transaction_date) VALUES (%s, %s)：预定义插入语句，使用占位符。
cursor.execute(insert_query, transaction)：执行插入操作。
if transaction_count % 10000 == 0：每插入 10000 条数据提交一次，这样能有效提高性能。

步骤 4：执行插入操作

在上面的脚本中，我们已经在循环中执行插入操作。通过分批提交，减少了内存使用，并加快了插入速度。

步骤 5：验证插入是否成功

插入完成后，您可以通过以下查询来验证数据是否成功插入：

SELECT COUNT(*) FROM transactions;

这将返回 transactions 表中的记录数，确认其为 1800000。

状态图

在完整流程中，我们可以用状态图来展示整个插入流程。以下是一个简单的状态图描述：

stateDiagram
    [*] --> 准备数据库和表
    准备数据库和表 --> 生成测试交易数据
    生成测试交易数据 --> 编写插入数据的 SQL 语句
    编写插入数据的 SQL 语句 --> 执行插入操作
    执行插入操作 --> 验证插入是否成功
    验证插入是否成功 --> [*]