使用“LOAD DATA”命令将CSV文件导入mysql表

Question

I am running a windows machine for local development and attempting to load large csv files into mysql. My code looks like this:

$sql_query = 'LOAD DATA LOCAL 
    INFILE "' . $tempLoc . '"
    INTO TABLE users
    FIELDS
    TERMINATED BY " "
    ENCLOSED BY "\'"
    LINES
    TERMINATED BY "\
"                                
    (
     id,
     name,
     value 
    )';

$statement = $this->adapter->query($sql_query);
$resultSet = $statement->execute();
$this->adapter->getDriver()->getConnection()->commit();

When I load the file I got this error:-

"PDOStatement::execute() [pdostatement.execute]: LOAD DATA LOCAL INFILE forbidden in..."

I have searched for an answer, but I have not been able to resolve the issue. But when i connect the db with local db in my local m/c(ie, localhost with root) it runs well

• my application is in local machine
• DB is connected to another server

Answer 1

1. Make sure the server allows LOAD DATA LOCAL INFILE

   [server]local-infile=1

2. Make sure that your MySQL user has 'FILE' privilege

3. Set the PDO attribute in your PHP script,

   $pdo = new PDO($dsn, $username, $password, array(PDO::MYSQL_ATTR_LOCAL_INFILE=>1))