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

如果文本文件中有数据，则可以轻松地将它们上载到数据库中的一个或多个表。
在MySQL数据库（或MariaDB）中，使用“ load data infile”命令，您可以将数据从文本文件上传到表。
load data infile命令提供了几个灵活的选项，可以将各种格式的数据从文本文件加载到表中。

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

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

    `#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 rumenzdata;

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

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

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

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

    `select * from employee;

+-----+--------+------------+--------+`

注意：如果要备份和还原整个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”选项，您可以指定逗号字段分隔符，如下所示。

    `USE rumenzdata;

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

如果字段以冒号分隔，则在上面的命令中使用以下选项：

    `FIELDS TERMINATED BY ':';`

3.使用“附件”选项上传数据

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

    `#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 '"';`

    `select * from employee;

+-----+--------------+-------------------+--------+`

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

假设您在特定字段的值中有逗号。例如，在下面的示例中，第二个字段名称具有以下格式的值：“名字，姓氏”。

    `#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条警告”

    `LOAD DATA INFILE 'employee4.txt' 

->INTO TABLE employee
->FIELDS TERMINATED BY ',';
Query OK, 5 rows affected, 10 warnings (0.00 sec)
Records: 5Deleted: 0Skipped: 0Warnings: 10`

由于字段之一的值中有逗号，因此记录也没有正确加载。

    `select * from employee;
    <pre>

+-----+--------+---------+--------+`

</pre>

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

`#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`

这次将正常工作，因为我们使用\作为转义字符。

    `LOAD DATA INFILE 'employee4.txt' 

->INTO TABLE employee
->FIELDS TERMINATED BY ',';

+-----+---------------+------------+--------+`

您还可以使用其他转义字符，如下所示。在此示例中，我们使用^作为转义字符，而不是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
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上传记录，如下所示。

    `select * from employee;

+-----+--------+------------+--------+`

如果输入文件来自Windows计算机，则可能要使用此命令：'\ r \ n'终止的行
如果您使用CSV文件将数据上传到表格，请尝试以下方法之一：1）以'\ r'终止的行2）以'\ r \ n'终止的行

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

您还可以在输入文本文件中为记录添加一些前缀，在上传过程中可以忽略这些前缀。
例如，在下面的employee6.txt文件中，对于第一条记录，第二条记录和第五条记录，我们在行的开头有“数据：”。您可以通过忽略行前缀来仅上传这些记录。

    `#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 starts by”选项，如下所示。

    `LOAD DATA INFILE 'employee6.txt' 

INTO TABLE employee
FIELDS TERMINATED BY ','
LINES STARTING BY 'Data:';

输出

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

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

    `select * from employee;

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 lines选项，如下所示。

    `LOAD DATA INFILE 'employee7.txt' 

INTO TABLE employee
FIELDS TERMINATED BY ','
IGNORE 1 LINES;`

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

    `select * from employee;

+-----+--------+------------+--------+`

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，如下所示。

    `select * from employee;

+-----+--------+------+--------+`

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;`

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

    `select * from employee;

+-----+--------+------------+--------+`

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 rumenzdata`

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