[SQL] 일부 단어 MySQL의 전체 텍스트 검색

일부 단어 MySQL의 전체 텍스트 검색

MySQL의 전체 텍스트 검색은 위대하고 SQL에서 검색하는 가장 좋은 방법이 될 것으로 보인다. 그러나, 나는이 부분 단어를 검색하지 않습니다 있다는 사실에 붙어 것 같다. 나는 "MySQL의 튜토리얼"라는 제목의 기사를 가지고 "MySQL의"를 검색하면 예를 들어, 그것을 찾을 수 없습니다.

나는 (내가 5.1.40을 사용하고 있습니다) MySQL의 4에 나오는이 지원에 대한 다양한 참조를 발견 몇 가지 검색을 수행하는 데. 나는 (I 제안 발견 하나 개의 링크는 별했지만, 당신은 단지 마지막에 그것을 또는 둘 다하지 시작 수) 일을 "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 %에서 존재"라는 단어가 있기 때문에 검색 결과가 비어 있습니다 "따라서, 그것은 효과적으로 중지 단어로 취급되는 대용량 데이터 세트의 경우, 이것이 가장 바람직한 행동은 다음과 같습니다.. 자연 언어 질의 1GB의 테이블에서 모든 제 2 열을 반환 할 것이다. 작은 데이터 세트의, 덜 바람직 할 수있다. "

해결법

1. ==============================

   1.나의 이해는 MySQL의 FULLTEXT 인덱스는 접두사 (MATCH (a.article_name)에 대한 ( 'MySQL의 *'IN BOOLEAN MODE를)) 만 검색을 지원한다는 것입니다.

   나의 이해는 MySQL의 FULLTEXT 인덱스는 접두사 (MATCH (a.article_name)에 대한 ( 'MySQL의 *'IN BOOLEAN MODE를)) 만 검색을 지원한다는 것입니다.

2. ==============================

   2.MySQL의 설명서에서 인용 : MySQL은 "행의 50 % 이상에 존재한다." "말씀이 때문에 검색 결과가 비어 있습니다" http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

   MySQL의 설명서에서 인용 : MySQL은 "행의 50 % 이상에 존재한다." "말씀이 때문에 검색 결과가 비어 있습니다" http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

from https://stackoverflow.com/questions/2716748/mysql-full-text-search-with-partial-words by cc-by-sa and MIT license