将文本文件数据上传到表的 10 个 MySQL 加载数据 Infile 示例

举报
Tiamo_T 发表于 2021/11/25 20:26:19 2021/11/25
【摘要】 如果您有文本文件中的数据，您可以轻松地将它们上传到数据库中的一个或多个表中。 在 MySQL 数据库（或 MariaDB）中，使用“load data infile”命令，可以将文本文件中的数据上传到表中。 load data infile 命令提供了几个灵活的选项来将各种格式的数据从文本文件加载到表中。

如果您有文本文件中的数据，您可以轻松地将它们上传到数据库中的一个或多个表中。

在 MySQL 数据库（或 MariaDB）中，使用“load data infile”命令，可以将文本文件中的数据上传到表中。

load data infile 命令提供了几个灵活的选项来将各种格式的数据从文本文件加载到表中。

本教程涵盖了以下负载数据示例：

  1. 从文本文件加载数据的基本示例
  2. 使用“字段终止于”选项上传数据
  3. 使用“封闭于”选项上传数据
  4. 在文本文件数据中使用转义字符
  5. 使用“行终止于”选项上传数据
  6. 使用“Starting By”选项忽略上传文件中的行前缀
  7. 忽略上传文件中的标题行
  8. 从上传文件仅上传特定列（并忽略其他列）
  9. 在上传过程中使用“设置”选项使用变量
  10. 编写 Shell 脚本以从文本文件加载数据

1. 从文本文件加载数据的基本示例

在以下示例中，employee1.txt 文件具有由制表符分隔的字段值。

# cat employee1.txt 
100     Thomas  Sales   5000
200     Jason   Technology      5500
300     Mayla   Technology      7000
400     Nisha   Marketing       9500
500     Randy   Technology      6000

默认情况下，load data infile 命令使用 TAB 作为默认字段分隔符。

首先，转到要上传文本文件的数据库。在这个例子中，我们将上面的employee1.txt 文件上传到geekstuff mysql 数据库下的employee 表。

USE tiamo;

下面的 MySQL 命令会将上面的employee1.txt 文件中的记录加载到employee 表中，如下所示。此命令不使用任何额外选项。

LOAD DATA INFILE 'employee1.txt' 
 INTO TABLE employee;

注意：在上面的例子中，该命令假设employee1.txt 文件位于数据库目录下。例如，如果您在geekstuff数据库中执行上述命令，则将文件放在：/var/lib/mysql/thegeekstuff/

以下将是上述命令的输出。

Query OK, 5 rows affected (0.00 sec)                 
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

在上面：

  • 第一行“Query OK”表示查询执行没有任何错误。它还说总共有 5 行被上传到表中。这还会显示将数据从文本文件上传到表格所花费的时间（以秒为单位）。
  • 第二行显示上传的总行数、跳过的行数以及上传期间显示警告的记录数。

数据加载完毕后，我们将在employee表中看到以下内容。

MariaDB [tiamo]> select * from employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   5000 |
| 200 | Jason  | Technology |   5500 |
| 300 | Mayla  | Technology |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
| 500 | Randy  | Technology |   6000 |
+-----+--------+------------+--------+

注意：如果要备份和恢复整个 MySQL 数据库，请使用mysqldump 命令。

2. 使用“字段终止于”选项上传数据

在以下示例中，在输入文件 employee2.txt 中，字段值以逗号分隔。

# cat employee2.txt 
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000

要将上述记录上传到员工表，请使用以下命令。

在上传过程中，使用“FIELDS TERMINATED BY”选项，您可以指定逗号字段分隔符，如下所示。

LOAD DATA INFILE 'employee2.txt' 
 INTO TABLE employee 
FIELDS TERMINATED BY ',';

同样，仅当字段值由 TAB 以外的任何内容分隔时才使用此选项。如果字段以冒号结尾，您将在上述命令中使用以下选项：

FIELDS TERMINATED BY ':';

如果您不熟悉 MySQL，请阅读以下内容：MySQL 教程：安装、创建数据库和表、插入和选择记录

以下是MySQL上传过程中可能发生的几个基本错误

错误 1：如果文本文件未位于正确的目录下，您可能会收到以下错误消息“ERROR 13 (HY000) Can't get stat of (Errcode: 2)”。

MariaDB [tiamo]> LOAD DATA INFILE 'employee2.txt' INTO TABLE employee;
ERROR 13 (HY000): Can't get stat of '/var/lib/mysql/thegeekstuff/employee2.txt' (Errcode: 2)

