问题描述
我有一个可以获取采样值的表: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:如果定义的行相同,则不要插入新行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!