45. 数据导入导出与批处理

45.1 导入 CSV：LOAD DATA INFILE

批量导入大量数据时，逐条 INSERT 太慢。LOAD DATA INFILE 是 MySQL 提供的高速导入方式，直接从文件读入表中。

基本语法

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','        -- 字段分隔符
ENCLOSED BY '"'                 -- 字段引用符
LINES TERMINATED BY '\n'        -- 行分隔符
IGNORE 1 ROWS;                  -- 跳过首行（表头）

示例

假设 users.csv 内容：

id,username,email,age
1,张三,zhangsan@example.com,25
2,李四,lisi@example.com,30
3,王五,wangwu@example.com,28

导入：

LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

查看和设置 secure_file_priv

-- 查看允许的目录
SELECT @@secure_file_priv;
-- 结果如：C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\
-- 或：/var/lib/mysql-files/

如果值为空字符串，表示不允许任何文件操作；如果为 NULL，表示完全禁止。需要修改配置文件：

[mysqld]
secure_file_priv = /var/lib/mysql-files

修改后重启 MySQL 生效。

处理重复数据

导入时遇到主键/唯一键冲突的处理：

-- 替换冲突行
LOAD DATA INFILE '/path/users.csv'
REPLACE INTO TABLE users
FIELDS TERMINATED BY ','
IGNORE 1 ROWS;

-- 忽略冲突行
LOAD DATA INFILE '/path/users.csv'
IGNORE INTO TABLE users
FIELDS TERMINATED BY ','
IGNORE 1 ROWS;

45.2 导出数据：SELECT … INTO OUTFILE

把查询结果导出到文件：

SELECT id, username, email
INTO OUTFILE '/var/lib/mysql-files/users_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM users;

命令行客户端导出（客户端侧）

如果想在客户端机器生成文件，用 mysql 命令重定向：

mysql -u root -p -e "SELECT * FROM myapp.users" > users.tsv

或者用 SELECT ... INTO DUMPFILE（单行）或配合 mysqldump。

45.3 mysqldump：逻辑备份与恢复

mysqldump 是 MySQL 自带的逻辑备份工具，生成 SQL 脚本文件，包含建表语句和 INSERT 数据。

备份

# 备份单个数据库
mysqldump -u root -p myapp > myapp_backup.sql

# 备份多个数据库
mysqldump -u root -p --databases myapp other_db > backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql

# 只备份表结构（不加数据）
mysqldump -u root -p --no-data myapp > schema.sql

# 只备份指定表
mysqldump -u root -p myapp users orders > tables.sql

恢复

# 恢复数据库
mysql -u root -p myapp < myapp_backup.sql

# 恢复前库不存在时先创建
mysql -u root -p -e "CREATE DATABASE myapp;"
mysql -u root -p myapp < myapp_backup.sql

常用参数

参数	说明
--single-transaction	InnoDB 表一致性备份（不锁表）
--quick	逐行检索，不缓存整个结果集（大表必备）
--routines	包含存储过程和函数
--triggers	包含触发器（默认包含）
--events	包含事件
--set-gtid-purged=OFF	不记录 GTID 信息（非主从环境）

生产备份推荐写法：

mysqldump -u root -p --single-transaction --routines --quick myapp > myapp_$(date +%Y%m%d).sql

45.4 批处理模式执行 SQL 脚本

前面都是交互式输入 SQL。批处理模式是把 SQL 写在文件里，一次性交给 mysql 执行。

为什么用批处理

• 重复执行的定时任务（日报、清理脚本），写成脚本避免重输
• 多行复杂 SQL，写错编辑方便
• 开发阶段调试长脚本，改完直接重跑

执行方式

# 方式一：重定向输入
mysql -u root -p myapp < script.sql

# 方式二：source 命令（登录后执行）
mysql> source /path/to/script.sql;

# 方式三：-e 执行 source
mysql -u root -p -e "source /path/to/script.sql"

脚本文件示例

init_data.sql：

-- 初始化数据
USE myapp;

INSERT INTO users (username, email, age) VALUES
    ('张三', 'zhangsan@example.com', 25),
    ('李四', 'lisi@example.com', 30);

INSERT INTO orders (user_id, amount, status) VALUES
    (1, 100.00, 'paid'),
    (2, 200.00, 'pending');

执行：

mysql -u root -p < init_data.sql

批处理容错

默认遇到错误就停止。想忽略错误继续执行：

mysql -u root -p --force myapp < script.sql

或者脚本里用 INSERT IGNORE / REPLACE 处理冲突。

45.5 导入导出方法对比

方法	方向	速度	适用场景
LOAD DATA INFILE	文件 → 表	最快	大批量导入 CSV/TSV
SELECT INTO OUTFILE	表 → 文件	快	导出查询结果到服务器文件
mysqldump	库 → SQL 脚本	中	逻辑备份、跨版本迁移
mysql < script	SQL 脚本 → 库	中	恢复备份、执行初始化脚本
mysql > file	查询 → 客户端文件	中	客户端侧导出