MySql加载数据本地语法？ [英] MySql Load Data Local Syntax?

问题描述

我有一个文本文件result.txt，使用管道'|'分隔字段。我使用PhpMyAdmin并通过指定使用CSV LOAD DATA并指出字段应该用'|'分隔，成功导入到我的表中。

I have a text file "result.txt" that used pipes '|' to separate out the fields. I used PhpMyAdmin and successfully imported it to my table by specifying using "CSV LOAD DATA" and telling it the fields should be separated by '|'.

PhpMyAdmin也给了完全查询它，所以我复制它，并将其粘贴到我的php脚本，它看起来像这样：

PhpMyAdmin also gave the full query for it, so I copied it and paste it into my php script, which looked like this:

 mysql_query("LOAD DATA LOCAL INFILE 'C:/wamp/www/TouchStone/result.txt' INTO TABLE customer_change FIELDS TERMINATED BY '|' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' ")
 or die(mysql_error());

我总是会收到错误说：

您的SQL语法有错误;检查对应于您的MySQL服务器版本的手册在第2行的'附近使用正确的语法

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 2

因为我复制完全相同的查询生成的phpmyadmin，我认为它肯定会工作在这里。但为什么会发生这样的错误？

I was wondering, since I copied exactly the same query generated by phpmyadmin, I think it will definitely work here. But why will such error happen?

我尝试修剪查询，只包含FIELDS TERMINATED BY，它工作。但是以这种方式填充的数据库将包含不正确的数据。所以我真的想知道为什么原来更长的查询会失败。

I tried trimming the query to contain only "FIELDS TERMINATED BY " and it worked. But the database populated this way will contain incorrect data. So I am really wishing to learn why would the original longer query would fail?

谢谢。

推荐答案

您使用的是双引号字符串，因此 \r\\\
将被视为文字回车符和换行符。您还需要对其进行双重转义： \\r\\\\
。

You're using a double-quoted string, so the \r\n will be seen as literal carriage return and line feed characters. You'll need to double-escape them as well: \\r\\n.

错误消息中的on line 2是这样的证据 - 如果你的查询没有实际的第二行，但是因为嵌入的换行符/回车，有一次它到达MySQL。

The "on line 2" in the error message is evidence of this - there's no actual second line if your query, but because of the embedded newline/carriage return, there is once it gets to MySQL.

这篇关于MySql加载数据本地语法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！