Mysql的全文检索

show global VARIABLES where Variable_name like 'innodb_ft%'

Variable_name	Value
---
innodb_ft_aux_table
innodb_ft_cache_size	8000000
innodb_ft_enable_diag_print	OFF
innodb_ft_enable_stopword	ON
innodb_ft_max_token_size	84
innodb_ft_min_token_size	3
innodb_ft_num_word_optimize	2000
innodb_ft_result_cache_limit	2000000000
innodb_ft_server_stopword_table
innodb_ft_sort_pll_degree	2
innodb_ft_total_cache_size	640000000
innodb_ft_user_stopword_table

CREATE TABLE articles (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	title VARCHAR (200),
	body TEXT,
	FULLTEXT (title, body)
) ENGINE = INNODB;

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 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 ...'
	);

    # 后面查询表数据时需要执行下面sql才能将调试定位到这个表中
	set GLOBAL innodb_ft_aux_table = 'test/articles';

SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_FT%';

Tables_in_information_schema (INNODB_FT%)
---
INNODB_FT_CONFIG
INNODB_FT_BEING_DELETED
INNODB_FT_DELETED
INNODB_FT_DEFAULT_STOPWORD
INNODB_FT_INDEX_TABLE
INNODB_FT_INDEX_CACHE

select * from INFORMATION_SCHEMA.INNODB_FT_CONFIG

KEY	                         VALUE
---
optimize_checkpoint_limit	  180
synced_doc_id	               8
stopword_table_name
use_stopword	               1

select * from INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD

value
---
a
about
an
are
as
at
be
by
com
de
en
for
from
how
i
in
is
it
la
of
on
or
that
the
this
to
was
what
when
where
who
will
with
und
the
www

select * from INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE limit 5

WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
---
1001	5	5	1	5	0
after	3	3	1	3	22
comparison	6	6	1	6	44
configured	7	7	1	7	20
database	2	6	2	2	31

##
OPTIMIZE TABLE articles;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE LIMIT 5;


## 上面select的结果集
WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
---
1001        	5       	5         	1        	5   	0
after       	3        	3	        1        	3   	22
comparison   	6	        6	        1        	6   	44
configured	    7 	        7        	1         	7   	20
database	    2	        6        	2        	2   	31

search_modifier:
  {
       IN NATURAL LANGUAGE MODE         -- 这个是默认的
     | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
     | IN BOOLEAN MODE
     | WITH QUERY EXPANSION
  }

SELECT count(*) count FROM articles WHERE MATCH(title,body) AGAINST('database')

count
---
2

# 由于全文检索默认是进行优先级排序;count可以通过下面sql来避开排序来提升性能
SELECT    COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))    AS count    FROM articles;

select id,MATCH(title,body) AGAINST ('tutorial abdc esf') as score FROM articles
# WHERE MATCH(title,body) AGAINST ('tutorial abdc esf' );

id	score
---
1	0.22764469683170319
2	0
3	0.22764469683170319
4	0
5	0
6	0

select * FROM articles where MATCH(title,body) AGAINST ('+MYSQL -configured -tutorial' IN BOOLEAN MODE);

id	title	body
---
2	How To Use MySQL Well	After you went through a ...
4	1001 MySQL Tricks	1. Never run mysqld as root. 2. ...
5	MySQL vs. YourSQL	In the following database comparison ...

SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' IN NATURAL LANGUAGE MODE);

id	title	body
---
1	MySQL Tutorial	DBMS stands for DataBase ...
5	MySQL vs. YourSQL	In the following database comparison ...

SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' WITH QUERY EXPANSION);

id	title	body
---
5	MySQL vs. YourSQL	In the following database comparison ...
1	MySQL Tutorial	DBMS stands for DataBase ...
3	Optimizing MySQL	In this tutorial we show ...
6	MySQL Security	When configured properly, MySQL ...
2	How To Use MySQL Well	After you went through a ...
4	1001 MySQL Tricks	1. Never run mysqld as root. 2. ...

CREATE TABLE my_stopwords(value VARCHAR(25)) ENGINE INNODB;
INSERT into my_stopwords (value) values ('Ishmael'),('Ralph');

# 将新表设置为停用词使用的表
SET GLOBAL innodb_ft_server_stopword_table = 'test/my_stopwords';

# 新建另一张表进行测试
CREATE TABLE `opening_lines` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `opening_line` text,
  `author` varchar(200) DEFAULT NULL,
  `title` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `ft_opening_lines` (`opening_line`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO opening_lines (opening_line, author, title)
VALUES
	(
		'Call me Ishmael.',
		'Herman Melville',
		'Moby-Dick'
	),
	(
		'A screaming comes across the sky.',
		'Thomas Pynchon',
		'Gravity\'s Rainbow'
	),
	(
		'I am an invisible man.',
		'Ralph Ellison',
		'Invisible Man'
	),
	(
		'Where now? Who now? When now?',
		'Samuel Beckett',
		'The Unnamable'
	),
	(
		'It was love at first sight.',
		'Joseph Heller',
		'Catch-22'
	),
	(
		'All this happened, more or less.',
		'Kurt Vonnegut',
		'Slaughterhouse-Five'
	),
	(
		'Mrs. Dalloway said she would buy the flowers herself.',
		'Virginia Woolf',
		'Mrs. Dalloway'
	),
	(
		'It was a pleasure to burn.',
		'Ray Bradbury',
		'Fahrenheit 451'
	);

# 还是前面的表; 建一个ngram全文检索索引,前一个全文索引一定要删掉,不然这个不生效
ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;


# 插入数据
INSERT INTO articles (title,body) VALUES
    ('数据库管理','在本教程中我将向你展示如何管理数据库'),
    ('数据库应用开发','学习开发数据库应用程序');


SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position LIMIT 15;

WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
---
数据库管理	9	9	1	9	0
数据	9	10	2	9	0
据库	9	10	2	9	3
库管	9	9	1	9	6
管理	9	9	1	9	9
在本教程中我将向你展示如何管理数据库	9	9	1	9	16
在本	9	9	1	9	16
本教	9	9	1	9	19
教程	9	9	1	9	22
程中	9	9	1	9	25
中我	9	9	1	9	28
我将	9	9	1	9	31
将向	9	9	1	9	34
向你	9	9	1	9	37
你展	9	9	1	9	40


SELECT * FROM articles where MATCH(title,body) AGAINST('数据库应用')

id	title	body
---
8	数据库应用开发	学习开发数据库应用程序
7	数据库管理	在本教程中我将向你展示如何管理数据库