分类层级验证SQL查询优化：新增分类双规则校验需求

需求概述 #

需为Node.js环境下的分类创建功能实现两项验证规则：

• 若传入parentId，则该parentId必须是categories表中存在的有效ID
• 同一层级（相同parentId）的分类名称不能重复

现有categories表结构及数据：

id  | parentId  | name  
   -----|-----------|------
    1   | NULL      | CatA
    2   | 1         | CatA.1

禁止操作 #

• 使用无效的parentId
• 创建parentId为NULL且名称为CatA的分类
• 创建parentId为1且名称为CatA.1的分类

错误类型 #

需返回两类验证错误：

• 提供的parentId无效
• 检测到重复名称

优化后的单SQL验证查询 #

以下SQL可一次性完成两项规则的验证，通过查询结果即可判断验证状态：

SELECT
    -- 验证parentId是否有效：有效返回1，无效返回0
    CASE 
        WHEN (@parentId IS NULL) OR EXISTS (SELECT 1 FROM categories WHERE id = @parentId) 
        THEN 1 
        ELSE 0 
    END AS is_parent_valid,
    -- 验证同层级名称是否重复：重复返回1，不重复返回0
    CASE 
        WHEN EXISTS (SELECT 1 FROM categories WHERE parentId = @parentId AND name = @category_name) 
        THEN 1 
        ELSE 0 
    END AS is_name_duplicate

使用逻辑 #

1. 将查询中的@parentId和@category_name替换为创建分类时的实际参数（若未传入parentId，则传入NULL）
2. 执行查询后，根据返回值判断：
   • 若is_parent_valid = 0：说明传入的parentId无效
   • 若is_name_duplicate = 1：说明当前层级下已存在同名分类
   • 若is_parent_valid = 1且is_name_duplicate = 0：验证通过，可执行分类创建操作

内容的提问来源于stack exchange，提问作者StormTrooper