截断MySQL表但排除第一列

Question

I'm a little confused to how I can do this.

I am basically wanting to give my first column a 'NOT NULL AUTO_INCREMENT' and give each row it's own 'id'. The issue I am having is that the script I am using truncates the whole SQL table with a CSV file that is cron'd daily to update data.

I am currently using this script:

<?php

$databasehost = "localhost";
$databasename = "";
$databasetable = "";
$databaseusername="";
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "
";
$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.
";

?>

Is it possible to amend this script to ignore my first column and truncate all of the data in the table apart from the first column?

I could then give all of the rows in the first column their own ID's any idea how I could do this?

I am still very nooby so please go easy on me :)

Answer 1

From the database's point of view, your question makes no sense: to truncate a table means to completely remove all rows from that table, and the bulk insert creates a whole load of new rows in its place. There is no notion in SQL of "deleting a column", or of "inserting columns into existing rows".

In order to add or overwrite data in existing rows, you need to update those rows. If you are bulk inserting data, that means you need to somehow line up each new row with an existing row. What happens if the number of rows changes? And if you are only keeping the ID of the row, what is it you are actually trying to line up? It's also worth pointing out that rows in a table don't really have an order, so if your thought is to match the rows "in order", you still need something to order by...

I think you need to step back and consider what problem you're actually trying to solve (look up "the X/Y problem" for more on getting stuck thinking about a particular approach rather than the real problem).

Some possibilities:

• You need to assign the new data IDs which reuse the same range of IDs as the old data, but with different content.
• You need to identify which imported rows are new, which updates, and which existing rows to delete, based on some matching criteria.
• You don't actually want to truncate the data at all, because it's referenced elsewhere so needs to be "soft deleted" (marked inactive) instead.