MySQL: Do not insert a new row if defined rows are same

Question

I have a table which I get sampling values: AeroSamples

id    time    temperature    pressure    humidity

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.

I do this like that:

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)");
    ...
}

How can I do this SQL only?

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.

maybe this link? http://stackoverflow.com/questions/16460397/sql-insert-into-table-only-if-record-doesnt-exist — Goikiu, Dec 27 '13 at 15:27
@DigitalChris If at least one field is different it should be recorded. If the 3 fields' all are same will not be recorded. Changing ORs to ANDs will not help me if only one field is different. — zkanoca, Dec 27 '13 at 15:29
Also, this question gives me the willies. Unchanged data is entirely different from missing data. In analyzing the data you want to differentiate between 2 hours of unchanged weather and 2 hours of server downtime. — Digital Chris, Dec 27 '13 at 15:29
@DigitalChris I do not care about server downtime matters. Just want to know if this check could be done using MySQL only. — zkanoca, Dec 27 '13 at 15:32

score 4 · Accepted Answer · answered Dec 27 '13 at 15:34

4

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

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;

answered Dec 27 '13 at 15:34

Gordon Linoff

1,242,037
58
646
786

Nice answer. I'd like to do this too but in my case I do a multirow insert with thousands of rows. Would there be any way of making it do this check for each row? – Clox Mar 06 '15 at 09:13
@clox . . . You should ask another question with details about what you want to happen. But, yes, I think this can be adapted. – Gordon Linoff Mar 06 '15 at 16:10
Yeah, I did that too =) http://stackoverflow.com/questions/28898733/insert-multiple-records-only-if-they-differ-from-the-last-ones – Clox Mar 07 '15 at 11:58

score 0 · Answer 2 · answered Dec 27 '13 at 15:37

0

In order to use ON DUPLICATE you will need to add a unique index to your table.

create unique index aerosamples_ux1 on AeroSamples(temperature, pressure, humidity);

than you can use ON DUPLICATE KEY UPDATE or ON DUPLICATE KEY IGNORE inside your queries... also keep in mind if you dont use ON DUPLICATE you query will give you an error and won't add a duplicate record after adding this index.

answered Dec 27 '13 at 15:37

Madcoe

213
1
6

There should be no duplicating combinations of temparature, pressure and humidity *in two neighbouring rows*, not the entire table. – lafor Dec 27 '13 at 15:41
Yo are right in this case it may not be useful unless you add another field to database and index. I just wanted to clarify use case of ON DUPLICATE because it was a part in original question. – Madcoe Dec 27 '13 at 15:45

MySQL: Do not insert a new row if defined rows are same

2 Answers2

Linked