SELECT @@innodb_ft_server_stopword_table;
@@innodb_ft_server_stopword_table
NULL
SELECT @@innodb_ft_enable_stopword;
@@innodb_ft_enable_stopword
1
SELECT @@innodb_ft_user_stopword_table;
@@innodb_ft_user_stopword_table
NULL
SET NAMES utf8;
# Test 1 : default latin1_swedish_ci
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200)
) ENGINE=InnoDB;
INSERT INTO articles (title) VALUES
('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
('lṓve'),('LṒVE');
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
1 love
2 LOVE
3 lòve
4 LÒVE
CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB;
INSERT INTO user_stopword VALUES('lòve');
SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
DROP INDEX ft_idx ON articles;
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('love' IN NATURAL LANGUAGE MODE);
id title
DROP TABLE articles;
DROP TABLE user_stopword;
# Test 2 : latin1_general_ci
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200)
) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
INSERT INTO articles (title) VALUES
('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
('lṓve'),('LṒVE');
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
3 lòve
4 LÒVE
CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
INSERT INTO user_stopword VALUES('lòve');
SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
DROP INDEX ft_idx ON articles;
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('love' IN NATURAL LANGUAGE MODE);
id title
1 love
2 LOVE
DROP TABLE articles;
DROP TABLE user_stopword;
# Test 3 : latin1_spanish_ci
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200)
) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci;
INSERT INTO articles (title) VALUES
('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
('lṓve'),('LṒVE');
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
1 love
2 LOVE
3 lòve
4 LÒVE
5 löve
6 LÖVE
7 løve
8 LØVE
CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci;
INSERT INTO user_stopword VALUES('lòve');
SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
DROP INDEX ft_idx ON articles;
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('love' IN NATURAL LANGUAGE MODE);
id title
DROP TABLE articles;
DROP TABLE user_stopword;
# Test 4 : utf8_general_ci
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO articles (title) VALUES
('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
('lṓve'),('LṒVE');
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
1 love
2 LOVE
3 lòve
4 LÒVE
5 löve
6 LÖVE
9 lṓve
10 LṒVE
CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO user_stopword VALUES('lòve');
SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
DROP INDEX ft_idx ON articles;
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('love' IN NATURAL LANGUAGE MODE);
id title
DROP TABLE articles;
DROP TABLE user_stopword;
# Test 5 : utf8_unicode_ci
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci;
INSERT INTO articles (title) VALUES
('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
('lṓve'),('LṒVE');
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
1 love
2 LOVE
3 lòve
4 LÒVE
9 lṓve
10 LṒVE
CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci;
INSERT INTO user_stopword VALUES('lòve');
SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
DROP INDEX ft_idx ON articles;
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('love' IN NATURAL LANGUAGE MODE);
id title
DROP TABLE articles;
DROP TABLE user_stopword;
# Test 6 : utf8_unicode_ci
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
INSERT INTO articles (title) VALUES
('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
('lṓve'),('LṒVE');
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
1 love
2 LOVE
3 lòve
4 LÒVE
5 löve
6 LÖVE
9 lṓve
10 LṒVE
CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
INSERT INTO user_stopword VALUES('lòve');
SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
DROP INDEX ft_idx ON articles;
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('love' IN NATURAL LANGUAGE MODE);
id title
DROP TABLE articles;
DROP TABLE user_stopword;
# Test 7 : gb2312_chinese_ci
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200)
) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
INSERT INTO articles (title) VALUES
('相亲相爱'),('怜香惜爱'),('充满可爱'),('爱恨交织');
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('相亲相爱' IN NATURAL LANGUAGE MODE);
id title
1 相亲相爱
CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
INSERT INTO user_stopword VALUES('相亲相爱');
SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
DROP INDEX ft_idx ON articles;
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('相亲相爱' IN NATURAL LANGUAGE MODE);
id title
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('怜香惜爱' IN NATURAL LANGUAGE MODE);
id title
2 怜香惜爱
DROP TABLE articles;
DROP TABLE user_stopword;
# Test 8 : test shutdown to check if stopword still works
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200)
) ENGINE=InnoDB;
INSERT INTO articles (title) VALUES
('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
('lṓve'),('LṒVE');
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
1 love
2 LOVE
3 lòve
4 LÒVE
CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB;
INSERT INTO user_stopword VALUES('lòve');
SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
DROP INDEX ft_idx ON articles;
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('love' IN NATURAL LANGUAGE MODE);
id title
# Shutdown and restart mysqld
SET NAMES utf8;
INSERT INTO articles (title) VALUES
('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
('lṓve'),('LṒVE');
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('love' IN NATURAL LANGUAGE MODE);
id title
DROP TABLE articles;
DROP TABLE user_stopword;
# Test 9 : drop user stopwrod table,test shutdown to check if it works
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200)
) ENGINE=InnoDB;
INSERT INTO articles (title) VALUES
('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
('lṓve'),('LṒVE');
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
1 love
2 LOVE
3 lòve
4 LÒVE
CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB;
INSERT INTO user_stopword VALUES('lòve');
SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
DROP INDEX ft_idx ON articles;
CREATE FULLTEXT INDEX ft_idx ON articles(title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('love' IN NATURAL LANGUAGE MODE);
id title
DROP TABLE user_stopword;
# Shutdown and restart mysqld
SET NAMES utf8;
INSERT INTO articles (title) VALUES
('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
('lṓve'),('LṒVE');
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
id title
11 love
12 LOVE
13 lòve
14 LÒVE
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('love' IN NATURAL LANGUAGE MODE);
id title
11 love
12 LOVE
13 lòve
14 LÒVE
DROP TABLE articles;
SET SESSION innodb_ft_enable_stopword=1;
SET GLOBAL innodb_ft_server_stopword_table=default;
SET SESSION innodb_ft_user_stopword_table=default;