在数据处理场景中，将外部数据文件导入MySQL数据库是高频操作，不同格式的文件对应不同的导入逻辑，下面分别介绍JSON、Text、XML、CSV四种格式文件的导入方法。
CSV文件导入MySQL
CSV是最常用的结构化数据文件格式，MySQL提供了原生的LOAD DATA语句支持CSV文件导入，操作效率很高。
首先需要在MySQL中创建对应的表结构，假设我们要导入用户数据，表结构如下：
-- 创建用户表
CREATE TABLE user_info (
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(50) NOT NULL,
age INT,
email VARCHAR(100)
);
假设CSV文件user.csv内容如下，路径为/tmp/user.csv：
张三,25,zhangsan@ipipp.com 李四,30,lisi@ipipp.com 王五,28,wangwu@ipipp.com
使用LOAD DATA语句导入的SQL如下：
LOAD DATA INFILE '/tmp/user.csv' INTO TABLE user_info FIELDS TERMINATED BY ',' -- 字段分隔符为逗号 LINES TERMINATED BY 'n' -- 行分隔符为换行 IGNORE 0 ROWS -- 忽略表头行，如果没有表头改为0 (user_name, age, email); -- 指定CSV列对应表的字段，自增id会自动生成
如果MySQL没有开启local_infile权限，需要先在配置文件中开启，或者执行以下命令：
SET GLOBAL local_infile = 1;
Text文件导入MySQL
Text文件通常是非结构化或者半结构化文本，导入方式需要根据文本的格式调整，如果是按固定分隔符分割的结构化Text文件，同样可以使用LOAD DATA语句。
假设Text文件user.txt使用竖线分割字段，内容如下：
张三|25|zhangsan@ipipp.com 李四|30|lisi@ipipp.com 王五|28|wangwu@ipipp.com
导入SQL只需要调整字段分隔符参数即可：
LOAD DATA INFILE '/tmp/user.txt' INTO TABLE user_info FIELDS TERMINATED BY '|' -- 字段分隔符为竖线 LINES TERMINATED BY 'n' (user_name, age, email);
如果是非结构化的Text文件，需要先将文本拆分提取出结构化字段，再通过程序拼接成SQL插入，或者使用LOAD DATA导入后做二次处理。
JSON文件导入MySQL
MySQL 5.7及以上版本原生支持JSON类型，导入JSON文件可以通过两种方式实现，一种是直接导入JSON格式的行数据，另一种是解析JSON文件中的字段映射到表字段。
假设JSON文件user.json内容如下，每行是一个独立的JSON对象：
{"user_name": "张三", "age": 25, "email": "zhangsan@ipipp.com"}
{"user_name": "李四", "age": 30, "email": "lisi@ipipp.com"}
{"user_name": "王五", "age": 28, "email": "wangwu@ipipp.com"}
首先创建包含JSON字段的表，或者解析JSON字段到普通字段：
-- 方式1：直接存储JSON到JSON类型字段
CREATE TABLE user_json (
id INT PRIMARY KEY AUTO_INCREMENT,
data JSON
);
-- 方式2：解析JSON字段到普通表字段
CREATE TABLE user_info_json (
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(50),
age INT,
email VARCHAR(100)
);
导入到JSON字段表的SQL：
LOAD DATA INFILE '/tmp/user.json' INTO TABLE user_json LINES TERMINATED BY 'n' (@json_data) -- 临时变量接收每行内容 SET data = @json_data; -- 赋值给JSON字段
导入到解析后普通字段表的SQL：
LOAD DATA INFILE '/tmp/user.json' INTO TABLE user_info_json LINES TERMINATED BY 'n' (@json_data) SET user_name = JSON_EXTRACT(@json_data, '$.user_name'), age = JSON_EXTRACT(@json_data, '$.age'), email = JSON_EXTRACT(@json_data, '$.email');
XML文件导入MySQL
MySQL没有原生的XML导入语句，通常需要借助编程语言解析XML后生成插入SQL，或者使用第三方工具。下面以Python为例，演示解析XML文件后导入MySQL的流程。
假设XML文件user.xml内容如下：
<users>
<user>
<user_name>张三</user_name>
<age>25</age>
<email>zhangsan@ipipp.com</email>
</user>
<user>
<user_name>李四</user_name>
<age>30</age>
<email>lisi@ipipp.com</email>
</user>
<user>
<user_name>王五</user_name>
<age>28</age>
<email>wangwu@ipipp.com</email>
</user>
</users>
Python解析并导入的代码如下：
import xml.etree.ElementTree as ET
import pymysql
# 连接MySQL数据库
conn = pymysql.connect(
host='127.0.0.1',
user='root',
password='your_password',
database='test_db',
charset='utf8mb4'
)
cursor = conn.cursor()
# 解析XML文件
tree = ET.parse('/tmp/user.xml')
root = tree.getroot()
# 遍历XML节点生成插入SQL
for user in root.findall('user'):
user_name = user.find('user_name').text
age = user.find('age').text
email = user.find('email').text
sql = "INSERT INTO user_info (user_name, age, email) VALUES (%s, %s, %s)"
cursor.execute(sql, (user_name, age, email))
# 提交事务并关闭连接
conn.commit()
cursor.close()
conn.close()
导入注意事项
- 导入前确认文件编码和MySQL表编码一致，避免中文乱码，建议统一使用utf8mb4编码。
- 使用
LOAD DATA语句时，需要确认MySQL进程对文件有读取权限，文件路径需要是MySQL服务器可访问的路径。 - 大文件导入时建议分批操作，避免单次导入数据量过大导致数据库压力过大。
- 导入前可以先备份目标表数据，防止导入错误导致原有数据丢失。