MySQL 기초 튜토리얼 10 - 기능 전체 텍스트 검색 기능

구문:

• MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION])

MySQL은 전체 텍스트 인덱싱 및 검색 기능을 지원합니다. MySQL의 전체 텍스트 인덱스 유형 FULLTEXT 인덱스입니다. FULLTEXT 인덱스는 MyISAM 테이블에서만 사용할 수 있습니다. CREATE TABLE 문의 일부로 CHAR, VARCHAR 또는 TEXT 열에서 생성하거나 나중에 ALTER TABLE 또는 CREATE INDEX를 사용하여 추가할 수 있습니다. 더 큰 데이터 세트의 경우 FULLTEXT 인덱스가 없는 테이블에 데이터를 입력한 다음 인덱스를 생성하는 것이 기존 FULLTEXT 인덱스에 데이터를 입력하는 것보다 빠릅니다.

전체 텍스트 검색은 MATCH() 함수를 사용하여 수행됩니다.

mysql> CREATE TABLE articles (    
->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,    
->   title VARCHAR(200),    
->   body TEXT,    
->   FULLTEXT (title,body)    
-> );Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles (title,body) VALUES    
-> ('MySQL Tutorial','DBMS stands for DataBase ...'),    
-> ('How To Use MySQL Well','After you went through a ...'),    
-> ('Optimizing MySQL','In this tutorial we will show ...'),    
-> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),    
-> ('MySQL vs. YourSQL','In the following database comparison ...'),    
-> ('MySQL Security','When configured properly, MySQL ...');Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles    
-> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             | body          |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...            |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

MATCH() 함수는 데이터베이스 내에서 문자열에 대한 자연어 검색을 수행합니다. 데이터베이스는 FULLTEXT에 포함된 1개 또는 2개의 열 집합입니다. 검색 문자열은 AGAINST()에 인수로 제공됩니다. 테이블의 각 행에 대해 MATCH()는 상관 값, 즉 검색 문자열과 MATCH() 테이블의 지정된 열에 있는 해당 행의 텍스트 간의 유사성 측정값을 반환합니다.

기본적으로 검색은 대소문자를 구분하지 않고 수행됩니다. 그러나 인덱싱된 열에 대해 이진 정렬을 사용하면 대/소문자를 구분하는 전체 텍스트 검색을 수행할 수 있습니다. 예를 들어, latin1 문자 집합을 사용하는 열에 latin1_bin 정렬 방법을 제공하여 전체 텍스트 검색에서 대소문자를 구분하도록 할 수 있습니다.

위의 예와 같이 WHERE 문에서 MATCH()를 사용할 경우 해당 값은 음수가 아닌 부동 소수점 숫자입니다. 상관관계가 0이면 유사성이 없음을 의미합니다. 관련성 계산은 해당 줄의 단어 수, 해당 줄의 고유 항목 수, 데이터베이스의 총 단어 수 및 특정 단어가 포함된 파일(줄) 수를 기반으로 합니다.

자연어 전체 텍스트 검색의 경우 MATCH() 함수에 명명된 열이 테이블의 일부 FULLTEXT 인덱스에 포함된 열과 동일해야 합니다. 위 쿼리의 경우 MATCH() 함수에 명명된 열(제목 및 전체 텍스트)이 기사 테이블의 FULLTEXT 인덱스에 있는 열과 동일하다는 점에 유의하세요. 제목과 전문을 별도로 검색하려면 각 열에 FULLTEXT 인덱스를 생성해야 합니다.

또는 부울 검색을 실행하거나 쿼리 확장을 사용하여 검색하세요.

위의 예는 기본적으로 상관관계가 감소하는 순서로 행을 반환하는 MATCH() 함수를 사용하는 방법을 보여줍니다. 다음 예에서는 관련 값을 명시적으로 검색하는 방법을 보여줍니다. SELECT 문에 WHERE 또는 ORDER BY 절이 포함되어 있지 않기 때문에 반환된 행의 순서는 확실하지 않습니다.

mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial')    
-> FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
|  1 |                        0.65545833110809 |
|  2 |                                       0 |
|  3 |                        0.66266459226608 |
|  4 |                                       0 |
|  5 |                                       0 |
|  6 |                                       0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)

