MariaDB 数据导入指南

使用 LOAD DATA INFILE 语句，把外部文件的数据高效导入 MariaDB 表。

本指南介绍向 MariaDB 批量导入数据的方法和工具，包括准备数据、使用 LOAD DATA INFILE 与 mariadb-import、处理常见导入问题，以及应对可能的限制。

准备数据文件

批量导入最常见的方式是使用带分隔符的文本文件。

1. **导出源数据：**在原软件（如 MS Excel、MS Access）中载入数据，导出为带分隔符的文本文件。

– **字段分隔符：**选择数据中不常出现的字符。竖线 | 通常合适，制表符 \t 也很常见。

– **记录分隔符：**使用换行符 \n 分隔记录。

2. **对齐列（有助于简化操作）：**文本文件的列数和顺序最好与目标 MariaDB 表一致。

– 表中存在而文件没有的额外列，将使用默认值或 NULL。

– 文件中存在而表没有的额外列，需要指定载入哪些文件列（见下方“文件列与表列的映射”），或从文件中删除。

3. **清理数据：**删除表头行和页脚信息，除非计划在导入时跳过它们（见 IGNORE N LINES）。

4. **上传文件：**将文本文件传到 MariaDB 服务器能够访问的位置。

– FTP 传输使用 ASCII 模式，以确保正确的行结束符。

– 为安全起见，把数据文件上传到服务器的非公开目录。

使用 LOAD DATA INFILE

LOAD DATA INFILE 是从文本文件导入数据的强大 SQL 命令。确保 MariaDB 用户具有 FILE 权限。

基本语法

首先使用 mariadb 客户端连接 MariaDB，并选择目标数据库：

USE sales_dept; -- Or your database name

随后载入数据：

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|';

– 用服务器上数据文件的实际路径替换 /tmp/prospects.txt。Windows 路径使用正斜杠，例如 'C:/tmp/prospects.txt'。

– prospect_contact 是目标表，也可写为 database_name.table_name。

– FIELDS TERMINATED BY '|' 指定字段分隔符。制表符分隔使用 '\t'。

– 默认记录分隔符是换行符 \n。

指定行终止符与包围字符

如果文件有自定义行结束符，或字段由引号等字符包围：

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|' ENCLOSED BY '"'
LINES STARTING BY '"' TERMINATED BY '"\r\n';

– ENCLOSED BY '"'：字段由双引号包围。

– LINES STARTING BY '"'：每行以双引号开头。

– TERMINATED BY '"\r\n'：每行以双引号及 Windows 风格的回车、换行结尾。

– 单引号作为包围字符时，可转义，或用双引号包住：ENCLOSED BY '\'' 或 ENCLOSED BY "'"。

处理重复行

导入时，记录的主键值可能已经存在于目标表中。

– **默认行为：**MariaDB 尝试导入所有行。如果重复记录违反主键或唯一键约束，会发生错误，后续行可能不再导入。

– **REPLACE：**要让文件的新数据覆盖主键相同的现有行：

  LOAD DATA INFILE '/tmp/prospects.txt'
  REPLACE INTO TABLE prospect_contact
  FIELDS TERMINATED BY '|';

– **IGNORE：**要保留现有行并跳过文件中的重复记录：

  LOAD DATA INFILE '/tmp/prospects.txt'
  IGNORE INTO TABLE prospect_contact
  FIELDS TERMINATED BY '|';

导入正在使用的表

如果目标表正在使用，导入可能锁住表，阻止其他访问。

– **LOW_PRIORITY：**为了让其他用户在载入操作等待期间仍可读取表，可使用 LOW_PRIORITY。载入操作会等待，直到没有其他客户端读取该表。

  LOAD DATA LOW_PRIORITY INFILE '/tmp/prospects.txt'
  INTO TABLE prospect_contact
  FIELDS TERMINATED BY '|';

如果没有使用 LOW_PRIORITY 或 CONCURRENT，表通常会在导入期间保持锁定。

LOAD DATA INFILE 的高级选项

二进制行结束符

如果文件采用 Windows CRLF 行结束符，并以二进制模式上传，可指定十六进制值：

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|'
LINES TERMINATED BY 0x0d0a; -- 0x0d is carriage return, 0x0a is line feed

注意：十六进制值不加引号。

跳过表头行

要忽略文件开头一定数量的行（如表头）：

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|'
IGNORE 1 LINES; -- Skips the first line

处理转义字符

如果字段由引号包围，并且内部引号由特殊字符转义，例如使用 # 而不是默认反斜杠 \：

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|'
    ENCLOSED BY '"'
    ESCAPED BY '#'
