LOAD DATA INFILE——但也为每一行添加这个列条目 - LOAD DATA INFILE - but also add this column entry for every row?

LOAD DATA INFILE - but also add this column entry for every row?

本文关键字：添加一行 INFILE DATA LOAD | 更新日期: 2023-09-27

代码如下:

    $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一样简单。

你可以试试:

$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

注意:你可能应该在查询中使用数据绑定，而不是直接包含$filename