PDO和LOAD DATA LOCAL INFILE不起作用 [英] PDO and LOAD DATA LOCAL INFILE not working
问题描述
我刚刚尝试将LOAD DATA LOCL INFILE与pdo一起使用.没为我解决.这是我的功能
function connect($db_name,$db_host,$db_user,$db_pass)
{
try
{
$this->connect = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass);
$this->connect->exec("LOAD DATA LOCAL INFILE 'http://localhost/testoo.csv'
INTO TABLE 'parsed'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
('name','link','price','brand','imageurl')");
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
目前没有任何反应.相同的查询适用于普通的mysql_query.关于这个问题有什么建议吗?
2019编辑
7年后的萨米奇(Sammitch)在这里说我的原始答案是毛骨悚然.我什至无法弄清楚我所说的"fgetcsv()资源使用问题"到底是什么. 7年前，PHP可能缺少当今的某些IO流优化，但我愿意成为与PHP无关的资源限制.
Jay Dhameliya在下面的回答很可能是您想要的方式. LOAD DATA INFILE应该将数据尽可能快地直接爆发到mySQL中.
为了完整起见，假设存在 阻止使用LOAD DATA INFILE的功能(例如最近发现的巨大安全漏洞)，并且您想有效地从文件中加载数据，我可能想利用事务来批量处理IO和索引写入.例如:
$fname = 'myfile.csv';
if( ! $fh = fopen($myfile, 'r') ) {
throw new Exception("Could not open $fname for reading.");
}
$dbh = new PDO(...);
$dbh->beginTransaction();
$stmt = $dbh->prepare('INSERT INTO table VALUES (?,?,?,...)')
try {
while( $params = fgetcsv($fh) ) {
$stmt->execute($params);
}
} catch( \PDOException $e ) {
$dbh->rollBack();
throw $e;
}
$dbh->commit();
将所有批次批处理成一个事务仍然是LOAD DATA INFILE如此之快的部分原因，并且可能也是@Benjamin关于使用扩展插入的建议的很大一部分.
原始总答案
- 禁止在PHP中加载数据本地文件
- 确保mySQL和www用户都可以访问有问题的文件.
或者:使用 fgetcsv() 并以编程方式创建插入. /p>
为了避免fgetcsv()的资源使用问题(因为它试图一次读取整个文件)，您可以创建一个类似于下面的循环来读取/插入可管理的块.
<?php
$fname = 'myfile.csv';
$chunksize = 50;
if( ! $fh = fopen($myfile, 'r') ) {
throw new Exception("Could not open $fname for reading.");
}
$i=0;
$buffer = array()
while(!feof($fh)) {
$buffer[] = fgets($fh);
$i++;
if( ($i % $chunksize) == 0 ) {
commit_buffer($buffer);
//run inserts
$buffer = array(); //blank out the buffer.
}
}
//clean out remaining buffer entries.
if( count($buffer) ) { commit_buffer($buffer); }
function commit_buffer($buffer) {
foreach( $buffer as $line ) {
$fields = explode(',', $line);
//create inserts
}
//run inserts
$buffer = array(); //blank out the buffer.
}
通过这种方式，在任何给定时间仅将$chunksize行保存在内存中.
您可能需要其他代码来处理诸如包含逗号和换行符的封装字符串之类的事情，但是，如果您无法使LOAD DATA LOCAL INFILE工作，我不会看到其他选择.
I just tried to use LOAD DATA LOCL INFILE with pdo. Didn't worked out for me. Here my function
function connect($db_name,$db_host,$db_user,$db_pass)
{
try
{
$this->connect = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass);
$this->connect->exec("LOAD DATA LOCAL INFILE 'http://localhost/testoo.csv'
INTO TABLE 'parsed'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
('name','link','price','brand','imageurl')");
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
For now nothing happens. The same query works with normal mysql_query. Any pointers for this issue?
2019 Edit
7-years-later Sammitch here to say that my original answer is gross. I can't even figure out what the heck I was talking about with "fgetcsv() resource usage issues". It's possible that 7 years ago PHP was lacking some of the IO stream optimizations that is has today, but I'm willing to be that it was resource constraints unrelated to PHP.
Jay Dhameliya's answer below is most likely the way you want to go. LOAD DATA INFILE should blast the data directly into mySQL as fast as could be possible.
For the sake of completeness, assuming that there is something preventing the use of LOAD DATA INFILE [like the giant security hole recently uncovered] and you want to efficiently load data from a file, you'll likely want to leverage transactions to batch IO and index writes. Eg:
$fname = 'myfile.csv';
if( ! $fh = fopen($myfile, 'r') ) {
throw new Exception("Could not open $fname for reading.");
}
$dbh = new PDO(...);
$dbh->beginTransaction();
$stmt = $dbh->prepare('INSERT INTO table VALUES (?,?,?,...)')
try {
while( $params = fgetcsv($fh) ) {
$stmt->execute($params);
}
} catch( \PDOException $e ) {
$dbh->rollBack();
throw $e;
}
$dbh->commit();
Having everything batched into a single transaction is still part of the reason with LOAD DATA INFILE is so fast, as well as likely being a large part of @Benjamin's suggestion of using extended inserts.
Original Gross Answer
- LOAD DATA LOCAL INFILE forbidden in... PHP
- Make sure that both the mySQL and www users have access to the file in question.
Alternatively: Use fgetcsv() and create the inserts programmatically.
edit:
To avoid the resource usage issues with fgetcsv() [because it tries to read the whole file at once] you can create a loop similar to below to read/insert manageable chunks.
<?php
$fname = 'myfile.csv';
$chunksize = 50;
if( ! $fh = fopen($myfile, 'r') ) {
throw new Exception("Could not open $fname for reading.");
}
$i=0;
$buffer = array()
while(!feof($fh)) {
$buffer[] = fgets($fh);
$i++;
if( ($i % $chunksize) == 0 ) {
commit_buffer($buffer);
//run inserts
$buffer = array(); //blank out the buffer.
}
}
//clean out remaining buffer entries.
if( count($buffer) ) { commit_buffer($buffer); }
function commit_buffer($buffer) {
foreach( $buffer as $line ) {
$fields = explode(',', $line);
//create inserts
}
//run inserts
$buffer = array(); //blank out the buffer.
}
In this way only $chunksize lines are held in memory at any given time.
You'll likely need additional code to handle things like encapsulated strings containing commas and line breaks, but if you can't get LOAD DATA LOCAL INFILE working I don't see much other choice.
这篇关于PDO和LOAD DATA LOCAL INFILE不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
