MySQL 在值更改时插入新行

Question

对于我现在正在进行的个人项目，我想制作一张 Steam、Impulse、EA Origins 和其他几个网站上游戏价格随时间变化的折线图。目前，我已经修改了 SteamCalculator.com 使用的脚本来记录每个可能的国家/地区代码或每个网站中每个游戏的当前价格（如果适用，则为促销价）。我还有一列用于存储价格的日期。我当前的表格看起来像这样：

THIS STRUCTURE IS NO LONGER VALID. SEE BELOW
+----------+------+------+------+------+------+------+------------+
| steam_id |  us  |  at  |  au  |  de  |  no  |  uk  |    date    |
+----------+------+------+------+------+------+------+------------+
|  112233  |  999 |  899 |  999 | NULL |  899 |  699 |  2011-8-21 |
|  123456  | 1999 |  999 | 1999 |  999 |  999 |  999 |  2011-8-20 |
|    ...   |  ... |  ... |  ... |  ... |  ... |  ... |     ...    |
+----------+------+------+------+------+------+------+------------+

目前每个国家/地区都是单独更新的（有一个遍历国家/地区的 for 循环），尽管如果可以简化它，那么可以对其进行修改以暂时将新价格存储到数组中，然后更新整个国家/地区一次排。无论如何，出于性能原因，我最终可能会这样做。

现在我的问题是确定如果其中一个价格发生变化，如何最好地更新此表。例如，假设游戏 112233 于 2011 年 8 月 22 日在美国发售，售价为 4.99 美元，在奥地利售价为 3.99 欧元，其他价格保持不变。我需要表格看起来像这样：

THIS STRUCTURE IS NO LONGER VALID. SEE BELOW
+----------+------+------+------+------+------+------+------------+
| steam_id |  us  |  at  |  au  |  de  |  no  |  uk  |    date    |
+----------+------+------+------+------+------+------+------------+
|  112233  |  999 |  899 |  999 | NULL |  899 |  699 |  2011-8-21 |
|  123456  | 1999 |  999 | 1999 |  999 |  999 |  999 |  2011-8-20 |
|    ...   |  ... |  ... |  ... |  ... |  ... |  ... |     ...    |
|  112233  |  499 |  399 |  999 | NULL |  899 |  699 |  2011-8-22 |
+----------+------+------+------+------+------+------+------------+

我不想每次检查价格时都创建一个新行，否则我最终会日复一日地得到数百万行重复的价格。我也不想为每个更改的价格创建一个新行，如下所示：

THIS STRUCTURE IS NO LONGER VALID. SEE BELOW
+----------+------+------+------+------+------+------+------------+
| steam_id |  us  |  at  |  au  |  de  |  no  |  uk  |    date    |
+----------+------+------+------+------+------+------+------------+
|  112233  |  999 |  899 |  999 | NULL |  899 |  699 |  2011-8-21 |
|  123456  | 1999 |  999 | 1999 |  999 |  999 |  999 |  2011-8-20 |
|    ...   |  ... |  ... |  ... |  ... |  ... |  ... |     ...    |
|  112233  |  499 |  899 |  999 | NULL |  899 |  699 |  2011-8-22 |
|  112233  |  499 |  399 |  999 | NULL |  899 |  699 |  2011-8-22 |
+----------+------+------+------+------+------+------+------------+

我可以通过使每个 (steam_id,) 成为唯一索引然后添加来防止第一个问题，但不能防止第二个问题ON DUPLICATE KEY UPDATE 对每个数据库查询。如果价格不同，这只会添加一行，但它会为每个发生变化的国家/地区添加一个新行。它还不允许单个游戏在不同的两天内采用相同的价格（例如，假设游戏 112233 稍后停售并返回到 9.99 美元），因此这显然是一个糟糕的选择。

我可以通过将 (steam_id, date) 设置为唯一索引，然后向每个查询添加 ON DUPLICATE KEY UPDATE 来防止第二个问题，但不能防止第一个问题。每天运行脚本时，日期都会发生变化，因此它将创建一个新行。这种方法最终会导致每天都有数百条相同价格的行。

当（且仅当）任何价格自最新日期以来发生变化时，我如何告诉 MySQL 创建一个新行？

更新 -

根据此线程中的人员的建议，我更改了数据库的架构，以便于将来添加新的国家/地区代码，并避免需要一次更新整行的问题。新模式看起来像这样：

+----------+------+---------+------------+
| steam_id |  cc  |  price  |    date    |
+----------+------+---------+------------+
|  112233  |  us  |   999   |  2011-8-21 |
|  123456  |  uk  |   699   |  2011-8-20 |
|    ...   |  ... |   ...   |     ...    |
+----------+------+---------+------------+

在这个新模式之上，我发现我可以使用以下 SQL 查询从最近的更新中获取价格：

SELECT `price` FROM `steam_prices` WHERE `steam_id` = 112233 AND `cc`='us' ORDER BY `date` ASC LIMIT 1

此时我的问题归结为：

是否可以（使用仅 SQL 而不是应用程序逻辑）仅在条件为真时才插入行？例如：

INSERT INTO `steam_prices` (...) VALUES (...) IF price<>(SELECT `price` FROM `steam_prices` WHERE `steam_id` = 112233 AND `cc`='us' ORDER BY `date` ASC LIMIT 1)

从 MySQL 手册 我找不到任何方法来做到这一点。我只发现如果唯一索引相同，您可以忽略或更新。但是，如果我将价格设置为唯一索引（如果日期相同，则允许我更新日期），那么我将无法识别游戏何时开始销售，然后返回到其原始价格。例如：