IGNORE 1 LINES;

文件列与表列的映射

如果文本文件的列顺序或列数与表不同，可在 LOAD DATA INFILE 末尾指定列映射。

假设 prospect_contact 表的列为 (row_id INT AUTO_INCREMENT, name_first VARCHAR, name_last VARCHAR, telephone VARCHAR)。

prospects.txt 的列依次为姓、名、电话号码。

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|' -- Or your actual delimiter, e.g., 0x09 for tab
ENCLOSED BY '"'
ESCAPED BY '#'
IGNORE 1 LINES
(name_last, name_first, telephone);

– MariaDB 将文件第一列映射到 name_last，第二列映射到 name_first，第三列映射到 telephone。

– 未列入映射的 row_id 列将使用其默认机制，例如 AUTO_INCREMENT、DEFAULT 值，或 NULL。

使用 mariadb-import 工具

mariadb-import 是 LOAD DATA INFILE 的命令行包装程序，适合脚本化导入。

**语法：**

mariadb-import --user='your_username' --password='your_password' \
    --fields-terminated-by='|' --lines-terminated-by='\r\n' \
    --replace --low-priority --fields-enclosed-by='"' \
    --fields-escaped-by='#' --ignore-lines='1' --verbose \
    --columns='name_last,name_first,telephone' \
    sales_dept '/tmp/prospect_contact.txt'

– 在系统 shell 中运行此命令，而不是在 mariadb 客户端内部。

– 为方便阅读，这里使用 \ 续行，也可写在一行中。

– --password：省略密码值时，程序会提示输入。

– 数据库名 sales_dept 放在文件路径之前。

– **文件命名：**mariadb-import 要求文本文件名去掉扩展名后与目标表名相同。例如 prospect_contact.txt 对应 prospect_contact 表。如果文件为 prospects.txt 而表为 prospect_contact，可能需要重命名文件，或先导入名为 prospects 的临时表。

– --verbose：显示进度。

– 可以列出多个文本文件，将它们导入对应名称的表。

应对虚拟主机限制

部分虚拟主机服务会出于安全原因禁用 LOAD DATA INFILE 或 mariadb-import。一种替代方式是使用 mariadb-dump：

1. **本地准备数据：**准备带分隔符的文本文件，例如 prospects.txt。

2. **本地导入：**如果有本地 MariaDB 服务器，按前述方法用 LOAD DATA INFILE 导入本地表，例如 local_db.prospect_contact。

3. **使用 mariadb-dump 导出：**把本地表导出为包含 INSERT 语句的 SQL 文件。

   mariadb-dump --user='local_user' --password='local_pass' --no-create-info local_db prospect_contact > /tmp/prospects.sql

– --no-create-info 或 -t 不输出 CREATE TABLE，只输出 INSERT。远程服务器已经存在该表时，这很有用。

4. **上传 SQL 文件：**以 ASCII 模式把生成的 .sql 文件（如 prospects.sql）上传到 Web 服务器。

5. **远程导入 SQL 文件：**登录远程服务器 shell，使用 mariadb 客户端导入：

   mariadb --user='remote_user' --password='remote_pass' remote_sales_dept < /tmp/prospects.sql

处理 mariadb-dump 输出中的重复记录

mariadb-dump 没有像 LOAD DATA INFILE 那样的 REPLACE 标志。目标表可能有重复记录时：

– 用文本编辑器打开生成的 .sql 文件。

– 搜索并替换，把全部 INSERT INTO 改为 REPLACE INTO。两种语句的数据值部分语法足够相似，因此通常可行。务必充分测试。

主要注意事项

– **灵活性：**MariaDB 提供强大灵活的数据导入选项。理解 LOAD DATA INFILE 和 mariadb-import 的细节可以节省大量工作。

– **数据校验：**这些工具适合高效批量载入，但除基本类型兼容外，可能不会进行大量数据验证。导入前应尽可能清理和校验数据。

– **字符集：**确保数据文件的字符集与目标表兼容，避免数据损坏。LOAD DATA INFILE 可以指定字符集。

– **其他工具和方法：**复杂转换或 ETL（提取、转换、载入）可能更适合专用 ETL 工具或脚本语言，例如 Python、Perl 配合数据库模块。这些超出本指南范围。

—

原文：Importing Data Guide。作者：MariaDB 文档贡献者。原文许可声明：**CC BY-SA / Gnu FDL**；本文为中文翻译，保留该声明。原页未注明许可版本，本文不自行添加版本号。