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

问题描述

我有一个使用管道'|'的文本文件result.txt"分离字段.我使用了 PhpMyAdmin 并通过指定使用CSV 负载数据"并告诉它字段应该用|"分隔来成功地将它导入到我的表中.

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 '
' ")
 or die(mysql_error());

我总是会收到错误提示:

I will always receieve error saying:

您的 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\n.

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

错误消息中的第 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屋！