≡菜单

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

MySQL徽标如果文本文件中有数据,则可以轻松地将它们上载到数据库中的一个或多个表。

在MySQL数据库(或MariaDB)中,使用“load data infile”命令,您可以将数据从文本文件上传到表。

The 加载数据文件 command provides several flexible options to load various formats of data from text file to tables.

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

  1. 从文本文件加载数据的基本示例
  2. 使用上传数据“Fields terminated 通过” Option
  3. 使用上传数据“Enclosed 通过” Option
  4. 在文本文件数据中使用转义符
  5. 使用上传数据“Lines terminated 通过” Option
  6. 使用以下命令忽略上传文件中的行前缀“Starting By” Option
  7. 从上传文件中忽略标题行
  8. 从上传文件仅上传特定列(并忽略其他列)
  9. 在上传过程中使用变量“Set” Option
  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

By default, the 加载数据文件 command uses TAB as the default field delimiter.

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

USE thegeekstuff;

下面的MySQL命令将从上面的employee1.txt文件中将记录加载到employee表中,如下所示。该命令没有’不要使用任何其他选项。

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

注意:在上面的示例中,该命令假定employee1.txt文件位于数据库目录下。例如,如果要在geekstuff数据库中执行上述命令,则将文件放在以下位置:/ var / lib / mysql / thegeekstuff /

以下是上述命令的输出。

查询确定, 5 rows affected (0.00 sec)                 
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

在上面:

  • 第一行“Query OK”表示查询已执行,没有任何错误。它还表示总共有5行上载到该表。这还将显示将数据从文本文件上传到表所花费的时间(以秒为单位)。
  • 第二行显示上传的总行数,跳过的行数以及在上传过程中显示警告的记录数。

数据加载后,以下是我们’会在员工表中看到。

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

注意:如果要备份和还原整个MySQL数据库,请使用 mysqldump命令.

2.使用上传数据“Fields terminated 通过” Option

在下面的示例中,在输入文件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分隔时,才使用此选项。如果字段以冒号表示,则您’ll在上面的命令中使用以下选项:

领域 TERMINATED BY ':';

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

以下是MySQL上传期间可能发生的一些基本错误

错误1:如果文本文件不在正确的目录下,您可能会收到以下信息“ERROR 13 (HY000) Can’t获得(Errcode:2)的统计信息” error message.

MariaDB [thegeekstuff]> 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。如果没有,你’ll获得加载数据文件权限被拒绝的错误消息。

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

错误2:如果您不’t指定正确的终止字段,然后您’会在上传中看到一些问题。在此示例中,仅第一个字段“id”上传了。所有其他字段的值为NULL。这是因为以下命令没有’t指定以option结尾的字段,因为输入文件用逗号作为字段定界符。

MariaDB [thegeekstuff]> 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 | 部门 | salary |
+-----+------+------+--------+
| 100 | NULL | NULL |   NULL |
| 200 | NULL | NULL |   NULL |
| 300 | NULL | NULL |   NULL |
| 400 | NULL | NULL |   NULL |
| 500 | NULL | NULL |   NULL |
+-----+------+------+--------+

3. 使用上传数据“Enclosed 通过” Option

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

# 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 通过”选项如下所示。

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

上面的命令将正确地上传记录,如下所示 mysql选择命令:

MariaDB [thegeekstuff]> select * from employee;
+-----+--------------+-------------------+--------+
| id  | name         | 部门              | 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”如下所示两次,它将显示以下错误消息:

领域 TERMINATED BY ',' 领域 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 warnings”

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

由于字段之一的值中包含逗号,因此记录也未正确加载。

MariaDB [thegeekstuff]> select * from employee;
+-----+--------+---------+--------+
| id  | name   | 部门    | 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 [thegeekstuff]> LOAD DATA INFILE 'employee4.txt' 
    ->  INTO TABLE employee 
    ->  领域 TERMINATED BY ',';

MariaDB [thegeekstuff]> select * from employee;
+-----+---------------+------------+--------+
| id  | name          | 部门       | 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 |
+-----+---------------+------------+--------+

您还可以使用另一个转义符,如下所示。在此示例中,我们使用^作为转义字符,而不是defualt \。

# 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

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

LOAD DATA INFILE 'employee41.txt' 
 INTO TABLE employee 
 领域 TERMINATED BY ',' 逃脱 '\^'

请注意,某些字符不能用作转义字符。例如,如果您使用%作为转义字符,则您’ll得到以下错误信息。

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

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

5.使用上传数据“Lines terminated 通过” Option

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

在下面的示例中,每个记录都由|分隔。符号。

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

To upload the above file, use the lines terminated 通过选项如下所示。

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

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

MariaDB [thegeekstuff]> select * from employee;
+-----+--------+------------+--------+
| id  | name   | 部门       | 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)终止符‘\r’2)终止行‘\r\n’

6.使用以下命令忽略上传文件中的行前缀“Starting By” Option

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

例如,在下面的employee6.txt文件中,对于第一,第二和第五条记录,我们有“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

要忽略行前缀并上传这些记录,例如:“Data:”在以上文件中),使用“lines starting 通过”选项如下所示。

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

以下是上述命令的输出:

查询确定, 3 rows affected (0.00 sec)                 
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

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

MariaDB [thegeekstuff]> select * from employee;
+-----+--------+------------+--------+
| id  | name   | 部门       | 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

During upload, we want to ignore the 1st header lien from the employee7.txt file. For this, use the IGNORE 1 lines选项如下所示。

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

从下面的输出中可以看到,即使输入文件有6行,它也忽略了第一行(即标题行),并上传了剩余的5行。

查询确定, 5 rows affected (0.00 sec)                 
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [thegeekstuff]> select * from employee;
+-----+--------+------------+--------+
| id  | name   | 部门       | 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 
 领域 TERMINATED BY ','
 (id, name, salary);

因为我们没有’t specify the “dept”在上面的命令列中,我们’将会看到此列为NULL,如下所示。

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

再次提醒您,当您不这样做时’t指定列列表,该命令将期望所有列都出现在输入文件中。

另外,如果你不’在最后一行中指定列列表’ll会得到语法错误,如下所示。

MariaDB [thegeekstuff]> LOAD DATA INFILE 'employee7.txt' 
    ->  INTO TABLE employee (id, name, salary)
    ->  领域 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.在上传过程中使用变量“Set” Option

对于此示例,让我们使用以下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
 领域 TERMINATED BY ','
 (id, name, 部门, @salary)
 SET salary = @salary+500;

从以下输出中可以看到,在从文本文件上载数据期间,所有记录的salary列增加了500。

MariaDB [thegeekstuff]> select * from employee;
+-----+--------+------------+--------+
| id  | name   | 部门       | 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
 领域 TERMINATED BY ','

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

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

或者,您可以将其放入外壳脚本中,如下所示。在此示例中,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

如果您喜欢这篇文章,您可能还会喜欢..

  1. 50个Linux Sysadmin教程
  2. 50个最常用的Linux命令(包括示例)
  3. 排名前25位的最佳Linux性能监视和调试工具
  4. 妈妈,我找到了! – 15个实用的Linux Find命令示例
  5. Linux 101 Hacks第二版电子书 Linux 101黑客手册

Bash 101 Hacks书 Sed和Awk 101黑客手册 Nagios Core 3书 Vim 101黑客手册

{ 1 评论… 加一 }

发表评论