This question is related to a previous one. I have 2 MySQL tables, raw_contacts and distilled_contacts. The table structures are as follows:
这个问题与前一个问题有关。我有2个MySQL表,raw_contacts和distilled_contacts。表结构如下:
raw_contacts
--------------------------------------------
ID (primary auto-increment, int)
PHONE (composite unique with NAME, varchar)
NAME (composite unique with PHONE, varchar)
FREQUENCY (int)
Composite unique key is named PHONENUM
distilled_contacts
---------------------------------------------
ID (primary auto-increment, int)
PHONE (unique, varchar)
POPULARNAME (varchar)
Every time a new row is inserted into raw_contacts, I need a new entry to be made in distilled_contacts for the phone number in context with the name with highest frequency updated in the POPULARNAME field. If an entry already exists for that phone number in distilled_contacts, it should just be updated with the changes, if any. I am able to retrieve the name with the highest frequency using this query:
每次在raw_contacts中插入新行时,我都需要在distilled_contacts中为新的条目创建一个上下文中的电话号码,并在POPULARNAME字段中更新频率最高的名称。如果Rhine_contacts中的该电话号码已存在条目,则应仅使用更改进行更新(如果有)。我可以使用此查询检索具有最高频率的名称:
select NAME from `raw_contacts` RC1 where PHONE="11111"
and FREQUENCY>=all (select FREQUENCY from `raw_contacts` RC2 where RC2.PHONE=RC1.PHONE) LIMIT 1
Now how do I write a trigger to insert/update this name and phone number into distilled_contacts? Here's my attempt:
现在如何编写触发器以将此名称和电话号码插入/更新为distilled_contacts?这是我的尝试:
CREATE TRIGGER trigger1
AFTER UPDATE
ON `raw_contacts` FOR EACH ROW
BEGIN
INSERT INTO `distilled_contacts`
(PHONE, POPULARNAME)
select PHONE, NAME from `raw_contacts` RC1 where PHONE="11111"
and FREQUENCY>=all (select FREQUENCY from `raw_contacts` RC2 where RC2.PHONE=RC1.PHONE) LIMIT 1 ON DUPLICATE KEY UPDATE POPULARNAME = NAME
END;
But I am still unable to get my head around how to use a variable instead of "11111" to refer to the phone number in context, i.e. the value being inserted under the PHONE column of raw_contacts.
但是我仍然无法理解如何使用变量而不是“11111”来引用上下文中的电话号码,即在raw_contacts的PHONE列下插入的值。
1 个解决方案
#1
Finally, got this to work:
最后,让这个工作:
CREATE TRIGGER trigger1
AFTER UPDATE
ON `raw_contacts` FOR EACH ROW
BEGIN
INSERT INTO `distilled_contacts`
(PHONE, POPULARNAME)
select PHONE, NAME from `raw_contacts` RC1 where PHONE=NEW.PHONE
and FREQUENCY>=all (select FREQUENCY from `raw_contacts` RC2 where RC2.PHONE=RC1.PHONE) LIMIT 1 ON DUPLICATE KEY UPDATE POPULARNAME = NAME
END;
I won't yet, however, mark this as accepted since I want to see if anyone out there has any suggestions to optimize the trigger (or the underlying query) further. This trigger will be fired after every insert/update and there will be potentially thousands of such events simultaneously. So any suggestion to make this more performance-friendly is greatly appreciated!
但是,我还没有将此标记为已被接受,因为我想看看是否有人建议进一步优化触发器(或基础查询)。每次插入/更新后都会触发此触发器,同时可能会有数千个此类事件。因此,非常感谢任何使这个更加性能友好的建议!
#1
Finally, got this to work:
最后,让这个工作:
CREATE TRIGGER trigger1
AFTER UPDATE
ON `raw_contacts` FOR EACH ROW
BEGIN
INSERT INTO `distilled_contacts`
(PHONE, POPULARNAME)
select PHONE, NAME from `raw_contacts` RC1 where PHONE=NEW.PHONE
and FREQUENCY>=all (select FREQUENCY from `raw_contacts` RC2 where RC2.PHONE=RC1.PHONE) LIMIT 1 ON DUPLICATE KEY UPDATE POPULARNAME = NAME
END;
I won't yet, however, mark this as accepted since I want to see if anyone out there has any suggestions to optimize the trigger (or the underlying query) further. This trigger will be fired after every insert/update and there will be potentially thousands of such events simultaneously. So any suggestion to make this more performance-friendly is greatly appreciated!
但是,我还没有将此标记为已被接受,因为我想看看是否有人建议进一步优化触发器(或基础查询)。每次插入/更新后都会触发此触发器,同时可能会有数千个此类事件。因此,非常感谢任何使这个更加性能友好的建议!