PHP：LOAD DATA INFILE语法错误 [英] PHP: LOAD DATA INFILE syntax errors
问题描述
这里是我使用的import（）函数 -
function import（）{
$ this-> db = mydb :: getConnection（）;
//设置所选日记表的列名称
if（$ this-> table =bsp_journals）{
$ columns =category ，discipline，subject，sourcetype，issn，publicationname，indabstart，indabstop，fulltextstart，fulltextstop，fulltextdelay，peerreviewed;
}
try {
$ sql =LOAD DATA LOCAL INFILE$ this-> file'
INTO TABLE'$ this-> table'
由'\\'
终止的字段由'\\\\
'终止的字符
可选项'\''
（$ columns）;
$ statement = $ this-> db-> prepare（$ sql）;
$ statement-> execute
$ this-> return = $ this-> files ['filename'] ['tmp_name'];
} catch（PDOException $ ex）{
// throw $ ex;
$ this-> return = $ ex-> getMessage（）。< br />< / br />。$ sql 。< br />< / br />文件名=。$ this->文件;
}
return $ this-> return;
}
包括mysql错误消息和SQL查询
- 导入结果：SQLSTATE [42000]：语法错误或访问冲突：在您的SQL语法中有错误;检查与您的MySQL服务器版本对应的手册，以获得在'bsp_journals'附近使用的正确语法。FIELDS TERMINATED BY'，'LINES TERMINATED BY'\ n'在第2行
LOAD DATA LOCAL （类别，学科，主题，sourcetype，issn，出版物名称，indabstart，文件名，文件名，文件名，文件名，文件名等） indabstop，fulltextstart，fulltextstop，fulltextdelay，peerreviewed）
文件名= files / buh-journals.csv
INTO TABLE'$ this-> table'
不要使用单引号引用表名。单引号用于字符串文字或日期文字。
请勿使用引号，否则返回分隔标识符。
INTO TABLE`$ this-> table`
重新发表您的评论：
您显然已从 文件名和tablename中删除了引号。这不是我的意思。只需从tablename中删除字符串引号。
例如：
$ sql =LOAD DATA LOCAL INFILE'$ this-> file'
INTO TABLE`$ this-> table`
终止终止'，'
终止' \\ n'
可选地由'\''
（$ columns）;
$ b b
查看 http：// dev。 mysql.com/doc/refman/5.6/en/load-data.html
请注意周围是否有引号， INFILE'file_name'和 INTO TABLE tbl_name ：
载入资料[LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
除了表名称包含特殊字符，空格或保留字之外，您实际上不需要在表名周围加上反号。
您遇到此错误：
一般错误：2030此命令在准备的语句协议中不支持
右键，并非所有SQL命令都与 prepare 。我没有检查你的情况，因为我们首先解决语法错误。您可以在此页面的准备语句中允许的SQL语法标题下找到可以 prepare（） d的命令列表：
http://dev.mysql.com /doc/refman/5.6/en/sql-syntax-prepared-statements.html
我链接到MySQL 5.6文档，但您应该访问docs为您使用的MySQL版本。
因此，您不能使用 prepare（） - - 您必须使用 exec（）或 query（） DATA INFILE 命令。
或者，如果您使用PDO，您可以将属性 PDO :: ATTR_EMULATE_PREPARES 设置为 true 。这将使PDO假出MySQL，所以prepare（）是一个无操作，查询实际上是在execute（）期间发送的。
I'm trying to import csv data into mysql using PDO and LOAD DATA INFILE (I've also tried LOAD DATA LOCAL INFILE) but I keep getting a syntax error and I have no idea why. The filepath and table names seem to be correct.
here is the import() function I am using -
function import() {
$this->db = mydb::getConnection();
// set the column names for the selected journal table
if ($this->table = "bsp_journals") {
$columns = "category, discipline, subject, sourcetype, issn, publicationname, indabstart, indabstop, fulltextstart, fulltextstop, fulltextdelay, peerreviewed";
}
try {
$sql = "LOAD DATA LOCAL INFILE '$this->file'
INTO TABLE '$this->table'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\\n'
OPTIONALLY ENCLOSED BY '\"'
($columns)";
$statement = $this->db->prepare($sql);
$statement->execute();
$this->return = $this->files['filename']['tmp_name'];
} catch (PDOException $ex) {
//throw $ex;
$this->return = $ex->getMessage() . "<br /></br />" . $sql . "<br /></br />File Name = " . $this->file;
}
return $this->return;
}
below is the message I get including the mysql error message and the SQL query
- import result : SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''bsp_journals' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ' at line 2
LOAD DATA LOCAL INFILE 'files/buh-journals.csv' INTO TABLE 'bsp_journals' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' OPTIONALLY ENCLOSED BY '"' (category, discipline, subject, sourcetype, issn, publicationname, indabstart, indabstop, fulltextstart, fulltextstop, fulltextdelay, peerreviewed)
File Name = files/buh-journals.csv
INTO TABLE '$this->table'
Don't use single-quotes to quote a table name. Single-quotes are for string literals or date literals.
Either use no quotes, or else back-ticks for delimited identifiers.
INTO TABLE `$this->table`
Re your comment:
You apparently removed quotes from both the filename and the tablename. This is not what I meant. Just remove the string-quotes from the tablename. You do need them for the filename.
Example:
$sql = "LOAD DATA LOCAL INFILE '$this->file'
INTO TABLE `$this->table`
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\\n'
OPTIONALLY ENCLOSED BY '\"'
($columns)";
Review the syntax documented at http://dev.mysql.com/doc/refman/5.6/en/load-data.html
Notice the presence or absence of quotes around INFILE 'file_name' and INTO TABLE tbl_name:
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
You don't actually need the back-ticks around the table name, unless your table name contains special characters, whitespace, or reserved words.
You got this error:
General error: 2030 This command is not supported in the prepared statement protocol yet
Right, not all SQL commands are compatible with prepare(). I didn't check that for your case, because we were resolving the syntax error first. You can find a list of the commands that can be prepare()d under the heading SQL Syntax Allowed in Prepared Statements on this page:
http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html
I linked to the MySQL 5.6 docs, but you should visit the docs for the version of MySQL you use. Because the list of compatible commands changes from release to release.
So you can't use prepare() -- you'll have to use exec() or query() instead for a LOAD DATA INFILE command.
Alternatively, if you're using PDO, you can set the attribute PDO::ATTR_EMULATE_PREPARES to true. That will make PDO fake out MySQL, so prepare() is a no-op and the query is actually sent during execute().
这篇关于PHP：LOAD DATA INFILE语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
