问答博客资讯标签用户活动
logo极客观点logo项目管理logoHarmonyOS
javascript
前端
python
node.js
react
vue.js
php
laravel
go
人工智能
mysql
linux
ios
java
android
css
typescript
spring
程序员
logoONES 研发管理logo思否企业问答logo安谋科技 XPU

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

头像
于家汉
    2082889

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

    demo信息如下：

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

    角色信息表

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

    用户信息表

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

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

    科目表：语、数、英……

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

    注：数据库环境为MySQL

    后端性能优化数据库mysql
    阅读 509
    avatarAI BotBETA

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

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

    1. 预处理CSV数据：
    * 首先，读取CSV文件，并将其内容解析为数据结构（如Python中的列表或字典）。
* 确保数据的完整性和准确性，处理任何可能的错误或缺失值。
    1. 使用事务：
    * 开始一个事务，以确保数据的完整性。
* 批量插入数据到角色信息表和用户信息表。
    1. 批量插入到角色信息表：
    * 对于角色信息表，可以一次性插入多条记录。例如，使用`INSERT INTO`语句，并列出所有要插入的记录。
* 插入完成后，获取新插入的角色ID（如果是自增的，MySQL会自动分配）。
    1. 根据角色ID批量插入到用户信息表：
    * 使用上一步中获得的角色ID，为用户信息表中的每条记录设置`角色id`字段。
* 同样，一次性插入多条记录到用户信息表。
    1. 提交事务：
    * 如果所有插入操作都成功，则提交事务。
* 如果在插入过程中发生错误，可以回滚事务，以确保数据的一致性。

    示例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()

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

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

    1 个回答
    得票最新
    头像
    一只小羊
      14114

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

      撰写回答
      你尚未登录，登录后可以
      • 和开发者交流问题的细节
      • 关注并接收问题和回答的更新提醒
      • 参与内容的编辑和改进，让解决方法与时俱进
      推荐问题