MySQL:如果定义的行相同，则不要插入新行 [英] MySQL: Do not insert a new row if defined rows are same

问题描述

我有一个可以获取采样值的表:AeroSamples

I have a table which I get sampling values: AeroSamples

id    time    temperature    pressure    humidity

我每隔5分钟取样一次.在将新行插入表中之前，我检查最后一行的温度，压力和湿度值是否与当前值相同.如果是这样，我不想添加新行.否则，可以添加新记录.

I sample the values at a 5 minute period. Before inserting a new row into the table, I check if the last row's temperature, pressure and humidity values are same with current values. If so, I do not want to add a new row. Else A new record could be added.

我这样做是这样的:

SELECT temperature, pressure, humidity FROM AeroSamples ORDER BY id DESC LIMIT 1

当我获得最后一个值时，我将三个字段与当前值进行比较，这是我不喜欢的方式:

When I get the last values, I compare three fields with current values which is the way I do not like:

if($row["temperature"] !== $curTemp || $row["pressure"] !== $curPres || $row["humidity"] !== $curHumi)
{
    $db->prepare("INSERT INTO AeroSamples (temperature, pressure, humidity) VALUES(:t,:p,:h)");
    ...
}

如何仅执行此SQL?

How can I do this SQL only?

ON DUPLICATE KEY UPDATE ...对我有帮助吗?我不这么认为.因为我不确定一次是否对多个字段都有效.

Does ON DUPLICATE KEY UPDATE ... help me? I do not think so. Because I am not sure if it is valid for multiple fields at a time.

推荐答案

先前的值不相同，因为时间不同. las.

The previous values will not be the same, because the time is different. Alas.

您可以使用insert . . . select语法执行此操作.这个想法是选择最后插入的行，并使用where子句来过滤行.当值相同时，过滤器将不返回任何行(因此也不会插入):

You can do this using the insert . . . select syntax. The idea is to select the last row inserted and use a where clause to filter the rows. The filter will return no rows (and hence no insert) when the values are the same:

insert into AeroSamples(temperature, pressure, humidity) 
    select :t, :p, :h
    from (select temperature, pressure, humidity
          from AeroSamples
          order by id desc
          limit 1
         ) as1
    where as1.temperature <> :t or as1.pressure <> :p or as1.humidity <> :h;

这篇关于MySQL:如果定义的行相同，则不要插入新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！