如何在数据库中为商品新增带匹配百分比的相似商品匹配字段

方案1：PostgreSQL（最推荐，内置原生支持相似度计算） #

PostgreSQL的pg_trgm扩展内置了三元组相似度计算能力，可以直接实现全表两两文本相似度匹配，不需要额外引入第三方服务。

操作步骤 #

1. 启用扩展

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. 导入现有商品数据到PostgreSQL表，表结构参考：

CREATE TABLE products (
    id INT PRIMARY KEY,
    title TEXT NOT NULL,
    price NUMERIC NOT NULL,
    matches JSON DEFAULT '[]'::JSON
);

3. （可选）数据量超过1万条时，提前建立GIN索引优化查询速度：

CREATE INDEX idx_products_title_trgm ON products USING GIN (title gin_trgm_ops);

4. 执行单条SQL即可完成全量匹配并写入matches字段：

WITH product_matches AS (
    SELECT 
        p1.id AS source_id,
        p2.id AS matched_id,
        similarity(p1.title, p2.title) AS match_score
    FROM products p1
    -- 排除自身匹配
    INNER JOIN products p2 ON p1.id != p2.id
    -- 自定义匹配得分阈值，0-1之间，数值越高匹配越严格
    WHERE similarity(p1.title, p2.title) >= 0.7
)
UPDATE products p
SET matches = COALESCE((
    SELECT JSON_AGG(JSON_BUILD_OBJECT('score', match_score, 'productId', matched_id))
    FROM product_matches pm
    WHERE pm.source_id = p.id
), '[]'::JSON);

方案2：MongoDB原生实现（无需换库） #

如果不想更换现有存储，可以使用MongoDB 5.0及以上版本内置的$levenshteinDistance编辑距离算子，配合Shell脚本实现全量匹配：

// 直接在Mongo Shell中执行即可
db.products.find({}).forEach(doc => {
  // 计算当前商品和其他所有商品的相似度
  const matches = db.products.aggregate([
    // 排除自身
    { $match: { id: { $ne: doc.id } } },
    // 计算相似度得分：(标题长度-编辑距离)/两个标题的最大长度，结果范围0-1
    {
      $addFields: {
        score: {
          $divide: [
            { $subtract: [ { $strLenCP: doc.title }, { $levenshteinDistance: { input1: doc.title, input2: "$title" } } ] },
            { $max: [ { $strLenCP: doc.title }, { $strLenCP: "$title" } ] }
          ]
        }
      }
    },
    // 过滤低于阈值的匹配
    { $match: { score: { $gte: 0.7 } } },
    // 输出符合matches字段要求的结构
    { $project: { productId: "$id", score: 1, _id: 0 } }
  ]).toArray();
  // 写入匹配结果
  db.products.updateOne({ id: doc.id }, { $set: { matches: matches } });
});

注：数据量超过10万条时建议分批次执行，避免长时间占用数据库资源。

方案3：Elasticsearch（适合百万级以上大规模数据集） #

如果数据量极大，推荐使用Elasticsearch存储数据，利用内置的BM25相似度算法和more_like_this查询实现毫秒级匹配，匹配精度和性能都远高于前两种方案。

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