LINES TERMINATED BY和FIELDS TERMINATED BY的多种可能性 - MySQL

问题描述

我想在我的内容管理系统中创建一个功能，用户可以上传一个CSV文件，然后解析该文件，并将数据放入MySQL数据库。为此，我使用文件输入和此SQL查询。

I'm trying to create a feature in my Content Management System in which users can upload a CSV file which is then parsed and the data is put in a MySQL database. To do this I use a file input and this SQL query.

$sql = "LOAD DATA LOCAL INFILE '".$_FILES["file"]["tmp_name"]."'
            INTO TABLE persons
            FIELDS TERMINATED BY ';'
            LINES TERMINATED BY '\r'
            (id, name, email, contacts)";

这对于 .csv 我在我的计算机上创建，但不是CSV文件有字段由分号结束并且有以 \r 结束的行。现在我想这个查询适用于所有 .csv 文件。否则，此功能将无法在我的CMS中实施。有任何方式，我可以使这项工作为 .csv 文件，有其他字段和行尾？ （例如\\\
，\r\\\
，，）

This works perfectly for a .csv file I created on my computer, but not al CSV files have fields that are terminated by a semicolon and have lines that are terminated with a \r. Now I want this query to work for all .csv files. Otherwise this feature won't work well enough to be implemented in my CMS. Is there any way I can make this work for .csv files which have other field and line endings? (e.g \n, \r\n, ,)

推荐答案

尝试 LINES TERMINATED BY '\r\\\
'

这将适用于以'\r\\\
'结尾的文件， '\r'和'\\\
'。从MySQL文档：

This will work for files ending lines with '\r\n', but also with '\r' and '\n'. From the MySQL docs:

您还需要添加 \r\\\
添加到 FIELDS TERMINATED BY 子句以便告诉解析器不要包含行符号在第一行的最后一个字段。

You also need to add \r\n to the clause FIELDS TERMINATED BY in order to tell the parser not to include the end-of-lines symbols in the last field of the first line. So

FIELDS TERMINATED BY';，\t\r\\\
'

在大多数情况下都适用。

will work in most cases.

这篇关于LINES TERMINATED BY和FIELDS TERMINATED BY的多种可能性 - MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！