Geek Answers Handbook

Update MySQL timestamp even if row data is not changed

I have a PHP script that imports sql scripts into a MySQL 5 database. Each script contains only statements UPDATE, each update line 1 in the table (MyISAM).

If the line was not inside one of these scenarios for 2 days, it must be deleted. The table has a timestamp column. However, when the UPDATE statement does not change any columns, the timestamp is not updated, and I cannot say that the line was in the import file or not. Is there a way to force update this timestamp, even if the data does not change?

EDIT: Further clarification.

Imported files are gzipped files containing about 450,000 lines, each line is 1 UPDATE statement.

Here's the PHP function that processes import files:

private function ImportFile($filename) {
    $importfile = gzopen($filename, "r");
    if (!$importfile) {
        throw new Exception("Could not open Gzip file " . $filename);
    }

    while (!gzeof($importfile)) {
        $line = gzgets($importfile, 4096);
        if (!$line) {
            throw new Exception("Error reading line number $line Gzip file $filename");
        }

        if (strlen(trim($line)) > 0) {
            $this->DB->Query($line);
        }
    }

    gzclose($importfile);
}
+5
source share
2 answers

You can simply update all fields where they are not updated, for example:

UPDATE mytable SET timestamp=NOW() WHERE id IN (1, 5, 6, ...);
+9
source

http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

Similar to using NOW () - if the TIMESTAMP column does not allow NULL values, you can force it to be updated by setting this column to NULL.

UPDATE mytable SET timestamp=NULL
+3
source

More articles:


All Articles