71 lines
3.6 KiB
SQL
71 lines
3.6 KiB
SQL
-- 修复重复朋友圈配置，并保证同一用户最多只有一条有效配置。
|
|
-- 适用 MySQL 5.7 / 8.0。执行前选择业务库，并备份 member_recent_cfg。
|
|
-- 先部署兼容重复数据/唯一键冲突的代码；执行期间暂停配置写入（登录初始化、隐私设置、后台编辑）。
|
|
-- DDL 会隐式提交；如中途失败，修复原因后可重跑，不能依靠 ROLLBACK 撤销整个脚本。
|
|
-- 只逻辑删除较旧行，不重建默认配置，不修改保留行的隐私/通知选项。
|
|
-- member_id 引用 member_user 的全局主键，唯一键不额外拼接可能为 NULL 的 tenant_id。
|
|
|
|
-- 预检：完整展示重复配置，便于核对 home_hide/global_hide/privacy_secure/short_note/is_push 等实际字段。
|
|
-- 保留顺序与服务查询一致：update_time 最新优先，时间相同取 id 最大；NULL 更新时间排在最后。
|
|
SELECT cfg.*
|
|
FROM `member_recent_cfg` cfg
|
|
INNER JOIN (
|
|
SELECT `member_id`, COUNT(*) AS active_count
|
|
FROM `member_recent_cfg`
|
|
WHERE `deleted` = b'0'
|
|
GROUP BY `member_id`
|
|
HAVING COUNT(*) > 1
|
|
) duplicated ON duplicated.`member_id` = cfg.`member_id`
|
|
WHERE cfg.`deleted` = b'0'
|
|
ORDER BY cfg.`member_id`, cfg.`update_time` DESC, cfg.`id` DESC;
|
|
|
|
-- 先物化待删除 id，再更新，避免修改 update_time 影响同一批次的保留顺序。
|
|
-- GROUP BY 阻止 MySQL 5.7/8 合并派生表，避免 1093 自查询更新错误。
|
|
UPDATE `member_recent_cfg` obsolete
|
|
INNER JOIN (
|
|
SELECT older.`id`
|
|
FROM `member_recent_cfg` older
|
|
INNER JOIN `member_recent_cfg` newer
|
|
ON newer.`member_id` = older.`member_id`
|
|
AND newer.`deleted` = b'0'
|
|
AND (
|
|
newer.`update_time` > older.`update_time`
|
|
OR (older.`update_time` IS NULL AND newer.`update_time` IS NOT NULL)
|
|
OR (newer.`update_time` <=> older.`update_time` AND newer.`id` > older.`id`)
|
|
)
|
|
WHERE older.`deleted` = b'0'
|
|
GROUP BY older.`id`
|
|
) duplicate_ids ON duplicate_ids.`id` = obsolete.`id`
|
|
SET obsolete.`deleted` = b'1', obsolete.`update_time` = CURRENT_TIMESTAMP
|
|
WHERE obsolete.`deleted` = b'0';
|
|
SELECT ROW_COUNT() AS logically_deleted_duplicate_configs;
|
|
|
|
-- 已删除行生成 NULL，保留所有删除历史，同时允许以后重新创建配置。
|
|
SET @cfg_add_column_sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'member_recent_cfg' AND COLUMN_NAME = 'active_unique_flag') = 0,
|
|
'ALTER TABLE `member_recent_cfg` ADD COLUMN `active_unique_flag` tinyint GENERATED ALWAYS AS (CASE WHEN `deleted` = 0 THEN 1 ELSE NULL END) STORED COMMENT ''有效配置唯一标记，删除记录为NULL''',
|
|
'SELECT ''active_unique_flag already exists'' AS migration_step'
|
|
);
|
|
PREPARE cfg_stmt FROM @cfg_add_column_sql;
|
|
EXECUTE cfg_stmt;
|
|
DEALLOCATE PREPARE cfg_stmt;
|
|
|
|
SET @cfg_add_index_sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'member_recent_cfg' AND INDEX_NAME = 'uk_member_recent_cfg_active') = 0,
|
|
'ALTER TABLE `member_recent_cfg` ADD UNIQUE KEY `uk_member_recent_cfg_active` (`member_id`, `active_unique_flag`)',
|
|
'SELECT ''uk_member_recent_cfg_active already exists'' AS migration_step'
|
|
);
|
|
PREPARE cfg_stmt FROM @cfg_add_index_sql;
|
|
EXECUTE cfg_stmt;
|
|
DEALLOCATE PREPARE cfg_stmt;
|
|
|
|
-- 验收：应无重复分组；索引 NON_UNIQUE 应为 0。
|
|
SELECT `member_id`, COUNT(*) AS active_count
|
|
FROM `member_recent_cfg`
|
|
WHERE `deleted` = b'0'
|
|
GROUP BY `member_id`
|
|
HAVING COUNT(*) > 1;
|
|
SHOW INDEX FROM `member_recent_cfg` WHERE Key_name = 'uk_member_recent_cfg_active';
|