10个MySQL加载数据内文件示例以将文本文件数据上传到表
如果文本文件中有数据，则可以轻松地将它们上载到数据库中的一个或多个表。
在MySQL数据库(或MariaDB)中，使用“ load data infile”命令，您可以将数据从文本文件上传到表。
load data infile命令提供了几个灵活的选项，可以将各种格式的数据从文本文件加载到表中。
文本文件加载数据的基本示例
使用“字段终止于”选项上传数据
使用“附件”选项上传数据
在文本文件数据中使用转义符
使用“行终止于”选项上传数据
使用“开始方式”选项忽略上传文件中的行前缀
从上传文件中忽略标题行
从上传文件仅上传特定列(并忽略其他列)
通过“设置”选项在上传过程中使用变量
编写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;
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”选项，您可以指定逗号字段分隔符，如下所示。
`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;
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
+-----+--------------+-------------------+--------+`
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;
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`
这次将正常工作，因为我们使用\作为转义字符。
`LOAD DATA INFILE 'employee4.txt'
->INTO TABLE employee
->FIELDS TERMINATED BY ',';
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
+-----+---------------+------------+--------+`
您还可以使用其他转义字符，如下所示。在此示例中，我们使用^作为转义字符，而不是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;
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计算机，则可能要使用此命令：'\ 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;
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 lines选项，如下所示。
`LOAD DATA INFILE 'employee7.txt'
INTO TABLE employee
FIELDS TERMINATED BY ','
IGNORE 1 LINES;`
从下面的输出中可以看到，即使输入文件有6行，它也忽略了第一行(即标题行)，并上传了其余5行。
`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，如下所示。
`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
+-----+--------+------+--------+`
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;
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 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`