后端 - 如何在MySQL中高效处理多表插入操作？

Question

目前有个需求是要将csv文件内容分别插入到某数据库的两张表中，有一定依赖关系。由于一直是学生本科刚大四没毕业，故实战经验与性能优化经验薄弱

demo信息如下：

两张表分别是角色信息表和用户信息表

角色信息表

学号/工号	姓名	性别	角色（依赖权限表id）	科目表id（依赖于科目表）

用户信息表

uuid	手机号	密码	角色id（依赖于角色信息表）

权限表：老师、管理员、学生

科目表：语、数、英……

想请教各位大佬，用sql语句一次性批量插入与使用for循环去插入，性能方面影响大吗

---

注：数据库环境为MySQL

Answer 1

会excel吗？也可以在excel中处理好数据，navicat直接导入excel。以【角色信息表】、【科目表】为例：
1）导入【科目表】数据
2）导出【科目表】数据为excel文件（包含科目表id），为E1
3）将【角色信息表】.csv文件转为excel，为E2。用excel的vlookup函数，将E2中的 科目表名称 替换为E1中的 科目表id
......
这个会替换操作之后，其他的也是替换为对应的id，然后再将整理好的excel导入

Answer 2

在MySQL中，一次性批量插入通常比循环插入性能更好。这是因为批量插入可以减少与数据库的交互次数，降低网络开销，并允许数据库更有效地处理数据。

对于你的情况，考虑到你有两张表，并且它们之间存在依赖关系，建议采取以下策略：

1. 预处理CSV数据：

* 首先，读取CSV文件，并将其内容解析为数据结构（如Python中的列表或字典）。* 确保数据的完整性和准确性，处理任何可能的错误或缺失值。

2. 使用事务：

* 开始一个事务，以确保数据的完整性。* 批量插入数据到角色信息表和用户信息表。

3. 批量插入到角色信息表：

* 对于角色信息表，可以一次性插入多条记录。例如，使用`INSERT INTO`语句，并列出所有要插入的记录。* 插入完成后，获取新插入的角色ID（如果是自增的，MySQL会自动分配）。

4. 根据角色ID批量插入到用户信息表：

* 使用上一步中获得的角色ID，为用户信息表中的每条记录设置`角色id`字段。* 同样，一次性插入多条记录到用户信息表。

5. 提交事务：

* 如果所有插入操作都成功，则提交事务。* 如果在插入过程中发生错误，可以回滚事务，以确保数据的一致性。

示例SQL语句（假设你使用的是Python）：

import mysql.connector# 连接数据库cnx = mysql.connector.connect(user='your_username', password='your_password', host='your_host', database='your_database')cursor = cnx.cursor()# 预处理CSV数据# 假设 data 是一个包含所有数据的列表，每个元素是一个字典，表示一条记录# 开始事务cnx.start_transaction()try:    # 批量插入角色信息    query = "INSERT INTO 角色信息表 (学号/工号, 姓名, 性别, 角色, 科目表id) VALUES (%s, %s, %s, %s, %s)"    cursor.executemany(query, [(item['学号/工号'], item['姓名'], item['性别'], item['角色'], item['科目表id']) for item in data])    # 获取新插入的角色ID    cnx.commit()    cursor.execute("SELECT LAST_INSERT_ID()")    last_role_id = cursor.fetchone()[0]    # 批量插入用户信息    query = "INSERT INTO 用户信息表 (uuid, 手机号, 密码, 角色id) VALUES (%s, %s, %s, %s)"    cursor.executemany(query, [(item['uuid'], item['手机号'], item['密码'], last_role_id) for item in data])    # 提交事务    cnx.commit()except mysql.connector.Error as err:    # 发生错误时回滚事务    cnx.rollback()    print("Error:", err)finally:    cursor.close()    cnx.close()

注意：这只是一个基本示例，你可能需要根据你的具体需求和数据结构进行调整。

总的来说，使用批量插入而不是循环插入可以显著提高性能，特别是在处理大量数据时。但是，你也需要确保数据的一致性和完整性，这通常意味着使用事务来管理你的插入操作。