LINES TERMINATED BY和FIELDS TERMINATED BY的多种可能性 - MySQL [英] Multiple possibilities for LINES TERMINATED BY and 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:

终止终止符，LINES STARTING BY和LINES终止符
值可以是多个字符。例如，要编写由回车/换行符对结束的行
，或者读取包含这些行的
文件，请指定LINES TERMINATED BY'\r\\\
'
子句。

The FIELDS TERMINATED BY, LINES STARTING BY, and LINES TERMINATED BY values can be more than one character. For example, to write lines that are terminated by carriage return/linefeed pairs, or to read a file containing such lines, specify a LINES TERMINATED BY '\r\n' clause.

您还需要添加 \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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！