1
2

[mysqld]
local_infile = on  # 启用本地文件加载功能

1
2

sudo systemctl restart mysql  # Ubuntu/Debian
sudo systemctl restart mysqld  # CentOS/RHEL

1
2
3
4
5
6
7
8
9

import mysql.connector
conn = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database",
    allow_local_infile=True  # 关键参数
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

pythonCopy Codedef bulk_load_csv(csv_path):
    """高性能CSV数据导入"""
    conn = mysql.connector.connect(
        host="localhost",
        user="your_username",
        password="your_password",
        database="your_database",
        allow_local_infile=True
    )
    cursor = conn.cursor()
        load_sql = f"""
    LOAD DATA LOCAL INFILE '{csv_path}'
    INTO TABLE users
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"'
    LINES TERMINATED BY '\\n'
    IGNORE 1 LINES
    (id, name, age)
    """
        try:
        cursor.execute(load_sql)
        conn.commit()
        print(f"成功导入 {cursor.rowcount} 条数据")
    except mysql.connector.Error as err:
        print(f"导入失败: {err}")
    finally:
        cursor.close()
        conn.close()
# 执行导入（测试10万行CSV）
bulk_load_csv('data.csv')

1
2
3
4
5
6
7
8

sqlCopy Code/* 执行前禁用索引 */
ALTER TABLE users DISABLE KEYS;
/* 执行后重建索引 */
ALTER TABLE users ENABLE KEYS;
/* 调整缓冲区大小（按需设置） */
SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256;  -- 256MB