MySQL 重复键更新的替代方案

Question

我正在尝试从多个表中提取数据并插入表中，以根据创建的类别在moodle数据库中创建角色分配，但我需要它在重复键上进行更新，但我无法使用ON DUPLICATE KEY UPDATE，因为我尝试匹配角色 id、上下文 id 和用户 id 的字段不是 mdl_role_assignments 表中的主键。

insert into vclassmoodle.mdl_role_assignments (roleid,contextid,userid,timemodified,modifierid,itemid,sortorder) select 
mdl_role.id as roleid, mdl_context.id as contextid, mdl_user.id as userid, unix_timestamp() as timemodified, 3 as modifierid, 0 as itemid, 0 as sortorder
from
mdl_context
    left join
mdl_course_categories ON mdl_context.instanceid = mdl_course_categories.id
    left join
mdl_user ON mdl_course_categories.idnumber = mdl_user.idnumber
    join
mdl_role ON mdl_role.shortname = 'manager'
where mdl_context.contextlevel = 40 and mdl_course_categories.depth > 1

如果我需要澄清任何事情，请告诉我

谢谢

Answer 1

刚刚查看了/lib/accesslib.php 中的函数 role_assign()

如果存在重复项，则它不会更新，因此您可以忽略重复项。

尽管您确实应该使用 role_assign() 函数而不是直接插入数据。以防将来角色分配发生变化，而且还因为它会触发可能在其他地方使用的 role_signed 事件。

仍然使用您的查询，但忽略现有记录并创建一个循环来调用 role_assign()，如下所示

SELECT mdl_role.id as roleid,
       mdl_context.id as contextid,
       mdl_user.id as userid
FROM mdl_context
JOIN mdl_course_categories ON mdl_context.instanceid = mdl_course_categories.id
JOIN mdl_user ON mdl_course_categories.idnumber = mdl_user.idnumber
JOIN mdl_role ON mdl_role.shortname = 'manager'
WHERE mdl_context.contextlevel = 40
AND mdl_course_categories.depth > 1
AND NOT EXISTS (
    SELECT mdl_role_assignments.id
    FROM mdl_role_assignments
    WHERE mdl_role_assignments.roleid = mdl_role.id
    AND mdl_role_assignments.contextid = mdl_context.id
    AND mdl_role_assignments.userid = mdl_user.id
    AND mdl_role_assignments.itemid = 0
    AND mdl_role_assignments.component = '')

请注意，重复项是 roleid、userid 和 contextid 的组合，也是组件和 itemid 的组合。因此，还需要检查 component = ''。