文章目录一、批量插入的优势二、MySQL 表的创建示例三、Python 实现批量插入1. 安装 PyMySQL 和 Faker 库2. 生成 1 万条随机的学生数据3. 批量插入数据到 MySQL4. 代码详解四、性能优化建议五、总结在日常开发中我们经常需要将大量数据批量插入到 MySQL 数据库中。然而逐行插入单条执行INSERT INTO的方式效率较低尤其在处理大规模数据时会导致性能瓶颈。为了解决这个问题我们可以使用批量插入技术显著提升数据插入效率。本文将介绍批量插入的原理、实现方法并结合 Python 和PyMySQL库提供详细的实战示例。一、批量插入的优势批量插入数据有以下几个优点减少网络交互批量插入一次性传输多条记录减少客户端与数据库之间的网络通信次数。提高事务效率批量插入可以减少事务的提交次数从而降低事务管理的开销。提高插入性能批量插入可以有效地降低数据库的锁定资源时间使插入操作更高效。二、MySQL 表的创建示例我们以学生信息表为例假设有如下的表结构CREATE TABLE students ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), age INT, gender ENUM(M, F), grade VARCHAR(10) );表students用于存储学生的基本信息包括id主键name姓名age年龄gender性别以及grade成绩。三、Python 实现批量插入接下来我们使用 Python 的PyMySQL库来连接 MySQL并实现批量插入数据。1. 安装 PyMySQL 和 Faker 库首先确保已经安装了PyMySQL和Faker库。如果尚未安装可以使用以下命令进行安装pip install pymysql faker2. 生成 1 万条随机的学生数据使用Faker库生成随机的学生信息数据包括姓名、年龄、性别和成绩。以下是生成数据的代码import random from faker import Faker # 初始化 Faker fake Faker() # 随机生成学生数据 def generate_random_students(num_records10000): students_data [] for _ in range(num_records): name fake.name() age random.randint(18, 25) # 随机年龄在 18 到 25 岁之间 gender random.choice([M, F]) # 随机选择性别 grade random.choice([A, B, C, D, F]) # 随机选择成绩 students_data.append((name, age, gender, grade)) return students_data # 生成 1 万条学生数据 students_data generate_random_students(10000) # 输出前 5 条数据查看 for student in students_data[:5]: print(student)3. 批量插入数据到 MySQL批量插入的核心思路是将数据分成若干批次使用executemany方法执行批量插入操作。下面是批量插入的完整代码import pymysql from tqdm import tqdm # 创建数据库连接 connection pymysql.connect( hostlocalhost, useryour_username, passwordyour_password, databaseyour_database, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor ) # 批量插入的批次大小 BATCH_SIZE 1000 try: with connection.cursor() as cursor: batch [] for student in tqdm(students_data, totallen(students_data)): batch.append(student) # 当批次达到 BATCH_SIZE 时执行批量插入 if len(batch) BATCH_SIZE: sql INSERT INTO students (name, age, gender, grade) VALUES (%s, %s, %s, %s) cursor.executemany(sql, batch) batch [] # 清空批次 # 插入剩余的未满批次的数据 if batch: sql INSERT INTO students (name, age, gender, grade) VALUES (%s, %s, %s, %s) cursor.executemany(sql, batch) # 提交事务 connection.commit() except Exception as e: print(f插入数据时出现错误: {e}) connection.rollback() finally: # 关闭数据库连接 connection.close()4. 代码详解生成随机数据使用generate_random_students函数生成 1 万条随机学生数据并存储在students_data列表中。数据库连接使用PyMySQL连接到 MySQL 数据库并禁用自动提交模式以便手动管理事务。批量插入将数据分成大小为BATCH_SIZE的批次进行插入操作。使用cursor.executemany方法批量插入每个批次的数据这样可以减少 SQL 执行次数提高效率。处理剩余数据如果数据量不足一个批次最后将剩余数据插入。事务管理在插入成功后调用connection.commit()提交事务如果发生错误则进行回滚。关闭连接无论操作是否成功都需要关闭数据库连接。四、性能优化建议调整批次大小可以根据具体的硬件和数据量情况适当调整批次大小BATCH_SIZE通常 500 到 1000 条为一个批次较为合适。禁用自动提交将自动提交模式禁用connection.autocommit(False)可以提高插入效率。删除或禁用索引在大量数据插入时可以暂时禁用或删除表上的索引插入完成后再重新建立索引。批量插入语句优化可以将INSERT INTO语句改为INSERT IGNORE或INSERT ON DUPLICATE KEY UPDATE来处理主键冲突的情况。unique: 尽量少用unique。当表的数据量很大时每插入一个数据都会判断该值是否唯一会导致数据插入数据越来越慢。五、总结批量插入是提高 MySQL 数据插入性能的重要手段。通过使用批量插入技术可以显著减少 SQL 执行次数提高数据导入的效率。本文通过一个学生信息表的实战示例详细介绍了批量插入的实现方法并提供了性能优化的建议。希望这篇文章对您在处理大规模数据时有所帮助。如果有更复杂的数据处理需求您还可以考虑使用 MySQL 的LOAD DATA语句或专门的 ETL 工具来进行数据导入操作。