shanshanzhong
7d5b4fcb84
All checks were successful
Build docker and publish / build (20.15.1) (push) Successful in 7m13s
110 lines
4.0 KiB
SQL
110 lines
4.0 KiB
SQL
-- ============================================================
|
||
-- 修复脚本：为有多设备但无家庭组的用户补建家庭组
|
||
-- 影响用户数：30
|
||
-- 每个用户：2 个设备，0 个家庭组
|
||
-- 执行前请先备份！
|
||
-- ============================================================
|
||
|
||
-- ============================================================
|
||
-- Step 0: 确认受影响数据（只读，不做任何修改）
|
||
-- ============================================================
|
||
SELECT
|
||
d.user_id,
|
||
COUNT(*) as device_count,
|
||
GROUP_CONCAT(d.id ORDER BY d.id) as device_ids
|
||
FROM user_device d
|
||
LEFT JOIN user_family_member fm ON fm.user_id = d.user_id AND fm.status = 1
|
||
WHERE d.enabled = 1 AND fm.id IS NULL
|
||
GROUP BY d.user_id
|
||
HAVING device_count > 1
|
||
ORDER BY d.user_id;
|
||
|
||
-- 预期结果：30 行
|
||
|
||
-- ============================================================
|
||
-- Step 1: 为每个用户创建 user_family（家庭组）
|
||
-- owner_user_id = user_id, max_members = 2, status = 1(active)
|
||
-- ============================================================
|
||
INSERT INTO user_family (owner_user_id, max_members, status, created_at, updated_at)
|
||
SELECT
|
||
d.user_id,
|
||
2, -- max_members = 2（当前都是 2 设备）
|
||
1, -- status = active
|
||
MIN(d.created_at), -- 用最早设备的创建时间
|
||
NOW()
|
||
FROM user_device d
|
||
LEFT JOIN user_family_member fm ON fm.user_id = d.user_id AND fm.status = 1
|
||
LEFT JOIN user_family f ON f.owner_user_id = d.user_id AND f.deleted_at IS NULL
|
||
WHERE d.enabled = 1
|
||
AND fm.id IS NULL -- 没有 active 家庭成员记录
|
||
AND f.id IS NULL -- 没有已存在的家庭
|
||
GROUP BY d.user_id
|
||
HAVING COUNT(*) > 1;
|
||
|
||
-- 预期影响：30 行
|
||
|
||
-- ============================================================
|
||
-- Step 2: 为每个用户创建 user_family_member（owner 身份）
|
||
-- role = 1(owner), status = 1(active), join_source = 'data_fix'
|
||
-- ============================================================
|
||
INSERT INTO user_family_member (family_id, user_id, role, status, join_source, joined_at, created_at, updated_at)
|
||
SELECT
|
||
f.id, -- 刚创建的 family_id
|
||
f.owner_user_id, -- user_id
|
||
1, -- role = owner
|
||
1, -- status = active
|
||
'data_fix', -- 标记来源，方便追溯
|
||
f.created_at, -- joined_at = family 创建时间
|
||
NOW(),
|
||
NOW()
|
||
FROM user_family f
|
||
LEFT JOIN user_family_member fm ON fm.user_id = f.owner_user_id AND fm.status = 1
|
||
WHERE fm.id IS NULL -- 还没有 active 家庭成员记录
|
||
AND f.deleted_at IS NULL
|
||
AND f.owner_user_id IN (
|
||
-- 只处理我们目标用户
|
||
SELECT d.user_id
|
||
FROM user_device d
|
||
WHERE d.enabled = 1
|
||
GROUP BY d.user_id
|
||
HAVING COUNT(*) > 1
|
||
);
|
||
|
||
-- 预期影响：30 行
|
||
|
||
-- ============================================================
|
||
-- Step 3: 验证修复结果
|
||
-- ============================================================
|
||
|
||
-- 3a. 确认所有多设备用户都有了家庭组
|
||
SELECT
|
||
d.user_id,
|
||
COUNT(DISTINCT d.id) as device_count,
|
||
f.id as family_id,
|
||
f.max_members,
|
||
fm.role,
|
||
fm.status as member_status,
|
||
fm.join_source
|
||
FROM user_device d
|
||
JOIN user_family f ON f.owner_user_id = d.user_id AND f.deleted_at IS NULL
|
||
JOIN user_family_member fm ON fm.user_id = d.user_id AND fm.status = 1
|
||
WHERE d.enabled = 1
|
||
AND fm.join_source = 'data_fix'
|
||
GROUP BY d.user_id, f.id, f.max_members, fm.role, fm.status, fm.join_source
|
||
ORDER BY d.user_id;
|
||
|
||
-- 预期结果：30 行，每行 device_count=2, role=1, member_status=1
|
||
|
||
-- 3b. 确认没有遗漏（多设备无家庭组的用户应该为 0）
|
||
SELECT COUNT(*) as remaining_orphans
|
||
FROM (
|
||
SELECT d.user_id
|
||
FROM user_device d
|
||
LEFT JOIN user_family_member fm ON fm.user_id = d.user_id AND fm.status = 1
|
||
WHERE d.enabled = 1 AND fm.id IS NULL
|
||
GROUP BY d.user_id
|
||
HAVING COUNT(*) > 1
|
||
) orphans;
|
||
|
||
-- 预期结果：0
|