mirror of
https://github.com/MariaDB/server.git
synced 2025-10-30 18:36:12 +01:00
754 lines
33 KiB
Text
754 lines
33 KiB
Text
SET @innodb_ft_server_stopword_table_orig=@@innodb_ft_server_stopword_table;
|
|
SET @innodb_ft_enable_stopword_orig=@@innodb_ft_enable_stopword;
|
|
SET @innodb_ft_user_stopword_table_orig=@@innodb_ft_user_stopword_table;
|
|
call mtr.add_suppression("\\[ERROR\\] InnoDB: user stopword table not_defined does not exist.");
|
|
call mtr.add_suppression("\\[ERROR\\] InnoDB: user stopword table test/user_stopword_session does not exist.");
|
|
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
|
|
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 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 ...');
|
|
SELECT * FROM articles WHERE MATCH (title,body)
|
|
AGAINST ('the' IN NATURAL LANGUAGE MODE);
|
|
id title body
|
|
set global innodb_ft_server_stopword_table = "not_defined";
|
|
ERROR 42000: Variable 'innodb_ft_server_stopword_table' can't be set to the value of 'not_defined'
|
|
set global innodb_ft_server_stopword_table = NULL;
|
|
create table user_stopword(value varchar(30)) engine = innodb;
|
|
set global innodb_ft_server_stopword_table = "test/user_stopword";
|
|
drop index title on articles;
|
|
create fulltext index idx on articles(title, body);
|
|
SELECT * FROM articles WHERE MATCH (title,body)
|
|
AGAINST ('the' IN NATURAL LANGUAGE MODE);
|
|
id title body
|
|
5 MySQL vs. YourSQL In the following database comparison ...
|
|
CREATE TABLE articles_2 (
|
|
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
title VARCHAR(200),
|
|
body TEXT,
|
|
FULLTEXT (title,body)
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO articles_2 (title, body)
|
|
VALUES ('test for stopwords','this is it...');
|
|
SELECT * FROM articles_2 WHERE MATCH (title,body)
|
|
AGAINST ('this' IN NATURAL LANGUAGE MODE);
|
|
id title body
|
|
1 test for stopwords this is it...
|
|
insert into user_stopword values("the");
|
|
delete from user_stopword;
|
|
insert into user_stopword values("this");
|
|
CREATE TABLE articles_3 (
|
|
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
title VARCHAR(200),
|
|
body TEXT,
|
|
FULLTEXT (title,body)
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO articles_3 (title, body)
|
|
VALUES ('test for stopwords','this is it...');
|
|
SELECT * FROM articles_3 WHERE MATCH (title,body)
|
|
AGAINST ('this' IN NATURAL LANGUAGE MODE);
|
|
id title body
|
|
create table user_stopword_session(value varchar(30)) engine = innodb;
|
|
insert into user_stopword values("this");
|
|
delete from user_stopword;
|
|
insert into user_stopword_session values("session");
|
|
set session innodb_ft_user_stopword_table="test/user_stopword_session";
|
|
CREATE TABLE articles_4 (
|
|
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
title VARCHAR(200),
|
|
body TEXT,
|
|
FULLTEXT (title,body)
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO articles_4 (title, body)
|
|
VALUES ('test for session stopwords','this should also be excluded...');
|
|
SELECT * FROM articles_4 WHERE MATCH (title,body)
|
|
AGAINST ('session' IN NATURAL LANGUAGE MODE);
|
|
id title body
|
|
SELECT * FROM articles_4 WHERE MATCH (title,body)
|
|
AGAINST ('this' IN NATURAL LANGUAGE MODE);
|
|
id title body
|
|
1 test for session stopwords this should also be excluded...
|
|
connect con1,localhost,root,,;
|
|
CREATE TABLE articles_5 (
|
|
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
title VARCHAR(200),
|
|
body TEXT,
|
|
FULLTEXT (title,body)
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO articles_5 (title, body)
|
|
VALUES ('test for session stopwords','this should also be excluded...');
|
|
SELECT * FROM articles_5 WHERE MATCH (title,body)
|
|
AGAINST ('session' IN NATURAL LANGUAGE MODE);
|
|
id title body
|
|
1 test for session stopwords this should also be excluded...
|
|
connection default;
|
|
drop table articles;
|
|
drop table articles_2;
|
|
drop table articles_3;
|
|
drop table articles_4;
|
|
drop table articles_5;
|
|
drop table user_stopword;
|
|
drop table user_stopword_session;
|
|
SET GLOBAL innodb_ft_enable_stopword=@innodb_ft_enable_stopword_orig;
|
|
SET GLOBAL innodb_ft_server_stopword_table=default;
|
|
CREATE TABLE articles (
|
|
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
title VARCHAR(200),
|
|
body TEXT,
|
|
FULLTEXT `idx` (title,body)
|
|
) ENGINE=InnoDB;
|
|
SHOW CREATE TABLE articles;
|
|
Table Create Table
|
|
articles CREATE TABLE `articles` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`title` varchar(200) DEFAULT NULL,
|
|
`body` text DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
FULLTEXT KEY `idx` (`title`,`body`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
INSERT INTO articles (title,body) VALUES
|
|
('MySQL from Tutorial','DBMS stands for DataBase ...') ,
|
|
('when To Use MySQL Well','After that you went through a ...'),
|
|
('where will Optimizing MySQL','In what 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 ...');
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
|
|
id title body
|
|
INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words');
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
|
|
id title body
|
|
UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
|
|
WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
|
|
UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
|
|
WHERE id = 7;
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
|
|
id title body
|
|
DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
|
|
SELECT * FROM articles WHERE id = 7;
|
|
id title body
|
|
7 update the record to see will is indexed or not
|
|
DELETE FROM articles WHERE id = 7;
|
|
SET global innodb_ft_server_stopword_table = NULL;
|
|
SET SESSION innodb_ft_enable_stopword = 0;
|
|
select @@innodb_ft_enable_stopword;
|
|
@@innodb_ft_enable_stopword
|
|
0
|
|
SET global innodb_ft_user_stopword_table = NULL;
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
|
|
id title body
|
|
INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words');
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
|
|
id title body
|
|
UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
|
|
WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
|
|
UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
|
|
WHERE id = 8;
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
|
|
id title body
|
|
SELECT * FROM articles WHERE id = 8;
|
|
id title body
|
|
8 update the record to see will is indexed or not
|
|
DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
|
|
SELECT * FROM articles WHERE id = 8;
|
|
id title body
|
|
8 update the record to see will is indexed or not
|
|
DELETE FROM articles WHERE id = 8;
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
SHOW CREATE TABLE articles;
|
|
Table Create Table
|
|
articles CREATE TABLE `articles` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`title` varchar(200) DEFAULT NULL,
|
|
`body` text DEFAULT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
ANALYZE TABLE articles;
|
|
Table Op Msg_type Msg_text
|
|
test.articles analyze status Engine-independent statistics collected
|
|
test.articles analyze Warning Engine-independent statistics are not collected for column 'body'
|
|
test.articles analyze status OK
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
|
|
id title body
|
|
2 when To Use MySQL Well After that you went through a ...
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
1 MySQL from Tutorial DBMS stands for DataBase ...
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
2 when To Use MySQL Well After that 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("whe*" IN BOOLEAN MODE);
|
|
id title body
|
|
2 when To Use MySQL Well After that you went through a ...
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
|
|
id title body
|
|
1 MySQL from Tutorial DBMS stands for DataBase ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words');
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
|
|
id title body
|
|
9 the record will not index the , will words
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
|
|
id title body
|
|
9 the record will not index the , will words
|
|
UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
|
|
WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
|
|
SELECT COUNT(*),max(id) FROM articles;
|
|
COUNT(*) max(id)
|
|
7 9
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
|
|
id title body
|
|
9 update the record to see will is indexed or not
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
9 update the record to see will is indexed or not
|
|
DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
|
|
SELECT * FROM articles WHERE id = 9;
|
|
id title body
|
|
DROP TABLE articles;
|
|
SET SESSION innodb_ft_enable_stopword=@innodb_ft_enable_stopword_orig;
|
|
SET GLOBAL innodb_ft_server_stopword_table=@innodb_ft_server_stopword_table_orig;
|
|
SET GLOBAL innodb_ft_user_stopword_table=@innodb_ft_user_stopword_table_orig;
|
|
SET SESSION innodb_ft_user_stopword_table=default;
|
|
CREATE TABLE articles (
|
|
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
title VARCHAR(200),
|
|
body TEXT,
|
|
FULLTEXT `idx` (title,body)
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO articles (title,body) VALUES
|
|
('MySQL from Tutorial','DBMS stands for DataBase ...') ,
|
|
('when To Use MySQL Well','After that you went through a ...'),
|
|
('where will Optimizing MySQL','In what 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 ...');
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
|
|
id title body
|
|
create table user_stopword(value varchar(30)) engine = innodb;
|
|
set session innodb_ft_user_stopword_table = "test/user_stopword";
|
|
create table server_stopword(value varchar(30)) engine = innodb;
|
|
set global innodb_ft_server_stopword_table = "test/server_stopword";
|
|
insert into user_stopword values("when"),("where");
|
|
delete from user_stopword;
|
|
insert into user_stopword values("this"),("will"),("the");
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
|
|
id title body
|
|
insert into server_stopword values("what"),("where");
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
DELETE FROM user_stopword;
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
insert into user_stopword values("this"),("will"),("the");
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
SET SESSION innodb_ft_enable_stopword = 0;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SET SESSION innodb_ft_enable_stopword = 1;
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
|
|
id title body
|
|
SET SESSION innodb_ft_enable_stopword = 1;
|
|
SET SESSION innodb_ft_user_stopword_table = default;
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
DROP TABLE articles,user_stopword,server_stopword;
|
|
SET innodb_ft_enable_stopword=@innodb_ft_enable_stopword_orig;
|
|
SET GLOBAL innodb_ft_server_stopword_table=default;
|
|
SET SESSION innodb_ft_user_stopword_table=default;
|
|
CREATE TABLE articles (
|
|
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
title VARCHAR(200),
|
|
body TEXT,
|
|
FULLTEXT `idx` (title,body)
|
|
) ENGINE=InnoDB;
|
|
SHOW CREATE TABLE articles;
|
|
Table Create Table
|
|
articles CREATE TABLE `articles` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`title` varchar(200) DEFAULT NULL,
|
|
`body` text DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
FULLTEXT KEY `idx` (`title`,`body`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
INSERT INTO articles (title,body) VALUES
|
|
('MySQL from Tutorial','DBMS stands for DataBase ...') ,
|
|
('when To Use MySQL Well','After that you went through a ...'),
|
|
('where will Optimizing MySQL','In what 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 ...');
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
|
|
id title body
|
|
create table user_stopword(value varchar(30)) engine = innodb;
|
|
set session innodb_ft_user_stopword_table = "test/user_stopword";
|
|
insert into user_stopword values("mysqld"),("DBMS");
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE);
|
|
id title body
|
|
1 MySQL from Tutorial DBMS stands for DataBase ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld');
|
|
id title body
|
|
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld');
|
|
id title body
|
|
set session innodb_ft_user_stopword_table = default;
|
|
create table server_stopword(value varchar(30)) engine = innodb;
|
|
set global innodb_ft_server_stopword_table = "test/server_stopword";
|
|
insert into server_stopword values("root"),("properly");
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly');
|
|
id title body
|
|
set session innodb_ft_user_stopword_table = "test/user_stopword";
|
|
set global innodb_ft_server_stopword_table = "test/server_stopword";
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE);
|
|
id title body
|
|
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly');
|
|
id title body
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
set session innodb_ft_user_stopword_table = "test/user_stopword";
|
|
DELETE FROM user_stopword;
|
|
set global innodb_ft_server_stopword_table = "test/server_stopword";
|
|
DELETE FROM server_stopword;
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE);
|
|
id title body
|
|
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly');
|
|
id title body
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE);
|
|
id title body
|
|
1 MySQL from Tutorial DBMS stands for DataBase ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld');
|
|
id title body
|
|
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
|
|
DROP TABLE articles,user_stopword,server_stopword;
|
|
SET SESSION innodb_ft_enable_stopword=@innodb_ft_enable_stopword_orig;
|
|
SET GLOBAL innodb_ft_server_stopword_table=default;
|
|
SET SESSION innodb_ft_user_stopword_table=default;
|
|
CREATE TABLE articles (
|
|
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
title VARCHAR(200),
|
|
body TEXT,
|
|
FULLTEXT `idx` (title,body)
|
|
) ENGINE=InnoDB;
|
|
SHOW CREATE TABLE articles;
|
|
Table Create Table
|
|
articles CREATE TABLE `articles` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`title` varchar(200) DEFAULT NULL,
|
|
`body` text DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
FULLTEXT KEY `idx` (`title`,`body`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
INSERT INTO articles (title,body) VALUES
|
|
('MySQL from Tutorial','DBMS stands for DataBase ...') ,
|
|
('when To Use MySQL Well','After that you went through a ...'),
|
|
('where will Optimizing MySQL','In what 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 ...');
|
|
SET SESSION innodb_ft_enable_stopword = 0;
|
|
select @@innodb_ft_enable_stopword;
|
|
@@innodb_ft_enable_stopword
|
|
0
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
"In connection 1"
|
|
connection con1;
|
|
select @@innodb_ft_enable_stopword;
|
|
@@innodb_ft_enable_stopword
|
|
1
|
|
ANALYZE TABLE articles;
|
|
Table Op Msg_type Msg_text
|
|
test.articles analyze status Engine-independent statistics collected
|
|
test.articles analyze Warning Engine-independent statistics are not collected for column 'body'
|
|
test.articles analyze status OK
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
|
|
id title body
|
|
2 when To Use MySQL Well After that you went through a ...
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
1 MySQL from Tutorial DBMS stands for DataBase ...
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
2 when To Use MySQL Well After that 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("whe*" IN BOOLEAN MODE);
|
|
id title body
|
|
2 when To Use MySQL Well After that you went through a ...
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
|
|
id title body
|
|
1 MySQL from Tutorial DBMS stands for DataBase ...
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SET SESSION innodb_ft_enable_stopword = 1;
|
|
select @@innodb_ft_enable_stopword;
|
|
@@innodb_ft_enable_stopword
|
|
1
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
|
|
id title body
|
|
"In connection default"
|
|
connection default;
|
|
select @@innodb_ft_enable_stopword;
|
|
@@innodb_ft_enable_stopword
|
|
0
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
|
|
id title body
|
|
INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words');
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
|
|
id title body
|
|
SET SESSION innodb_ft_enable_stopword = 1;
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
|
|
id title body
|
|
"In connection 1"
|
|
connection con1;
|
|
SET SESSION innodb_ft_enable_stopword = 1;
|
|
create table user_stopword(value varchar(30)) engine = innodb;
|
|
set session innodb_ft_user_stopword_table = "test/user_stopword";
|
|
insert into user_stopword values("this"),("will"),("the");
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
|
|
id title body
|
|
"In connection default"
|
|
connection default;
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
|
|
id title body
|
|
select @@innodb_ft_user_stopword_table;
|
|
@@innodb_ft_user_stopword_table
|
|
NULL
|
|
create table user_stopword_1(value varchar(30)) engine = innodb;
|
|
set session innodb_ft_user_stopword_table = "test/user_stopword_1";
|
|
insert into user_stopword_1 values("when");
|
|
SET SESSION innodb_ft_enable_stopword = 1;
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE);
|
|
id title body
|
|
2 when To Use MySQL Well After that you went through a ...
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('when');
|
|
id title body
|
|
2 when To Use MySQL Well After that you went through a ...
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('when');
|
|
id title body
|
|
"In connection 1"
|
|
connection con1;
|
|
SET SESSION innodb_ft_enable_stopword = 1;
|
|
SET SESSION innodb_ft_user_stopword_table=default;
|
|
select @@innodb_ft_user_stopword_table;
|
|
@@innodb_ft_user_stopword_table
|
|
NULL
|
|
select @@innodb_ft_server_stopword_table;
|
|
@@innodb_ft_server_stopword_table
|
|
NULL
|
|
create table server_stopword(value varchar(30)) engine = innodb;
|
|
SET GLOBAL innodb_ft_server_stopword_table = "test/server_stopword";
|
|
select @@innodb_ft_server_stopword_table;
|
|
@@innodb_ft_server_stopword_table
|
|
test/server_stopword
|
|
insert into server_stopword values("when"),("the");
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the');
|
|
id title body
|
|
disconnect con1;
|
|
"In connection default"
|
|
connection default;
|
|
SET SESSION innodb_ft_enable_stopword = 1;
|
|
SET SESSION innodb_ft_user_stopword_table=default;
|
|
select @@innodb_ft_server_stopword_table;
|
|
@@innodb_ft_server_stopword_table
|
|
test/server_stopword
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where');
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
insert into server_stopword values("where"),("will");
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE);
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where');
|
|
id title body
|
|
3 where will Optimizing MySQL In what tutorial we will show ...
|
|
ALTER TABLE articles DROP INDEX idx;
|
|
ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the');
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE);
|
|
id title body
|
|
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where');
|
|
id title body
|
|
DROP TABLE articles,user_stopword,user_stopword_1,server_stopword;
|
|
SET GLOBAL innodb_ft_user_stopword_table=@innodb_ft_user_stopword_table_orig;
|
|
SET GLOBAL innodb_ft_server_stopword_table=@innodb_ft_server_stopword_table_orig;
|