MySQL将最高匹配到最低匹配的最佳方法是什么？(MySQL what would the best approach to ranking highest to lowest possible ma

MySQL将最高匹配到最低匹配的最佳方法是什么？(MySQL what would the best approach to ranking highest to lowest possible match?)

我有一个我正在搜索的MySQL数据库。 让我们说这是一个人的数据库。 查询特定记录时，可以在每个属性上找到100％的匹配项。 但查询数据库以找到最接近的概率匹配（表属性上最接近的匹配）更多的是策略。

在这种情况下，创建临时表（非常类似于计数表）以指示哪些属性匹配/存在哪些属性是否有意义？ 在这样的数据库上进行高级搜索的典型方法是什么？

假设存储过程的示例（下面）

*参数只是为了举例说明我的搜索方式。 我不关心如何执行我的选择。 问题是关于方法，策略，技术*

call FindPerson ("Brown Eyes", "Brown hair", "Height:6'1", "white", "Name:Joe" ,"weight180", "Age 34" "sex m"); RESULT TABLE NAME AGE HEIGHT WEIGHT HAIR SKIN sex RANK_MATCH Joe 32 6'1 180 Brown white m 1 Mike 33 6'1 179 Brown white m 2 James 31 6'0 179 Brown black m 3

I have a MySQL database I'm searching through. Lets say this is a database of people. When querying for a specific record, it is possible to find a match 100% on each attribute. But querying the database to find closest match on probability (closest matches on table attributes) is more of the strategy.

In this scenario, does it make sense to create a temporary table (much like a tally-sheet) to indicate what attributes match/what attributes are present? What is the typical approach to doing advanced searches on database like this?

Example (below) of a hypothetical stored Procedure

*parameters are just to exemplify how I would search. I'm not concerned how to perform my selects. Question is about approach, strategy, technique *

call FindPerson ("Brown Eyes", "Brown hair", "Height:6'1", "white", "Name:Joe" ,"weight180", "Age 34" "sex m"); RESULT TABLE NAME AGE HEIGHT WEIGHT HAIR SKIN sex RANK_MATCH Joe 32 6'1 180 Brown white m 1 Mike 33 6'1 179 Brown white m 2 James 31 6'0 179 Brown black m 3

最满意答案

只是出于我的想法。 您可以创建自己的分数并按其排序。 就像是

SELECT `id`, (IF(`age`=32,1,0)+IF(`height`="6'1",1,0)+...) as `score` FROM `people` HAVING `score` > 0 ORDER BY `score` DESC LIMIT 10;

通过这种方式，您可以通过自己的比较处理每个字段，并且还可以通过不仅添加1而是添加2或更多来对各个属性进行加权。 但我很安静，不确定，这是多么高效。

Just out of my mind. You can create your own score and sort by it. Something like

SELECT `id`, (IF(`age`=32,1,0)+IF(`height`="6'1",1,0)+...) as `score` FROM `people` HAVING `score` > 0 ORDER BY `score` DESC LIMIT 10;

With this, you can handle every field with its own comparison, and also weight the individual attributes by not just add 1 but 2 or more. But I'm quiet not sure, how performant this is.