使用LOAD DATA LOCAL INFILE，Transaction和Commit将记录从文件插入数据库到数据库时，如何获取行号? [英] How to get Row Number when insert records from File to DB with LOAD DATA LOCAL INFILE, Transaction and Commit?

问题描述

在使用LOAD DATA LOCAL INFILE，Transaction和Commit将文件中的记录从文件插入到DB中时，如何获取行号?

How to get Row Number when insert records from File to DB with LOAD DATA LOCAL INFILE, Transaction and Commit?

但是，在存在var转储的情况下，只能获取true或false(失败)

However, only getting true or false(in fail) with the var dump present

声明:

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE tablename 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY ' ' ( `Col1`, `Col2`, `Col3`)

脚本:

public function PDO_UL_IUPD($dbUsing, $stmtpre) {
        $started = microtime(true);
        $DB      = [];
        $val     = [];
        $conn    = new PDO(
            "mysql:host=" . DB_HOST . ";dbname=" . DB_PRE . "" . $dbUsing . "",
            DB_USERNAME,
            DB_PASS,
            array(
                PDO::MYSQL_ATTR_LOCAL_INFILE       => TRUE,
                PDO::MYSQL_ATTR_INIT_COMMAND       => "SET NAMES UTF8",
                PDO::ATTR_EMULATE_PREPARES         => FALSE,
                PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,
                PDO::ATTR_ERRMODE                  => PDO::ERRMODE_EXCEPTION,
            ));
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        try {
            $conn->beginTransaction();
            $conn->exec("set names utf8");
            $count  = 0;
            $RowReg = 0;
            // our SQL statements
            foreach ($stmtpre as $stmt) {
                $count++;
                $conn->exec($stmt);
            }
            $RowReg = $conn->commit();
            echo var_dump($RowReg);
            $DB['SMG'] = "Correct Execution, a csv file has been dumped.<br><br>";
            $DB['R']   = true;
        } catch (PDOException $e) {
            $DB['SMG'] = "Error: " . $e->getMessage();
            $DB['R']   = false;
        }
        return $DB;
    }

Update1脚本:

尝试从EXEC获取行号返回0

Trying to get Row Number from EXEC return 0

脚本:

public function PDO_UL_IUPD($dbUsing, $stmtpre) {
        $started = microtime(true);
        $DB      = [];
        $val     = [];
        $conn    = new PDO(
            "mysql:host=" . DB_HOST . ";dbname=" . DB_PRE . "" . $dbUsing . "",
            DB_USERNAME,
            DB_PASS,
            array(
                PDO::MYSQL_ATTR_LOCAL_INFILE       => TRUE,
                PDO::MYSQL_ATTR_INIT_COMMAND       => "SET NAMES UTF8",
                PDO::ATTR_EMULATE_PREPARES         => FALSE,
                PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,
                PDO::ATTR_ERRMODE                  => PDO::ERRMODE_EXCEPTION,
            ));
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $conn->beginTransaction();
        try {
            $conn->exec("set names utf8");
            $count  = 0;
            $RowReg=0;
            $cReg=0;
            // our SQL statements
            foreach ($stmtpre as $stmt) {
                //echo $stmt."<br><br><br>";
                $count++;
                $cReg=$conn->exec($stmt);
                $RowReg=$RowReg+$cReg;
            }
            $conn->commit();
            echo var_dump($RowReg);
            $DB['SMG'] = "Correct Execution, a csv file has been dumped.<br><br><br>";
            $DB['R']   = true;
        } catch (PDOException $e) {
            $DB['SMG'] = "Error en Tiempo de Ejecucion: " . $e->getMessage();
            $DB['R']   = false;
        }
        return $DB;
    }

更新2条语句:

添加此返回1

SET @row=0;
LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE tablename 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY ' ' ( `Col1`, `Col2`, `Col3`)
SET file_line_no = @row:=@row+1;

推荐答案

解决了:

此脚本已被完全支持:

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE tablename 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY ' ' ( `Col1`, `Col2`, `Col3`)

但是我将其与其他查询合并，例如:

But i merge it with other in some Query Like that:

DELETE FROM tablename WHERE date BETWEEN '$Date1' AND '$Date2';
ALTER TABLE tbalename AUTO_INCREMENT = 1;
LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE tablename 
    FIELDS TERMINATED BY ',' 
    LINES TERMINATED BY ' ' ( `Col1`, `Col2`, `Col3`);

这是错误的，因为$PDO->exec()无法返回受影响的表或行的数量.

this is wrong, becouse $PDO->exec() Cant return the number of affected table or rows.

代替它，要解决我需要经过一个数组的问题，因为我的脚本像这样支持它:

Instead of it, to solve i need past an array, becouse my script support it like that:

$stmtpre[1] = "DELETE FROM tablename WHERE date BETWEEN '$Date1' AND '$Date2';";
$stmtpre[2] = "ALTER TABLE tbalename AUTO_INCREMENT = 1;";
$stmtpre[3] = "LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE tablename 
        FIELDS TERMINATED BY ',' 
        LINES TERMINATED BY ' ' ( `Col1`, `Col2`, `Col3`);";

这篇关于使用LOAD DATA LOCAL INFILE，Transaction和Commit将记录从文件插入数据库到数据库时，如何获取行号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！