doushitang4276
2014-02-24 14:33
浏览 118
已采纳

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

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

图片转代码服务由CSDN问答提供 功能建议

我正在运行一台用于本地开发的Windows机器,并尝试将大型csv文件加载到mysql中。 我的代码如下所示:

  $ sql_query ='LOAD DATA LOCAL 
 INFILE“'。$ tempLoc。'”
 INTO TABLE users 
 FIELDS 
 TERMINATED BY  “
 
 
”
“
 
”
“
 
 
 
 
 
 
 
 \ LINES 
终止于”\
“
(
 id,
 name,
 value 
)'; 
 
 $ statement = $ this  - > adapter-> query($ sql_query); 
 $ resultSet = $ statement-> execute(); 
 $ this-> adapter-> getDriver() - > getConnection() - >  commit(); 
   
 
 

当我加载文件时出现此错误: -

“PDOStatement :: execute() [pdostatement.execute]:禁止加载数据本地INFILE ...“

我已经搜索了答案,但我无法解决问题。 但是当我在本地m / c中连接数据库与本地数据库(即带有root的localhost)时,它运行良好

  • 我的应用程序在本地机器中
  • DB连接到另一台服务器
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doukun0888 2014-02-26 04:49
    已采纳
    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))

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • douao3636 2014-02-24 14:40

    When you use LOAD DATA LOCAL, you're telling the MySQL server process to open a local file, on its own file system, and read it. Most MySQL operations use a TCP/IP connection between your client and the MySQL server to send and receive all data. But this operation is different.

    If your client code is running on one machine and the server is running on another, this won't work unless they are sharing a file system. Hint: if you're using one of those $5 per month hosting services, this probably won't work.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题