此外，您可以在 load data infile 命令中指定文件的完整路径，如下所示。如果这样做，请确保 mysql 可以访问该文件。如果不是，请适当地将所有权更改为 mysql。如果没有，您将收到加载数据 infile 权限被拒绝的错误消息。

MariaDB [tiamo]> LOAD DATA INFILE '/data/employee2.txt' INTO TABLE employee;

错误 2：如果您没有指定正确的字段终止于，那么您会在上传中看到一些问题。在这个例子中，只有第一个字段“id”被上传。所有其他字段的值为 NULL。这是因为，以下命令未指定由选项终止的字段，因为输入文件以逗号作为字段分隔符。

MariaDB [tiamo]> LOAD DATA INFILE 'employee2.txt' INTO TABLE employee;
Query OK, 5 rows affected, 20 warnings (0.00 sec)    
Records: 5  Deleted: 0  Skipped: 0  Warnings: 20
 
MariaDB [thegeekstuff]> select * from employee;
+-----+------+------+--------+
| id  | name | dept | salary |
+-----+------+------+--------+
| 100 | NULL | NULL |   NULL |
| 200 | NULL | NULL |   NULL |
| 300 | NULL | NULL |   NULL |
| 400 | NULL | NULL |   NULL |
| 500 | NULL | NULL |   NULL |
+-----+------+------+--------+

3. 使用“Enclosed by”选项上传数据

在以下示例中，输入文本文件的文本字段值用双引号括起来。即名称和部门值周围有双引号。

# cat employee3.txt
100,"Thomas Smith","Sales & Marketing",5000
200,"Jason Bourne","Technology",5500
300,"Mayla Jones","Technology",7000
400,"Nisha Patel","Sales & Marketing",9500
500,"Randy Lee","Technology",6000

在这种情况下，请使用“enclosed by”选项，如下所示。

LOAD DATA INFILE 'employee3.txt' 
 INTO TABLE employee 
 FIELDS TERMINATED BY ',' ENCLOSED BY '"';

上面的命令将通过mysql select 命令正确上传记录，如下所示：

MariaDB [tiamo]> select * from employee;
+-----+--------------+-------------------+--------+
| id  | name         | dept              | salary |
+-----+--------------+-------------------+--------+
| 100 | Thomas Smith | Sales & Marketing |   5000 |
| 200 | Jason Bourne | Technology        |   5500 |
| 300 | Mayla Jones  | Technology        |   7000 |
| 400 | Nisha Patel  | Sales & Marketing |   9500 |
| 500 | Randy Lee    | Technology        |   6000 |
+-----+--------------+-------------------+--------+

请注意，当您将字段终止于和包含的字段组合在一起时，您不必如下所示使用关键字“FIELDS”两次，这将显示以下错误消息：

FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"';

以上将显示以下“ERROR 1064 (42000)”错误：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FIELDS ENCLOSED BY '"'' at line 4

4. 在文本文件数据中使用转义字符

假设您在特定字段的值中有逗号。

例如，在以下示例中，第二个字段名称具有以下格式的值：“firstname, lastname”。

# cat employee4.txt 
100,Thomas, Smith,Sales,5000
200,Jason, Bourne,Technology,5500
300,Mayla, Jones,Technology,7000
400,Nisha, Patel,Marketing,9500
500,Randy, Lee,Technology,6000

如果您使用以下命令加载上述文件，您将看到它将显示“10 个警告”

MariaDB [tiamo]> LOAD DATA INFILE 'employee4.txt' 
    ->  INTO TABLE employee 
    ->  FIELDS TERMINATED BY ',';
Query OK, 5 rows affected, 10 warnings (0.00 sec)    
Records: 5  Deleted: 0  Skipped: 0  Warnings: 10

记录也未正确加载，因为其中一个字段的值中有逗号。

MariaDB [tiamo]> select * from employee;
+-----+--------+---------+--------+
| id  | name   | dept    | salary |
+-----+--------+---------+--------+
| 100 | Thomas |  Smith  |      0 |
| 200 | Jason  |  Bourne |      0 |
| 300 | Mayla  |  Jones  |      0 |
| 400 | Nisha  |  Patel  |      0 |
| 500 | Randy  |  Lee    |      0 |
+-----+--------+---------+--------+

正确的文件：要解决上述问题，请在名称字段值中的逗号前使用反斜杠（\），如下所示。

# cat employee4.txt 
100,Thomas\, Smith,Sales,5000
200,Jason\, Bourne,Technology,5500
300,Mayla\, Jones,Technology,7000
400,Nisha\, Patel,Marketing,9500
500,Randy\, Lee,Technology,6000

