如何在laravel 5.4中加载DATA LOCAL INFILE [英] how to do LOAD DATA LOCAL INFILE in laravel 5.4

问题描述

我需要在laravel 5.4中转换以下查询

i need to convert the following query in laravel 5.4

$loadDataToTempTableSql = "LOAD DATA LOCAL INFILE '".$filename."' INTO TABLE ABC FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r' IGNORE 1 LINES";
                    $loadDataToTempTableRes = mysqli_query($link,$loadDataToTempTableSql);

                    $loadedData = mysqli_affected_rows($link);

我做了

第一步:

DB::select($load_data_to_temp_table_sql);

这将引发异常:

SQLSTATE [HY000]:常规错误:2014无法在执行查询的同时 其他未缓冲的查询处于活动状态.考虑使用 PDOStatement :: fetchAll().或者，如果您的代码只是 要针对mysql运行，您可以通过设置启用查询缓冲 PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY属性. (SQL:LOAD DATA 当地资料 '/Library/WebServer/Documents/public/abc copy.csv'到以'，'终止的表ABC字段中 可选用"终止的"行 '忽略1行)

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. (SQL: LOAD DATA LOCAL INFILE '/Library/WebServer/Documents/public/abc copy.csv' INTO TABLE ABC FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ' ' IGNORE 1 LINES)

第二步:

$pdo = DB::connection()->getPdo();
$pdo->exec($load_data_to_temp_table_sql);

再次例外:

PDO :: exec():禁止加载数据本地文件

PDO::exec(): LOAD DATA LOCAL INFILE forbidden

请指导，怎么办?

推荐答案

我也遇到过类似的问题，在使用谷歌搜索后，现在已经解决了.您应该将以下内容添加到您的mysql/my.cnf文件中:

I have faced same kind of problems and after googling it has been solved now. You should add the following to your mysql/my.cnf file:

[Server]
local_infile=true

之后，将'options' => array(PDO::MYSQL_ATTR_LOCAL_INFILE => true)添加到项目的config/database.php文件中.

After that add 'options' => array(PDO::MYSQL_ATTR_LOCAL_INFILE => true) into your project's config/database.php file.

现在从cmd/terminal重新启动MySQL.运行LOAD DATA LOCAL INFILE会很好.如果仍然遇到问题，可以访问 https://tenerant.com/blog/using-load-data-local-infile-in-a-laravel-migration/.

Now restart your MySQL from cmd/terminal. It will be fine to run the LOAD DATA LOCAL INFILE. If you still face a problem, you can visit https://tenerant.com/blog/using-load-data-local-infile-in-a-laravel-migration/.

这篇关于如何在laravel 5.4中加载DATA LOCAL INFILE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！