从 PHP 将上传的 CSV 导入 MySQL 表时，正确的 LINES TERMINATED BY

Correct LINES TERMINATED BY when importing uploaded CSV into MySQL table from PHP

提问by Solid I

I'm having trouble identifying how my CSV's lines are terminated. I'm using the following PHP code to load data from a CSV into my DB.

我无法确定我的 CSV 行是如何终止的。我正在使用以下 PHP 代码将数据从 CSV 加载到我的数据库中。

mysql_query('LOAD DATA LOCAL INFILE "temp/test.csv"
             INTO TABLE test
             FIELDS TERMINATED BY ","
             LINES TERMINATED BY "///"
             IGNORE 1 LINES (one, two, three, four, five)');

Now when I use LINES TERMINATED BY "///", the data is put into my DB properly, except for the fact that the 1st column on each row is “a return” followed by the cell data. That's not what I want, I want the return to disappear.

现在，当我使用 时LINES TERMINATED BY "///"，数据已正确放入我的数据库中，除了每一行的第一列是“返回”，然后是单元格数据。那不是我想要的，我想要回归消失。

I think that the sequence of last column cell, ///, return and first column cell in the CSV is split by ///and should be split by ///+ return. I should change the LINES TERMINATED BY. I've tried \n, \r, \r\n, \n\r. After a while I tried <br>and knew I needed help.

我认为///CSV中最后一列单元格，返回和第一列单元格的序列被拆分///，应该被///+返回拆分。我应该改变LINES TERMINATED BY. 我试过\n，\r，\r\n，\n\r。一段时间后，我尝试<br>并知道我需要帮助。

I'm using MS Office on a Mac and saving my CSV as Comma Separated Values (.csv)(sample). I've also tried the mentioned LINES TERMINATED BYwith MS-DOS Comma Separatedand Windows Comma Separatedfiles.

我在 Mac 上使用 MS Office 并将我的 CSV 保存为Comma Separated Values (.csv)（示例）。我也试过提到LINES TERMINATED BY的MS-DOS Comma Separated和Windows Comma Separated文件。

Result of SELECT HEX(one) FROM testafter running my query:

SELECT HEX(one) FROM test运行我的查询后的结果：

Array
(
    [0] => 0D6131
)
Array
(
    [0] => 0D6132
)
Array
(
    [0] => 0D6133
)

回答by Palec

Correct query

正确查询

Your CSV has rows delimited by three slashes and carriage return. Just use LINES TERMINATED BY "///\r"in your query and it should work. At least it works for me on my MySQL 5.1.49.

您的 CSV 具有由三个斜杠和回车符分隔的行。只需LINES TERMINATED BY "///\r"在您的查询中使用它就可以了。至少它适用于我的 MySQL 5.1.49。

LOAD DATA LOCAL INFILE "temp/test.csv"
INTO TABLE test
FIELDS TERMINATED BY ","
LINES TERMINATED BY "///\r"
IGNORE 1 LINES (one, two, three, four, five)

Debugging unprintable characters

调试不可打印的字符

When in doubt about what the unprintable characters are, you can always look at hexdump and decode the characters manually. Usually these characters are ASCII. On *NIXes, see man ascii, otherwise look the table up on Internet (e.g. http://man-ascii.com/).

当对不可打印字符是什么有疑问时，您可以随时查看 hexdump 并手动解码字符。通常这些字符是ASCII。在 *NIX 上，请参阅man ascii，否则请在 Internet 上查找该表（例如http://man-ascii.com/）。

In MySQL, hexdump of a string is obtained by HEX()function application, e.g.

在 MySQL 中，字符串的 hexdump 是通过HEX()函数应用获得的，例如

SELECT HEX(one) FROM test

In POSIX shell, hexdump is generated using

在 POSIX shell 中，使用 hexdump 生成

od -t x1 temp/test.csv

If you do not want to be that hardcore, for common whitespace it is enough to convert it to C escape sequences (e.g. \rfor carriage return) using

如果你不想成为那个铁杆，对于普通的空格，\r使用

od -c temp/test.csv

Maybe even simpler (but generally harder to explain) approach to identifying EOL type is just using the text editor of your choice and letting it tell you. CSV is a plain text format (in contrast to binary format). E.g. in Vim open the file and run se ff?. This will show you fileformat=<EOL-type>where <EOL-type>is

也许更简单（但通常更难解释）的识别 EOL 类型的方法就是使用您选择的文本编辑器并让它告诉您。CSV 是纯文本格式（与二进制格式相反）。例如，在 Vim 中打开文件并运行se ff?. 这会告诉你fileformat=<EOL-type>哪里<EOL-type>是

• unixfor \n
• dosfor \r\n
• macfor \r

• unix为了 \n
• dos为了 \r\n
• mac为了 \r

Beware multiple escaping levels

当心多个转义级别

Notice that at the beginning of my answer I wrote just the query, not PHP command to execute it. In SQL, some characters are special and need to be escaped to be taken literally (quote inside string), other gain their special meaning through escaping (nin \n). In PHP, the SQL query must be placed in string, where another level of escaping takes place (and for different characters!). Another level of escaping comes e.g. when you call PHP from shell as php -r '…'.

请注意，在我的回答开头我只写了查询，而不是 PHP 命令来执行它。在 SQL 中，有些字符是特殊的，需要转义才能按字面意思理解（字符串内的引号），另一些则通过转义（nin \n）获得它们的特殊含义。在 PHP 中，SQL 查询必须放在字符串中，在那里进行另一级别的转义（针对不同的字符！）。例如，当您从 shell 调用 PHP 作为php -r '…'.

If you want to be sure that you got the escaping right, print the query before sending it to database.

如果您想确保转义正确，请在将查询发送到数据库之前打印查询。

$query = '…';
print $query;
#$result = mysql_query($query);

mysqlAPI was deprecated in PHP 5.5, removed in PHP 7.0

mysqlAPI 在 PHP 5.5 中被弃用，在 PHP 7.0 中被移除

The old mysqlAPI (mysql_*functions) was deprecated in PHP 5.5and removed in PHP 7.0, it will not be available in future versions. Consult the PHP manual for choice of MySQL API. See also other relevantanswers.

旧的mysqlAPI（mysql_*函数）是在PHP 5.5弃用，并在PHP 7.0取出，也不会在将来的版本。请查阅 PHP 手册以选择 MySQL API。另请参阅其他相关答案。

回答by Patrick Moore

Remove the ///occurrences from your CSV file.

///从 CSV 文件中删除出现的事件。

Make sure your CSV file is uploaded in ASCII (not BINARY) FTP format.

确保您的 CSV 文件以 ASCII（而非 BINARY）FTP 格式上传。

Then query:

然后查询：

mysql_query("LOAD DATA LOCAL INFILE 'temp/test.csv' 
             INTO TABLE test 
             FIELDS TERMINATED BY ',' 
             LINES TERMINATED BY '\n' 
             IGNORE 1 LINES (one, two, three, four, five)");