这次以下将正常工作，没有任何错误，因为我们将 \ 作为转义字符。

MariaDB [tiamo]> LOAD DATA INFILE 'employee4.txt' 
    ->  INTO TABLE employee 
    ->  FIELDS TERMINATED BY ',';

MariaDB [thegeekstuff]> select * from employee;
+-----+---------------+------------+--------+
| id  | name          | dept       | salary |
+-----+---------------+------------+--------+
| 100 | Thomas, Smith | Sales      |   5000 |
| 200 | Jason, Bourne | Technology |   5500 |
| 300 | Mayla, Jones  | Technology |   7000 |
| 400 | Nisha, Patel  | Marketing  |   9500 |
| 500 | Randy, Lee    | Technology |   6000 |
+-----+---------------+------------+--------+

您还可以使用不同的转义字符，如下所示。在这个例子中，我们使用 ^ 作为转义字符而不是默认的 \。

# cat employee41.txt 
100,Thomas^, Smith,Sales,5000
200,Jason^, Bourne,Technology,5500
300,Mayla^, Jones,Technology,7000
400,Nisha^, Patel,Marketing,9500
500,Randy^, Lee,Technology,6000

在这种情况下，请使用“ESCAED BY”选项，如下所示。

LOAD DATA INFILE 'employee41.txt' 
 INTO TABLE employee 
 FIELDS TERMINATED BY ',' ESCAPED BY '\^'

请注意，某些字符不能用作转义字符。例如，如果您使用 % 作为转义字符，您将收到以下错误消息。

LOAD DATA INFILE 'employee41.txt' 
 INTO TABLE employee 
FIELDS TERMINATED BY ',' ESCAPED BY '\%'

ERROR 1083 (42000): Field separator argument is not what is expected; check the manual

5. 使用“行终止于”选项上传数据

除了将所有记录放在单独的行上，您还可以将它们放在同一行上。

在以下示例中，每条记录都由 | 分隔。象征。

# cat employee5.txt 
100,Thomas,Sales,5000|200,Jason,Technology,5500|300,Mayla,Technology,7000|400,Nisha,Marketing,9500|500,Randy,Technology,6000

要上传上述文件，请使用由选项终止的行，如下所示。

LOAD DATA INFILE 'employee5.txt' 
 INTO TABLE employee 
 FIELDS TERMINATED BY ','
 LINES TERMINATED BY '|';

上面的命令将从employee5.txt上传记录，如下所示。

MariaDB [tiamo]> select * from employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   5000 |
| 200 | Jason  | Technology |   5500 |
| 300 | Mayla  | Technology |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
| 500 | Randy  | Technology |   6000 |
+-----+--------+------------+--------+

以下几点需要牢记：

  • 如果输入文件来自 Windows 计算机，则您可能需要使用以下命令：LINES TERMINATED BY '\r\n'
  • 如果您使用 CSV 文件将数据上传到表，请尝试以下方法之一 1) LINES TERMINATED BY '\r' 2) LINES TERMINATED BY '\r\n'

6. 使用“Starting By”选项忽略上传文件中的行前缀

您还可以为输入文本文件中的记录添加一些前缀，在上传过程中可以忽略这些前缀。

例如，在下面的employee6.txt 文件中，对于第1、第2 和第5 条记录，我们在行的开头有“Data:”。您可以通过忽略行前缀来仅上传这些记录。

# cat employee6.txt
Data:100,Thomas,Sales,5000
Data:200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
Data:500,Randy,Technology,6000

要忽略行前缀并上传这些记录（例如：上面文件中的“数据：”），请使用“行开始于”选项，如下所示。

LOAD DATA INFILE 'employee6.txt' 
 INTO TABLE employee 
 FIELDS TERMINATED BY ','
 LINES STARTING BY 'Data:';

以下是上述命令的输出：

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

如下所示，上面的命令只上传了以前缀“Data:”开头的记录。这有助于选择性地仅上传具有特定前缀的记录。

MariaDB [tiamo]> select * from employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   5000 |
| 200 | Jason  | Technology |   5500 |
| 500 | Randy  | Technology |   6000 |
+-----+--------+------------+--------+
3 rows in set (0.00 sec)

7. 忽略上传文件的标题行

在以下输入文本文件中，第一行是标题行，其中包含列的名称。

# cat employee7.txt 
empid,name,department,salary
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000

在上传过程中，我们想忽略employee7.txt 文件中的第一个标题留置权。为此，请使用 IGNORE 1 行选项，如下所示。

