mysql - MySQL - 在重复键上用旧列值更新另一列

Question

我不知道如何处理一个简单的问题。我的 MySQL 数据库包含有关经纬度点收集的数据。我的目标是计算行更新期间的距离和方向。

这就是我现在插入的内容：

INSERT INTO positions 
            (NAME, 
             lat, 
             longitude, 
             update_at) 
VALUES      ( 'Object 1', 
              '50.123456', 
              '20.123456', 
              '2020-05-27 18:07:26') 

此查询每分钟更新一次，但有很多单独的对象。为了简单起见，我正在实施ON DUPLICATE KEY UPDATE.

现在查询如下所示：

INSERT INTO positions 
            ( 
                        NAME , 
                        latitude , 
                        longitude , 
                        update_at 
            ) 
            VALUES 
            ( 
                        'Object 1' , 
                        '50.123456' , 
                        '20.123456' , 
                        '2020-05-27 18:07:26' 
            ) 
ON DUPLICATE KEY UPDATE 
              name = VALUES(NAME), 
              lat = VALUES(latitude),
              lon = VALUES(longitude), 
              update_at = VALUES(update_at)

这是我想在更新期间首先存储old_latitude和值的时刻。old_longitude第一个问题是：

期间如何获取旧值ON DUPLICATE KEY UPDATE？

我想存储这些数据，因为有时我不想上传旧值。尤其是当两点之间的距离小于例如 10 米时。

如果该值的距离更长，我想计算方向。

总体问题是如何在 MySQL 查询中以最简单的方式实现这种情况？

我会很高兴得到一些建议或帮助！:)

编辑：简单的一步一步最小可重现示例：

桌子：

CREATE TABLE Positions (
       name VCHAR(128) PRIMARY KEY,
       lat DOUBLE(30) NOT NULL,
       lon DOUBLE(30) NOT NULL,
       update_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

第一步：

 INSERT INTO positions 
            (NAME, 
             lat, 
             longitude, 
             update_at) 

 VALUES 
            ( 'Obj1'
            , '50.123456'
            , '20.123456'
            , '2020-05-27 10:00:00')
 ON DUPLICATE KEY 
 UPDATE name = VALUES(name)
            , latitude = VALUES(lat)
            , longitude = VALUES(lon)
            , update_at = VALUES(update_at)
            , IF(distance(VALUES(lat), VALUES(lon), OLD_VALUES(lat), OLD_VALUES(lon)>10, (
                 old_lat = OLD_VALUES(lat)
               , old_lon = OLD_VALUES(lon),
               , dir = direction(VALUES(lat), VALUES(lon), OLD_VALUES(lat), OLD_VALUES(lon))
         ))

结果：

-----------------------------------------------------------
| name |  lat |  lon | update_at | old_lat | old_lon| dir |
-----------------------------------------------------------
| Obj1 | 50.1 | 21.1 |2020-05-27 |    0    |   0    |  0  |
|      |      |      | 10:00:00  |         |        |     |
-----------------------------------------------------------

第二步：

 INSERT INTO positions 
            (NAME, 
             lat, 
             longitude, 
             update_at) 
 VALUES 
            ( 'Obj1'
            , '52.123456'
            , '25.123456'
            , '2020-05-27 11:00:00'),
            ( 'Obj2'
            , '32.123456'
            , '15.123456'
            , '2020-05-27 12:00:00'),
 ON DUPLICATE KEY 
 UPDATE name = VALUES(name)
            , latitude = VALUES(lat)
            , longitude = VALUES(lon)
            , update_at = VALUES(update_at)
            , IF(distance(VALUES(lat), VALUES(lon), OLD_VALUES(lat), OLD_VALUES(lon)>10, (
                old_lat = OLD_VALUES(lat)
               , old_lon = OLD_VALUES(lon),
               , dir = direction(VALUES(lat), VALUES(lon), OLD_VALUES(lat), OLD_VALUES(lon))
         ))

结果：

-----------------------------------------------------------
| name |  lat |  lon | update_at | old_lat | old_lon| dir |
-----------------------------------------------------------
| Obj1 | 52.1 | 25.1 |2020-05-27 |   50.1  |   21.1 |  20 |
|      |      |      | 11:00:00  |         |        |     |
---------------------------------------------------------
| Obj2 | 32.1 | 15.1 |2020-05-27 |    0    |    0   |  0  |
|      |      |      | 12:00:00  |         |        |     |
-----------------------------------------------------------

第三步：

 INSERT INTO positions 
            (NAME, 
             lat, 
             longitude, 
             update_at) 
  VALUES 
            ( 'Obj1'
            , '52.987654'
            , '25.987654'
            , '2020-05-27 13:00:00'),
  ON DUPLICATE KEY 
  UPDATE name = VALUES(name)
            , latitude = VALUES(lat)
            , longitude = VALUES(lon)
            , update_at = VALUES(update_at)
            , IF(distance(VALUES(lat), VALUES(lon), OLD_VALUES(lat), OLD_VALUES(lon)>10, (
                 old_lat = OLD_VALUES(lat)
               , old_lon = OLD_VALUES(lon),
               , dir = direction(VALUES(lat), VALUES(lon), OLD_VALUES(lat), OLD_VALUES(lon))
         ))

结果：

-----------------------------------------------------------
| name |  lat |  lon | update_at | old_lat | old_lon| dir |
-----------------------------------------------------------
| Obj1 | 52.9 | 25.9 |2020-05-27 |   50.1  |  21.1  |  20 |
|      |      |      | 13:00:00  |         |        |     |
-----------------------------------------------------------
| Obj2 | 32.1 | 15.1 |2020-05-27 |    0    |   0    |  0  |
|      |      |      | 12:00:00  |         |        |     |
-----------------------------------------------------------

标签： mysqlsql-updateon-duplicate-key