MySQL -为什么加载数据信息要替换我没有用NULL指定的列中的数据？

Question

我试图获取TSV文件和“POST”输入，并将TSV文件的内容加载到DB表中，将任何现有数据替换为指定的列。TSV可能包含任意数量的列和行，第一行指定应该修改的列。

我的问题涉及在运行代码生成的LOAD DATA INFILE ... REPLACE INTO TABLE ... MySQL语句时不应该修改的列中的数据。当我运行我的代码(见下文)时，没有在$columnsText中指定的列的数据(这是从TSV文件的第一行生成的)最终会被设置为NULL或其默认值。另一方面，在$columnsText中指定的列的数据会按预期替换它们的内容。

由我的代码生成并正在运行的MySQL语句的一个例子是：LOAD DATA INFILE 'C:\\MyProject\\public\\1459772537-cities7.tsv' REPLACE INTO TABLE cities FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY ' ' IGNORE 1 LINES (id,UNLOCODE,name_english,UN_subdiv) --对于所有TSV文件中提到的行，这个语句将正确地更新所提到的列(id、UNLOCODE、name_english、UN_subdiv)，但是行中所有未提及的列都将被设置为NULL！

如何修改此代码以避免将未指定列的数据设置为其默认值/空值？或者更简单地说，要解决问题的根源，如何修复生成的MySQL语句以实现我的目标？

我用PHP和Laravel。

