mysql只替换成一些字段 [英] mysql REPLACE INTO only some fields

问题描述

我有一个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只替换成一些字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！