php - 通过php上传csv文件到MySQL数据库

Question

我正在制作一个网页，我的不懂技术的客户可以在其中上传包含 25,000 多行的 csv 文件。我找到了一种使用 fgetcsv() 的方法，并进行如下所示的查询:

INSERT INTO `usbcData` (first, last, usbc, year) values
("Joshua", "Doom", "1324-1324", "2015");
INSERT INTO `usbcData` (first, last, usbc, year) values
("Turd", "Fergason", "1324-1325", "2014");
INSERT INTO `usbcData` (first, last, usbc, year) values
("Bruce", "Wayne", "1324-1326", "2013");
<Insert 20,000 more INSERTS here.>

问题是这需要太长时间。 20-30分钟太长了。所以现在我正在尝试让 MySQL 的 LOAD DATA INFILE 工作。当我包含/home/joshua/file.csv 中的文件(我在笔记本电脑上运行 GNU/Linux)时，我可以使其与 phpmyadmin 一起工作，但不能与我的 php 代码一起工作。我尝试过使用带或不带 local 关键字、pdo 扩展、mysqli 扩展和系统调用的查询，但都不起作用。

我遵循了这个指南:Error 1148 MySQL The used command is not allowed with this MySQL version设置我的 my.cnf 以允许 local 关键字工作，并配置我的 pdo 连接以允许使用 local 关键字。

[mysqld]
local-infile 

[mysql]
local-infile 

这是 php 脚本，用于发送用户上传的 csv 文件。它包含几种尝试使用 MySQL 的 LOAD DATA INFILE 工作的方法:

<?php
ini_set('display_errors', 'On');
error_reporting(E_ALL | E_STRICT);

echo "The file name is ".$_FILES['userfile']['name']."<br>";
echo "The file type is ".$_FILES['userfile']['type']."<br>";
echo "The size of the file is ".$_FILES['userfile']['size']." bytes <br>";
echo "The file is stored on the server as ".$_FILES['userfile']['tmp_name']."<br>";

$file_name = $_FILES['userfile']['name'];
$file_size = $_FILES['userfile']['size'];
$file_tmp = $_FILES['userfile']['tmp_name'];
$file_type = $_FILES['userfile']['type'];

$user = "root";
$pass = "passord";

try {
  $dbh = new PDO('mysql:host=localhost;dbname=usbc', $user, $pass,
               array(PDO::MYSQL_ATTR_LOCAL_INFILE => true,
                     PDO::ATTR_PERSISTENT => true)
);
} catch (Exception $e) {
  die("Unable to connect: " . $e->getMessage());
}


$dbh->query("TRUNCATE TABLE `usbcData`");

if (chmod($file_tmp, 0777)) {
    echo "chmod worked on $file_tmp <br>";
} else {
    echo "chmod didn't work on $file_tmp <br>";
}
$sql = 'load data infile "'.$file_tmp.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$dbh->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";

$sql = 'load data infile "'.$file_name.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$dbh->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";

$sql = 'load data local infile "'.$file_tmp.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$dbh->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";

$sql = 'load data local infile "'.$file_name.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$dbh->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";

$dbh = null;

$mysqli = new mysqli("localhost", "root", "password", "usbc");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$sql = 'load data infile "'.$file_tmp.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$mysqli->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";

$sql = 'load data infile "'.$file_name.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$mysqli->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";

$sql = 'load data local infile "'.$file_tmp.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$mysqli->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";

$sql = 'load data local infile "'.$file_name.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$mysqli->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";




$dbUser = "root";
$dbHost = "localhost";
$dbPass = "password";
$dbName = "usbc";

$sql = "LOAD DATA INFILE '$file_tmp' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';";
echo $sql."<br>";
system("mysql -u $dbUser -h $dbHost --password=$dbPass --local_infile=1 -e \"$sql\" $dbName");

$sql = "LOAD DATA INFILE '$file_name' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';";
echo $sql."<br>";
system("mysql -u $dbUser -h $dbHost --password=$dbPass --local_infile=1 -e \"$sql\" $dbName");

$sql = "LOAD DATA local INFILE '$file_tmp' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';";
echo $sql."<br>";
system("mysql -u $dbUser -h $dbHost --password=$dbPass --local_infile=1 -e \"$sql\" $dbName");

$sql = "LOAD DATA local INFILE '$file_name' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';";
echo $sql."<br>";
system("mysql -u $dbUser -h $dbHost --password=$dbPass --local_infile=1 -e \"$sql\" $dbName");

?>

输出如下所示:

The file name is MonthlyCSVDataFileforIndianaYouth.csv
The file type is text/csv
The size of the file is 759494 bytes 
The file is stored on the server as /tmp/phpT9cyBK
/tmp/phpT9cyBK is writable
chmod worked on /tmp/phpT9cyBK 
load data infile "/tmp/phpT9cyBK" into table `usbcData` fields terminated by "," lines terminated by "\n" (first, last, usbc, year) 
load data infile "MonthlyCSVDataFileforIndianaYouth.csv" into table  `usbcData` fields terminated by "," lines terminated by "\n" (first, last,  usbc, year) 
load data local infile "/tmp/phpT9cyBK" into table `usbcData` fields terminated by "," lines terminated by "\n" (first, last, usbc, year) 
load data local infile "MonthlyCSVDataFileforIndianaYouth.csv" into table `usbcData` fields terminated by "," lines terminated by "\n" (first, last, usbc, year) 
load data infile "/tmp/phpT9cyBK" into table `usbcData` fields terminated by "," lines terminated by "\n" (first, last, usbc, year) 
load data infile "MonthlyCSVDataFileforIndianaYouth.csv" into table `usbcData` fields terminated by "," lines terminated by "\n" (first, last, usbc, year) 
load data local infile "/tmp/phpT9cyBK" into table `usbcData` fields terminated by "," lines terminated by "\n" (first, last, usbc, year) 
load data local infile "MonthlyCSVDataFileforIndianaYouth.csv" into table `usbcData` fields terminated by "," lines terminated by "\n" (first, last, usbc, year) 
LOAD DATA INFILE '/tmp/phpT9cyBK' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';
LOAD DATA INFILE 'MonthlyCSVDataFileforIndianaYouth.csv' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';
LOAD DATA local INFILE '/tmp/phpT9cyBK' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';
LOAD DATA local INFILE 'MonthlyCSVDataFileforIndianaYouth.csv' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';

任何帮助都会很好。 :)

Answer 1

如果您尝试通过脚本语言(例如 PHP)发送文件，则不需要

LOCAL 关键字。尝试在没有 LOCAL 的情况下运行查询。

如果这不起作用，请尝试将文件复制到某个位置并在LOAD DATA INFILE中使用新路径，最后删除该文件。