mysql循环分批提交

import mysql.connector

# 连接到MySQL数据库
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

cursor = db.cursor()

# 假设我们有一个包含大量数据的列表
data_to_insert = [
    (1, "Alice"),
    (2, "Bob"),
    (3, "Charlie"),
    # ... 更多数据
]

batch_size = 1000
for i in range(0, len(data_to_insert), batch_size):
    batch = data_to_insert[i:i + batch_size]
    sql = "INSERT INTO users (id, name) VALUES (%s, %s)"
    cursor.executemany(sql, batch)
    db.commit()
    print(f"Committed batch {i // batch_size + 1}")

cursor.close()
db.close()

SET GLOBAL innodb_lock_wait_timeout = 120; -- 设置全局事务超时时间为120秒

ALTER TABLE users ADD INDEX idx_name (name); -- 添加索引以减少锁竞争