22 lines
1.0 KiB
MySQL
22 lines
1.0 KiB
MySQL
|
-- =============================================
|
||
|
|
-- 租户-项目两级架构 - 默认数据迁移
|
||
|
|
-- 为每个现有租户创建默认项目，为所有用户关联默认项目
|
||
|
|
-- =============================================
|
||
|
|
|
||
|
|
-- Step 1: 为每个现有租户创建默认项目
|
||
|
|
INSERT INTO system_project (tenant_id, name, code, status, remark, creator, updater, deleted)
|
||
|
|
SELECT id, name, 'DEFAULT', 0, '系统自动生成默认项目', 'system', 'system', 0
|
||
|
|
FROM system_tenant WHERE deleted = 0;
|
||
|
|
|
||
|
|
-- Step 2: 为所有用户关联默认项目
|
||
|
|
INSERT INTO system_user_project (user_id, project_id, tenant_id, creator, updater, deleted)
|
||
|
|
SELECT u.id, p.id, u.tenant_id, 'system', 'system', 0
|
||
|
|
FROM system_users u
|
||
|
|
JOIN system_project p ON p.tenant_id = u.tenant_id AND p.code = 'DEFAULT' AND p.deleted = 0
|
||
|
|
WHERE u.deleted = 0;
|
||
|
|
|
||
|
|
-- 验证
|
||
|
|
SELECT '默认项目数量' AS metric, COUNT(*) AS value FROM system_project WHERE code = 'DEFAULT' AND deleted = 0
|
||
|
|
UNION ALL
|
||
|
|
SELECT '用户-项目关联数量', COUNT(*) FROM system_user_project WHERE deleted = 0;
|