MySQL MATCH AGAINST无法正常工作 [英] MySQL MATCH AGAINST not working

问题描述

我实际上正在一个有两个表的网站上工作，几乎两个表完全相同，而MATCH AGAINST可以在一个表上工作，而不能在另一个表上工作.为了弄清楚为什么我试图将其简化为一个简单的让我们创建一个简单的表"，但事实并非如此.

I'm actually working on a website where there are two tables, almost identical, where MATCH AGAINST works on one but not on the other. To find out why I've tried to reduce this to a simple, "Let's make a simple table work" - but it doesn't.

我正在用phpMyAdmin进行测试，这是MySQL 5.1.41.

I'm doing testing with phpMyAdmin and this is MySQL 5.1.41.

我构建的测试使用以下内容来定义表...

The test I build used the following to define the table...

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL,
  `title` text NOT NULL,
  `body` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ALTER TABLE `test` ADD FULLTEXT (`title`, `body`);

当我从测试中进行SHOW INDEX时，我看到有一个包含标题和正文的FULLTEXT键.

and when I do a SHOW INDEX FROM test I see that there is a FULLTEXT key comprising title and body.

我用插入几行

INSERT INTO `test` (`id`, `title`, `body`) VALUES 
('1', 'Lorem Ipsum', 'Lorem ipsum dolor sit amet, consectetur ... lacus porta euismod.'), 
('2', 'Lorem Ipsum (cont)', 'Nunc leo massa, vulputate ... euismod fringilla.');

(为简洁起见，删除了一些身体内容)

(some body content removed for brevity)

然后当我跑步

SELECT * FROM `test` WHERE MATCH (`title`, `body`) AGAINST ('consectetur');

我得到一个空结果集-没有找到行，但是如果我运行

I get an empty result set - no rows found but if I run

SELECT * FROM `test` WHERE `body` LIKE '%consectetur%';

然后找到一条记录.

尽管我在MySQL方面有很多经验，但这是我第一次使用MATCH，所以我会做些愚蠢的事情吗?为什么这不起作用?是否需要建立索引(我已经在表上进行了REPAIR修复)还是应该全部自动进行?

Although I've a lot of experience with MySQL, this is the first time I've used MATCH so am I doing something daft? Why is this not working? Does the index need to be built (I've done a REPAIR on the table) or is it all supposed to happen automatically?

仅供参考，有效表定义为

Just for information, the table which works is defined with

CREATE TABLE IF NOT EXISTS `web_pages1` (
  `id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `slug` varchar(255) DEFAULT NULL,
  `meta_keywords` text,
  `meta_description` text,
  `snippet` text,
  `body` mediumtext,
  `created_by` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_published` date DEFAULT NULL,
  `author` varchar(255) DEFAULT NULL,
  `edited_by` int(11) DEFAULT NULL,
  `date_edited` datetime DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '1',
  `parent_id` tinyint(11) DEFAULT NULL,
  `menu_id` int(11) DEFAULT NULL,
  `short_name` varchar(255) DEFAULT NULL,
  `sort_order` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `search` (`title`,`slug`,`meta_keywords`,`meta_description`,`snippet`,`body`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

是导致我出现问题并将我带到这条路线的那个人

and the one which is causing me problems and sent me down this route is

CREATE TABLE IF NOT EXISTS `web_news1` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `meta_keywords` text,
  `meta_description` text,
  `snippet` text NOT NULL,
  `body` text NOT NULL,
  `created_by` int(11) NOT NULL,
  `date_created` datetime NOT NULL,
  `date_published` date DEFAULT NULL,
  `author` varchar(255) DEFAULT NULL,
  `edited_by` int(11) DEFAULT NULL,
  `date_edited` datetime DEFAULT NULL,
  `status` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `search` (`title`,`slug`,`meta_keywords`,`meta_description`,`snippet`,`body`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

尽管我尝试像定义第一个一样完全定义第二个，但是仍然行不通.

Although I've tried defining the second one exactly like the first and still it doesn't work.

推荐答案

您需要在末尾添加IN BOOLEAN MODE

SELECT * FROM `test` WHERE MATCH (title, body) AGAINST ('Ipsum' IN BOOLEAN MODE);

请在这里看看:

http://www.sqlfiddle.com/#!2/1b80b/2

这篇关于MySQL MATCH AGAINST无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！