mysql - 我需要对 mysql 数据库进行某种全文搜索

Question

我遇到了一个非常棘手的问题。

我有来自不同仓库的产品 list ，其中每个产品都有:品牌和型号以及一些额外的详细信息。 Model 同一个产品在不同的仓库可能会有很大的不同，但是 Brand 总是一样的。

我将所有产品列表存储在一个表中，假设它将是 Product 表。然后我有另一个表 - Model，其中包含 CORRECT Model Name、Brand 和图像、描述等其他详细信息。此外，我还有关键字列，其中我尝试手动添加所有关键字。

这就是问题所在，我需要将我从仓库收到的每件产品与我的 Model 表中的一条记录相关联。现在我正在 bool 模式下使用全文搜索，但这很痛苦，而且效果不佳。我需要做很多体力劳动。

以下是我的几个名字示例:

• WINT.SPORT3D
• WINT.SPORT3D XL
• WINT.SPORT 3D
• WINT.SPORT3D MO
• 冬季运动 3D

所有这些项目的正确名称应该是:WINTER SPORT 3D，所以它们应该都分配给同一个模型。

那么，有什么方法可以改进全文搜索或其他一些技术来解决我的问题吗？

我正在使用的数据库是 MySQL，我不想更改它。

Answer 1

我将从对表进行更正式的定义开始:

warehouse:
    warehouse_id,
    warehouse_product_id,
    product_brand,
    product_name,
    local_id

在这里，我将使用 local_id 作为“模型”表的外键 - 但为了避免进一步混淆，我将其称为“本地”

local:
    id,
    product_brand,
    product_name

您描述为“产品”的表格似乎是多余的。

显然，在交叉引用数据之前，local_id 将为空。但在它被填充后就不必改变了，给定一个 warehouse_id、一个 band 和一个产品，你可以很容易地找到你的本地描述符:

SELECT local.*
FROM local, warehouse
WHERE local.id=warehouse.local_id
AND warehouse.product_brand=local.product_brand
AND warehouse_id=_____
AND warehouse.product_brand=____
AND warehouse.product_name=____

所以您需要做的就是填充链接。 Soundex 是一个相当粗糙的工具 - 一个更好的解决方案是 Levenstein distance algorithm .有一个 mysql implementation here

给定仓库表中需要填充的一组行:

SELECT w.*
FROM warehouse w
WHERE w.local_id IS NULL;

...对于每一行，将最佳匹配标识为(使用上一个查询中的值作为 w.*)...

SELECT local.id
FROM local
WHERE local.product_brand=w.product_brand
ORDER BY levenstein(local.product_name, w.product_name) ASC
LIMIT 0,1

但这会找到最佳匹配，即使 2 个字符串完全不同!因此....

SELECT local.id
FROM local
WHERE local.product_brand=w.product_brand
AND levenstein(local.product_name, w.product_name)<
    (IF LENGTH(local.product_name)<LENGTH(w.product_name),
          LENGTH(local.product_name), LENGTH(w.product_name))/2
ORDER BY levenstein(local.product_name, w.product_name) ASC
LIMIT 0,1

...至少需要一半的字符串才能匹配。

因此这可以在单个更新语句中实现:

UPDATE warehouse w
SET local_id=(
   SELECT local.id
   FROM local
   WHERE local.product_brand=w.product_brand
   AND levenstein(local.product_name, w.product_name)<
    (IF LENGTH(local.product_name)<LENGTH(w.product_name),
          LENGTH(local.product_name), LENGTH(w.product_name))/2
   ORDER BY levenstein(local.product_name, w.product_name) ASC
   LIMIT 0,1
 )
 WHERE local_id IS NULL;