PHP &MySQL:在截断的表上使用“NOT NULL AUTO_INCREMENT" [英] PHP & MySQL: Using a 'NOT NULL AUTO_INCREMENT' on a table that truncates

问题描述

我真的被困在这里我有这个 PHP 脚本:

I'm really stuck here I have this PHP script:

<?php


$databasehost = "localhost"; 
$databasename = ""; 
$databasetable = ""; 
$databaseusername=""; 
$databasepassword = ""; 
$fieldseparator = ","; 
$lineseparator = "\n";
$enclosedbyquote = '"';
$csvfile = "db-core/feed/csv/csv.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 文件截断我的表格.它替换当前 MySQL 表中的所有数据.

So as you can see that script is truncating my table with a CSV file. It replaces all the data currently in MySQL table.

此数据用于创建包含库存车辆的列表页面，每一行包含一辆车的数据.该脚本每天运行一次，以替换不再有库存的车辆.

This data is used to create a listing page that includes vehicles that are in stock, each row contains the data for one vehicle. This script is ran once a day to replace vehicles that are no longer in stock.

我现在想为我的每个 SQL 行分配自己的页面，有人告诉我我需要在id"键上使用NOT NULL AUTO_INCREMENT"属性.

I now want to give each of my SQL rows it's own page, I've been told I will need to use a 'NOT NULL AUTO_INCREMENT' attribute on an 'id' key.

然而，当我的表被截断时，每次运行我的脚本时 ID 不会被删除吗?

However seen as my table truncates wont the ID removed each time my script is ran?

我可以在我的脚本中添加任何东西来解决这个问题吗?

Could I add anything to my script to combat this problem?

谢谢

推荐答案

是的.截断一个表相当于从表中删除并核对所有记录.但是 auto_increment 不会再次重置为 0.最后使用的 auto_inc 值是表元数据的一部分，不受截断操作的影响.

Yes. Truncating a table is the equivalent of delete from table and nuking all the records. But the auto_increment won't reset to 0 again. The last used auto_inc value is part of the table's metadata and is not affected by a truncate operation.

如果您有记录 0->100，截断表格，然后添加新记录，它们将从 #101 开始并从那里爬升.

If you had records 0->100, truncated the table, then added new records, they would start at #101 and climb from there.

如果您希望在截断后重置 auto_increment 值，您必须这样做:

If you want the auto_increment value to be reset after the truncation, you'd have to do:

TRUNCATE TABLE foo; // delete all records
ALTER TABLE foo SET auto_increment=1; // reset auto-increment to 1
INSERT ...

所以是的...结果 truncate 确实重置了 auto_increment 值.绝对看起来是错误的，因为它显然在截断时也不会处理任何级联删除.重新填充在其他地方用作外键的截断表无疑会导致错误的记录连接.

So yep... turns out truncate does reset the auto_increment value. Definitely seems wrong, since it also apparently won't process any cascade deletes while truncating. Repopulating a truncated table that is used as a foreign key elsewhere will undoubtedly lead to incorrect record joinings.

这篇关于PHP &MySQL:在截断的表上使用“NOT NULL AUTO_INCREMENT"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！