我们的网站上有一个使用 MySQL 全文搜索的简单搜索,但由于某种原因,它似乎没有返回正确的结果。我不知道这是否是 Amazon RDS(我们的数据库服务器所在的位置)或我们请求的查询的某种问题。
这是数据库表的结构:
CREATE TABLE `items` (
`object_id` int(9) unsigned NOT NULL DEFAULT '0',
`slug` varchar(100) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`object_id`),
FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
这是对该表的简单全文搜索查询和返回的结果:
select object_id ,slug,name from items where MATCH (name) AGAINST ('+ski*' IN BOOLEAN MODE) order by name;
+-----------+-----------------------------------+------------------+
| object_id | slug | name |
+-----------+-----------------------------------+------------------+
| 10146041 | us/new-hampshire/dartmouth-skiway | Dartmouth Skiway |
+-----------+-----------------------------------+------------------+
如果我改为使用 LIKE 我会得到一组不同的结果:
select object_id,slug,name from items where name LIKE "%ski%" order by name;
+-----------+------------------------------------------+----------------------------------+
| object_id | slug | name |
+-----------+------------------------------------------+----------------------------------+
| 10146546 | us/new-york/brantling-ski | Brantling Ski |
| 10146548 | us/new-york/buffalo-ski-club | Buffalo Ski Club |
| 10146041 | us/new-hampshire/dartmouth-skiway | Dartmouth Skiway |
| 10146352 | us/montana/discover-ski | Discover Ski |
| 10144882 | us/california/donner-ski-ranch | Donner Ski Ranch |
| 10146970 | us/new-york/hickory-ski-center | Hickory Ski Center |
| 10146973 | us/new-york/holimont-ski-area | Holimont Ski Area |
| 10146283 | us/minnesota/hyland-ski | Hyland Ski |
| 10145911 | us/nevada/las-vegas-ski-snowboard-resort | Las Vegas Ski & Snowboard Resort |
| 10146977 | us/new-york/maple-ski-ridge | Maple Ski Ridge |
| 10146774 | us/oregon/mount-hood-ski-bowl | Mt. Hood Ski Bowl |
| 10145949 | us/new-mexico/sipapu-ski | Sipapu Ski |
| 10145952 | us/new-mexico/ski-apache | Ski Apache |
| 10146584 | us/north-carolina/ski-beech | Ski Beech |
| 10147973 | canada/quebec/ski-bromont | Ski Bromont |
| 10146106 | us/michigan/ski-brule | Ski Brule |
| 10145597 | us/massachusetts/ski-butternut | Ski Butternut |
| 10145117 | us/colorado/ski-cooper | Ski Cooper |
| 10146917 | us/pennsylvania/ski-denton | Ski Denton |
| 10145954 | us/new-mexico/ski-santa-fe | Ski Santa Fe |
| 10146918 | us/pennsylvania/ski-sawmill | Ski Sawmill |
| 10145299 | us/illinois/ski-snowstar | Ski Snowstar |
| 10145138 | us/connecticut/ski-sundown | Ski Sundown |
| 10145598 | us/massachusetts/ski-ward | Ski Ward |
+-----------+------------------------------------------+----------------------------------+
我完全不知道为什么使用全文搜索的查询不起作用。我希望一些 MySQL 专家能够指出我们查询中的错误。
在此先感谢您的帮助!