MySQL LOAD DATA大量数据快速导入数据库语句

MySQL数据库的LOAD DATA语句是一种用于快速导入大量数据到数据库中的强大工具。它允许你从文件或查询结果中加载数据到表中，同时支持一些高级选项，如跳过行，替换或忽略重复数据等。可以和SELECTI...INTO OUTFILE进行导出和导入。

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

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'  
    [REPLACE | IGNORE]  
    INTO TABLE table_name  
    [FIELDS TERMINATED BY 'field_term']  
    [ENCLOSED BY 'encl_term']  
    [ESCAPED BY 'escape_term']  
    [LINES TERMINATED BY 'line_term']  
    [IGNORE number LINES]  
    [(column1, column2, ...)]

让我们逐个解释这些关键字：

• LOW_PRIORITY：表示如果存在任何正在等待的读取请求，那么这些请求将被LOAD DATA语句暂停，直到数据加载完成。

• CONCURRENT：表示允许其他会话在数据加载过程中读取或更改表。

• LOCAL：表示文件位于客户端，而不是服务器。如果未指定该选项，则默认情况下，文件名必须在服务器上。

• INFILE 'file_name'：指定要加载的数据文件。

• REPLACE：表示如果存在与要插入的数据相匹配的行，则删除它们并插入新行。

• IGNORE：表示如果存在与要插入的数据相匹配的行，则忽略它们。

• FIELDS TERMINATED BY 'field_term'：指定字段之间的分隔符。默认情况下，这是逗号。

• ENCLOSED BY 'encl_term'：指定包含在字段中的字符。默认情况下，没有字符。

• ESCAPED BY 'escape_term'：指定转义字符。默认情况下，这是反斜杠。

• LINES TERMINATED BY 'line_term'：指定行之间的分隔符。默认情况下，这是换行符。

• IGNORE number LINES：表示跳过文件开头的几行。这对于处理标题行非常有用。

• (column1, column2, ...)：指定要导入的列。如果未指定此部分，则默认情况下将导入所有列。

LOAD DATA示例

-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `type` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

以上新建了一个“products”空表。再在e盘建一个data文件夹和bb.txt文件（可点击下载 bb.txt）

导入文件数据到products表,字段由逗号分隔，行由换行符分隔。

mysql> load data local infile 'e:/data/bb.txt' into table products FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
mysql> SELECT *  from products;
+----+------+------+
| id | name | type |
+----+------+------+
|  3 | b2   | c1   |
|  4 | b3   | c2   |
|  5 | b4   | c3   |
+----+------+------+

从第几行开始导入

假设第一行是标题行，将其忽略。先将表请空，还是用bb.txt文件中数据加载"products"表中。

mysql> load data local infile 'e:/data/bb.txt' into table products
    -> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
mysql> SELECT *  from products;
+----+------+------+
| id | name | type |
+----+------+------+
|  4 | b3   | c2   |
|  5 | b4   | c3   |
+----+------+------+

导入指定的列

这次我们只导入两列id,name。先将表请空，还是用bb.txt文件中数据加载"products"表中。

mysql> load data local infile 'e:/data/bb.txt' into table products
    -> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (id,name);
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 3
mysql> SELECT *  from products;
+----+------+------+
| id | name | type |
+----+------+------+
|  3 | b2   | NULL |
|  4 | b3   | NULL |
|  5 | b4   | NULL |
+----+------+------+

加载时做数据处理

这次我们多type列进行重新定义。先将表请空，还是用bb.txt文件中数据加载"products"表中。

mysql> load data local infile 'e:/data/bb.txt' into table products
    -> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' set type=10;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
mysql> SELECT *  from products;
+----+------+------+
| id | name | type |
+----+------+------+
|  3 | b2   | 10   |
|  4 | b3   | 10   |
|  5 | b4   | 10   |
+----+------+------+

二次处理

mysql> load data local infile 'e:/data/bb.txt' into table products
    -> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES(id,@name) set name=concat(@name,1);

Query OK, 2 rows affected, 2 warnings (0.04 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 2

mysql> SELECT *  from products;
+----+------+------+
| id | name | type |
+----+------+------+
|  4 | b31  | NULL |
|  5 | b41  | NULL |
+----+------+------+

导入csv文件类型

.csv是一种通用的、相对简单的文件格式。逗号分隔值（Comma-Separated Values，CSV，有时也称为字符分隔值，因为分隔字符也可以不是逗号），其文件以纯文本形式存储表格数据（数字和文本）。csv文件可以用记事本直接打开，可以用Excel等软件打开。

再在e盘建一个data文件夹和bb.csv文件（可以Excel新建后另存为csv格式）

mysql> load data local infile 'e:/data/bb.csv' into table products
    -> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT *  from products;
+----+------+------+
| id | name | type |
+----+------+------+
|  3 | b2   | c1   |
|  4 | b3   | c2   |
|  5 | b4   | c3   |
+----+------+------+

注意事项：

• 使用LOAD DATA语句时，要确保文件路径是正确的，并且文件内容格式与表结构匹配。否则，可能会导入不完整或错误的数据。

• 在生产环境中使用LOAD DATA语句时，请务必注意性能问题。大量数据的导入可能会对数据库服务器的负载产生影响。如果可能的话，最好在低峰时间进行导入操作。

• 导入大量数据时，建议先在测试环境中进行尝试，以确保一切正常工作，并避免对生产数据造成不可逆转的损坏。

• 在某些情况下，使用LOAD DATA语句可能比使用常规的INSERT语句更有效，尤其是在处理大量数据时。但是，这也取决于具体的数据量和服务器配置。在决定使用哪种方法之前，最好先进行基准测试和性能评估。