网站模糊搜索 php,php – 什么是最容易实现的网站搜索应用程序,它支持模糊搜索？...

最新推荐文章于 2021-06-04 22:40:01 发布

玩弄命运

最新推荐文章于 2021-06-04 22:40:01 发布

阅读量98

点赞数

文章标签：网站模糊搜索 php

ewemli的答案是正确的方向,但你应该结合FULLTEXT和soundex映射,而不是替换全文,否则你的LIKE查询可能会非常慢.

create table with_soundex (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

original TEXT,

soundex TEXT,

FULLTEXT (soundex)

);

insert into with_soundex (original, soundex) values

('add some test cases', CONCAT_WS(' ', soundex('add'), soundex('some'), soundex('test'), soundex('cases'))),

('this is some text', CONCAT_WS(' ', soundex('this'), soundex('is'), soundex('some'), soundex('text'))),

('one more test case', CONCAT_WS(' ', soundex('one'), soundex('more'), soundex('test'), soundex('case'))),

('just filling the index', CONCAT_WS(' ', soundex('just'), soundex('filling'), soundex('the'), soundex('index'))),

('need one more example', CONCAT_WS(' ', soundex('need'), soundex('one'), soundex('more'), soundex('example'))),

('seems to need more', CONCAT_WS(' ', soundex('seems'), soundex('to'), soundex('need'), soundex('more')))

('some helpful cases to consider', CONCAT_WS(' ', soundex('some'), soundex('helpful'), soundex('cases'), soundex('to'), soundex('consider')))

select * from with_soundex where match(soundex) against (soundex('test'));

+----+---------------------+---------------------+

| id | original | soundex |

+----+---------------------+---------------------+

| 1 | add some test cases | A300 S500 T230 C000 |

| 2 | this is some text | T200 I200 S500 T230 |

| 3 | one more test case | O500 M600 T230 C000 |

+----+---------------------+---------------------+

select * from with_soundex where match(soundex) against (CONCAT_WS(' ', soundex('test'), soundex('some')));

+----+--------------------------------+---------------------------+

| id | original | soundex |

+----+--------------------------------+---------------------------+

| 1 | add some test cases | A300 S500 T230 C000 |

| 2 | this is some text | T200 I200 S500 T230 |

| 3 | one more test case | O500 M600 T230 C000 |

| 7 | some helpful cases to consider | S500 H414 C000 T000 C5236 |

+----+--------------------------------+---------------------------+

这样可以获得非常好的结果(在soundex算法的范围内),同时最大限度地利用索引(任何查询LIKE’％foo’都必须扫描表中的每一行).

注意在每个单词上运行soundex的重要性,而不是整个短语.您也可以在每个单词上运行自己的soundex版本,而不是让SQL执行它,但在这种情况下,请确保在存储和检索时都执行此操作,以防算法之间存在差异(例如,MySQL的算法不限制)本身到标准4 chars)

关注