如何基于ENUM筛选数据，无筛选值时返回全部行？

我正在开发一个项目资源管理应用，资源表包含多个字段，其中安全许可等级是ENUM类型，定义如下：

CREATE TYPE "clearance" AS ENUM (
  'None',
  'Baseline',
  'NV1',
  'NV2',
  'TSPV'
);

资源表结构：

CREATE TABLE "resource" (
  "employee_id" integer PRIMARY KEY,
  "name" varchar NOT NULL,
  "email" varchar NOT NULL,
  "job_title_id" integer NOT NULL,
  "manager_id" integer NOT NULL,
  "workgroup_id" integer NOT NULL,
  "clearance_level" clearance,
  "specialties" text[],
  "certifications" text[],
  "active" boolean DEFAULT 't'
);

查询时希望通过URL参数筛选，比如：

curl localhost:6543/v1/resources # 返回分页的全部资源
curl localhost:6543/v1/resources?specialties=NSX  # 返回拥有NSX专长的所有资源
curl localhost:6543/v1/resources?manager=John+Smith # 返回向John Smith汇报的资源
curl localhost:6543/v1/resources?jobTitle=Senior+Consultant # 返回所有高级顾问

也需要支持安全许可等级筛选：

curl localhost:6543/v1/resources?clearance=NV2

当提供clearance参数时查询正常工作，但未提供时无法合理实现返回全部结果。目前临时方案是通过拼接不同的筛选条件来实现，但代码有明显坏味道：

func (m *ResourceModel) GetAll(name string, workgroups []string, clearance string, specialties []string,
    certifications []string, manager string, active bool, filters Filters) ([]*Resource, Metadata, error) {
    // 代码异味
    // 当需要返回所有许可等级时，不得不使用一个空的筛选参数
    // 还没找到在ENUM字段上实现“无筛选则返回全部”的好方法
    var clearance_filter = `AND (r.name = $2 OR $2 = '')`
    if clearance != "" {
        clearance_filter = `AND (r.clearance_level = $2::clearance)`
    }

    query := fmt.Sprintf(`
        SELECT count(*) OVER(), r.employee_id, r.name, r.email, job_title.title, m.name AS manager, workgroup.workgroup_name, r.clearance_level, r.specialties, r.certifications, r.active
        FROM (((resource r
            INNER JOIN job_title ON r.job_title_id=job_title.title_id)
            INNER JOIN resource m ON r.manager_id=m.employee_id)
            INNER JOIN workgroup ON workgroup.workgroup_id=r.workgroup_id)
        WHERE (workgroup.workgroup_name = ANY($1) OR $1 = '{}')
        %s
        AND (r.specialties @> $3 OR $3 = '{}')
        AND (r.certifications @> $4 OR $4 = '{}')
        AND (m.name = $5 OR $5 = '')
        AND (r.active = $6)
        AND (r.name = $7 OR $7 = '')
        ORDER BY %s %s, r.employee_id ASC
        LIMIT $8 OFFSET $9`, clearance_filter, fmt.Sprintf("r.%s", filters.sortColumn()), filters.sortDirection())
...
...
}

是否有更优的实现方式？我甚至考虑放弃ENUM类型，改成独立的许可等级表：

CREATE TABLE clearance (
 "level" varchar NOT NULL
);

1. 统一WHERE条件逻辑，避免动态SQL拼接 #

不需要动态生成筛选片段，直接在WHERE子句中同时处理“无筛选”和“有筛选”两种情况：当clearance参数为空时，条件恒成立；否则匹配对应的ENUM值。这种方式完全消除代码异味，同时降低SQL注入风险。

修改后的SQL核心条件：

WHERE (workgroup.workgroup_name = ANY($1) OR $1 = '{}')
AND ($2 = '' OR r.clearance_level = $2::clearance)
AND (r.specialties @> $3 OR $3 = '{}')
AND (r.certifications @> $4 OR $4 = '{}')
AND (m.name = $5 OR $5 = '')
AND (r.active = $6)
AND (r.name = $7 OR $7 = '')

对应的Go代码简化为：

func (m *ResourceModel) GetAll(name string, workgroups []string, clearance string, specialties []string,
    certifications []string, manager string, active bool, filters Filters) ([]*Resource, Metadata, error) {
    baseQuery := `
        SELECT count(*) OVER(), r.employee_id, r.name, r.email, job_title.title, m.name AS manager, workgroup.workgroup_name, r.clearance_level, r.specialties, r.certifications, r.active
        FROM (((resource r
            INNER JOIN job_title ON r.job_title_id=job_title.title_id)
            INNER JOIN resource m ON r.manager_id=m.employee_id)
            INNER JOIN workgroup ON workgroup.workgroup_id=r.workgroup_id)
        WHERE (workgroup.workgroup_name = ANY($1) OR $1 = '{}')
        AND ($2 = '' OR r.clearance_level = $2::clearance)
        AND (r.specialties @> $3 OR $3 = '{}')
        AND (r.certifications @> $4 OR $4 = '{}')
        AND (m.name = $5 OR $5 = '')
        AND (r.active = $6)
        AND (r.name = $7 OR $7 = '')
        ORDER BY r.%s %s, r.employee_id ASC
        LIMIT $8 OFFSET $9`

    // 仅格式化排序相关部分
    formattedQuery := fmt.Sprintf(baseQuery, filters.sortColumn(), filters.sortDirection())

    // 直接传入参数执行查询
    rows, err := m.db.Query(formattedQuery, workgroups, clearance, specialties, certifications, manager, active, filters.limit(), filters.offset())
    // 后续处理逻辑...
}

2. 用NULL标识“无筛选”（更语义化的方案） #

如果空字符串可能存在业务歧义，可以将clearance参数改为指针类型，未提供筛选时传入nil，SQL中判断参数是否为NULL：

AND ($2 IS NULL OR r.clearance_level = $2::clearance)

对应的Go代码调整：

func (m *ResourceModel) GetAll(name string, workgroups []string, clearance *string, specialties []string,
    certifications []string, manager string, active bool, filters Filters) ([]*Resource, Metadata, error) {
    // ... 核心SQL与上述类似，将$2的判断改为IS NULL
    rows, err := m.db.Query(formattedQuery, workgroups, clearance, specialties, certifications, manager, active, filters.limit(), filters.offset())
}

这种方式语义更清晰，NULL明确表示“未提供筛选条件”。

3. 无需放弃ENUM类型 #

ENUM类型在PostgreSQL中优势明显：能强制保证数据合法性（仅允许存入预定义的许可等级），查询效率高，且扩展方便——未来新增等级只需执行ALTER TYPE clearance ADD VALUE 'new_level';即可。改成独立表反而会增加关联查询复杂度，还需要额外约束保证数据一致性，完全没必要。

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