1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
|
#
# InnoDB FULLTEXT SEARCH: CJK support - ngram parser
#
#
# Must have debug code to use SET DEBUG
--source include/have_debug.inc
# Enable diag print to print query parse tree
SET GLOBAL innodb_ft_enable_diag_print = 1;
-- echo # Test Case 1: Test English with Default(latin1) Charset
# Ignore ngram token check for latin1.
SET DEBUG="+d,fts_instrument_ignore_ngram_check";
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200)
) ENGINE=InnoDB;
ALTER TABLE articles ADD FULLTEXT INDEX (title) WITH PARSER ngram;
INSERT INTO articles (title) VALUES
('what is M'),
('Myname is wonder'),
('My SQL is good'),
('MySQL database'),
('Mind a good thing'),
('Mind good idea');
SELECT * FROM articles WHERE
MATCH(title) AGAINST('m*' IN BOOLEAN MODE);
SELECT * FROM articles WHERE
MATCH(title) AGAINST('my*' IN BOOLEAN MODE);
SELECT * FROM articles WHERE
MATCH(title) AGAINST('+mind -mysql' IN BOOLEAN MODE);
SELECT * FROM articles WHERE
MATCH(title) AGAINST('+(mind thing) -idea' IN BOOLEAN MODE);
--sorted_result
SELECT * FROM articles WHERE
MATCH(title) AGAINST('mysql');
SELECT * FROM articles WHERE
MATCH(title) AGAINST('mysql' IN BOOLEAN MODE);
SELECT * FROM articles WHERE
MATCH(title) AGAINST('"mysql"' IN BOOLEAN MODE);
SELECT * FROM articles WHERE
MATCH(title) AGAINST('"my sql"' IN BOOLEAN MODE);
SET DEBUG="-d,fts_instrument_ignore_ngram_check";
DROP TABLE articles;
SET NAMES utf8mb4;
-- echo # Test Case 2: Test Stopwords
# Compare with results without stopword check.
SET DEBUG="+d,fts_instrument_ignore_ngram_check";
# Without stopwords
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
CREATE FULLTEXT INDEX ft_index ON articles(title, body) WITH PARSER ngram;
INSERT INTO articles (title, body) VALUES
('从简单购物清单到画展','或企业网络中的海量数据'),
('要想将数据添加到数据库','访问、处理计算机数据库中保存的数据'),
('从简单的购物清单到画展','或企业网络中的海量数据'),
('要想将数据添加到数据库','或访问、处理计算机数据库中保存的数据'),
('数据库是数据的结构化集合','它可以是任何东西');
--sorted_result
SELECT * FROM articles WHERE
MATCH(title, body) AGAINST('的* 或* 东西' IN BOOLEAN MODE);
SELECT * FROM articles WHERE
MATCH(title, body) AGAINST('简单的' IN BOOLEAN MODE);
SELECT * FROM articles WHERE
MATCH(title, body) AGAINST('或访问' IN BOOLEAN MODE);
SELECT * FROM articles WHERE
MATCH(title, body) AGAINST('"数据的结构化"' IN BOOLEAN MODE);
SET DEBUG="-d,fts_instrument_ignore_ngram_check";
DROP TABLE articles;
# With Stopwords
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
# Define a user stopword table and set to it
CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
INSERT INTO user_stopword VALUES('的'),('或'),('东西'),('ab');
SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
INSERT INTO articles (title, body) VALUES
('从简单购物清单到画展','或企业网络中的海量数据'),
('要想将数据添加到数据库','访问、处理计算机数据库中保存的数据');
CREATE FULLTEXT INDEX ft_index ON articles(title, body) WITH PARSER ngram;
INSERT INTO articles (title, body) VALUES
('从简单的购物清单到画展','或企业网络中的海量数据'),
('要想将数据添加到数据库','或访问、处理计算机数据库中保存的数据'),
('要想将数据添加到数据库','或访问、处理计算机数据库中保存的数据 abc xyz'),
('数据库是数据的结构化集合','它可以是任何东西');
SELECT * FROM articles WHERE
MATCH(title, body) AGAINST('的* 或* 东西' IN BOOLEAN MODE);
SELECT * FROM articles WHERE
MATCH(title, body) AGAINST('简单的' IN BOOLEAN MODE);
SELECT * FROM articles WHERE
MATCH(title, body) AGAINST('或访问' IN BOOLEAN MODE);
SELECT * FROM articles WHERE
MATCH(title, body) AGAINST('"数据的结构化"' IN BOOLEAN MODE);
SELECT * FROM user_stopword WHERE value like 'ab' ;
SELECT * FROM articles WHERE
MATCH(title, body) AGAINST('"ab"' );
SELECT * FROM articles WHERE
MATCH(title, body) AGAINST('数据 +ab' IN BOOLEAN MODE);
--sorted_result
SELECT * FROM articles WHERE
MATCH(title, body) AGAINST('数据 (+xyz -bc)' IN BOOLEAN MODE);
DROP TABLE user_stopword;
DROP TABLE articles;
SET GLOBAL innodb_ft_server_stopword_table=default;
-- echo # Test Case 3: Check with FTS debug points
CREATE TABLE articles (
seq INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
c1 VARCHAR(200) CHARACTER SET big5 COLLATE big5_chinese_ci,
c2 CHAR(200) CHARACTER SET gbk COLLATE gbk_chinese_ci,
c3 CHAR(200) CHARACTER SET utf8,
j1 TEXT CHARACTER SET ujis COLLATE ujis_japanese_ci,
j2 VARCHAR(200) CHARACTER SET sjis COLLATE sjis_japanese_ci,
j3 VARCHAR(200) CHARACTER SET ujis,
k1 CHAR(200) CHARACTER SET euckr COLLATE euckr_korean_ci,
k2 CHAR(200) CHARACTER SET utf8,
e1 CHAR(200) CHARACTER SET latin1,
FULLTEXT KEY `con1` (c1),
FULLTEXT KEY `con2` (j1,j3),
FULLTEXT KEY `con3` (k2) WITH PARSER ngram
) ENGINE=InnoDB ;
SET DEBUG='+d,alter_table_rollback_new_index';
-- error ER_UNKNOWN_ERROR
ALTER TABLE articles ADD FULLTEXT INDEX (c2(64));
-- error ER_UNKNOWN_ERROR
ALTER TABLE articles ADD FULLTEXT INDEX (j1,j3);
-- error ER_UNKNOWN_ERROR
ALTER TABLE articles ADD FULLTEXT INDEX (k1);
-- error ER_UNKNOWN_ERROR
ALTER TABLE articles ADD FULLTEXT INDEX (e1);
SET DEBUG='-d,alter_table_rollback_new_index';
# Abort the operation in dict_create_index_step by setting
# return status of dict_create_index_tree_step() to DB_OUT_OF_MEMORY
# The newly create dict_index_t should be removed from fts cache
SET DEBUG="+d,ib_dict_create_index_tree_fail";
--error ER_OUT_OF_RESOURCES
ALTER TABLE articles ADD FULLTEXT INDEX (c2(64));
--error ER_OUT_OF_RESOURCES
ALTER TABLE articles ADD FULLTEXT INDEX (j1,j3);
--error ER_OUT_OF_RESOURCES
ALTER TABLE articles ADD FULLTEXT INDEX (k1);
--error ER_OUT_OF_RESOURCES
ALTER TABLE articles ADD FULLTEXT INDEX (e1);
SET DEBUG="-d,ib_dict_create_index_tree_fail";
--error ER_INNODB_FT_LIMIT
ALTER TABLE articles ADD FULLTEXT INDEX (e1), ADD FULLTEXT INDEX (k1);
DROP TABLE articles;
SET GLOBAL innodb_ft_enable_diag_print = default;
|
