mysql导入外部文件

基础概念

MySQL导入外部文件是指将数据从一个文件（如CSV、TXT等）导入到MySQL数据库中的过程。这通常用于批量插入数据，提高数据导入效率。

类型

LOAD DATA INFILE：MySQL提供的一个命令，用于从服务器主机上的文本文件中快速地导入数据到表中。
mysqlimport：一个命令行工具，用于从文本文件中导入数据到MySQL数据库。
使用编程语言（如Python、PHP等）的库：通过编程语言的数据库连接库，可以编写脚本来实现数据导入。

应用场景

数据迁移：将旧数据库的数据迁移到新数据库。
批量数据更新：一次性导入大量更新后的数据。
初始数据填充：为新数据库或新表填充初始数据。

常见问题及解决方法

问题1：无法导入文件

原因：可能是文件路径不正确，或者MySQL用户没有足够的权限读取文件。

解决方法：

确保文件路径正确，并且文件存在于指定路径。
检查MySQL用户的权限，确保其有权读取文件。可以通过GRANT命令授予权限。

GRANT FILE ON *.* TO 'username'@'localhost';

问题2：数据格式不匹配

原因：导入的数据格式与目标表的字段类型或格式不匹配。

解决方法：

检查导入文件的格式，确保其与目标表的字段类型一致。
使用LOAD DATA INFILE命令时，可以通过FIELDS TERMINATED BY、LINES TERMINATED BY等选项指定字段和行的分隔符。

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

问题3：编码问题

原因：导入文件的编码与MySQL数据库的编码不一致，导致乱码。

解决方法：

确保导入文件的编码与MySQL数据库的编码一致。可以通过SET NAMES命令设置字符集。

SET NAMES 'utf8mb4';

示例代码

以下是一个使用Python和mysql-connector-python库导入CSV文件的示例：

import mysql.connector
import csv

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

cursor = db.cursor()

# 打开CSV文件
with open('data.csv', newline='') as csvfile:
    csvreader = csv.reader(csvfile)
    next(csvreader)  # 跳过标题行
    for row in csvreader:
        cursor.execute("INSERT INTO table_name (column1, column2, column3) VALUES (%s, %s, %s)", row)

# 提交事务
db.commit()

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