php - LOAD DATA INFILE - 但还要为每一行添加此列条目？

Question

下面是代码。

    $host = '';
    $username = '';
    $password = '';
    $database = '';
    $con = new mysqli($host,$username,$password,$database); 

    if ($con->connect_error) 
    {
        die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
    }

    $di = new RecursiveDirectoryIterator('/var/www/html/cdr');
    foreach (new RecursiveIteratorIterator($di) as $filename => $file) 
    {
        if ($filename == "/var/www/html/cdr/.")
        {

        } 
        else 
        {
            $con->query("LOAD DATA INFILE '" . $filename . "' 
                        INTO TABLE cdr 
                        FIELDS TERMINATED BY ',' 
                        ENCLOSED BY '\"'
                        LINES TERMINATED BY '\n'
                        IGNORE 1 LINES"
                        );
            echo $filename . " added to Database and deleted";
            echo "<br />";
            unlink($filename);
        }
    }

当前代码按预期工作。它遍历 csv 文件目录并将每个文件导入数据库。

我想做的是在数据库中添加一个名为“文件名”的额外列，其中包含每条记录的来源文件名。

有没有办法修改 MySQL 查询以在每行的末尾添加指定的列数据，$filename 是数据？

谢谢。

更新

以下是更新后的代码，现在正在执行所需的操作。

    $di = new RecursiveDirectoryIterator('/var/www/html/cdr');
    foreach (new RecursiveIteratorIterator($di) as $filename => $file) 
    {
        $fileName = $filename;
        if ($fileName == "/var/www/html/cdr/.")
        {

        } else {
            $con->query("LOAD DATA INFILE '" . $fileName . "' 
                        INTO TABLE cdr 
                        FIELDS TERMINATED BY ',' 
                        ENCLOSED BY '\"'
                        LINES TERMINATED BY '\n'
                        IGNORE 1 LINES
                        SET filename = '" . $fileName . "'"
                        );
            echo $fileName . " added to Database and deleted";
            echo "<br />";
            unlink($fileName);
        }
    }

就像在查询末尾添加 SET columnName = columnEntry 一样简单。

Answer 1

你可以试试这个:

$sql = "LOAD DATA INFILE '" . $filename . "' 
                    INTO TABLE cdr 
                    FIELDS TERMINATED BY ',' 
                    ENCLOSED BY '\"'
                    LINES TERMINATED BY '\n'
                    IGNORE 1 LINES 
                    (`col2`,`col3`, `colX`)
                    SET filenameColumnName='{$filename}'";

甚至没有列列表:

$sql = "LOAD DATA INFILE '" . $filename . "' 
                    INTO TABLE cdr 
                    FIELDS TERMINATED BY ',' 
                    ENCLOSED BY '\"'
                    LINES TERMINATED BY '\n'
                    IGNORE 1 LINES
                    SET filenameColumnName='{$filename}'";

MySQL 文档中有这样的示例:

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

注意:您应该在查询中使用数据绑定(bind)，而不是直接包含 $filename