+----------+------+---------+------------+
| steam_id |  cc  |  price  |    date    |
+----------+------+---------+------------+
|  112233  |  us  |   999   |  2011-8-20 |
|  112233  |  us  |   499   |  2011-8-21 |
|  112233  |  us  |   999   |  2011-8-22 |
|    ...   |  ... |   ...   |     ...    |
+----------+------+---------+------------+

另外，在找到并阅读 MySQL Conditional INSERT 之后，我创建并尝试了以下查询：

INSERT INTO `steam_prices`(
    `steam_id`,
    `cc`,
    `update`,
    `price`
)
SELECT '7870', 'us', NOW(), 999
FROM `steam_prices`
WHERE
    `price`<>999
    AND `update` IN (
        SELECT `update`
        FROM `steam_prices`
        ORDER BY `update`
        ASC LIMIT 1
    )

这个想法是当（且仅当）最新的 price 时插入行 '7870', 'us', NOW(), 999 update 不是 999。当我运行此命令时，出现以下错误：

1235 - 此版本的 MySQL 尚不支持“LIMIT &” IN/ALL/ANY/SOME 子查询'

有什么想法吗？

Answer 1

经过实验，并在 MySQL Conditional INSERT 和 http://www.artfulsoftware.com/infotree/queries.php#101，我发现有效的查询：

INSERT INTO `steam_prices`( 
    `steam_id`, 
    `cc`, 
    `price`,
    `update` 
) 
SELECT 7870, 'us', 999, NOW() 
FROM `steam_prices` AS p1
LEFT JOIN `steam_prices` AS p2 ON p1.`steam_id`=p2.`steam_id` AND p1.`update` < p2.`update`
WHERE 
    p2.`steam_id` IS NULL
    AND p1.`steam_id`=7870
    AND p1.`cc`='us'
    AND (
        p1.`price`<>999
    )

答案是首先返回没有较早时间戳的所有行。这是通过组内聚合完成的。仅在时间戳较早的行上将表与其自身连接。如果它无法加入（时间戳不早），那么您就知道该行包含最新的时间戳。这些行在连接表中将有一个 NULL id（连接失败）。

选择具有最新时间戳的所有行后，仅获取 steam_id 是您要查找的 steam_id 并且价格与您输入的新价格不同的行。如果此时该游戏没有具有不同价格的行，则自上次更新以来价格没有更改，因此返回空集。当返回空集时，SELECT 语句将失败并且不会插入任何内容。如果 SELECT 语句成功（发现不同的价格），则它将返回插入到表中的行 7870, 'us', 999, NOW()。

编辑 - 实际上，不久之后我发现了上述查询的错误，并且我已经对其进行了修改。如果价格自上次更新以来发生了变化，上面的查询将插入一个新行，但如果数据库中当前没有该商品的价格，则不会插入行。

为了解决这个问题，我必须利用DUAL表（它总是包含一行），然后在where子句中使用OR来测试不同的价格OR空集

INSERT INTO `steam_prices`( 
    `steam_id`, 
    `cc`, 
    `price`,
    `update` 
) 
SELECT 12345, 'us', 999, NOW() 
FROM DUAL
WHERE
    NOT EXISTS (
        SELECT `steam_id`
        FROM `steam_prices`
        WHERE `steam_id`=12345
    )
    OR
    EXISTS (
        SELECT p1.`steam_id`
        FROM `steam_prices` AS p1 
        LEFT JOIN `steam_prices` AS p2 ON p1.`steam_id`=p2.`steam_id` AND p1.`update` < p2.`update`
        WHERE 
            p2.`steam_id` IS NULL 
            AND p1.`steam_id`=12345 
            AND p1.`cc`='us' 
            AND ( 
                p1.`price`<>999
            )
    )

很长，很丑，而且很复杂。但它的工作原理与广告中的完全一样。如果数据库中没有某个 steam_id 的价格，则会插入一个新行。如果已经存在价格，则它会检查最近更新的价格，如果不同，则插入新行。

Answer 2

如果您只需将模式更改为类似以下内容，您可能会发现这更容易：（

steam_id      integer
country       varchar(2)
date          date
price         float
primary key   (steam_id,country,date)

使用其他适当的索引），然后只依次担心每个国家/地区。

换句话说，您的 for 循环具有唯一的 ID/国家/地区组合，因此它可以简单地查询该组合的最新日期记录，并在不同时添加新行。

这将使您的选择变得更加复杂，但我相信这是一个更好的解决方案，尤其如果将来有可能添加更多国家/地区（在这种情况下它不会破坏架构） ）。

Answer 3

首先，我建议您以每个国家/地区较少硬编码的形式存储数据：

+----------+--------------+------------+-------+
| steam_id | country_code | date       | price |
+----------+--------------+------------+-------+
|   112233 | us           | 2011-08-20 | 12.45 |
|   112233 | uk           | 2011-08-20 | 12.46 |
|   112233 | de           | 2011-08-20 | 12.47 |
|   112233 | at           | 2011-08-20 | 12.48 |
|   112233 | us           | 2011-08-21 | 12.49 |
|   ...... | ..           | .......... | ..... |
+----------+--------------+------------+-------+

从这里开始，您将主键放在前三列上...

现在关于不创建额外行的问题...这就是简单事务+应用程序逻辑所擅长的。

1. 启动一个事务
2. 运行一个选择来查看有问题的记录是否存在
3. 如果不存在，则插入一条

该方法有问题吗？

希望这有帮助。