18.4.4 用LOAD DATA INFILE命令将文本文件导入数据表中-MySQL从入门到精通(五)

当前位置:　首页>> 技术小册>> MySQL从入门到精通(五)

### 18.4.4 用`LOAD DATA INFILE`命令将文本文件导入数据表中

在MySQL数据库中，高效地批量导入数据是数据库管理员和开发者经常面临的任务之一。`LOAD DATA INFILE`语句是MySQL提供的一个非常强大的工具，它允许用户直接将本地文件系统中的文本文件内容加载（导入）到数据库表中，极大地提高了数据迁移和批量数据处理的效率。本章节将深入解析`LOAD DATA INFILE`命令的使用方法、语法结构、常见场景、最佳实践以及可能遇到的挑战与解决方案。

#### 1. 命令简介

`LOAD DATA INFILE`命令是MySQL特有的一个SQL语句，它直接从文件系统中读取指定格式的文件内容，并将这些数据插入到指定的MySQL数据表中。该命令在处理大规模数据导入时尤为有效，因为它绕过了常规的SQL INSERT语句逐行插入的过程，从而大幅提升了数据导入的速度。

#### 2. 语法结构

`LOAD DATA INFILE`命令的基本语法如下：

```sql
LOAD DATA INFILE 'file_path'
INTO TABLE table_name
[FIELDS TERMINATED BY 'string']
[OPTIONALLY ENCLOSED BY 'char']
[LINES TERMINATED BY 'string']
[IGNORE number LINES]
[(column_list)]
[SET column_name = expr, ...]
```

- `file_path`：要导入的文件的路径。注意，MySQL服务器需要有权限访问这个文件。
- `table_name`：目标数据表的名称。
- `FIELDS TERMINATED BY`：字段之间的分隔符，默认为制表符`\t`。
- `OPTIONALLY ENCLOSED BY`：字段值可选的包围字符，如单引号`'`或双引号`"`，用于处理包含分隔符或换行符的字段值。
- `LINES TERMINATED BY`：记录之间的分隔符，默认为换行符`\n`。
- `IGNORE number LINES`：跳过文件开头的指定数量的行，通常用于跳过文件头部（如标题行）。
- `(column_list)`：可选，指定要加载数据的列列表，如果省略，则按照表中列的顺序加载所有数据。
- `SET column_name = expr`：允许在加载数据时对列值进行转换或计算。

#### 3. 使用场景

- **数据迁移**：将旧系统中的数据迁移到MySQL数据库中。
- **数据备份恢复**：从备份文件中恢复数据。
- **批量数据处理**：处理大量日志数据、用户行为数据等。
- **性能测试**：生成大量测试数据以评估数据库性能。

#### 4. 最佳实践

1. **文件准备**：确保文件格式与目标表结构相匹配，包括字段顺序、分隔符等。
2. **权限配置**：确保MySQL服务器有权限访问文件路径，并考虑使用`LOCAL`关键字让MySQL客户端读取文件（需客户端和服务器配置支持）。
3. **错误处理**：使用`IGNORE`关键字忽略错误行，或结合`LINES TERMINATED BY`处理非标准格式的行。
4. **性能优化**：
   - 关闭唯一性检查（`UNIQUE_CHECKS=0`）、外键约束检查（`FOREIGN_KEY_CHECKS=0`）等，在导入过程中提高性能。
   - 使用`DELAYED`关键字（在MySQL 5.6及之前版本）或调整事务处理策略来减少锁的竞争。
5. **安全性**：确保文件路径不会暴露敏感信息，避免使用绝对路径，并在导入完成后清理或删除文件。

#### 5. 示例

假设有一个名为`employees.txt`的文本文件，内容格式如下（以逗号分隔）：

```
1,John Doe,25,New York
2,Jane Smith,30,Los Angeles
...
```

目标是将这些数据导入到名为`employees`的MySQL表中，该表有`id`、`name`、`age`、`city`四个字段。

```sql
LOAD DATA INFILE '/path/to/employees.txt'
INTO TABLE employees
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, age, city);
```

#### 6. 挑战与解决方案

- **文件权限问题**：确保MySQL服务账户有权访问文件。如果无法直接访问，考虑使用`LOCAL`关键字（如果MySQL配置允许）或调整文件路径和权限。
- **字符集问题**：如果文件和目标表的字符集不一致，可能会导致数据乱码。可以在`LOAD DATA INFILE`语句中指定`CHARACTER SET`来解决。
- **性能瓶颈**：对于非常大的文件，导入过程可能会成为性能瓶颈。考虑分批导入、优化数据库配置或升级硬件。
- **数据格式错误**：使用`IGNORE`关键字或结合错误日志来定位并处理格式错误的行。

#### 7. 结论

`LOAD DATA INFILE`是MySQL中一个非常强大的工具，它允许用户高效地将本地文件系统的文本文件内容导入到MySQL数据库中。通过合理使用该命令，可以极大地提高数据迁移、备份恢复和批量数据处理的效率。然而，在使用时也需要注意文件权限、字符集、性能瓶颈和数据格式等问题，以确保数据能够正确、高效地导入到目标数据表中。

该分类下的相关小册推荐：

细说MySQL(零基础到高级应用)

MySQL 实战 45 讲

SQL零基础到熟练应用(增删改查)

MySQL从入门到精通(二)

MySQL从入门到精通(三)

MySQL从入门到精通(一)

MySQL必会核心问题

MySQL从入门到精通(四)

MySQL8.0入门与实践