MySQL 查询，插入重复项的问题 [英] MySQL Query, problems with with duplicates on insert into

问题描述

所以我正在使用论坛软件 Xenforo，我需要有关将定期执行的 SQL 查询的帮助.该论坛将有大约 300-500 名活跃用户.所以没什么大不了的.

So I'm using the forum software Xenforo and I need help with a SQL query which will be executed periodically. The forum will have approximately 300-500 active users. So nothing too big.

所有用户xf_users都需要订阅node_id 71，但属于某些user_group_id的用户除外.

All users xf_users need to be subscribed to node_id 71 except users which are part of certain user_group_id.

现在用户可以退订论坛或更改notify_on 和send_alert &send_email 字段.我不要那个.因此，如果用户取消订阅"，则需要再次添加.

Now users can unsubscribe from the forum or change the notify_on and send_alert & send_email fields. I do not want that. So if users 'unsubscribe' they need to be added again.

唯一可接受的更改是 notify_on 也可能是message".

The only accepted change is that notify_on may also be "message".

总结:

• 如果用户不在 node_id 71 的表上，则将用户插入 xf_forum_watch 和 node_id 71，notify_on 设置为线程"；和 send_alert &send_email 设置为1".

• If the user is not on the table with node_id 71, then insert the users into xf_forum_watch with the node_id 71, notify_on is set to "thread" and send_alert & send_email are set to "1".

如果存在 user_id 和 node_id 71 的行，将 send_alert 和 send_email 更新为1"如果其中之一是0".如果 notify_on 为空:更新为线程".

If the line with the user_id and node_id 71 exists, update send_alert and send_email to "1" if one of them is "0". If notify_on is blank: update to "thread".

我的想法 #1 是一个触发器.问题是，我从来没有写过触发器，也不知道如何用它来解决我的问题.

My thought #1 was a trigger. Thing is, i have never written a trigger and don't know how to solve my issue with it.

想法 #2 是编写一个事件"在定期运行查询的 phpMyAdmin 中.

Thought #2 was to write an "Event" in phpMyAdmin which runs a query periodically.

我的代码可以一次性将所有正确的用户插入列表中，但我在更新列表时遇到了问题.

I got the code working for inserting all the right users into the list once but I'm having issues with keeping the list updated.

我曾尝试使用 ON DUPLICATE KEY UPDATE...但我没有让它按预期工作.

I have tried using ON DUPLICATE KEY UPDATE... but I didn't get it to work like intended.

我的快速和肮脏的解决方案是删除 node_id 71 的所有行并使用下面的查询重新添加所有行，但这不允许用户选择 notify_on 消息".还有

My quick&dirty solution would be to delete all lines of node_id 71 and re-add all with the query below, but this wouldn't allow users to select notify_on "message". Also

帮助?♥

表格截图

(!!该表还将包含不同 node_id 的行.这些行必须保持不变！！)

(!! the table will also include lines which are a different node_id. These must remain untouched !!)

insert into xf_forum_watch
    select user_id,
    "71" as node_id,
    "thread" as notify_on,
    "1" as send_alert,
    "1" as send_email
    from xf_user
where user_group_id NOT IN (1, 18, 40);

这会将正确的用户添加到列表中(见截图).但是，如果用户已经订阅，则会抛出错误.

This will add the correct users to the list (see screenshot). However, it will throw an error if the user is already subscribed.

我希望我没有遗漏任何细节.如果还有问题，请告诉我.

I hope that I didn't leave out any details. If there are still questions, please let me know.

我感谢各种帮助.

PS:我希望我没有破坏格式.我不习惯在这里发帖.

PS: I hope I didn't ruin the formatting. I am not used to posting here.

感谢@GMB 的解决方案.

edit: Thanks to @GMB for their solution.

我有一个后续问题.我想将工作查询放入存储过程，但它给了我一个错误:

I have a follow-up question. I want to put the working query into a stored procedure but it gives me an error:

DELIMITER $$

CREATE PROCEDURE forceSubscribeToNode(
    IN node_id_var INT
)
BEGIN
    insert into xf_forum_watch
    (user_id, node_id, notify_on, send_alert, send_email)
    select user_id,
    node_id_var as node_id,
    "thread" as notify_on,
    1 as send_alert,
    1 as send_email
    from xf_user
where user_group_id NOT IN (1, 18, 40)
on duplicate key update 
    send_alert = 1,
    send_email = 1,
    notify_on = case when notify_on is null or notify_on = '' then 'thread' else notify_on end
END $$

DELIMITER ;

错误:

#1064 - 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 18 行的END"附近使用的正确语法

(第 18 行是send_email = 1")

(Line 18 being "send_email = 1,")

我做错了什么?

推荐答案

您只需在查询中添加重复键:

insert into xf_forum_watch
    (user_id, node_id, notify_on, send_alert, send_email)
    select user_id,
    71 as node_id,
    'thread' as on notify_on,
    1 as send_alert,
    1 as send_email
    from xf_user
where user_group_id NOT IN (1, 18, 40)
on duplicate key update 
    send_alert = 1,
    send_email = 1,
    notify_on = 'thread'

这假设您设置了正确的主键唯一性，以便正确识别重复项.

This assumes that you have the proper unique of primary key set up so that duplicates are properly identified.

如果您想在 on duplicate key 子句中使用条件逻辑，那也是可能的:

If you want conditional logic in the on duplicate key clause, that’s also possible:

on duplicate key update 
    send_alert = case when send_alert = 0 then 1 else send_alert end,
    send_email = case when send_email = 0 then 1 else send_email end,
    notify_on = case when notify_on is null or notify_on = '' then  'thread' else notify_on end

这篇关于MySQL 查询，插入重复项的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！