MariaDB跨多表含行存元数据通过JOIN实现INSERT/UPDATE操作问题

实现方案 #

你可以通过单条INSERT语句完成需求，核心逻辑是两次关联行存储的元数据表，分别取出用户名和手机号字段，拼接为目标表需要的列格式即可：

INSERT INTO dbc_jot_groupmembers (jot_grpid, jot_bbmemid, jot_grpmemname, jot_grpmemnum, jot_grpmemts)
SELECT 
  g.group_id AS jot_grpid,
  g.user_id AS jot_bbmemid,
  name_prof.value AS jot_grpmemname,
  -- 按照示例要求拼接国家码前缀，不需要可以直接取phone_prof.value
  CONCAT('+1', phone_prof.value) AS jot_grpmemnum,
  -- 可根据需求替换为源表的时间字段，比如g.date_modified
  NOW() AS jot_grpmemts
FROM dbc_bp_groups_members g
-- 关联取用户名（field_id=3）
LEFT JOIN dbc_bp_xprofile_data name_prof 
  ON g.user_id = name_prof.user_id AND name_prof.field_id = 3
-- 关联取手机号（field_id=4）
LEFT JOIN dbc_bp_xprofile_data phone_prof 
  ON g.user_id = phone_prof.user_id AND phone_prof.field_id = 4
-- 若需要过滤掉无姓名/无手机号的用户，取消注释下面的条件
-- WHERE name_prof.value IS NOT NULL AND phone_prof.value IS NOT NULL;

可选增量同步方案 #

如果后续需要同步源数据变更到目标表，可先给目标表加唯一联合索引，再用ON DUPLICATE KEY UPDATE实现插入+更新二合一：

1. 先建唯一索引

ALTER TABLE dbc_jot_groupmembers ADD UNIQUE KEY idx_uid_gid (jot_bbmemid, jot_grpid);

2. 同步语句

INSERT INTO dbc_jot_groupmembers (jot_grpid, jot_bbmemid, jot_grpmemname, jot_grpmemnum, jot_grpmemts)
SELECT 
  g.group_id AS jot_grpid,
  g.user_id AS jot_bbmemid,
  name_prof.value AS jot_grpmemname,
  CONCAT('+1', phone_prof.value) AS jot_grpmemnum,
  NOW() AS jot_grpmemts
FROM dbc_bp_groups_members g
LEFT JOIN dbc_bp_xprofile_data name_prof 
  ON g.user_id = name_prof.user_id AND name_prof.field_id = 3
LEFT JOIN dbc_bp_xprofile_data phone_prof 
  ON g.user_id = phone_prof.user_id AND phone_prof.field_id = 4
ON DUPLICATE KEY UPDATE
  jot_grpmemname = VALUES(jot_grpmemname),
  jot_grpmemnum = VALUES(jot_grpmemnum),
  jot_grpmemts = NOW();

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