MariaDB 教程 / CSV 导入导出:路径、权限、编码与数据验证
导入 CSV 时,应先约定分隔符、引号、换行与编码,把原始值装入文本暂存表并验证,再转换到目标列类型。LOAD DATA LOCAL INFILE 读取客户端文件,而 SELECT INTO OUTFILE 在服务器写文件,两者的路径含义与权限要求不同。
先确认文件由哪台机器读取或写入
CSV 是用分隔符组织字段的文本格式,不仅是一个文件扩展名。客户端与服务器即使在同一台电脑,执行文件操作的主体也不同;远程连接时,这个区别尤其重要。
| 操作 | 文件位置 | 主要条件 |
|---|---|---|
| LOAD DATA LOCAL INFILE | 客户端电脑 | 客户端和服务器均允许 LOCAL,且具有目标表插入权限 |
| LOAD DATA INFILE | 数据库服务器 | FILE 权限、表权限与服务器文件路径策略 |
| SELECT INTO OUTFILE | 数据库服务器 | FILE 权限、secure_file_priv 与操作系统写权限 |
仅对可信服务器启用 LOCAL,并考虑客户端进程能够读取和发送哪些文件。遇到错误时先定位是客户端能力、服务器策略、表权限还是路径问题,不要一次放宽全局选项或文件系统权限。路径与 SQL 文件执行可参考命令行客户端与 SOURCE 方法,列的对应关系可参考INSERT 的列与值映射。
具体语法与行为依据为 MariaDB 的 LOAD DATA INFILE 文档和 SELECT INTO OUTFILE 文档。
准备 UTF-8 文件与原始文本暂存表
在客户端电脑的 C:/mariadb-lab 下新建 products.csv,保留已有同名文件,必要时另取文件名并同步修改路径。将以下内容保存为 UTF-8。第一行是字段名称;第一个商品名意为“大号笔记本”,其中含逗号,因此必须按本例加引号。
product_id,product_name,price
1,"노트, 대형",3500.00
2,"머그컵",8000.00
3,"볼펜",1500.00
以有建表和插入权限的学习账户连接,执行 SELECT VERSION(), DATABASE(); 和 SHOW TABLES;。确认 shopcourse 中 csv45_stage 与 csv45_products 未被使用,再在 MariaDB 客户端创建下列两表。若表已存在,先检查数据与结构,不要直接覆盖。
USE shopcourse;
CREATE TABLE csv45_stage(raw_id TEXT,raw_name TEXT,raw_price TEXT)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE csv45_products(product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,price DECIMAL(10,2) NOT NULL CHECK(price>=0))
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
暂存表将三个字段先保留为文本,以便检查原始输入;目标表再约束正整数编号、最长 100 字符的名称和非负 DECIMAL 金额。直接载入数值列可能让不合规文本先发生转换,导致难以追查原值。
载入客户端文件,并按顺序检查原始值
在操作系统终端运行客户端命令,在提示处输入密码。地址、端口与账户按实际学习服务器调整;这里只为当前客户端显式开启 LOCAL 能力,并不代替服务器的许可策略。
mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --local-infile=1 --default-character-set=utf8mb4 shopcourse
下面的导入与检查在 MariaDB 客户端执行。首先查看 SHOW WARNINGS 及暂存原文,再确认格式检查结果为空;通过后才使用数值转换做规范化重复与范围检查。
LOAD DATA LOCAL INFILE 'C:/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;
SELECT * FROM csv45_stage;
SELECT * FROM csv45_stage
WHERE raw_id IS NULL OR raw_id NOT REGEXP '^[0-9]+$'
OR raw_price IS NULL OR raw_price NOT REGEXP '^[0-9]+([.][0-9]{1,2})?$'
OR raw_name IS NULL OR raw_name='';
SELECT CAST(raw_id AS UNSIGNED) AS normalized_id,COUNT(*)
FROM csv45_stage GROUP BY CAST(raw_id AS UNSIGNED) HAVING COUNT(*)>1;
-- After format checks pass, verify numeric ranges and name length
SELECT * FROM csv45_stage
WHERE CAST(raw_id AS DECIMAL(65,0))<1
OR CAST(raw_id AS DECIMAL(65,0))>2147483647
OR CAST(raw_price AS DECIMAL(65,2))>99999999.99
OR CHAR_LENGTH(raw_name)>100;
若服务器的 local_infile 策略禁止操作,应与管理员审查所需范围。不能因为启用了严格模式就认定 LOCAL 会拒绝每种坏输入;仍须逐项查看警告和原始字符串。输入缺列、非法金额、过长名称或转换后重号时,应停止转入目标表。
示例按 LF 分行,再去除最后一个字段末尾的 CR,可处理这个简单样本常见的 CRLF 换行。字段内部嵌入换行、反斜线转义或不同引号约定时,需要先与生成工具约定格式,不能从本例推断所有 CSV 方言都适用。
若客户端是 Ubuntu,先在终端准备工作目录,并把同一份 UTF-8 文件保存到该目录:
mkdir -p "$HOME/mariadb-lab"
printf '%s\n' "$HOME/mariadb-lab/products.csv"
使用打印出的真实绝对路径替换 LOCAL 路径。以下仅假设登录名为 learner:
LOAD DATA LOCAL INFILE '/home/learner/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;
Windows 与 Ubuntu 导入方案只选符合客户端环境的一种执行,不要两种都载入同一暂存表,否则会追加重复记录。Ubuntu 上的导出目录属于服务器端,不能与这里的客户端主目录混为一谈。
只把验证通过的记录转换到目标列
格式错误、规范化重复和范围检查都必须返回空结果。编号需适合正的有符号 INT,金额需适合 DECIMAL(10,2),名称最多 100 字符。文本编号 01 和 1 在转换后可能相等,因此不能只检查原始文本是否重复。
若验证失败,修正文件或明确转换政策,再重新检查;不要先执行 INSERT 期待目标约束替代整个输入审查。通过后执行:
INSERT INTO csv45_products(product_id,product_name,price)
SELECT CAST(raw_id AS UNSIGNED),raw_name,CAST(raw_price AS DECIMAL(10,2))
FROM csv45_stage;
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id;
应得到三件商品,金额依次为 3500.00、8000.00 与 1500.00。第一件名称中的逗号应完整保留,另外两件韩文名称分别代表马克杯和圆珠笔。不能只核对行数,还应比较编号、名称与金额。
重复 LOAD 会继续向暂存表追加记录;重复向目标表 INSERT 可能因主键重复失败。重做前应保留输入文件、警告及诊断记录,检查本次数据是否确为可丢弃,再决定清理或使用新的练习表名,而不是直接删除不明来源的数据。
由有授权的账户导出服务器端文件
使用经批准、具有 FILE 权限和目标表读取权限的导出账户或管理员。先查看 secure_file_priv、服务器进程的操作系统写权限,并选择不存在的新文件名。若连接远程服务器,C: 路径属于那台服务器,不是当前客户端电脑。
SELECT @@secure_file_priv;
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE 'C:/mariadb-lab/products-export.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
输出为三条 CSV 记录,没有标题行;已有文件不会被覆盖。若需要标题,应明确设计导出流程。普通 mariadb --batch 输出通常按制表符分隔,只把扩展名改成 .csv 并不会改变格式。
Ubuntu 服务器也要先检查同一变量和服务账户写权限。若批准的目录确实是 /var/lib/mysql-files/,可在该服务器写一个新文件:
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE '/var/lib/mysql-files/products-export-new.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
路径仍需替换为实际批准目录,按所在服务器选择执行一次。不要为绕过错误而删除已有文件、给普通应用账户增加全局 FILE 权限,或扩大服务器目录的写入范围。
按字段核对往返结果,再单独处理清理
导出后用支持约定格式的工具读取文件,逐字段比较逗号、Unicode 字符、金额、空字符串、NULL 和换行,而不是只看记录数。不同 CSV 工具的 NULL 约定未必相同;空字段表示空文本还是缺失值,需要预先规定。本样本三列都有值,不代表已覆盖复杂空值与多行字段场景。
源 CSV 和导出文件的保留期限应单独决定,删表不会替你管理这些文件。最后检查当前数据库和两张表,备份需要保留的数据,只清理确认可丢弃的本篇表。DROP TABLE 删除结构与记录,普通 ROLLBACK 无法恢复。
DROP TABLE shopcourse.csv45_products;
DROP TABLE shopcourse.csv45_stage;




