我的LOAD DATA INFILE语法有什么问题？ [英] What is wrong in my LOAD DATA INFILE Syntax?
问题描述
我试图将CSV文件导入MySQL数据库，但它没有工作。
I'm trying to import a CSV file into MySQL database but it didn't work.
我的数据库中有5列：
id，userID，movieID ，rate，timestamp
id , userID, movieID, rate, timestamp
在我的CSV文件中，我只有4列由两个冒号'::'分隔。
这是示例数据：
And in my CSV file I have only 4 columns separated by two colons '::'.
This is the sample data:
1 :: 1193 :: 5 :: 978300760
1::1193::5::978300760
这是我的查询
$ query =LOAD DATA INFILE$ uploadfile 'INTO TABLE rating_table
FILEDS TERMINATED BY'::'LINES TERMINATED BY'\\\
'（
userID，movieID，rate，timestamp）;
$query = "LOAD DATA INFILE '$uploadfile' INTO TABLE rating_table FIELDS TERMINATED BY '::' LINES TERMINATED BY '\n' ( userID, movieID, rate, timestamp)";
我使用 PDO_MySQL 这是额外的代码，让你可以理解。
And I'm using PDO_MySQL this is the additional code so that you can understand.
$stmt = $dbh->prepare($query);
try{
if($stmt->execute()){
echo "Sucessful importing of CSV file into the MySQL database!";
}
}
catch (PDOException $e) {
echo "Error importing of CSV file into the MySQL database!";
echo "Error!: " . $e->getMessage() . "<br/>";
die();
}
echo "</p>";
我已经尝试过很多次，但是当我签入我的数据库时，它仍然是空的。
我似乎不知道什么是错的，你能帮助我吗？谢谢！
I've tried many times but when I've checked in my database, it is still empty.
I can't seem to figure out what is wrong, can you help me? Thanks!
这是错误。
致命错误：使用消息
'未捕获异常'PDOException'SQLSTATE [HY000]：常规错误：2014无法执行查询
其他未缓冲的查询处于活动状态。考虑使用
PDOStatement :: fetchAll（）。或者，如果您的代码只有
要针对mysql运行，您可以通过在
中设置
PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY属性来启用查询缓冲C：\xampp\htdocs \movie\php\upload_file.php：57堆栈跟踪：＃0
C：\xampp\htdocs\movie\php\upload_file.php（57）：PDOStatement-> execute ）
1 {main}在第57行的C：\ xampp\htdocs\movie\php\upload_file.php中抛出
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in C:\xampp\htdocs\movie\php\upload_file.php:57 Stack trace: #0 C:\xampp\htdocs\movie\php\upload_file.php(57): PDOStatement->execute() 1 {main} thrown in C:\xampp\htdocs\movie\php\upload_file.php on line 57
这是我的连接设置：
This is my connection settings:
$ dbh = new PDO（'mysql： host = localhost; dbname = movie'，'root'，''，
array（
PDO :: MYSQL_ATTR_INIT_COMMAND =>SET NAMES UTF8，
PDO :: ATTR_EMULATE_PREPARES => FALSE，
PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE，
PDO :: ATTR_ERRMODE => PDO :: ERRMODE_EXCEPTION，PDO :: MYSQL_ATTR_LOCAL_INFILE => TRUE
））;
$dbh = new PDO('mysql:host=localhost;dbname=movie','root', '', array( PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES UTF8", PDO::ATTR_EMULATE_PREPARES => FALSE, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_LOCAL_INFILE => TRUE ));
推荐答案
为了在输出中看到错误消息，只需使用 PDO :: ERRMODE_EXCEPTION ，类似这样：
In order to see error messages on your output, just initialize your PDO with PDO::ERRMODE_EXCEPTION, something like this:
$db = new PDO('mysql:host=' . $config['db_host'] . ';dbname=' . $config['db_name'], $config['db_username'], $config['db_password'],
array(
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES UTF8",
PDO::ATTR_EMULATE_PREPARES => FALSE,
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));
之后，您将可以看到错误。发布更新，我/我们将能够帮助你！
Afterwards you will be able to see the error. Post it in an update and I/we will be able to help you out!
请查看 PDO_MYSQL 的手册。
您可能还需要：
PDO::MYSQL_ATTR_LOCAL_INFILE => TRUE
注意，此常数只能在driver_options数组中使用
构建新的数据库句柄。
Note, this constant can only be used in the driver_options array when constructing a new database handle.
编辑2：
根据 MySQL手册，MySQL必须用 - enable-local-infile 编译：
EDIT 2:
According to MySQL Manual, MySQL must be compiled with --enable-local-infile:
使用MySQL的完整路径，否则
将使用它的内部处理程序，它不使用新LOAD
DATA。
You MUST have compiled PHP using the full path to MySQL, otherwise it will use it's internal handlers, which don't work with the "new" LOAD DATA.
- with-mysql = / usr / local / mysql（假设您的MySQL位于此处）
--with-mysql=/usr/local/mysql (assuming your MySQL is located here)
您必须使用选项'--local-infile = 1'启动MySQL守护进程
You MUST start the MySQL daemon with the option '--local-infile=1'
或启用它， code> /etc/my.cnf ：
Or enabling it, by adding to your /etc/my.cnf:
[mysql]
local-infile=1
[mysqld]
local-infile=1
$ b b
重新启动 mysqld 之后，通过运行服务mysqld restart 。
引号用于字符串字面值，列和表名称的反号。正确转义变量。将您的查询更改为：
Quotes are for string literals, back-ticks for columns and tables names. Escape a variable properly. Change your query to:
$query = "LOAD DATA INFILE ' . $uploadfile . ' INTO TABLE `rating_table` FIELDS TERMINATED BY '::' LINES TERMINATED BY '\n' ( `userID`, `movieID`, `rate`, `timestamp`)";
编辑4：
通常在您同时拥有多个查询 open 时引起。例如，你调用 fetch（），但是你不要调用它，直到它完成，然后你尝试执行第二个查询。
EDIT 4:
This error is normally caused when you have multiple queries open at the same time. For example, you call fetch(), but you don't call it until it's finished, and then you try to execute a second query.
通常，这个问题的解决方法是调用 closeCursor（） PHP：PDOStatement :: closeCursor - 手动。尝试调用：
Normally, the solution to this problem is to call closeCursor() PHP: PDOStatement::closeCursor - Manual. Try calling that:
/* The following call to closeCursor() may be required by some drivers */
$stmt->closeCursor();
/* Now we can execute the second statement */
$otherStmt->execute();
并查看是否会更改任何内容。
and see if that changes anything for you.
尝试对转义变量使用其他语法。使用以下操作：
Try using other syntax for escaping variable. Use the following:
$stmt = $dbh->prepare("
LOAD DATA INFILE
'" . $uploadfile . "'
INTO TABLE
`rating_table`
FIELDS TERMINATED BY
'::'
LINES TERMINATED BY
'\n' ( `userID`, `movieID`, `rate`, `timestamp`)
");
$stmt->execute();
EDIT 6：
PDO占位符只用于值，而不是数据库，表或列名！我认为错误来自使用'::'。将文件中的数据分隔符从'::'更改为，'。'。
EDIT 6:
PDO placeholders can only be used for values, and not database, table or column names! I think the error is coming from using '::'. Change your data delimiter in your files from '::' to, let's say '.'.
作为快速解决方法，您可以直接使用您的查询，而无需准备：
As quick workaround for checking you could use your query directly without prepare:
$stmt = $dbh->query("
LOAD DATA INFILE
'" . $uploadfile . "'
INTO TABLE
`rating_table`
FIELDS TERMINATED BY
'::'
LINES TERMINATED BY
'\n' ( `userID`, `movieID`, `rate`, `timestamp`)
");
如果不起作用，请尝试更改数据分隔符或尝试转义现有的删除程序c> :: ）。
If it doesn't work try changing data delimiter or try escaping your existing delemiter (::) somehow.
这篇关于我的LOAD DATA INFILE语法有什么问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
