MySQL LOAD DATA INFILE详解

1. 介绍

LOAD DATA INFILE是MySQL中一个非常有用的语句，它允许用户将外部文件中的数据加载到数据库表中。这个功能在处理大量数据时非常高效，可以大大提高数据导入的速度。

在本文中，我们将详细讨论LOAD DATA INFILE的用法，包括如何指定数据文件、字段分隔符、行分隔符等。

2. 语法

LOAD DATA INFILE语句的基本语法如下：

LOAD DATA INFILE 'file_name'
[INTO TABLE 'table_name']
[CHARACTER SET charset_name]
[FIELDS
    [TERMINATED BY 'field_delimiter']
    [ENCLOSED BY 'enclosure_character']
    [ESCAPED BY 'escape_character']
]
[LINES
    [STARTING BY 'line_prefix']
    [TERMINATED BY 'line_delimiter']
]
[IGNORE number_of_lines]
[(column_name,...)]
[SET column_name = expression,...]

下面我们将逐个解释每个参数的作用。

2.1 file_name

file_name指定要加载的数据文件的路径，可以是本地文件系统上的路径，也可以是远程服务器上的路径。如果路径是相对路径，它将相对于MySQL服务器的数据目录。

2.2 table_name (可选)

table_name指定要将数据加载到的目标表的名称。如果不指定表名，则数据将加载到file_name中的文件名相同的表中。

2.3 CHARACTER SET charset_name (可选)

charset_name指定数据文件的字符集。如果不指定，默认为数据库的字符集。

2.4 FIELDS

FIELDS关键字用于指定如何解析数据文件中的字段。

2.4.1 TERMINATED BY ‘field_delimiter’ (可选)

field_delimiter指定数据文件中的字段分隔符。常见的分隔符有逗号、制表符等。如果不指定，默认为制表符。

2.4.2 ENCLOSED BY ‘enclosure_character’ (可选)

enclosure_character指定数据文件中的字段的围栏字符。如果字段中包含分隔符，可以使用围栏字符将其包围起来。如果不指定，默认为双引号。

2.4.3 ESCAPED BY ‘escape_character’ (可选)

escape_character用于指定在数据文件中如何转义特殊字符。如果需要在字段中使用分隔符或围栏字符，可以使用转义字符进行转义。如果不指定，默认为反斜杠。

2.5 LINES

LINES关键字用于指定如何解析数据文件中的行。

2.5.1 STARTING BY ‘line_prefix’ (可选)

line_prefix指定数据文件中每一行的元素前缀。如果不指定，则所有行都被处理。

2.5.2 TERMINATED BY ‘line_delimiter’ (可选)

line_delimiter指定数据文件中的行分隔符。常见的分隔符有换行符、回车符等。如果不指定，默认为换行符。

2.6 IGNORE number_of_lines (可选)

IGNORE关键字用于指定要忽略的数据文件中的行数。可以用于跳过文件中的标题行等无关数据。

2.7 (column_name,…)

(column_name,...)用于指定要将数据加载到的目标表的列名。如果不指定列名，则默认为根据数据文件中的列顺序加载。

2.8 SET column_name = expression,… (可选)

可以使用SET关键字以及表达式为目标表的列设置默认值。

3. 示例

现在我们通过一个示例来了解LOAD DATA INFILE的实际用法。假设我们有一个名为employees的表，包含以下列：id, name, age, salary。

我们有一个文本文件employees.txt，包含以下数据：

1, John Doe, 25, 5000
2, Jane Smith, 30, 6000
3, Tom Johnson, 35, 7000

为了将这些数据加载到employees表中，我们可以使用以下LOAD DATA INFILE语句：

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

上述示例中，我们指定了数据文件的路径/path/to/employees.txt，将数据加载到名为employees的表中。我们还指定了字段分隔符为逗号，围栏字符为双引号，行分隔符为换行符，忽略了文件中的第一行标题行，并指定了要加载到的目标表的列名。

4. 特殊情况与注意事项

4.1 字符集问题

在使用LOAD DATA INFILE时，需要特别注意数据文件的字符集与数据库的字符集是否一致。如果不一致，可能会导致数据加载错误或乱码等问题。可以通过指定CHARACTER SET参数来解决字符集不一致的问题。

4.2 文件权限问题

在使用LOAD DATA INFILE时，需要确保MySQL服务器对数据文件具有读取权限。特别是当数据文件位于远程服务器上时，需要确保MySQL服务器可以访问到该文件。

4.3 数据文件格式

LOAD DATA INFILE支持多种数据文件格式，如CSV、文本文件等。在使用时，请确保数据文件的格式与指定的字段分隔符、行分隔符等参数一致。

4.4 数据完整性

在使用LOAD DATA INFILE时，需要确保数据文件中的数据与目标表的列定义完全一致，以确保数据的完整性和准确性。

5. 总结

LOAD DATA INFILE是MySQL中一个强大的语句，可以高效地将外部数据加载到数据库表中。本文对LOAD DATA INFILE的语法、参数进行了详细的解释，并通过示例帮助读者理解其用法。在使用LOAD DATA INFILE时，请留意字符集、文件权限、数据文件格式以及数据完整性等问题，以确保数据加载的准确性和完整性。