升鲜宝 SaaS Admin：SYS & SAAS 常用 CRUD SQL 示例（用于对账/排查/脚本）
-- =========================================================
-- 升鲜宝 SaaS Admin：SYS & SAAS 常用 CRUD SQL 示例（用于对账/排查/脚本）
-- 数据库：saas_master_db
--
-- 说明：
-- 1) 生产建议仍走 API + Service（SQL 仅示例）
-- 2) “禁用”使用 enabled=0；“软删除”使用 del_flag=1
-- 3) create_date / update_date 统一毫秒时间戳
-- =========================================================
SET NAMES utf8mb4;
SET @now_ms := ROUND(UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)) * 1000);-- =========================================================
-- 1) 用户 sys_user
-- =========================================================-- 1.1 用户列表（分页 + 关键字）
-- :keyword 为空则不过滤；:offset=: (page-1)*size；:size
SELECTu.id,u.user_code,u.user_name,u.status,u.enabled,u.del_flag,u.create_date,u.update_date
FROM sys_user u
WHERE u.del_flag = 0AND (:keyword IS NULL OR :keyword = ''OR u.user_code LIKE CONCAT('%', :keyword, '%')OR u.user_name LIKE CONCAT('%', :keyword, '%'))
ORDER BY u.id DESC
LIMIT :offset, :size;-- 1.2 用户详情
SELECTid, user_code, user_name, password_hash, status, enabled, del_flag, create_date, update_date
FROM sys_user
WHERE id = :userId
LIMIT 1;-- 1.3 新增用户（password_hash 建议用 BCrypt）
-- 注意：user_code 需唯一
INSERT INTO sys_user (id, user_code, user_name, password_hash,status, enabled, del_flag,create_date, update_date
)
VALUES (:id, :userCode, :userName, :passwordHash,1, 1, 0,@now_ms, @now_ms
);-- 1.4 修改用户（不改密码）
UPDATE sys_user
SETuser_name = :userName,status = :status,enabled = :enabled,update_date = @now_ms
WHERE id = :userIdAND del_flag = 0;-- 1.5 修改密码（重置）
UPDATE sys_user
SETpassword_hash = :passwordHash,update_date = @now_ms
WHERE id = :userIdAND del_flag = 0;-- 1.6 禁用 / 启用
UPDATE sys_user
SET enabled = 0, update_date = @now_ms
WHERE id = :userId AND del_flag = 0;UPDATE sys_user
SET enabled = 1, update_date = @now_ms
WHERE id = :userId AND del_flag = 0;-- 1.7 软删除
UPDATE sys_user
SET del_flag = 1, update_date = @now_ms
WHERE id = :userId;-- 1.8 用户角色列表
SELECTur.user_id,ur.role_id,r.role_code,r.role_name
FROM sys_user_role ur
JOIN sys_role r ON r.id = ur.role_id AND r.del_flag = 0
WHERE ur.user_id = :userId
ORDER BY r.id;-- 1.9 给用户分配角色（先删后插，或用事务）
DELETE FROM sys_user_role WHERE user_id = :userId;
INSERT INTO sys_user_role (id, user_id, role_id, create_date)
VALUES(:id1, :userId, :roleId1, @now_ms),(:id2, :userId, :roleId2, @now_ms);-- =========================================================
-- 2) 角色 sys_role
-- =========================================================-- 2.1 角色列表（分页 + 关键字）
SELECTr.id, r.role_code, r.role_name, r.enabled, r.del_flag, r.create_date, r.update_date
FROM sys_role r
WHERE r.del_flag = 0AND (:keyword IS NULL OR :keyword = ''OR r.role_code LIKE CONCAT('%', :keyword, '%')OR r.role_name LIKE CONCAT('%', :keyword, '%'))
ORDER BY r.id DESC
LIMIT :offset, :size;-- 2.2 新增角色
INSERT INTO sys_role (id, role_code, role_name,enabled, del_flag,create_date, update_date
)
VALUES (:id, :roleCode, :roleName,1, 0,@now_ms, @now_ms
);-- 2.3 修改角色
UPDATE sys_role
SETrole_name = :roleName,enabled = :enabled,update_date = @now_ms
WHERE id = :roleIdAND del_flag = 0;-- 2.4 禁用 / 启用
UPDATE sys_role SET enabled = 0, update_date = @now_ms WHERE id = :roleId AND del_flag = 0;
UPDATE sys_role SET enabled = 1, update_date = @now_ms WHERE id = :roleId AND del_flag = 0;-- 2.5 软删除
UPDATE sys_role SET del_flag = 1, update_date = @now_ms WHERE id = :roleId;-- =========================================================
-- 3) 菜单 sys_menu
-- =========================================================-- 3.1 菜单全量（用于构树）
SELECTid, parent_id, menu_type, menu_code, menu_name,route_path, component_path, perms_code, icon,sort_code, visible, enabled, del_flag
FROM sys_menu
WHERE del_flag = 0 AND enabled = 1
ORDER BY sort_code ASC, id ASC;-- 3.2 新增菜单
INSERT INTO sys_menu (id, parent_id, menu_type,menu_code, menu_name,route_path, component_path,perms_code, icon,sort_code, visible, enabled, del_flag,create_date, update_date
)
VALUES (:id, :parentId, :menuType,:menuCode, :menuName,:routePath, :componentPath,:permsCode, :icon,:sortCode, :visible, :enabled, 0,@now_ms, @now_ms
);-- 3.3 修改菜单
UPDATE sys_menu
SETparent_id = :parentId,menu_type = :menuType,menu_name = :menuName,route_path = :routePath,component_path = :componentPath,perms_code = :permsCode,icon = :icon,sort_code = :sortCode,visible = :visible,enabled = :enabled,update_date = @now_ms
WHERE id = :menuId AND del_flag = 0;-- 3.4 软删除菜单
UPDATE sys_menu SET del_flag = 1, update_date = @now_ms WHERE id = :menuId;-- =========================================================
-- 4) 权限点 sys_permission / 授权 sys_role_permission
-- =========================================================-- 4.1 权限点列表（分页 + 关键字）
SELECTp.id, p.perm_type, p.perm_code, p.perm_name, p.perm_value,p.menu_id, p.enabled, p.del_flag, p.create_date, p.update_date
FROM sys_permission p
WHERE p.del_flag = 0AND (:keyword IS NULL OR :keyword = ''OR p.perm_code LIKE CONCAT('%', :keyword, '%')OR p.perm_name LIKE CONCAT('%', :keyword, '%')OR p.perm_value LIKE CONCAT('%', :keyword, '%'))
ORDER BY p.id DESC
LIMIT :offset, :size;-- 4.2 新增权限点
INSERT INTO sys_permission (id, perm_type, perm_code, perm_name, perm_value,menu_id, enabled, del_flag,create_date, update_date
)
VALUES (:id, :permType, :permCode, :permName, :permValue,:menuId, 1, 0,@now_ms, @now_ms
);-- 4.3 修改权限点
UPDATE sys_permission
SETperm_type = :permType,perm_name = :permName,perm_value = :permValue,menu_id = :menuId,enabled = :enabled,update_date = @now_ms
WHERE id = :permId AND del_flag = 0;-- 4.4 禁用 / 启用权限点
UPDATE sys_permission SET enabled=0, update_date=@now_ms WHERE id=:permId AND del_flag=0;
UPDATE sys_permission SET enabled=1, update_date=@now_ms WHERE id=:permId AND del_flag=0;-- 4.5 软删除权限点
UPDATE sys_permission SET del_flag=1, update_date=@now_ms WHERE id=:permId;-- 4.6 查询角色拥有的权限点
SELECTrp.role_id,rp.permission_id,p.perm_code,p.perm_name,p.perm_value
FROM sys_role_permission rp
JOIN sys_permission p ON p.id = rp.permission_id AND p.del_flag=0
WHERE rp.role_id = :roleId
ORDER BY p.id;-- 4.7 角色授权（先删后插）
DELETE FROM sys_role_permission WHERE role_id = :roleId;
INSERT INTO sys_role_permission (id, role_id, permission_id, create_date)
VALUES(:id1, :roleId, :permId1, @now_ms),(:id2, :roleId, :permId2, @now_ms);-- =========================================================
-- 5) SaaS 套餐/租户/订阅（saas_plan / saas_tenant / saas_subscription）
-- =========================================================-- 5.1 套餐列表
SELECT id, plan_code, plan_name, billing_cycle, currency_code, base_price, enabled, del_flag
FROM saas_plan
WHERE del_flag=0
ORDER BY id DESC
LIMIT :offset, :size;-- 5.2 新增/修改/禁用套餐
INSERT INTO saas_plan (id, plan_code, plan_name, billing_cycle, currency_code, base_price, enabled, del_flag, create_date, update_date)
VALUES (:id, :planCode, :planName, :billingCycle, :currencyCode, :basePrice, 1, 0, @now_ms, @now_ms);UPDATE saas_plan
SET plan_name=:planName, billing_cycle=:billingCycle, currency_code=:currencyCode, base_price=:basePrice, enabled=:enabled, update_date=@now_ms
WHERE id=:planId AND del_flag=0;UPDATE saas_plan SET enabled=0, update_date=@now_ms WHERE id=:planId AND del_flag=0;
UPDATE saas_plan SET enabled=1, update_date=@now_ms WHERE id=:planId AND del_flag=0;-- 5.3 租户列表
SELECT id, tenant_code, tenant_name, tenant_state, plan_id, expire_date, shard_id, db_name, del_flag
FROM saas_tenant
WHERE del_flag=0
ORDER BY id DESC
LIMIT :offset, :size;-- 5.4 新增/修改/冻结/解冻租户
INSERT INTO saas_tenant (id, tenant_code, tenant_name, tenant_state, plan_id, expire_date, shard_id, db_name, del_flag, create_date, update_date)
VALUES (:id, :tenantCode, :tenantName, 1, :planId, :expireDate, :shardId, :dbName, 0, @now_ms, @now_ms);UPDATE saas_tenant
SET tenant_name=:tenantName, plan_id=:planId, expire_date=:expireDate, shard_id=:shardId, db_name=:dbName, update_date=@now_ms
WHERE id=:tenantId AND del_flag=0;UPDATE saas_tenant SET tenant_state=2, update_date=@now_ms WHERE id=:tenantId AND del_flag=0; -- 冻结
UPDATE saas_tenant SET tenant_state=1, update_date=@now_ms WHERE id=:tenantId AND del_flag=0; -- 解冻-- 5.5 订阅列表（按租户）
SELECT id, tenant_id, plan_id, sub_state, auto_renew, start_date, end_date, del_flag
FROM saas_subscription
WHERE del_flag=0 AND (:tenantId IS NULL OR tenant_id=:tenantId)
ORDER BY id DESC
LIMIT :offset, :size;-- 5.6 新增/修改订阅
INSERT INTO saas_subscription (id, tenant_id, plan_id, sub_state, auto_renew, start_date, end_date, del_flag, create_date, update_date)
VALUES (:id, :tenantId, :planId, :subState, :autoRenew, :startDate, :endDate, 0, @now_ms, @now_ms);UPDATE saas_subscription
SET plan_id=:planId, sub_state=:subState, auto_renew=:autoRenew, start_date=:startDate, end_date=:endDate, update_date=@now_ms
WHERE id=:subId AND del_flag=0;