如何编写6条MySQL UPDATE语句拆分table1的tag字段到对应列

以下6条语句严格匹配你给出的拆分规则：

• 规则1匹配：tag格式为school_signup_{var1value}且external_tags=0

UPDATE table1
SET tag2 = 'school_signup',
    var1 = SUBSTRING_INDEX(tag, 'school_signup_', -1)
WHERE external_tags = 0
  AND tag LIKE 'school_signup_%';

• 规则2匹配：tag格式为course_enroll_{var1value}且external_tags=0

UPDATE table1
SET tag2 = 'course_enroll',
    var1 = SUBSTRING_INDEX(tag, 'course_enroll_', -1)
WHERE external_tags = 0
  AND tag LIKE 'course_enroll_%';

• 规则3匹配：tag格式为automation_notify_bounced_{var1value}_{var2value}且external_tags=0

UPDATE table1
SET tag2 = 'automation_notify_bounced',
    var1 = SUBSTRING_INDEX(SUBSTRING_INDEX(tag, 'automation_notify_bounced_', -1), '_', 1),
    var2 = SUBSTRING_INDEX(SUBSTRING_INDEX(tag, 'automation_notify_bounced_', -1), '_', -1)
WHERE external_tags = 0
  AND tag LIKE 'automation_notify_bounced_%\_%';

• 规则4匹配：tag格式为registered_livestream_co_host_{var1value}_schedule_{var2value}且external_tags=0

UPDATE table1
SET tag2 = 'registered_livestream_co_host',
    var1 = SUBSTRING_INDEX(SUBSTRING_INDEX(tag, 'registered_livestream_co_host_', -1), '_schedule_', 1),
    var2 = SUBSTRING_INDEX(tag, '_schedule_', -1)
WHERE external_tags = 0
  AND tag LIKE 'registered_livestream_co_host_%\_schedule_%';

• 规则5匹配：tag格式为attended_recurring_live_class_{var1value}_rec_schedule_{var2value}且external_tags=0

UPDATE table1
SET tag2 = 'attended_recurring_live_class',
    var1 = SUBSTRING_INDEX(SUBSTRING_INDEX(tag, 'attended_recurring_live_class_', -1), '_rec_schedule_', 1),
    var2 = SUBSTRING_INDEX(tag, '_rec_schedule_', -1)
WHERE external_tags = 0
  AND tag LIKE 'attended_recurring_live_class_%\_rec_schedule_%';

• 规则6匹配：external_tags=1的场景

UPDATE table1
SET tag2 = tag
WHERE external_tags = 1;

注意：执行更新操作前建议先通过SELECT语句测试WHERE条件的匹配结果，或提前备份表数据，避免误操作导致数据异常。

内容的提问来源于stack exchange，提问作者Renji