다음 예는 더 복잡합니다. 쿼리는 관련 값을 반환하고 관련성이 낮은 순서대로 행을 정렬합니다. 이 결과를 얻으려면 MATCH()를 두 번 지정해야 합니다. 한 번은 SELECT 목록에, 한 번은 WHERE 절에 지정해야 합니다. 이는 MySQL 최적화 프로그램이 두 개의 MATCH() 호출이 동일하다는 것을 인식하고 전체 텍스트 검색 코드를 한 번만 활성화하기 때문에 추가 관리 작업이 발생하지 않습니다.

mysql> SELECT id, body, MATCH (title,body) AGAINST    
-> ('Security implications of running MySQL as root') AS score    
-> FROM articles WHERE MATCH (title,body) AGAINST    
-> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

테이블에 2개의 행이 있습니다(0.00초)

MySQL FULLTEXT 실행은 단일 단어 문자 프로토타입(문자, 숫자 및 밑줄 부분)의 모든 시퀀스를 단어로 처리합니다. 이 시퀀스에는 작은 따옴표(')가 포함될 수도 있지만 한 줄에 하나만 포함됩니다. 즉, aaa'bbb는 한 단어로 처리되고, aaa''bbb는 두 단어로 처리됩니다. FULLTEXT 구문 분석기에 의해 단어 앞이나 뒤에 있는 작은따옴표가 제거됩니다. 'aaa'bbb'는 aaa'bbb가 됩니다.

FULLTEXT 파서는 ' '(공백), ,(쉼표), (마침표)와 같은 특정 구분 기호를 찾아 단어가 시작하고 끝나는 위치를 결정합니다. 단어가 구분 기호로 구분되지 않은 경우(예: 중국어) FULLTEXT 구문 분석기는 단어의 시작 위치와 끝 위치를 결정할 수 없습니다. 이러한 언어에서 FULLTEXT 인덱스에 단어나 기타 인덱스 용어를 추가하려면 "와 같은 임의의 구분 기호로 구분되도록 사전 처리해야 합니다.

일부 단어는 다음에서 무시됩니다. 전체 텍스트 검색:

• 너무 짧은 단어는 무시됩니다. 전체 텍스트 검색으로 찾은 단어의 기본 최소 길이는

불용어에 포함된 단어는 무시됩니다. "the" 또는 "some"과 같은 단어는 의미론적으로 간주되기에는 너무 일반적입니다. 사용자 정의 목록에 의해 재정의될 수 있습니다.

• 어휘 및 검색어의 각 올바른 단어는 어휘 및 검색어의 중요도에 따라 측정됩니다. 이러한 방식으로 많은 문서에 나타나는 단어는 중요도가 낮습니다. 중요도 0) 이 특정 어휘에서 의미론적 값이 낮기 때문입니다. 반대로 단어가 드물면 단어의 중요도가 결합되어 행의 관련성을 계산하는 데 사용됩니다.

  这项技术最适合同大型词库一起使用 (事实上, 此时它经过仔细的调整 )。对于很小的表，单词分布并不能充分反映它们的语义价值， 而这个模式有时可能会产生奇特的结果。例如, 虽然单词 “MySQL” 出现在文章表中的每一行，但对这个词的搜索可能得不到任何结果：

  mysql> SELECT * FROM articles

  -> WHERE MATCH (title,body) AGAINST ('MySQL');

  找不到搜索的词(0.00 秒)

  这个搜索的结果为空，原因是单词 “MySQL” 出现在至少全文的50%的行中。 因此, 它被列入停止字。对于大型数据集，使用这个操作最合适不过了----一个自然语言问询不会从一个1GB 的表每隔一行返回一次。对于小型数据集，它的用处可能比较小。

  一个符合表中所有行的内容的一半的单词查找相关文档的可能性较小。事实上, 它更容易找到很多不相关的内容。我们都知道，当我们在因特网上试图使用搜索引擎寻找资料的时候，这种情况发生的频率颇高。可以推论，包含该单词的行因其所在特别数据集 而被赋予较低的语义价值。 一个给定的词有可能在一个数据集中拥有超过其50%的域值，而在另一个数据集却不然。

  当你第一次尝试使用全文搜索以了解其工作过程时，这个50% 的域值提供重要的蕴涵操作：若你创建了一个表，并且只将文章的1、2行插入其中， 而文中的每个单词在所有行中出现的机率至少为  50% 。那么结果是你什么也不会搜索到。一定要插入至少3行，并且多多益善。需要绕过该50% 限制的用户可使用布尔搜索代码。

  1. 布尔全文搜索

  利用IN BOOLEAN MODE修改程序， MySQL 也可以执行布尔全文搜索：

  mysql> SELECT * FROM articles WHERE MATCH (title,body)    
  -> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
  +----+-----------------------+-------------------------------------+
  | id | title                 | body                                |
  +----+-----------------------+-------------------------------------+
  |  1 | MySQL Tutorial        | DBMS stands for DataBase ...        |
  |  2 | How To Use MySQL Well | After you went through a ...        |
  |  3 | Optimizing MySQL      | In this tutorial we will show ...   |
  |  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ... |
  |  6 | MySQL Security        | When configured properly, MySQL ... |
  +----+-----------------------+-------------------------------------+

  这个问询检索所有包含单词“MySQL”的行，但不检索包含单词“YourSQL”的行。

  布尔全文搜索具有以下特点：

  • 它们不使用 50% 域值。.

  • 它们不会按照相关性渐弱的顺序将行进行分类。你可以从上述问询结果中看到这一点：相关性最高的行是一个包含两个“MySQL” 的行，但它被列在最后的位置，而不是开头位置。

  • 即使没有FULLTEXT，它们仍然可以工作，尽管这种方式的搜索执行的速度非常之慢。

  • 最小单词长度全文参数和最大单词长度全文参数均适用。

  • 停止字适用。

  布尔全文搜索的性能支持以下操作符：

  • +

  一个前导的加号表示该单词必须 出现在返回的每一行的开头位置。

  • -

  一个前导的减号表示该单词一定不能出现在任何返回的行中。

  • (无操作符)

  在默认状态下(当没有指定 + 或–的情况下)，该单词可有可无，但含有该单词的行等级较高。这和MATCH() ... AGAINST()不使用IN BOOLEAN MODE修改程序时的运作很类似。

  • > fb0fed43b83fb0652d5f29eaa4ee0614 操作符增强其影响，而 c338afddb723986043c602cf4e852541turnover 244371b22a295cff4e99352a71052b04 REPAIR TABLE tbl_name QUICK;

    myisamchk를 사용하여 테이블 인덱스를 수정하는 작업(예: 복구 또는 분석)을 수행하고 최소 및 최대 단어 길이와 중지 단어에 대한 기본 전체 텍스트 매개변수 값을 사용하여 FULLTEXT 인덱스를 다시 작성합니다. 달리 지정하지 않는 한. 이로 인해 쿼리가 실패하게 됩니다.

    发生这个问题的原因是只有服务器认识这些参数。它们的存储位置不在  MyISAM 索引文件中。若你已经修改了最小单词长度或最大单词长度或服务器中的停止字，为避免这个问题，为你对mysqld所使用的myisamchk 指定同样的ft_min_word_len、 ft_max_word_len和ft_stopword_file值。例如，假如你已经将最小单词长度设置为 3, 则你可以这样修改一个带有myisamchk的表：

    shell> myisamchk --recover --ft_min_word_len=3 tbl_name.MYI

    为保证 myisamchk 及服务器对全文参数使用相同的值， 可将每一项都放在供选文件中的 [mysqld]和 [myisamchk] 部分：

    [mysqld]
    ft_min_word_len=3
    [myisamchk]
    ft_min_word_len=3

    使用 REPAIR TABLE、 ANALYZE TABLE、OPTIMIZE TABLE或ALTER TABLE来代替使用 myisamchk 。这些语句通过服务器来执行，服务器知道使用哪个全文参数值更加合适。

     以上就是MySQL基础教程10 —— 函数之全文搜索功能的内容，更多相关内容请关注PHP中文网（www.php.cn）！