267 lines
7.7 KiB
Markdown
267 lines
7.7 KiB
Markdown
## 业务库清理说明
|
||
数据库：safety-eval-service
|
||
不清理的表：
|
||
basic_discipline_major
|
||
industry_professional_standards
|
||
org_business_scope_device_ref
|
||
|
||
|
||
|
||
## 租户数据清理操作文档
|
||
|
||
### 一、清理条件定义
|
||
|
||
执行前根据实际场景修改以下条件的值，并将修改后的值替换到下方所有 SQL 中。
|
||
|
||
| 条件 | 当前值 | 说明 |
|
||
|------|--------|------|
|
||
| 目标租户ID | `2067161116369793024` | 所有 SQL 中 `tenant_id =` 后面的值 |
|
||
| 排除账号 | `'cqanquan','15100110011','jianguan001'` | 所有 SQL 中 `NOT IN (...)` 里的账号列表 |
|
||
|
||
**修改方式**：在文档中全局替换。例如要清理另一个租户并调整排除账号，将所有 SQL 中的 `2067161116369793024` 替换为目标租户ID，将 `NOT IN ('cqanquan','15100110011','jianguan001')` 替换为实际的排除账号列表。
|
||
|
||
---
|
||
|
||
### 二、操作原则
|
||
|
||
1. **主表即删除目标**：每条 SELECT 的主表就是要删除数据的表，SELECT 列出的每一行就是将被 DELETE 删除的行。
|
||
2. **先查询后删除**：每个步骤必须先执行 SELECT 确认影响范围，核对无误后再执行对应的 DELETE。
|
||
3. **关联逻辑一致**：SELECT 和 DELETE 使用完全相同的 FROM/JOIN/WHERE 结构，只将 SELECT 替换为 DELETE 主表别名。
|
||
4. **按依赖顺序清理**：先删关联表（子表），再删主表，避免数据不一致。
|
||
5. **单步执行**：每一步独立执行并确认结果，不要批量粘贴所有 SQL。
|
||
|
||
---
|
||
|
||
### 三、清理步骤
|
||
|
||
#### 步骤 1：用户关联角色
|
||
|
||
- **执行库**：jjb-saas-system
|
||
- **主表（删除目标）**：sys_role_user
|
||
- **辅助表（过滤条件）**：sys_user
|
||
|
||
**1.1 查询确认**
|
||
|
||
```sql
|
||
SELECT ru.id, ru.user_id, ru.role_id
|
||
FROM sys_role_user ru
|
||
INNER JOIN sys_user u ON ru.user_id = u.id
|
||
WHERE u.tenant_id = 2067161116369793024
|
||
AND u.account NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
**1.2 执行删除**
|
||
|
||
```sql
|
||
DELETE ru FROM sys_role_user ru
|
||
INNER JOIN sys_user u ON ru.user_id = u.id
|
||
WHERE u.tenant_id = 2067161116369793024
|
||
AND u.account NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
---
|
||
|
||
#### 步骤 2：用户关联部门
|
||
|
||
- **执行库**：jjb-saas-system
|
||
- **主表（删除目标）**：sys_user_role_dept
|
||
- **辅助表（过滤条件）**：sys_user
|
||
|
||
**2.1 查询确认**
|
||
|
||
```sql
|
||
SELECT urd.id, urd.user_id, urd.role_id, urd.dept_id
|
||
FROM sys_user_role_dept urd
|
||
INNER JOIN sys_user u ON urd.user_id = u.id
|
||
WHERE u.tenant_id = 2067161116369793024
|
||
AND u.account NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
**2.2 执行删除**
|
||
|
||
```sql
|
||
DELETE urd FROM sys_user_role_dept urd
|
||
INNER JOIN sys_user u ON urd.user_id = u.id
|
||
WHERE u.tenant_id = 2067161116369793024
|
||
AND u.account NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
---
|
||
|
||
#### 步骤 3：角色关联权限组
|
||
|
||
- **执行库**：jjb-saas-system
|
||
- **主表（删除目标）**：sys_role_perms_group
|
||
- **辅助表（过滤条件）**：sys_role_user → sys_user
|
||
|
||
**3.1 查询确认**
|
||
|
||
```sql
|
||
SELECT rpg.id, rpg.role_id, rpg.group_id
|
||
FROM sys_role_perms_group rpg
|
||
INNER JOIN sys_role_user ru ON rpg.role_id = ru.role_id
|
||
INNER JOIN sys_user u ON ru.user_id = u.id
|
||
WHERE u.tenant_id = 2067161116369793024
|
||
AND u.account NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
**3.2 执行删除**
|
||
|
||
```sql
|
||
DELETE rpg FROM sys_role_perms_group rpg
|
||
INNER JOIN sys_role_user ru ON rpg.role_id = ru.role_id
|
||
INNER JOIN sys_user u ON ru.user_id = u.id
|
||
WHERE u.tenant_id = 2067161116369793024
|
||
AND u.account NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
---
|
||
|
||
#### 步骤 4：角色数据
|
||
|
||
- **执行库**：jjb-saas-system
|
||
- **主表（删除目标）**：sys_role
|
||
- **辅助表（过滤条件）**：sys_role_user → sys_user
|
||
|
||
**4.1 查询确认**
|
||
|
||
```sql
|
||
SELECT r.id, r.role_name, r.role_code
|
||
FROM sys_role r
|
||
INNER JOIN sys_role_user ru ON r.id = ru.role_id
|
||
INNER JOIN sys_user u ON ru.user_id = u.id
|
||
WHERE u.tenant_id = 2067161116369793024
|
||
AND u.account NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
**4.2 执行删除**
|
||
|
||
```sql
|
||
DELETE r FROM sys_role r
|
||
INNER JOIN sys_role_user ru ON r.id = ru.role_id
|
||
INNER JOIN sys_user u ON ru.user_id = u.id
|
||
WHERE u.tenant_id = 2067161116369793024
|
||
AND u.account NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
---
|
||
|
||
#### 步骤 5：用户数据
|
||
|
||
- **执行库**：jjb-saas-system
|
||
- **主表（删除目标）**：sys_user
|
||
- **辅助表**：无
|
||
|
||
**5.1 查询确认**
|
||
|
||
```sql
|
||
SELECT id, account, name, mobile, email, status_enum, create_time
|
||
FROM sys_user
|
||
WHERE tenant_id = 2067161116369793024
|
||
AND account NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
**5.2 执行删除**
|
||
|
||
```sql
|
||
DELETE FROM sys_user
|
||
WHERE tenant_id = 2067161116369793024
|
||
AND account NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
---
|
||
|
||
#### 步骤 6：账号租户关联
|
||
|
||
- **执行库**：jjb-saas-auth
|
||
- **主表（删除目标）**：auth_account_tenant_rel
|
||
- **辅助表（过滤条件）**：auth_account
|
||
|
||
**6.1 查询确认**
|
||
|
||
```sql
|
||
SELECT atr.id, atr.account_id, atr.tenant_id
|
||
FROM auth_account_tenant_rel atr
|
||
INNER JOIN auth_account a ON atr.account_id = a.id
|
||
WHERE atr.tenant_id = 2067161116369793024
|
||
AND a.username NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
**6.2 执行删除**
|
||
|
||
```sql
|
||
DELETE atr FROM auth_account_tenant_rel atr
|
||
INNER JOIN auth_account a ON atr.account_id = a.id
|
||
WHERE atr.tenant_id = 2067161116369793024
|
||
AND a.username NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
---
|
||
|
||
#### 步骤 7：账号租户密码
|
||
|
||
- **执行库**：jjb-saas-auth
|
||
- **主表（删除目标）**：auth_account_tenant_password
|
||
- **辅助表（过滤条件）**：auth_account
|
||
|
||
**7.1 查询确认**
|
||
|
||
```sql
|
||
SELECT atp.id, atp.account_id, a.username, atp.user_type_enum, atp.status_enum
|
||
FROM auth_account_tenant_password atp
|
||
INNER JOIN auth_account a ON atp.account_id = a.id
|
||
WHERE atp.tenant_id = 2067161116369793024
|
||
AND a.username NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
**7.2 执行删除**
|
||
|
||
```sql
|
||
DELETE atp FROM auth_account_tenant_password atp
|
||
INNER JOIN auth_account a ON atp.account_id = a.id
|
||
WHERE atp.tenant_id = 2067161116369793024
|
||
AND a.username NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
---
|
||
|
||
#### 步骤 8：账号数据
|
||
|
||
- **执行库**：jjb-saas-auth
|
||
- **主表（删除目标）**：auth_account
|
||
- **辅助表**：无
|
||
|
||
**8.1 查询确认**
|
||
|
||
```sql
|
||
SELECT id, username, mobile, name, status_enum, delete_enum
|
||
FROM auth_account
|
||
WHERE tenant_id = 2067161116369793024
|
||
AND username NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
**8.2 执行删除**
|
||
|
||
```sql
|
||
DELETE FROM auth_account
|
||
WHERE tenant_id = 2067161116369793024
|
||
AND username NOT IN ('cqanquan','15100110011','jianguan001');
|
||
```
|
||
|
||
---
|
||
|
||
### 四、清理顺序总览
|
||
|
||
| 顺序 | 步骤 | 库 | 主表（删除目标） | 辅助表 | 说明 |
|
||
|------|------|-----|-----------------|--------|------|
|
||
| 1 | 用户关联角色 | jjb-saas-system | sys_role_user | sys_user | 解除用户与角色的绑定 |
|
||
| 2 | 用户关联部门 | jjb-saas-system | sys_user_role_dept | sys_user | 解除用户与部门的绑定 |
|
||
| 3 | 角色关联权限组 | jjb-saas-system | sys_role_perms_group | sys_role_user → sys_user | 解除角色与权限组的绑定 |
|
||
| 4 | 角色数据 | jjb-saas-system | sys_role | sys_role_user → sys_user | 删除角色本身 |
|
||
| 5 | 用户数据 | jjb-saas-system | sys_user | 无 | 删除用户记录 |
|
||
| 6 | 账号租户关联 | jjb-saas-auth | auth_account_tenant_rel | auth_account | 解除账号与租户的绑定 |
|
||
| 7 | 账号租户密码 | jjb-saas-auth | auth_account_tenant_password | auth_account | 删除租户级密码数据 |
|
||
| 8 | 账号数据 | jjb-saas-auth | auth_account | 无 | 删除账号记录 |
|
||
|
||
> **注意**：权限组（sys_perms_group）和菜单（sys_menu）属于租户级资源，不随用户删除而删除。如需清理权限组和菜单，需额外执行。
|