mysql - 从一个表中的字段中检索最高频率值并将其更新到另一个表中

Question

我有 2 个具有以下结构的 MySQL 表:

**tblLocations**
ID [primary key]
CITY [non-unique varchar]
NAME [non-unique varchar]
----------------------------------
**tblPopularNames**
ID [primary key]
CITY [unique varchar]
POPULARNAME [non-unique varchar]

我通过 Web 表单接收用户的输入，然后 PHP 代码将数据插入 tbl 地点 .这部分很简单。现在，每次插入 tbl 地点 ，我需要触发以下 Action :

看看有没有 tblPopularNames 包含插入的 的条目城市 值(value)

如果条目存在，更新对应的大众名频率最高的场姓名 对 的值(value)城市 tblLocations 中的字段。

如果该条目不存在，则使用刚刚输入的值创建一个。

这可以在不使用任何查询嵌套的情况下完成吗？就内存使用而言，执行此操作的最便宜的方法是什么？

我可以看到一个相关的帖子 here但那里的答案只提供了正在寻找的值(value)的最大数量，这不是我想要做的。我需要最不做作的方式来完成这两项任务。此外，我不确切知道查询将如何处理关系，即两个名字在输入的城市中享有相同的频率。老实说，我不介意在这种情况下查询返回任何一个值，只要它不引发错误。

我希望我已经根据需要清楚地解释了它，但如果您有任何疑问，请随时发表评论。

附言不确定问题是属于这里还是属于 DBA。我之所以选择 SO，是因为我在该站点上看到了与查询有关的其他问题(例如， this one)。如果其中一位版主认为 DBA 更合适，请要求他们按照他们认为合适的方式移动它。

Answer 1

The first table accepts two values from users: their name and the city they live in. The fields affected in that table are CITY and NAME. Then each time a new entry is made to this table, another is made to tblPopularNames with that city and the name that occurs most frequently against that city in tblLocations. For example, if John is the most popular name in NY, tblPopularNames gets updated with NY, John. –

好的，让我们把它分解成一个触发器。每次创建新条目时都会转换为 AFTER INSERT ON tblLocations FOR EACH ROW ;在 tblLocations 中针对该城市最常出现的名称意味着我们运行 SELECT NEW.insertedCity, old.insertedName FROM tblLocations AS old WHERE insertedCity = NEW.insertedCity GROUP BY insertedName ORDER BY COUNT(*) DESC LIMIT 1 ;我们可能想在 ORDER BY 中添加一些内容，以避免随机提取相同频率的多个名称。

还有一个额外的要求，如果城市已经存在于 tblPopularNames 中，则更新条目。为此，我们需要 tblPopularNames.popularCity 上的唯一键；它将允许我们使用 ON DUPLICATE KEY UPDATE .

最后:

DELIMITER //
CREATE TRIGGER setPopularName
    AFTER INSERT ON tblLocations
    FOR EACH ROW BEGIN
        INSERT INTO tblPopularNames 
        SELECT NEW.insertedCity, insertedName 
            FROM tblLocations
            WHERE insertedCity = NEW.insertedCity
            GROUP BY insertedName
            ORDER BY COUNT(*) DESC, insertedName
            LIMIT 1 
        ON DUPLICATE KEY
            UPDATE popularName = VALUES(popularName)
        ;
    END;//
DELIMITER ;

测试

mysql> INSERT INTO tblLocations VALUES ('Paris', 'Jean'), ('Paris', 'Pierre'), ('Paris', 'Jacques'), ('Paris', 'Jean'), ('Paris', 'Etienne');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tblPopularNames;
+-------------+-------------+
| popularCity | popularName |
+-------------+-------------+
| Paris       | Jean        |
+-------------+-------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tblLocations VALUES ('Paris', 'Jacques'), ('Paris', 'Jacques'), ('Paris', 'Etienne');                                 Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tblPopularNames;
+-------------+-------------+
| popularCity | popularName |
+-------------+-------------+
| Paris       | Jacques     |
+-------------+-------------+
1 row in set (0.00 sec)

触发器与代码

不可否认@Phil_1984 的回答有很多很多很多优点。触发器有其用途，但它们不是 Elixir 。

此外，在这个阶段，设计可能还处于其生命周期的早期阶段，不值得将繁重的工作外包给触发器。例如，如果您决定采用上面提示的“计数器”解决方案怎么办？或者，如果您决定让popularName 的选择复杂化怎么办？

