mysql只替换成一些字段

问题描述：

我有一个mysql表，

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 ?

答

如 REPLACE语法，并已被其他人提及:

As documented under REPLACE Syntax and mentioned already by others:

REPLACE 是SQL标准的MySQL扩展.它可以插入，也可以删除并插入.有关插入或更新的标准SQL的另一个MySQL扩展，请参见第13.2节.5.3，"INSERT ... ON DUPLICATE KEY UPDATE语法" .

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".

手册继续说明:

所有列的值均取自REPLACE语句中指定的值.就像INSERT一样，所有缺少的列都将设置为其默认值.您不能引用当前行中的值，也不能在新行中使用它们.

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.

因此，您想要:

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

mysql只替换成一些字段

相关推荐