Полнотекстовый поиск в MySQL - отличный вариант для поиска в SQL. Тем не менее, я, кажется, застрял на том факте, что он не будет искать отдельные слова. Например, если у меня есть статья под названием «Учебник по MySQL» и поиск по «MySQL», она не будет найдена.

Сделав некоторые поиски, я нашел различные ссылки для поддержки этого в MySQL 4 (я использую 5.1.40). Я пытался использоватьMySQL"и"% MySQL% ", но ни одна из них не работает (одна найденная ссылка показала, что это звезды, но вы можете сделать это только в конце или в начале, но не в обоих случаях).

Вот моя структура таблицы и мой запрос, если кто-то может сказать мне, где я иду не так, это было бы здорово. Я предполагаю, что частичное совпадение слов как-то встроено.

CREATE TABLE IF NOT EXISTS `articles` (
  `article_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `article_name` varchar(64) NOT NULL,
  `article_desc` text NOT NULL,
  `article_link` varchar(128) NOT NULL,
  `article_hits` int(11) NOT NULL,
  `article_user_hits` int(7) unsigned NOT NULL DEFAULT '0',
  `article_guest_hits` int(10) unsigned NOT NULL DEFAULT '0',
  `article_rating` decimal(4,2) NOT NULL DEFAULT '0.00',
  `article_site_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `article_time_added` int(10) unsigned NOT NULL,
  `article_discussion_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `article_source_type` varchar(12) NOT NULL,
  `article_source_value` varchar(12) NOT NULL,
  PRIMARY KEY (`article_id`),
  FULLTEXT KEY `article_name` (`article_name`,`article_desc`,`article_link`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

INSERT INTO `articles` VALUES
(1, 'MySQL Tutorial', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 6, 3, 1, '1.50', 1, 1269702050, 1, '0', '0'),
(2, 'How To Use MySQL Well', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 1, 2, 0, '3.00', 1, 1269702050, 1, '0', '0'),
(3, 'Optimizing MySQL', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 0, 1, 0, '3.00', 1, 1269702050, 1, '0', '0'),
(4, '1001 MySQL Tricks', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 0, 1, 0, '3.00', 1, 1269702050, 1, '0', '0'),
(5, 'MySQL vs. YourSQL', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 0, 2, 0, '3.00', 1, 1269702050, 1, '0', '0'),
(6, 'MySQL Security', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 0, 2, 0, '3.00', 1, 1269702050, 1, '0', '0');

SELECT count(a.article_id) FROM articles a

            WHERE MATCH (a.article_name, a.article_desc, a.article_link) AGAINST ('mysql')
            GROUP BY a.article_id
            ORDER BY a.article_time_added ASC

Префикс используется, поскольку он происходит от функции, которая иногда добавляет дополнительные объединения.

Как видите, поиск по MySQL должен возвращать счетчик 6, но, к сожалению, это не так.

Обновить

Нет результатов, которые возвращаются, так как каждая строка соответствует.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-natural-language.html

«Результат поиска пуст, потому что слово« MySQL »присутствует как минимум в 50% строк. Таким образом, оно эффективно обрабатывается как стоп-слово. Для больших наборов данных это наиболее желательное поведение: запрос на естественном языке не должен возвращать каждую вторую строку из таблицы размером 1 ГБ. Для небольших наборов данных это может быть менее желательно. "