如何将txt文件导入mysql

LOAD DATA INFILE 'path/to/your/file.txt'
INTO TABLE your_table_name
FIELDS TERMINATED BY ',' -- 根据你的文件内容调整分隔符
ENCLOSED BY '"' -- 如果字段被引号包围
LINES TERMINATED BY '
' -- 根据你的操作系统和文件格式调整行终止符
IGNORE 1 LINES; -- 如果你的文件第一行是标题行，使用此选项忽略它

mysqlimport --local --fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='
' -u your_username -p your_database_name path/to/your/file.txt

import mysql.connector

# 连接到MySQL数据库
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database_name"
)

cursor = db.cursor()

# 打开txt文件
with open('path/to/your/file.txt', 'r') as file:
    for line in file:
        # 假设每行数据是用逗号分隔的
        data = line.strip().split(',')
        # 插入数据到MySQL
        sql = "INSERT INTO your_table_name (column1, column2, column3) VALUES (%s, %s, %s)"
        cursor.execute(sql, tuple(data))

# 提交事务
db.commit()

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