php - 截断表中除第一个列之外的所有 MySQL 列

Question

嘿伙计们，你们可以看到我有这个 PHP 脚本:

    <?php
    $databasehost = "localhost"; 
    $databasename = ""; 
    $databasetable = ""; 
    $databaseusername=""; 
    $databasepassword = ""; 
    $fieldseparator = ","; 
    $lineseparator = "\n";
    $enclosedbyquote = '"';
    $csvfile = "file.csv";

    if(!file_exists($csvfile)) {
        die("File not found. Make sure you specified the correct path.");
    }

    try {
        $pdo = new PDO("mysql:host=$databasehost;dbname=$databasename", 
            $databaseusername, $databasepassword,
            array(
                PDO::MYSQL_ATTR_LOCAL_INFILE => true,
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            )
        );
    } catch (PDOException $e) {
        die("database connection failed: ".$e->getMessage());
    }

    $pdo->exec("TRUNCATE TABLE `$databasetable`");

        $affectedRows = $pdo->exec("
        LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." REPLACE INTO TABLE `$databasetable`
        FIELDS OPTIONALLY ENCLOSED BY ".$pdo->quote($enclosedbyquote)."
         TERMINATED BY ".$pdo->quote($fieldseparator)." 
        LINES TERMINATED BY ".$pdo->quote($lineseparator)." 
        IGNORE 1 LINES");

    echo "Loaded a total of $affectedRows records from this csv file.\n";

    ?>

现在您可以看到，这是将 csv 文件导入到我的数据库中并截断整个 SQL 表。

我的问题是如何阻止表的第一列受到导入的影响？我将使用第一列作为 ID，知道如何做到这一点吗？

谢谢

Answer 1

简单。而不是:

TRUNCATE TABLE `$databasetable`

用途:

UPDATE `$databasetable` SET colB = '', colC = '', colD = '', colE = '';

其中 colA 是您要保留的列。如果您使用 NULL 字段，请更改为:

    UPDATE `$databasetable` SET colB = NULL; #etc.