MySQL 自然语言搜索没有像我希望的那样工作
我有一张人们的全名表。我希望用户能够通过部分名称和拼写错误的名称进行搜索。因此，搜索“Andrew”也应该返回“Andrea”等。我认为 FULLTEXT 搜索就是答案，但它的工作方式似乎与我使用 搜索没有任何不同...就像“%Andrew%”。
MySQL 中是否有函数或特性可以根据字符串相似度进行搜索？或者我是否必须使用 levenshtein() 或类似的东西在 PHP 端自行推出？
给定这个表：
CREATE TABLE `people` (
`FullName` varchar(30) default NULL,
`namesID` int(11) NOT NULL auto_increment,
PRIMARY KEY (`namesID`),
FULLTEXT KEY `fulltext_FullName` (`FullName`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
LOCK TABLES `people` WRITE;
/*!40000 ALTER TABLE `people` DISABLE KEYS */;
INSERT INTO `people` (`FullName`,`namesID`)
VALUES
('Mark Peters',1),
('Bob Jackson',2),
('Steve Kipp',3),
('Joe Runty',4),
('Tina Mardell',5),
('Tim Havers',6),
('Rich Beckett',7),
('Mary Dalson',8),
('Maria Grento',9),
('Michael Colt',10),
('Andrew Peters',11),
('Andre Bison',12),
('Andrea Masters',13),
('Marla Tool',14);
/*!40000 ALTER TABLE `people` ENABLE KEYS */;
UNLOCK TABLES;
和这个查询：
SELECT *
FROM people
WHERE MATCH(FullName) AGAINST('Andrew');
我只得到：
FullName namesID
Andrew Peters 11
当我也想得到：
Andre Bison
Andrea Masters
等。
I have a table of people's Full Names. I'd like users to be able to search it by partial names and misspelled names. So a search for 'Andrew' should also return 'Andrea', etc. I thought FULLTEXT search was the answer but it doesn't seem to work any differently than if I'd searched using ... LIKE '%Andrew%'.
Is there a function or feature in MySQL that will search based on string similarity? Or will I have to roll my own on the PHP end using levenshtein() or something similar?
Given This Table:
CREATE TABLE `people` (
`FullName` varchar(30) default NULL,
`namesID` int(11) NOT NULL auto_increment,
PRIMARY KEY (`namesID`),
FULLTEXT KEY `fulltext_FullName` (`FullName`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
LOCK TABLES `people` WRITE;
/*!40000 ALTER TABLE `people` DISABLE KEYS */;
INSERT INTO `people` (`FullName`,`namesID`)
VALUES
('Mark Peters',1),
('Bob Jackson',2),
('Steve Kipp',3),
('Joe Runty',4),
('Tina Mardell',5),
('Tim Havers',6),
('Rich Beckett',7),
('Mary Dalson',8),
('Maria Grento',9),
('Michael Colt',10),
('Andrew Peters',11),
('Andre Bison',12),
('Andrea Masters',13),
('Marla Tool',14);
/*!40000 ALTER TABLE `people` ENABLE KEYS */;
UNLOCK TABLES;
And This Query:
SELECT *
FROM people
WHERE MATCH(FullName) AGAINST('Andrew');
I only get:
FullName namesID
Andrew Peters 11
When I'd like to also get:
Andre Bison
Andrea Masters
etc.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
FULLTEXT 索引只不过是全文索引。它们只允许搜索您实际拥有的文本。
MySQL 确实有一个 SOUNDEX() 函数和一个简写 x SOUNDS LIKE y 运算符，它与 SOUNDEX(x) = SOUNDEX(y)< 相同/代码>。
如果 soundex 不能满足您的需求，您确实需要使用 PHP 等编程语言来完成您想要的任务。
FULLTEXT indices are nothing more than indices on the full text. They only allow searches on the text you actually have.
MySQL does have a
SOUNDEX()function, and a shorthandx SOUNDS LIKE yoperator, which is the same asSOUNDEX(x) = SOUNDEX(y).If soundex doesn't meet your needs, you would indeed need to involve a programming language like PHP to accomplish what you want.