毫无疑问，维护(包括彻底的现场测试)触发器比在代码中完成相同的事情要昂贵得多。

所以我真正要做的是首先设计一个函数或方法，目的是接收插入的值并做一些魔术。

然后我会用 PHP 中的几个查询来模拟触发代码，并包装在一个事务中。它们将与上面触发器中出现的查询相同。

然后我会继续进行其余的工作，我知道这个解决方案是有效的，如果可能会改进性能的话。

如果很久以后，设计令人信服并被提交，那么将很容易修改函数以仅运行一个 INSERT 查询并利用触发器——那个触发器，或者同时发展的稍微修改过的触发器。

如果稍作修改已被 creeping featurism 接管并且不容易向后移植到触发器，您无需执行任何操作，也不会丢失任何内容。否则，您已经失去了最初实现的时间(很少)，现在已经准备好获利了。

所以我的回答是: 两个 :-)

略有不同的用例(根据评论)

The thing is, the first query being performed by PHP is an indefinitely large one with potentially hundreds of entries being inserted at once. And I do need to update the second table every time a new entry is made to the first because by its very nature, the most popular name for a city can potentially change with every new entry, right? That's why I was considering a trigger since otherwise PHP would have to fire hundreds of queries simultaneously. What do you think?

问题是:在大批量的第一个和最后一个 INSERT 之间应该发生什么？

你在那个周期中使用流行的名字吗？

如果是，那么您别无选择:您需要在每次插入后检查流行度表(不是真的；有一种解决方法，如果您有兴趣......)。

如果不是，那么您可以在最后进行所有计算。

即，您有一长串

 NY        John
 Berlin    Gottfried
 Roma      Mario
 Paris     Jean
 Berlin    Lukas
 NY        Peter
 Berlin    Eckhart

您可以检索所有流行名称(或您插入的列表中包含城市的所有流行名称)及其频率，并将它们放在数组数组中:

 [
     [ NY,        John,    115 ],
     [ NY,        Alfred,  112 ],
     ...
 ]

然后从您的列表中“提取”频率:

 NY        John       1
 NY        Peter      1
 Berlin    Gottfried  1
 Roma      Mario      1
 Paris     Jean       1
 Berlin    Lukas      1
 Berlin    Eckhart    1

然后将频率添加(您仍在 PHP 中)您检索到的频率。在这种情况下，例如 NY,John 将从 115 变为 116。

您可以同时执行这两项操作，首先获取新插入的“蒸馏”频率，然后运行查询:

 while ($tuple = $exec->fetch()) {
     // $tuple is [ NY, John, 115 ]
     // Is there a [ NY, John ] in our distilled array?
     $found = array_filter($distilled, function($item) use ($tuple) {
         return (($item[0] === $tuple[0]) && ($item[1] === $tuple[1]));
     }
     if (empty($found)) {
         // This is probably an error: the outer search returned Rome,
         // yet there is no Rome in the distilled values. So how comes
         // we included Rome in the outer search?
         continue;
         // But if the outer search had no WHERE, it's OK; just continue
     }
     $datum = array_pop($found);
     // if (!empty($found)) { another error. Should be only one. }

     // So we have New York with popular name John and frequency 115
     $tuple[2] += $datum[2];
     $newFrequency[] = $tuple;
}

然后，您可以使用例如按城市和频率降序对数组进行排序。 uasort .

uasort($newFrequency, function($f1, $f2) {
    if ($f1[0] < $f2[0]) return -1;
    if ($f1[0] > $f2[0]) return 1;

    return $f2[2] - $f1[2];
});

然后你遍历数组

 $popularName = array();
 $oldCity     = null;
 foreach ($newFrequency as $row) {
     // $row = [ 'New York', 'John', 115 ]
     if ($oldCity != $row[0]) {
         // Given the sorting, this is the new maximum.
         $popularNames[] = array( $row[0], $row[1] );
         $oldCity = $row[0];
     }
 }

 // Now popularNames[] holds the new cities with the new popular name.
 // We can build a single query such as
 INSERT INTO tblPopularNames VALUES
     ( city1, name1 ),
     ( city2, name2 ),
     ...
     ( city3, name3 )
 ON DUPLICATE KEY
    UPDATE popularName = VALUES(popularName);

这将插入那些没有条目的城市，或者更新那些有条目的城市的流行名称。