Debian Codegraph @

mysql-test/suite/innodb_fts/t/ngram.test

raw
1
2 #
3 # InnoDB FULLTEXT SEARCH: CJK support - ngram parser
4 #
5
6 --source include/no_valgrind_without_big.inc
7 --source include/have_simple_parser.inc
8
9 SET NAMES utf8mb4;
10 -- echo # Test Case 1: Test Chinese with GB2312 Charset.
11 CREATE TABLE articles (
12 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
13 title VARCHAR(200),
14 body TEXT
15 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
16
17 INSERT INTO articles (title, body) VALUES
18 ('数据库是数据的结构化集合','它可以是任何东西'),
19 ('从简单的购物清单到画展','或企业网络中的海量数据');
20
21 ALTER TABLE articles ADD FULLTEXT INDEX (title, body) WITH PARSER ngram;
22
23 INSERT INTO articles (title, body) VALUES
24 ('要想将数据添加到数据库','或访问、处理计算机数据库中保存的数据'),
25 ('需要使用数据库管理系统','计算机是处理大量数据的理想工具');
26
27 --sorted_result
28 SELECT * FROM articles WHERE
29 MATCH(title, body) AGAINST('数据');
30
31 --sorted_result
32 SELECT * FROM articles WHERE
33 MATCH(title, body) AGAINST('数*' IN BOOLEAN MODE);
34
35 SELECT * FROM articles WHERE
36 MATCH(title, body) AGAINST('+数据库 -计算机' IN BOOLEAN MODE);
37
38 SELECT * FROM articles WHERE
39 MATCH(title, body) AGAINST('+(数据库 处理) -计算机' IN BOOLEAN MODE);
40
41 --sorted_result
42 SELECT * FROM articles WHERE
43 MATCH(title, body) AGAINST('数据库');
44
45 --sorted_result
46 SELECT * FROM articles WHERE
47 MATCH(title, body) AGAINST('数据库' IN BOOLEAN MODE);
48
49 --sorted_result
50 SELECT * FROM articles WHERE
51 MATCH(title, body) AGAINST('"数据库"' IN BOOLEAN MODE);
52
53 DROP TABLE articles;
54
55 -- echo # Test Case 2: Test Chinese with GBK Charset.
56 CREATE TABLE articles (
57 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
58 title VARCHAR(200),
59 body TEXT
60 ) ENGINE=InnoDB DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
61
62 INSERT INTO articles (title, body) VALUES
63 ('数据库是数据的结构化集合','它可以是任何东西'),
64 ('从简单的购物清单到画展','或企业网络中的海量数据');
65
66 ALTER TABLE articles ADD FULLTEXT INDEX (title, body) WITH PARSER ngram;
67
68 INSERT INTO articles (title, body) VALUES
69 ('要想将数据添加到数据库','或访问、处理计算机数据库中保存的数据'),
70 ('需要使用数据库管理系统','计算机是处理大量数据的理想工具');
71
72 --sorted_result
73 SELECT * FROM articles WHERE
74 MATCH(title, body) AGAINST('数据');
75
76 --sorted_result
77 SELECT * FROM articles WHERE
78 MATCH(title, body) AGAINST('数*' IN BOOLEAN MODE);
79
80 SELECT * FROM articles WHERE
81 MATCH(title, body) AGAINST('+数据库 -计算机' IN BOOLEAN MODE);
82
83 SELECT * FROM articles WHERE
84 MATCH(title, body) AGAINST('+(数据库 处理) -计算机' IN BOOLEAN MODE);
85
86 --sorted_result
87 SELECT * FROM articles WHERE
88 MATCH(title, body) AGAINST('数据库');
89
90 --sorted_result
91 SELECT * FROM articles WHERE
92 MATCH(title, body) AGAINST('数据库' IN BOOLEAN MODE);
93
94 --sorted_result
95 SELECT * FROM articles WHERE
96 MATCH(title, body) AGAINST('"数据库"' IN BOOLEAN MODE);
97
98 DROP TABLE articles;
99
100 -- echo # Test Case 3: Test Chinese with UTF8 Charset.
101 CREATE TABLE articles (
102 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
103 title VARCHAR(200),
104 body TEXT
105 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
106
107 INSERT INTO articles (title, body) VALUES
108 ('数据库是数据的结构化集合','它可以是任何东西'),
109 ('从简单的购物清单到画展','或企业网络中的海量数据');
110
111 ALTER TABLE articles ADD FULLTEXT INDEX (title, body) WITH PARSER ngram;
112
113 INSERT INTO articles (title, body) VALUES
114 ('要想将数据添加到数据库','或访问、处理计算机数据库中保存的数据'),
115 ('需要使用数据库管理系统','计算机是处理大量数据的理想工具');
116
117 --sorted_result
118 SELECT * FROM articles WHERE
119 MATCH(title, body) AGAINST('数据');
120
121 --sorted_result
122 SELECT * FROM articles WHERE
123 MATCH(title, body) AGAINST('数*' IN BOOLEAN MODE);
124
125 SELECT * FROM articles WHERE
126 MATCH(title, body) AGAINST('+数据库 -计算机' IN BOOLEAN MODE);
127
128 SELECT * FROM articles WHERE
129 MATCH(title, body) AGAINST('+(数据库 处理) -计算机' IN BOOLEAN MODE);
130
131 --sorted_result
132 SELECT * FROM articles WHERE
133 MATCH(title, body) AGAINST('数据库');
134
135 --sorted_result
136 SELECT * FROM articles WHERE
137 MATCH(title, body) AGAINST('数据库' IN BOOLEAN MODE);
138
139 --sorted_result
140 SELECT * FROM articles WHERE
141 MATCH(title, body) AGAINST('"数据库"' IN BOOLEAN MODE);
142
143 DROP TABLE articles;
144
145 -- echo # Test Case 4: Test Chinese with gb18030 Charset.
146 CREATE TABLE articles (
147 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
148 title VARCHAR(200),
149 body TEXT
150 ) ENGINE=InnoDB DEFAULT CHARSET=gb18030 COLLATE=gb18030_chinese_ci;
151
152
153 INSERT INTO articles (title, body) VALUES
154 ('数据库是数据的结构化集合','它可以是任何东西'),
155 ('从简单的购物清单到画展','或企业网络中的海量数据');
156
157 ALTER TABLE articles ADD FULLTEXT INDEX `idx1` (title, body) WITH PARSER ngram;
158
159 INSERT INTO articles (title, body) VALUES
160 ('要想将数据添加到数据库','或访问、处理计算机数据库中保存的数据'),
161 ('需要使用数据库管理系统','计算机是处理大量数据的理想工具');
162
163 --sorted_result
164 SELECT * FROM articles WHERE
165 MATCH(title, body) AGAINST('数据');
166
167 --sorted_result
168 SELECT * FROM articles WHERE
169 MATCH(title, body) AGAINST('数*' IN BOOLEAN MODE);
170
171 SELECT * FROM articles WHERE
172 MATCH(title, body) AGAINST('+数据库 -计算机' IN BOOLEAN MODE);
173
174 SELECT * FROM articles WHERE
175 MATCH(title, body) AGAINST('+(数据库 处理) -计算机' IN BOOLEAN MODE);
176
177 --sorted_result
178 SELECT * FROM articles WHERE
179 MATCH(title, body) AGAINST('数据库');
180
181 --sorted_result
182 SELECT * FROM articles WHERE
183 MATCH(title, body) AGAINST('数据库' IN BOOLEAN MODE);
184
185 --sorted_result
186 SELECT * FROM articles WHERE
187 MATCH(title, body) AGAINST('"数据库"' IN BOOLEAN MODE);
188
189 --sorted_result
190 SELECT * FROM articles WHERE
191 MATCH(title, body) AGAINST('数据库' WITH QUERY EXPANSION);
192
193 ALTER TABLE articles DROP INDEX `idx1`;
194 SELECT * FROM articles;
195 DROP TABLE articles;
196
197
198 -- echo # Test Case 5: Test Japanese with UJIS Charset.
199 CREATE TABLE articles (
200 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
201 title VARCHAR(200),
202 body TEXT
203 ) ENGINE=InnoDB DEFAULT CHARACTER SET ujis COLLATE ujis_japanese_ci;
204
205 INSERT INTO articles (title, body) VALUES
206 ('データベースは、構造化されたコレクションです','それは何もすることができます'),
207 ('シンプルな買い物リストから画像ギャラリーへ','または企業ネットワーク内のデータの膨大な量');
208
209 ALTER TABLE articles ADD FULLTEXT INDEX (title, body) WITH PARSER ngram;
210
211 INSERT INTO articles (title, body) VALUES
212 ('データベースにデータを追加するには','コンピュータのデータベースに格納されているアクセス、およびプロセスデータ'),
213 ('データベース管理システムを使用する必要が','コンピュータは、大量のデータを処理するための理想的なツールである');
214
215 --sorted_result
216 SELECT * FROM articles WHERE
217 MATCH(title, body) AGAINST('データ');
218
219 --sorted_result
220 SELECT * FROM articles WHERE
221 MATCH(title, body) AGAINST('デ*' IN BOOLEAN MODE);
222
223 SELECT * FROM articles WHERE
224 MATCH(title, body) AGAINST('+データベース -コンピュータ' IN BOOLEAN MODE);
225
226 SELECT * FROM articles WHERE
227 MATCH(title, body) AGAINST('+(データベース 処理) -コンピュータ' IN BOOLEAN MODE);
228
229 --sorted_result
230 SELECT * FROM articles WHERE
231 MATCH(title, body) AGAINST('データベース');
232
233 --sorted_result
234 SELECT * FROM articles WHERE
235 MATCH(title, body) AGAINST('データベース' IN BOOLEAN MODE);
236
237 --sorted_result
238 SELECT * FROM articles WHERE
239 MATCH(title, body) AGAINST('"データベース"' IN BOOLEAN MODE);
240
241 DROP TABLE articles;
242
243 -- echo # Test Case 6: Test Korean with EUCKR Charset.
244 CREATE TABLE articles (
245 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
246 title VARCHAR(200),
247 body TEXT
248 ) ENGINE=InnoDB DEFAULT CHARACTER SET euckr COLLATE euckr_korean_ci;
249
250 INSERT INTO articles (title, body) VALUES
251 ('데이터베이스의 구조 모음입니다','그것은 무엇이든 될 수 있습니다'),
252 ('간단한 쇼핑 목록에서 사진 갤러리','또는 기업 네트워크에서 데이터의 엄청난 양의');
253
254 ALTER TABLE articles ADD FULLTEXT INDEX (title, body) WITH PARSER ngram;
255
256 INSERT INTO articles (title, body) VALUES
257 ('데이터베이스에 데이터를 추가하려면','액세스, 프로세스 데이터를 컴퓨터 데이터베이스에 저장'),
258 ('데이터베이스 관리 시스템을 사용해야합니다','컴퓨터는 많은 양의 데이터를 처리하기위한 이상적인 도구입니다');
259
260 --sorted_result
261 SELECT * FROM articles WHERE
262 MATCH(title, body) AGAINST('데이터');
263
264 --sorted_result
265 SELECT * FROM articles WHERE
266 MATCH(title, body) AGAINST('데*' IN BOOLEAN MODE);
267
268 SELECT * FROM articles WHERE
269 MATCH(title, body) AGAINST('+데이터베이스 -컴퓨터' IN BOOLEAN MODE);
270
271 SELECT * FROM articles WHERE
272 MATCH(title, body) AGAINST('+(데이터베이스 처리) -컴퓨터' IN BOOLEAN MODE);
273
274 --sorted_result
275 SELECT * FROM articles WHERE
276 MATCH(title, body) AGAINST('데이터베이스');
277
278 --sorted_result
279 SELECT * FROM articles WHERE
280 MATCH(title, body) AGAINST('데이터베이스' IN BOOLEAN MODE);
281
282 --sorted_result
283 SELECT * FROM articles WHERE
284 MATCH(title, body) AGAINST('"데이터베이스"' IN BOOLEAN MODE);
285
286 DROP TABLE articles;
287
288 -- echo # Test Case 7: Test different ngram token size.
289 CREATE TABLE articles (
290 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
291 title VARCHAR(200),
292 body TEXT
293 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
294
295 INSERT INTO articles (title, body) VALUES
296 ('数据库是数据的结构化集合','它可以是任何东西'),
297 ('从简单的购物清单到画展','或企业网络中的海量数据'),
298 ('要想将数据添加到数据库','或访问、处理计算机数据库中保存的数据'),
299 ('需要使用数据库管理系统','计算机是处理大量数据的理想工具');
300
301 # bigram
302 CREATE FULLTEXT INDEX ft_index ON articles(title, body) WITH PARSER ngram;
303
304 # unigram
305 let $ngram_token_size=1;
306 -- source suite/innodb_fts/include/ngram_token_size.inc
307
308 # trigram
309 let $ngram_token_size=3;
310 -- source suite/innodb_fts/include/ngram_token_size.inc
311
312 # ngram = 5
313 let $ngram_token_size=5;
314 -- source suite/innodb_fts/include/ngram_token_size.inc
315
316 # ngram = 7
317 let $ngram_token_size=7;
318 -- source suite/innodb_fts/include/ngram_token_size.inc
319
320 # Restore to bigram
321 let $ngram_token_size=2;
322 -- source suite/innodb_fts/include/ngram_token_size.inc
323
324 DROP TABLE articles;
325
326 -- echo # Test Case 8: Test Optimize Table.
327 CREATE TABLE articles (
328 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
329 title VARCHAR(200),
330 body TEXT
331 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
332
333 INSERT INTO articles (title, body) VALUES
334 ('数据库是数据的结构化集合','它可以是任何东西'),
335 ('从简单的购物清单到画展','或企业网络中的海量数据');
336
337 CREATE FULLTEXT INDEX ft_index ON articles(title, body) WITH PARSER ngram;
338
339 SET GLOBAL innodb_ft_aux_table="test/articles";
340
341 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
342
343 SELECT * FROM articles WHERE
344 MATCH(title, body) AGAINST('数据');
345
346 DELETE FROM articles WHERE id = 1;
347
348 SET GLOBAL INNODB_OPTIMIZE_FULLTEXT_ONLY=1;
349 OPTIMIZE TABLE articles;
350 SET GLOBAL INNODB_OPTIMIZE_FULLTEXT_ONLY=default;
351
352 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
353
354 SELECT * FROM articles WHERE
355 MATCH(title, body) AGAINST('数据');
356
357 SET GLOBAL innodb_ft_aux_table=default;
358
359 DROP TABLE articles;
360
361 -- echo # Test Case 9: Test Rename Index
362 --replace_regex /\.dll/.so/
363 eval INSTALL PLUGIN simple_parser SONAME '$SIMPLE_PARSER';
364
365 CREATE TABLE articles (
366 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
367 title VARCHAR(200),
368 body TEXT
369 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
370
371 INSERT INTO articles (title, body) VALUES ('数据的结合','数据库');
372
373 CREATE FULLTEXT INDEX ft_1 ON articles(title) WITH PARSER ngram;
374
375 SELECT * FROM articles WHERE MATCH(title) AGAINST('数据');
376
377 ALTER TABLE articles RENAME INDEX ft_1 TO ft_2;
378
379 INSERT INTO articles (title, body) VALUES ('数据的结合','数据库');
380
381 SELECT * FROM articles WHERE MATCH(title) AGAINST('数据');
382
383 ALTER TABLE articles RENAME INDEX ft_2 to ft_1,
384 ADD FULLTEXT INDEX ft_2(body) WITH PARSER simple_parser;
385
386 INSERT INTO articles (title, body) VALUES ('数据的结合','数据库');
387
388 SELECT * FROM articles WHERE MATCH(title) AGAINST('数据');
389
390 SELECT * FROM articles WHERE MATCH(body) AGAINST('数据');
391 SELECT * FROM articles WHERE MATCH(body) AGAINST('数据库');
392
393 ALTER TABLE articles RENAME INDEX ft_2 to ft_1, RENAME INDEX ft_1 to ft_2;
394
395 INSERT INTO articles (title, body) VALUES ('数据的结合','数据库');
396
397 SELECT * FROM articles WHERE MATCH(title) AGAINST('数据');
398
399 SELECT * FROM articles WHERE MATCH(body) AGAINST('数据');
400 SELECT * FROM articles WHERE MATCH(body) AGAINST('数据库');
401
402 DROP TABLE articles;
403
404 UNINSTALL PLUGIN simple_parser;
405
406 -- echo # Test Case 10: Test Fulltext with UCS2
407 CREATE TABLE articles (
408 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
409 title VARCHAR(200),
410 body TEXT
411 ) ENGINE=InnoDB DEFAULT CHARACTER SET ucs2;
412
413 INSERT INTO articles (title, body) VALUES ('数据的结合','数据库');
414
415 # Fulltext with charset UCS2 is not allowed.
416 -- error ER_BAD_FT_COLUMN
417 CREATE FULLTEXT INDEX ft_idx ON articles(title) WITH PARSER ngram;
418
419 -- error ER_BAD_FT_COLUMN
420 CREATE FULLTEXT INDEX ft_idx ON articles(title);
421
422 DROP TABLE articles;