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

Question

问

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

gavinwu 发布于 2023-01-31 20:18

我在确定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)');

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

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

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

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

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

Palec.. 6

正确查询

您的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)



调试不可打印的字符

如果对无法打印的字符有疑问，可以随时查看十六进制转储并手动解码字符。通常这些字符是ASCII。在* NIXes上，请参阅man ascii，否则请在Internet（例如http://man-ascii.com/）上查找表格。

在MySQL中，字符串的十六进制转储是通过HEX()函数应用程序获得的，例如

SELECT HEX(one) FROM test


在POSIX shell中，hexdump是使用以下命令生成的

od -t x1 temp/test.csv


如果您不想成为那个顽固分子，那么对于常见的空格，只需使用以下命令将其转换为C转义序列即可（例如，\r用于回车）

od -c temp/test.csv


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


unix 对于 \n
dos 对于 \r\n
mac 对于 \r


提防多个转义级别

请注意，在回答的开头，我只写了查询，而不是执行它的PHP命令。在SQL中，某些字符是特殊字符，需要转义以按原义使用（字符串内的引号），其他字符则通过转义（nin \n）获得特殊含义。在PHP中，SQL查询必须放在字符串中，在该字符串中会进行另一级转义（并针对不同的字符！）。例如，当您从shell调用PHP为时，会出现另一种转义php -r '…'。

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



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


mysql API在PHP 5.5中已弃用，在PHP 7.0中已删除

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