// Get file, put it in a folder on the server.
    if (Input::hasFile('file')) {
        echo "POST has file <br>";
        $file = Input::file('file');
        $name = time() . '-' . $file->getClientOriginalName();
        $path = public_path();
        $file->move($path, $name);
        $pathName= $path .'\\'.$name;
        echo "location: ".$pathName."<br>";

        // Determine whether to use IGNORE OR REPLACE in MySQL query.
        if (isset($_POST['replace']) && $_POST['replace'] == true){
            $ignoreOrReplace = "REPLACE";
        }
        else {$ignoreOrReplace = "IGNORE";}
        echo "ignore or replace: ".$ignoreOrReplace."<br>";

        // Determine columns to insert in DB, based on values of input file's 1st row.
        $columnsText = "";
        if (($handle = fopen("$pathName", "r")) !== FALSE) { //"r" parameter = read-only, w file-pointer at start of file.
            $columns = fgetcsv($handle,0,"\t"); // makes an array of the column names that are in the 1st row of TSV file.
            $firstIteration = true;
            foreach ($columns as $column){
                if ($firstIteration){$firstIteration=false;}
                else {$columnsText .= ",";}
                $columnsText .= $column;
            }
            echo "DB columns to load: ".$columnsText;
            fclose($handle);
        }

        $query = sprintf(
            "LOAD DATA INFILE '%s' %s INTO TABLE %s
        FIELDS TERMINATED BY '\t'
        OPTIONALLY ENCLOSED BY '\"'
        ESCAPED BY '\"'
        LINES TERMINATED BY '\n'
        IGNORE 1 LINES (%s)",
            addslashes($pathName),$ignoreOrReplace,$_POST['mytable'],$columnsText
        );
        echo "<br>Here's the query: ".$query."<br>";

        echo "<br><br> Database update should be complete!<br><br>";
        echo '<a href="/">Return to Home Page</a><br>';
        DB::connection()->getpdo()->exec("SET sql_mode ='';"); // I forgot what this does.
        return DB::connection()->getpdo()->exec($query);

Answer 1

文档指出：

如果指定REPLACE，则输入行替换现有行。换句话说，对于主键或唯一索引具有与现有行相同值的行。见第13.2.8节，“替换语法”。

REPLACE不是UPDATE。REPLACE是MySQL的扩展，首先删除行(如果存在)，然后插入新的行()。

在INSERT上，MySQL对查询中未提供的字段使用默认值。在您的示例中，这些字段可能默认为NULL。

无法使用LOAD DATA INFILE更新现有行。

我建议您创建一个工作表，并仅用于将数据加载到其中，如下所示：

1. 在使用之前先使用TRUNCATE。
2. LOAD DATA INFILE在里面。
3. 将它与要更新的表连接起来，并在Join上使用UPDATE将您需要的字段从工作表复制到最后一个表。
4. 使用INSERT ... SELECT从联接中获取未位于最终表中的行，并插入它们。
5. TRUNCATE it。

使用表后不要删除它，下次您将再次需要它。最后一步的目标是将其磁盘使用量保持在最低限度；表定义占用的空间不大。

Answer 2

axiac提供的答案是广义的正确答案。

如果它对任何人有用，我已经在下面包含了解决我的问题的特定代码(PHP/Laravel/MySQL)。我不能说这是解决这个问题的最有效的方法，但它有效！)

    // (1) setup
    DB::connection()->disableQueryLog();

    // (2) Get file, put it in a folder on the server.
    if (Input::hasFile('file')) {
        $file = Input::file('file');
    }
    else {
        echo "<br>Input file not found! Please review inputed information.<br>";
        return null;
    }
    $name = time() . '-' . $file->getClientOriginalName();
    $path = public_path();
    $file->move($path, $name);
    $pathName= $path .'\\'.$name;
    echo "Input file location: ".$pathName."<br>";

    // (3) Determine main table and staging table.
    $mainTable = $_POST['mytable'];
    $stagingTable = $_POST['mytable'].'_staging'; // All staging tables are named: 'standardtable_staging'.

    // (4) Determine destination DB table's columns and columns to be inserted into that table (based on values of input file's 1st row).
    $columnsMain = Schema::getColumnListing($mainTable);
    $columnsInput = [];
    $columnsInputText = "";
    if (($handle = fopen("$pathName", "r")) !== FALSE) { //"r" parameter = read-only, w file-pointer at start of file.
        $columnsInput = fgetcsv($handle,0,"\t"); // makes an array of the column names that are in the 1st row of TSV file.
        $firstIteration = true;
        foreach ($columnsInput as $columnInput){
            if ($firstIteration){$firstIteration=false;}
            else {$columnsInputText .= ",";}
            $columnsInputText .= $columnInput;
        }
        echo "<br>DB columns to load: ".$columnsInputText."<br>";
        fclose($handle);
    }

    // (5) Create a new empty staging table.
    $statement = "DROP TABLE IF EXISTS ".$stagingTable; // we drop rather than truncate b/c we want to re-determine columns.
    DB::connection()->getpdo()->exec($statement);
    $statement = "CREATE TABLE ".$stagingTable." LIKE ".$mainTable;
    DB::connection()->getpdo()->exec($statement);

    // (6) The staging table only needs to have columns that exist in the TSV file, so let's minimize its columns.
    $columnsToDrop = [];
    foreach ($columnsMain as $columnMain){
        if (! in_array($columnMain,$columnsInput)){
            array_push($columnsToDrop,$columnMain);
        }
    }
    if (count($columnsToDrop) > 0){
        Schema::table($stagingTable, function($t) use ($columnsToDrop) {$t->dropColumn($columnsToDrop);});
    }

    // (7) Load data to the staging table.
    $statement = sprintf(
        "LOAD DATA INFILE '%s' INTO TABLE %s
        FIELDS TERMINATED BY '\t'
        OPTIONALLY ENCLOSED BY '\"'
        ESCAPED BY '\"'
        LINES TERMINATED BY '\n'
        IGNORE 1 LINES (%s)",
        addslashes($pathName),$stagingTable,$columnsInputText
    );
    echo "<br>Here's the MySQL staging statement: <br>".$statement."<br>";

    DB::connection()->getpdo()->exec("SET sql_mode ='';"); // don't actually recall why I put this here.
    DB::connection()->getpdo()->exec($statement);

    // (8) 'INSERT...ON DUPLICATE KEY UPDATE' is used here to get data from staging table to the actually-used table.
    // Note: Any new columns in the staging table MUST already be defined in the main table.
    $statement = sprintf("INSERT INTO %s (%s) SELECT * FROM %s ON DUPLICATE KEY UPDATE ", $mainTable,$columnsInputText,$stagingTable);
    $firstClause = true;
    foreach ($columnsInput as $columnInput) {
        if (strtoupper($columnInput) != "ID"){
            if ($firstClause){$firstClause=false;}
            else {$statement .= ", ";}
            $clause = $mainTable.".".$columnInput." = IF (".$stagingTable.".".$columnInput." <=> NULL,".
                $mainTable.".".$columnInput.",".
                $stagingTable.".".$columnInput.")";
            $statement .= $clause;
        }
    }
    echo "<br>Here's the staging-to-actual-table statement:<br>".$statement."<br>";
    DB::connection()->getpdo()->exec($statement);
    echo "<br>New information added to database!<br>";