我们如何将带有行前缀的文本文件导入 MySQL 表?
mysqlmysqli database
假设文本文件中有行前缀，那么借助‘LINES STARTING BY’选项，我们可以忽略该前缀并将正确的数据导入 MySQL 表。借助以下示例 −，可以理解这一点
示例
假设我们在文本文件中使用‘VALUE’作为‘LINE PREFIX’，如下所示 −
id, Name, Country, Salary VALUE:105, Chum*, Marsh,USA, 11000 106, Danny*, Harrison,AUS, 12000
现在，将此文本文件导入 MySQL 表时，我们还需要在查询中提及‘LINES STARTING BY’选项，如下所示 −
mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee8_tbl FIELDS TERMINATED BY ',' ESCAPED BY '*' LINES STARTING BY 'Value:' IGNORE 1 ROWS; Query OK, 1 row affected (0.07 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
现在，我们可以借助以下查询查看已导入的内容−
mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee8_tbl FIELDS TERMINATED BY ',' ESCAPED BY '*' LINES STARTING BY 'Value:' IGNORE 1 ROWS; Query OK, 1 row affected (0.07 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
现在，我们可以借助以下查询查看已导入的内容 −
mysql> Select * from employee8_tbl; +------+----------------+----------+--------+ | Id | Name | Country | Salary | +------+----------------+----------+--------+ | 105 | Chum,Marsh | USA | 11000 | +------+----------------+----------+--------+ 1 row in set (0.00 sec)
MySQL 只导入表中的一条记录，因为我们在第一行之前使用了行前缀，即"VALUE:"，因此它会忽略没有行前缀的行。现在，假设我们在文本文件中添加另一行，行前缀为"VALUE:"，那么 MySQL 也会上传这一行 −
id, Name, Country, Salary Value:105, Chum*, Marsh,USA, 11000 106, Danny*,Harrison, AUS, 12000 Value:107, Raman*,Kumar, IND, 25000
mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee8_tbl FIELDS TERMINATED BY ',' ESCAPED BY '*' LINES STARTING BY 'Value:' IGNORE 1 ROWS; Query OK, 2 rows affected (0.07 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
现在，我们可以借助以下查询查看已导入的内容 −
mysql> Select * from employee8_tbl; +------+----------------+----------+--------+ | Id | Name | Country | Salary | +------+----------------+----------+--------+ | 105 | Chum,Marsh | USA | 11000 | | 107 | Raman,Kumar | IND | 25000 | +------+----------------+----------+--------+ 2 rows in set (0.00 sec)