LOAD DATA INFILE 'employee7.txt' 
 INTO TABLE employee 
 FIELDS TERMINATED BY ','
 IGNORE 1 LINES;

正如您从以下输出中看到的，即使输入文件有 6 行，它也忽略了第一行（即标题行）并上传了剩余的 5 行。

Query OK, 5 rows affected (0.00 sec)                 
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [thegeekstuff]> select * from employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   5000 |
| 200 | Jason  | Technology |   5500 |
| 300 | Mayla  | Technology |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
| 500 | Randy  | Technology |   6000 |
+-----+--------+------------+--------+

8.从上传文件只上传特定列（并忽略其他列）

在以下示例中，我们只有三个字段的值。在此示例文件中我们没有部门列。

# cat employee8.txt 
100,Thomas,5000
200,Jason,5500
300,Mayla,7000
400,Nisha,9500
500,Randy,6000

要将输入记录中的值上传到表中的特定列，请在加载数据输入文件期间指定列名称，如下所示。以下命令中的最后一行包含应用于从输入文本文件上传记录的列名称。

LOAD DATA INFILE 'employee8.txt' 
 INTO TABLE employee 
 FIELDS TERMINATED BY ','
 (id, name, salary);

由于我们没有在上面的命令中指定“dept”列，我们将看到该列为 NULL，如下所示。

MariaDB [tiamo]> select * from employee;
+-----+--------+------+--------+
| id  | name   | dept | salary |
+-----+--------+------+--------+
| 100 | Thomas | NULL |   5000 |
| 200 | Jason  | NULL |   5500 |
| 300 | Mayla  | NULL |   7000 |
| 400 | Nisha  | NULL |   9500 |
| 500 | Randy  | NULL |   6000 |
+-----+--------+------+--------+

同样，请记住，当您不指定列列表时，该命令将期望所有列都出现在输入文件中。

此外，如果您没有在最后一行指定列列表，则会出现如下所示的语法错误。

MariaDB [tiamo]> LOAD DATA INFILE 'employee7.txt' 
    ->  INTO TABLE employee (id, name, salary)
    ->  FIELDS TERMINATED BY ',';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FIELDS TERMINATED BY ','' at line 3

9. 在上传过程中使用“设置”选项使用变量

对于此示例，让我们使用以下 employee2.txt 文件。

# cat employee2.txt 
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000

在这个例子中，我们希望在将工资上传到表之前将工资增加 500。例如，Thomas（第一条记录）的薪水是 5000。但是，在上传期间，我们希望将其增加 500 到 5500，并在表中更新这个增加的值。

为此，请使用 SET 命令并将工资用作变量并按如下所示进行增量。

LOAD DATA INFILE 'employee2.txt'
 INTO TABLE employee
 FIELDS TERMINATED BY ','
 (id, name, dept, @salary)
 SET salary = @salary+500;

正如您从以下输出中看到的，在从文本文件上传数据期间，所有记录的工资列增加了 500。

MariaDB [tiamo]> select * from employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   5500 |
| 200 | Jason  | Technology |   6000 |
| 300 | Mayla  | Technology |   7500 |
| 400 | Nisha  | Marketing  |  10000 |
| 500 | Randy  | Technology |   6500 |
+-----+--------+------------+--------+

10.编写Shell脚本从文本文件加载数据

有时您可能希望自动从文本文件上传数据，而不必每次都登录到 mysql 提示符。

假设我们想将以下命令放入 shell 脚本中，并在 geekstuff 数据库上自动执行。

LOAD DATA INFILE 'employee2.txt'
 INTO TABLE employee
 FIELDS TERMINATED BY ','

要从命令行执行加载，您将在 mysql 命令中使用 -e 选项并从 linux 提示符下执行它，如下所示。

# mysql -e "LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','" \
 -u root -pMySQLPassword tiamo

或者，您可以将其放入 shell 脚本中，如下所示。在这个例子中，load-data.sh shell 脚本有上面的 mysql 命令。

# cat load-data.sh 
mysql -e "\
   LOAD DATA INFILE 'employee2.txt'\
    INTO TABLE employee \
	FIELDS TERMINATED BY ','\
	" \
 -u root -pMySQLPwd4MDN! test

赋予这个 load-data.sh 脚本执行权限，并从命令行执行它，它会自动将数据加载到表中。您还可以将其安排为 cronjob，以按计划的时间间隔将文件中的数据自动加载到表中。

# chmod u+x load-data.sh

# ./load-data.sh
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入