Prisma+PostgreSQL全文搜索失效问题求助（NestJS项目）

问题背景 #

使用Prisma + PostgreSQL的NestJS项目中，全文搜索遇到以下异常：

1. UUID搜索：前缀4bb631af能匹配结果，但中间片段f437-4816无结果；
2. 手机号搜索：完整号码+911234567891能匹配，但去掉+的911234567891或子串567891无结果。

原Prisma Schema #

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id              String            @id @default(uuid())
  phoneNumber     String            @unique
}

测试数据 #

[
  {
    "id": "a6affa5b-a819-4637-9e9f-f788a02e4594",
    "phoneNumber": "+911234567891"
  },
  {
    "id": "39443bb5-bbb5-43c8-b165-135fe24d3e82",
    "phoneNumber": "+919876543211"
  },
  {
    "id": "4bb631af-f437-4816-8f35-d356a174011f",
    "phoneNumber": "+915678912345"
  }
]

问题原因 #

PostgreSQL的全文搜索基于分词和完整词匹配，默认规则导致：

1. UUID中的-会被当作分词分隔符，将UUID拆分为4bb631af、f437、4816等独立词，搜索f437-4816相当于要求同时匹配两个独立词，默认不会关联匹配；
2. 手机号中的+会被识别为非词字符过滤，剩下911234567891作为单个词，但全文搜索只匹配完整词，不支持子串匹配，所以567891无法命中。

解决方案 #

方案一：用contains+Trgm索引实现子串搜索（推荐） #

如果需求是子串匹配（而非语义化全文搜索），直接使用Prisma的contains操作符，配合PostgreSQL的gin_trgm_ops索引优化性能，是最简单高效的方式。

1. 修改Prisma Schema添加索引

model User {
  id              String            @id @default(uuid())
  phoneNumber     String            @unique
  // 添加GIN索引加速子串搜索
  @@index([id], type: Gin, ops: [gin_trgm_ops])
  @@index([phoneNumber], type: Gin, ops: [gin_trgm_ops])
}

执行prisma migrate dev生成并应用索引。

2. 编写查询代码

• 搜索UUID中间片段：

const users = await prisma.user.findMany({
  where: {
    id: { contains: "f437-4816" }
  }
});

• 搜索手机号子串：

const users = await prisma.user.findMany({
  where: {
    phoneNumber: { contains: "567891" }
  }
});

方案二：调整全文搜索配置（适合语义搜索场景） #

如果必须使用全文搜索，需要自定义PostgreSQL的文本搜索配置，修改分词规则：

1. 在PostgreSQL中创建自定义配置

执行以下SQL：

-- 基于默认english配置创建自定义规则
CREATE TEXT SEARCH CONFIGURATION custom_config (COPY = english);
-- 修改分词映射，让连字符、数字片段不被拆分
ALTER TEXT SEARCH CONFIGURATION custom_config
ALTER MAPPING FOR word, numword, hword, numhword, hword_part, numhword_part
WITH simple;

2. 使用原生SQL执行全文搜索

// 搜索UUID中间片段
const users = await prisma.$queryRaw`
  SELECT * FROM "User"
  WHERE to_tsvector('custom_config', "id") @@ to_tsquery('custom_config', 'f437 & 4816')
`;

// 搜索手机号子串
const users = await prisma.$queryRaw`
  SELECT * FROM "User"
  WHERE to_tsvector('custom_config', "phoneNumber") @@ to_tsquery('custom_config', '567891')
`;

总结 #

• 子串匹配场景优先选方案一，配置简单、性能优异；
• 语义化全文搜索场景再考虑方案二，但需要额外维护文本搜索配置。

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