mysql REPLACE INTO only some fields

问题:

I have a mysql table with

CREATE TABLE `gfs` (
    `localidad` varchar(20),
    `fecha` datetime,
    `pp` float(8,4) NOT NULL default '0.0000',
    `temp` float(8,4) NOT NULL default '0.0000',
    PRIMARY KEY (`localidad`,`fecha`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

when I try update a field with this

REPLACE INTO gfs(localidad,fecha,pp) VALUES ("some_place","2012-08-05 02:00","1.6")

the previous value en temp is lost. why ?

回答1:

As documented under REPLACE Syntax and mentioned already by others:

REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL—that either inserts or updates—see Section 13.2.5.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.

The manual goes on to explain:

Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT. You cannot refer to values from the current row and use them in the new row.

Therefore, you want:

INSERT INTO gfs (localidad, fecha, pp)
VALUES ('some_place', '2012-08-05 02:00', 1.6)
ON DUPLICATE KEY UPDATE pp=VALUES(pp);

回答2:

Because it's a REPLACE statement not an UPDATE. When replacing you'll get the unspecified values as the default column value.

Updating allows you to change the previous value and also operate on that value without having selected it beforehand (SET count = count+1). It allows all previously set values to remain set. That's what you want to do.

回答3:

From http://dev.mysql.com/doc/refman/5.0/en/replace.html:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Are you trying to do an UPDATE instead?