MySql 加载数据本地语法？
我有一个使用管道“|”的文本文件“result.txt”分离出字段。我使用 PhpMyAdmin 并通过指定使用“CSV LOAD DATA”并告诉它字段应该用“|”分隔来成功地将其导入到我的表中。
PhpMyAdmin 还给出了它的完整查询，所以我将其复制并粘贴到我的 php 脚本中，如下所示：
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 行 ''' 附近使用的正确语法
我想知道，因为我复制了由phpmyadmin生成的完全相同的查询，我认为它肯定会在这里工作。但为什么会出现这样的错误呢？
我尝试修剪查询以仅包含“FIELDS TERMINATED BY”并且它有效。但以这种方式填充的数据库将包含不正确的数据。所以我真的很想知道为什么原来的较长查询会失败？
谢谢。
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 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());
I will always receieve error saying:
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
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?
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?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您使用的是双引号字符串，因此
\r\n将被视为文字回车符和换行符。您还需要对它们进行两次转义：\\r\\n。错误消息中的“on line 2”证明了这一点 - 如果您的查询没有实际的第二行，但由于嵌入的换行符/回车符，一旦到达 MySQL，就会有第二行。
You're using a double-quoted string, so the
\r\nwill be seen as literal carriage return and line feed characters. You'll need to double-escape them as well:\\r\\n.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.
试试这个：
Try this :