mysql REPLACE INTO只有一些字段

I have a mysql table with

我有一个mysql表

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 ?

先前的值en temp将丢失。为什么?

3 个解决方案

#1

As documented under REPLACE Syntax and mentioned already by others:

正如REPLACE语法中所述,并已被其他人提及:

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是SQL标准的MySQL扩展。它插入,删除和插入。对于标准SQL的另一个MySQL扩展 - 插入或更新 - 请参见第13.2.5.3节“INSERT ... ON DUPLICATE KEY UPDATE语法”。

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.

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

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.

因为它是REPLACE语句而不是UPDATE。替换时,您将获取未指定的值作为默认列值。

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.

更新允许您更改之前的值,并且无需事先选择该值(SET count = count + 1)。它允许所有先前设置的值保持设置。这就是你想要做的。

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

REPLACE与INSERT的工作方式完全相同,只是如果表中的旧行与PRIMARY KEY或UNIQUE索引的新行具有相同的值,则在插入新行之前删除旧行。

Are you trying to do an UPDATE instead?

你想尝试更新吗?

#1