按Soundex(或类似名称)`Closeness`排序 [英] Sort By Soundex (or similar) `Closeness`

问题描述

有什么办法可以使MySQL与搜索词接近"多近?

Is there any way to have MySQL order results by how close they 'sound' to a search term?

我正在尝试订购包含用户输入的城市名称的字段.存在变体和拼写错误，我想在顶部显示最接近"的匹配项.

I'm trying to order fields that contain user input of city names. Variations and misspellings exist, and I'd like to show the 'closest' matches at the top.

我知道soundex可能不是最好的算法，但是如果它(或另一种方法)可以合理地成功-由数据库进行排序可能是值得的.

I know soundex may not be the best algorithm for this, but if it (or another method) could be reasonable successful - it may be worth having the sorting done by the database.

推荐答案

Soundex不适用于此类事情，因为不同的单词可以为您提供相同的Soundex结果，因此可以任意排序.一个更好的解决方案是Levenshein编辑距离算法，您也许可以在数据库中将其实现为一个函数:链接到 Levensheint展示.作为MySql存储功能 !!!

Soundex is no good for this sort of thing because different words can give you the same Soundex results and will therefore sort arbitrarily. A better solution for this is the Levenshein Edit Distance algorithm and you may be able to implement it as a function in your database: Link to Levensheint impl. as MySql stored function!!!

您还可以查看此 SO链接.它包含该算法的Sql服务器(特定于T-SQL)实现，但应该可以移植.该算法的机制非常简单，只需要一个2D数组并在字符串上循环即可.

You can also check out this SO link. It contains a Sql server (T-SQL-specific) implementation of the algorithm but it should be possible to port. The mechanics of the algorithm are fairly simple needing only a 2D array and looping over string.

这篇关于按Soundex(或类似名称)`Closeness`排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！