mysql 批量导入数据

基础概念

MySQL批量导入数据是指将大量数据一次性导入到MySQL数据库中，而不是逐条插入。这种方法通常用于快速加载初始数据集或在数据迁移时使用。

优势

1. 效率高：批量导入比逐条插入快得多，因为它减少了与数据库的交互次数。
2. 减少网络开销：批量导入减少了网络传输的数据量。
3. 减少锁竞争：批量操作可以减少对表的锁定时间，从而减少对其他并发操作的影响。

类型

1. 使用LOAD DATA INFILE命令：这是MySQL提供的一个高效的导入工具，可以直接从文件中导入数据。
2. 使用INSERT INTO ... VALUES (...), (...), ...语句：可以在一个INSERT语句中插入多行数据。
3. 使用程序语言的批量插入API：如Python的pymysql库或Java的JDBC。

应用场景

• 数据迁移：将数据从一个数据库迁移到另一个数据库。
• 初始化数据：在系统上线前导入初始数据集。
• 批量更新：需要一次性更新大量记录的场景。

常见问题及解决方法

问题1：导入速度慢

原因：可能是由于网络延迟、磁盘I/O瓶颈或数据库配置不当。

解决方法：

• 确保数据库服务器和客户端之间的网络连接良好。
• 使用SSD硬盘以提高磁盘I/O性能。
• 调整MySQL配置，如增加innodb_buffer_pool_size和innodb_log_file_size。

问题2：导入过程中出现错误

原因：可能是数据文件格式不正确、数据中包含非法字符或违反约束条件。

解决方法：

• 检查数据文件的格式是否正确，确保每行数据的列数和数据类型匹配。
• 使用LOAD DATA INFILE的IGNORE或REPLACE选项来处理非法数据。
• 在导入前对数据进行预处理，确保数据符合数据库的约束条件。

问题3：导入后数据不一致

原因：可能是由于并发操作或事务处理不当。

解决方法：

• 使用事务来确保批量导入操作的原子性。
• 在导入前禁用外键检查，导入后再启用。

示例代码

以下是一个使用Python和pymysql库进行批量插入的示例：

import pymysql

# 连接数据库
conn = pymysql.connect(host='localhost', user='user', password='password', db='database')
cursor = conn.cursor()

# 批量插入数据
data = [
    (1, 'Alice', 25),
    (2, 'Bob', 30),
    (3, 'Charlie', 35)
]

sql = "INSERT INTO users (id, name, age) VALUES (%s, %s, %s)"
cursor.executemany(sql, data)

# 提交事务
conn.commit()

# 关闭连接
cursor.close()
conn.close()

参考链接

• MySQL官方文档 - LOAD DATA INFILE
• Python pymysql库文档

希望这些信息对你有所帮助！如果有更多具体问题，欢迎继续提问。