仅当值与多插入的先前记录不同时才插入?

问题描述

我无法透露我存储的实际数据类型，但假设我有一个气象站及其更新的数据库.

I can't reveal the actual kind of data I'm storing but imagine I have a database of weatherstations and their updates.

更新表类似于以下内容:

The updates-table would be something like the following:

CREATE TABLE `updates` (
    `weatherStationId` INT NOT NULL,
    `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `temperature` DECIMAL(10,7) NULL,
    `humidity` DECIMAL(10,7) NULL,
    CONSTRAINT `FK__weatherstations` FOREIGN KEY (`weatherStationId`) REFERENCES `weatherstations` (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB

例如看起来像这样:

╔══════════════════╦═════════════════════╦═════════════╦══════════╗
║ weatherStationId ║        time         ║ temperature ║ humidity ║
╠══════════════════╬═════════════════════╬═════════════╬══════════╣
║               69 ║ 2015-03-06 00:04:56 ║           4 ║       29 ║
║             1337 ║ 2015-03-08 12:22:33 ║          29 ║       38 ║
╚══════════════════╩═════════════════════╩═════════════╩══════════╝

当有更新时，我想比较以前的新值(温度和湿度)，例如具有最新时间值的记录与我们获得更新的天气站 ID 相同.如果这些值不相同，或者该气象站没有存储更新，那么我只想插入具有新值的记录.否则我想将最新记录的时间更改为 now().

When there's an update, I'd like to compare the new values(temperature & humidity) of those of the previous ones, e.g. the record with the latest time-value among those of the same weatherstationId as the one we've got an update for. If those values are not the same, or there are no stored updates for that weatherstation then I simply want to insert the record with the new values. Otherwise I'd like to change the time of the latest record to now().

因此，如果在 2015-03-07 12:00:00 更新了 WeatherId 69、温度为 4、湿度为 30 的情况，上表将变为:

So with an update for weatherId 69 with a temperature of 4 and humidity of 30 at 2015-03-07 12:00:00 the table above would become:

╔══════════════════╦═════════════════════╦═════════════╦══════════╗
║ weatherStationId ║        time         ║ temperature ║ humidity ║
╠══════════════════╬═════════════════════╬═════════════╬══════════╣
║               69 ║ 2015-03-06 00:04:56 ║           4 ║       29 ║
║             1337 ║ 2015-03-08 12:22:33 ║          29 ║       38 ║
║               69 ║ 2015-03-09 12:00:00 ║           4 ║       30 ║
╚══════════════════╩═════════════════════╩═════════════╩══════════╝

(值与其余不同，因此插入新记录)

(Values differ from rest so insert new record)

除了湿度为 29 之外，使用相同的更新，表格现在应如下所示:

With the same update except with a humidity of 29 instead, the table should now look like this:

╔══════════════════╦═════════════════════╦═════════════╦══════════╗
║ weatherStationId ║        time         ║ temperature ║ humidity ║
╠══════════════════╬═════════════════════╬═════════════╬══════════╣
║               69 ║ 2015-03-09 12:00:00 ║           4 ║       29 ║
║             1337 ║ 2015-03-08 12:22:33 ║          29 ║       38 ║
╚══════════════════╩═════════════════════╩═════════════╩══════════╝

(新值和以前一样，所以只更新旧记录中的时间到新记录的时间.或者如果你愿意，插入新记录并删除旧记录)

(New values are same as previous, so only update the time in the old record to that of the new record. or if you will, insert new record and delete the old)

目前我在 PHP 中执行此操作，这很简单.到目前为止，我对这种方法没有真正的问题.我只是认为可能有一种方法可以用一个 sql 语句来完成这一切，我怀疑这意味着代码越来越干净，以及更好的性能，因为我认为 可能 成为一个小问题，因为数据库在增长.

Currently I do this in PHP which is a no-brainer. As of now I have no real issues with this approach. I just thought there might be a way of doing it all with one sql-statement which I suspect would mean less and more clean code, as well as better performance as I think that could become a small issue as the database grows.

我发现以下问题和答案处理了类似的问题 MySQL:如果定义的行相同，则不插入新行

I found the following question&answer that deals with a similar issue MySQL: Do not insert a new row if defined rows are same

问题是，在我的情况下，我一次为数以万计的气象站"插入更新.而且我无法让该解决方案与多插入一起工作.有办法吗?

The problem is that in my case I'm inserting updates for tens of thousands of "weatherstations" at once. And I was unable to get that solution to work with a multi-insert.Is there a way?

推荐答案

要使多行 INSERT 起作用，您需要以某种方式收集数据，然后批量 INSERT.

To get the multi-row INSERT to work, you need to collect the data in some way, then batch INSERT them.

计划 A:以某种方式将它们收集到您的非 mysql 代码中.如果可能，这是更好的方法.

Plan A: Collect them in your non-mysql code somehow. If this is possible it is the better approach.

计划B:有一个临时表"，可能是没有任何索引的MyISAM.将您的读数插入其中.然后，在阅读时间之间，执行 INSERT INTO Real SELECT * FROM Staging 和 TRUNCATE TABLE staging.

Plan B: Have a "staging table" that is perhaps MyISAM without any indexes. INSERT your readings into it. Then, between reading times, do INSERT INTO Real SELECT * FROM Staging and TRUNCATE TABLE staging.

这篇关于仅当值与多插入的先前记录不同时才插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！