Debian Codegraph @

mysql-test/suite/innodb_fts/include/ngram.inc

raw
1 SET NAMES utf8;
2
3 -- echo # Create table : Test with row_type $table_row_format with char,varchar,text columns
4 EVAL CREATE TABLE articles_1 (
5 seq INT ,
6 title_char VARCHAR(200),
7 title_varchar VARCHAR(300),
8 title_text VARCHAR(300)
9 ) charset latin1 ENGINE=InnoDB $table_row_format;
10
11 EVAL CREATE TABLE articles (
12 seq INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
13 c1 VARCHAR(200) CHARACTER SET big5 COLLATE big5_chinese_ci,
14 c2 CHAR(200) CHARACTER SET gbk COLLATE gbk_chinese_ci,
15 c3 CHAR(200) CHARACTER SET utf8,
16 j1 TEXT CHARACTER SET ujis COLLATE ujis_japanese_ci,
17 j2 VARCHAR(200) CHARACTER SET sjis COLLATE sjis_japanese_ci,
18 j3 VARCHAR(200) CHARACTER SET ujis,
19 k1 CHAR(200) CHARACTER SET euckr COLLATE euckr_korean_ci,
20 k2 CHAR(200) CHARACTER SET utf8,
21 e1 CHAR(200) CHARACTER SET latin1,
22 FULLTEXT KEY con1 (c1) WITH PARSER ngram,
23 FULLTEXT KEY con2 (j1,j3) WITH PARSER ngram,
24 FULLTEXT KEY con3 (k2) WITH PARSER ngram
25 ) ENGINE=InnoDB $table_row_format;
26
27 EVAL CREATE TABLE 名字 (
28 seq INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
29 c1 VARCHAR(200) CHARACTER SET big5 COLLATE big5_chinese_ci,
30 c2 CHAR(200) CHARACTER SET gbk COLLATE gbk_chinese_ci,
31 c3 CHAR(200) CHARACTER SET utf8,
32 さようなら TEXT CHARACTER SET ujis COLLATE ujis_japanese_ci,
33 j2 VARCHAR(200) CHARACTER SET sjis COLLATE sjis_japanese_ci,
34 j3 VARCHAR(200) CHARACTER SET ujis,
35 행운을빈다 CHAR(200) CHARACTER SET euckr COLLATE euckr_korean_ci,
36 k2 CHAR(200) CHARACTER SET utf8,
37 e1 CHAR(200) CHARACTER SET latin1,
38 FULLTEXT KEY con1 (c1) WITH PARSER ngram,
39 FULLTEXT KEY con2 (さようなら,j3) WITH PARSER ngram,
40 FULLTEXT KEY con3 (k2) WITH PARSER ngram
41 ) ENGINE=InnoDB $table_row_format;
42
43
44 SET SESSION innodb_ft_enable_stopword=0;
45 --echo # Case : run ngram on english text.
46 ALTER TABLE articles_1 ADD FULLTEXT INDEX (title_char) WITH PARSER ngram;
47 ALTER TABLE articles_1 ADD FULLTEXT INDEX (title_varchar) WITH PARSER ngram;
48 ALTER TABLE articles_1 ADD FULLTEXT INDEX (title_text) WITH PARSER ngram;
49 ALTER TABLE articles_1 ADD FULLTEXT INDEX (title_char,title_varchar) WITH PARSER ngram;
50 ALTER TABLE articles_1 ADD FULLTEXT INDEX (title_text,title_varchar) WITH PARSER ngram;
51 SHOW CREATE TABLE articles;
52 INSERT INTO articles_1 (seq,title_char,title_varchar,title_text) VALUES
53 (1,'English is a West Germanic language','It was first spoken in early medieval England','most widely used language in the world'),
54 (2,'spoken as a first language by the majority populations of several states','including the United Kingdom i*', ' and the United States, Canada, Australia, Ireland'),
55 (3,'English arose in the Anglo-Saxon kingdoms','its from of England and what is now southeast Scotland','Following the extensive influence of Great Britain and the United Kingdom'),
56 (5,'significant number of English words are constructed on the basis of roots from Latin', 'because Latin in some form was the lingua franca of the Christian Church','and of European intellectual life'),
57 (7,'spread across states by United Kingdom ~ England army','The Oxford English Dictionary liSts over 250,000 distinct words+','it became the dominant language in the United States, Canada, Australia and New Zealand');
58
59 -- disable_result_log
60 ANALYZE TABLE articles_1;
61 -- enable_result_log
62
63 # bigram search
64 SELECT * FROM articles_1 WHERE
65 MATCH(title_char) AGAINST('En');
66 --sorted_result
67 SELECT * FROM articles_1 WHERE
68 MATCH(title_varchar) AGAINST('nd');
69 SELECT * FROM articles_1 WHERE
70 MATCH(title_varchar) AGAINST('50');
71 SELECT * FROM articles_1 WHERE
72 MATCH(title_text) AGAINST('se');
73 SELECT * FROM articles_1 WHERE
74 MATCH(title_char,title_varchar) AGAINST('al');
75 SELECT * FROM articles_1 WHERE
76 MATCH(title_text,title_varchar) AGAINST('er');
77
78 # try search for an entire word
79 --sorted_result
80 SELECT * FROM articles_1 WHERE
81 MATCH(title_char) AGAINST('West');
82 --sorted_result
83 SELECT * FROM articles_1 WHERE
84 MATCH(title_varchar) AGAINST('first');
85 --sorted_result
86 SELECT * FROM articles_1 WHERE
87 MATCH(title_text) AGAINST('United');
88 --sorted_result
89 SELECT * FROM articles_1 WHERE
90 MATCH(title_char,title_varchar) AGAINST('in');
91 --sorted_result
92 SELECT * FROM articles_1 WHERE
93 MATCH(title_text,title_varchar) AGAINST('St');
94
95 # boolean mode
96 --sorted_result
97 SELECT * FROM articles_1 WHERE
98 MATCH(title_char) AGAINST('En la' IN BOOLEAN MODE);
99 SELECT * FROM articles_1 WHERE
100 MATCH(title_char) AGAINST('+En -la' IN BOOLEAN MODE);
101 SELECT * FROM articles_1 WHERE
102 MATCH(title_char) AGAINST('-En +la' IN BOOLEAN MODE);
103 --sorted_result
104 SELECT * FROM articles_1 WHERE
105 MATCH(title_char) AGAINST('+En +la' IN BOOLEAN MODE);
106 SELECT * FROM articles_1 WHERE
107 MATCH(title_char) AGAINST('-En -la' IN BOOLEAN MODE);
108
109 --sorted_result
110 SELECT * FROM articles_1 WHERE
111 MATCH(title_varchar) AGAINST('in nc' IN BOOLEAN MODE);
112 SELECT * FROM articles_1 WHERE
113 MATCH(title_varchar) AGAINST('+in -nc' IN BOOLEAN MODE);
114 --sorted_result
115 SELECT * FROM articles_1 WHERE
116 MATCH(title_varchar) AGAINST('+in <la' IN BOOLEAN MODE);
117 --sorted_result
118 SELECT * FROM articles_1 WHERE
119 MATCH(title_varchar) AGAINST('+la >la' IN BOOLEAN MODE);
120 --sorted_result
121 SELECT * FROM articles_1 WHERE
122 MATCH(title_varchar) AGAINST('~in la' IN BOOLEAN MODE);
123
124 --sorted_result
125 SELECT * FROM articles_1 WHERE
126 MATCH(title_varchar,title_text) AGAINST('Ca Ox' IN BOOLEAN MODE);
127 SELECT * FROM articles_1 WHERE
128 MATCH(title_varchar,title_text) AGAINST('s+ Ox' IN BOOLEAN MODE);
129 SELECT * FROM articles_1 WHERE
130 MATCH(title_varchar,title_text) AGAINST('s+ i*' IN BOOLEAN MODE);
131 SELECT * FROM articles_1 WHERE
132 MATCH(title_varchar,title_text) AGAINST('"Un" "-i*"' IN BOOLEAN MODE);
133
134 # query expansion mode
135 --sorted_result
136 SELECT * FROM articles_1 WHERE
137 MATCH(title_char) AGAINST('la' WITH QUERY EXPANSION);
138 --sorted_result
139 SELECT * FROM articles_1 WHERE
140 MATCH(title_varchar) AGAINST('po' WITH QUERY EXPANSION);
141 --sorted_result
142 SELECT * FROM articles_1 WHERE
143 MATCH(title_text) AGAINST('Ca' WITH QUERY EXPANSION);
144 --sorted_result
145 SELECT * FROM articles_1 WHERE
146 MATCH(title_char,title_varchar) AGAINST('Ca' WITH QUERY EXPANSION);
147 --sorted_result
148 SELECT * FROM articles_1 WHERE
149 MATCH(title_text,title_varchar) AGAINST('Ca' WITH QUERY EXPANSION);
150
151 # proximity search - not supported yet
152 SELECT * FROM articles_1
153 WHERE MATCH (title_text)
154 AGAINST ('"Ca da"@2' IN BOOLEAN MODE);
155 SELECT * FROM articles_1
156 WHERE MATCH (title_varchar,title_text)
157 AGAINST ('"Ca da"@5' IN BOOLEAN MODE);
158
159 # FTS condition and Join in where clause
160 --sorted_result
161 SELECT * FROM articles_1 t1,articles_1 t2
162 WHERE MATCH(t2.title_varchar,t2.title_text) AGAINST('Ca Ox' IN BOOLEAN MODE)
163 AND t1.seq = t2.seq ;
164 SELECT COUNT(*) FROM articles_1 t1,articles_1 t2
165 WHERE MATCH(t1.title_char) AGAINST('la')
166 AND t1.seq = t2.seq
167 AND MATCH(t2.title_varchar) AGAINST('-da' IN BOOLEAN MODE) ;
168
169 # Check non ngram FTS
170 # tescase create ngran and non ngram FTS index
171 ALTER TABLE articles_1 ADD FULLTEXT INDEX (title_char,title_text,title_varchar);
172 SELECT * FROM articles_1
173 WHERE MATCH (title_char,title_text,title_varchar)
174 AGAINST ('kingdom');
175 SELECT * FROM articles_1
176 WHERE MATCH (title_char,title_text,title_varchar)
177 AGAINST ('+"kingdom" -"States"' IN BOOLEAN MODE);
178 SELECT * FROM articles_1
179 WHERE MATCH (title_char,title_text,title_varchar)
180 AGAINST ('"United States"@2' IN BOOLEAN MODE);
181 --sorted_result
182 SELECT * FROM articles_1
183 WHERE MATCH (title_char,title_text,title_varchar)
184 AGAINST ('States' WITH QUERY EXPANSION);
185
186
187
188 SET SESSION innodb_ft_enable_stopword=1;
189 DROP TABLE articles_1;
190
191 -- echo # Test Case 2: CJK charset and collation on row_type
192 # This test covers additionally
193 # Create FTS index using ALTER
194 # Create multiple FTS index on table
195 # Simultanously Create multiple FTS index Chinese , Japanese , Korean column
196 INSERT INTO articles (c1,c2,c3,j1,j2,j3,k1,k2,e1) VALUES
197 ('歡迎','你好','喂 ','日の長','言も言わずに','つやってみよう','제가 미국인입니다.','한국말을 공부합니다%','testing row row1 xy'),
198 ('你好嗎?','我幾好,你呢','好耐冇見','左右','左右される','☆右折⇔左折','제 이름은 Charles입니다.','운동을 좋아해요*','row row1 value'),
199 ('救命啊!','火啊! 你好','生日快樂 ','雨季','雨が降りそう つやってみよう','雨雲','저분은 영어를 잘 합니다','저는 서울에서 살고 있습니다.','recording test');
200
201 -- disable_result_log
202 ANALYZE TABLE articles;
203 -- enable_result_log
204
205 # check default value ngram_token_size
206 SELECT @@ngram_token_size;
207
208 SELECT * FROM articles;
209 ALTER TABLE articles ADD FULLTEXT INDEX con4 (c3) WITH PARSER ngram;
210 SHOW CREATE TABLE articles;
211 ALTER TABLE articles ADD FULLTEXT INDEX con5 (c2) WITH PARSER ngram;
212 SHOW CREATE TABLE articles;
213 ALTER TABLE articles ADD FULLTEXT INDEX con6 (j2) WITH PARSER ngram;
214 SHOW CREATE TABLE articles;
215 ALTER TABLE articles ADD FULLTEXT INDEX con7 (j3) WITH PARSER ngram;
216 SHOW CREATE TABLE articles;
217 ALTER TABLE articles ADD FULLTEXT INDEX con8 (k1) WITH PARSER ngram;
218 SHOW CREATE TABLE articles;
219 ALTER TABLE articles ADD FULLTEXT INDEX con9 (e1) WITH PARSER ngram;
220 SHOW CREATE TABLE articles;
221
222 # natural language search
223 SELECT * FROM articles WHERE
224 MATCH(c1) AGAINST('救命');
225 SELECT * FROM articles WHERE
226 MATCH(c2) AGAINST('我幾');
227
228 SELECT * FROM articles WHERE
229 MATCH(j1,j3) AGAINST('やっ');
230 SELECT * FROM articles WHERE
231 MATCH(j1,j3) AGAINST('右さ');
232 SELECT * FROM articles WHERE
233 MATCH(j3) AGAINST('右さ');
234
235 SELECT * FROM articles WHERE
236 MATCH(k1) AGAINST('Ch');
237 SELECT * FROM articles WHERE
238 MATCH(k2) AGAINST('니다');
239
240 SELECT * FROM articles WHERE
241 MATCH(e1) AGAINST('row');
242
243
244 # boolean mode
245 SELECT * FROM articles WHERE
246 MATCH(c1) AGAINST("+啊! " IN BOOLEAN MODE);
247 SELECT * FROM articles WHERE
248 MATCH(c2) AGAINST("-我幾 火啊" IN BOOLEAN MODE);
249 SELECT * FROM articles WHERE
250 MATCH(c2) AGAINST("-我幾 -火啊" IN BOOLEAN MODE);
251 SELECT * FROM articles WHERE
252 MATCH(c2) AGAINST("我幾 火啊" IN BOOLEAN MODE);
253 --sorted_result
254 SELECT * FROM articles WHERE
255 MATCH(c2) AGAINST("<我幾 >火啊" IN BOOLEAN MODE);
256
257 INSERT INTO articles (c1,c2,c3,j1,j2,j3,k1,k2,e1) VALUES
258 ('歡迎','你好','喂 ','日の長','言も言わずに','つやてみよう','제가 미국인입니다.','한국말을 공부합니다%','testing row row1 xy');
259
260 -- disable_result_log
261 ANALYZE TABLE articles;
262 -- enable_result_log
263
264 SELECT * FROM articles WHERE
265 MATCH(j1,j3) AGAINST("つや +(てみ)" IN BOOLEAN MODE);
266 DELETE FROM articles WHERE seq = 4;
267
268 -- disable_result_log
269 ANALYZE TABLE articles;
270 -- enable_result_log
271
272 SELECT * FROM articles WHERE MATCH(c1) AGAINST('+ ! ' IN BOOLEAN MODE);
273
274 SELECT * FROM articles WHERE
275 MATCH(j2) AGAINST("やっ-(そう)" IN BOOLEAN MODE);
276 SELECT * FROM articles WHERE
277 MATCH(j1,j3) AGAINST("右さ -の長" IN BOOLEAN MODE);
278 SELECT * FROM articles WHERE
279 MATCH(j3) AGAINST(">の長" IN BOOLEAN MODE);
280
281 SELECT * FROM articles WHERE
282 MATCH(k2) AGAINST('-Ch +해요');
283
284 SELECT * FROM articles WHERE
285 MATCH(e1) AGAINST("row1 -row" IN BOOLEAN MODE) ;
286
287
288 # Query expansion mode
289 SELECT * FROM articles WHERE
290 MATCH(c1) AGAINST('命啊' WITH QUERY EXPANSION);
291 SELECT * FROM articles WHERE
292 MATCH(j2) AGAINST('やっそう' WITH QUERY EXPANSION);
293 SELECT * FROM articles WHERE
294 MATCH(j1,j3) AGAINST('右さ の長' WITH QUERY EXPANSION);
295 SELECT * FROM articles WHERE
296 MATCH(j3) AGAINST('の長' WITH QUERY EXPANSION);
297 #SELECT * FROM articles WHERE
298 # MATCH(k2) AGAINST('국말' WITH QUERY EXPANSION);
299 --sorted_result
300 SELECT * FROM articles WHERE
301 MATCH(e1) AGAINST('testing' WITH QUERY EXPANSION) ;
302
303
304
305 # Proximity search
306 SELECT * FROM articles WHERE
307 MATCH(c2) AGAINST('"我幾 火啊"@3' IN BOOLEAN MODE);
308 SELECT * FROM articles WHERE
309 MATCH(j2) AGAINST('"やっそう"@5' IN BOOLEAN MODE);
310 SELECT * FROM articles WHERE
311 MATCH(j1,j3) AGAINST('"右さ -の長"@4' IN BOOLEAN MODE);
312 SELECT * FROM articles WHERE
313 MATCH(k2) AGAINST('"국말 Ch"@2' IN BOOLEAN MODE);
314
315 SELECT * FROM articles WHERE
316 MATCH(e1) AGAINST('"row row1"@1' IN BOOLEAN MODE);
317
318
319 # Query with JOIN and FTS condition
320 SELECT COUNT(*) FROM articles t1,articles t2
321 WHERE MATCH(t2.j1,t2.j3) AGAINST("右さ +の長" IN BOOLEAN MODE)
322 AND t1.seq = t2.seq ;
323 SELECT COUNT(*) FROM articles t1,articles t2
324 WHERE MATCH(t1.c1) AGAINST("命啊")
325 AND t1.seq != t2.seq
326 AND MATCH(t2.k2) AGAINST("국말" IN BOOLEAN MODE) ;
327 SELECT COUNT(*) FROM articles t1,articles t2
328 WHERE MATCH(t1.c1) AGAINST("啊! " WITH QUERY EXPANSION)
329 AND t1.seq = t2.seq
330 AND MATCH(t2.k2) AGAINST("<국말" IN BOOLEAN MODE) ;
331
332
333 SELECT * FROM articles;
334
335 DROP TABLE articles;
336
337
338 -- echo # Test Case : CJK charset and collation with globalized table, column names
339 INSERT INTO 名字 (c1,c2,c3,さようなら,j2,j3,행운을빈다,k2,e1) VALUES
340 ('歡迎','你好','喂 ','日の長','言も言わずに','つやってみよう','제가 미국인입니다.','한국말을 공부합니다%','testing row'),
341 ('你好嗎?','我幾好,你呢','好耐冇見','左右','左右される','☆右折⇔左折','제 이름은 Charles입니다.','운동을 좋아해요*','row value'),
342 ('救命啊!','火啊! 你好','生日快樂 ','雨季','雨が降りそう つやってみよう','雨雲','저분은 영어를 잘 합니다','저는 서울에서 살고 있습니다.','recording test');
343
344 -- disable_result_log
345 ANALYZE TABLE 名字;
346 -- enable_result_log
347
348 SELECT * FROM 名字;
349 ALTER TABLE 名字 ADD FULLTEXT INDEX con4 (c3) WITH PARSER ngram;
350 ALTER TABLE 名字 ADD FULLTEXT INDEX con5 (c2) WITH PARSER ngram;
351 ALTER TABLE 名字 ADD FULLTEXT INDEX con6 (j2) WITH PARSER ngram;
352 ALTER TABLE 名字 ADD FULLTEXT INDEX con7 (j3) WITH PARSER ngram;
353 ALTER TABLE 名字 ADD FULLTEXT INDEX con8 (행운을빈다) WITH PARSER ngram;
354 ALTER TABLE 名字 ADD FULLTEXT INDEX con9 (e1) WITH PARSER ngram;
355 SELECT * FROM 名字 WHERE
356 MATCH(c1) AGAINST('命啊');
357 SELECT * FROM 名字 WHERE
358 MATCH(さようなら,j3) AGAINST('やっ');
359 SELECT * FROM 名字 WHERE
360 MATCH(행운을빈다) AGAINST('제가');
361 SELECT * FROM 名字 WHERE
362 MATCH(e1) AGAINST('row');
363 SELECT * FROM 名字 WHERE
364 MATCH(c2) AGAINST("-我幾 火啊" IN BOOLEAN MODE);
365 SELECT * FROM 名字 WHERE
366 MATCH(j2) AGAINST("やっ-(そう)" IN BOOLEAN MODE);
367 SELECT * FROM 名字 WHERE
368 MATCH(j3) AGAINST(">の長" IN BOOLEAN MODE);
369 SELECT * FROM 名字 WHERE
370 MATCH(행운을빈다) AGAINST("니다 -제가" IN BOOLEAN MODE);
371 SELECT * FROM 名字 WHERE
372 MATCH(e1) AGAINST("row -row1" IN BOOLEAN MODE);
373 SELECT * FROM 名字 WHERE
374 MATCH(c2) AGAINST('"我幾 火啊"@3' IN BOOLEAN MODE);
375 SELECT * FROM 名字 WHERE
376 MATCH(さようなら,j3) AGAINST('"降り やっ"@4' IN BOOLEAN MODE);
377 SELECT * FROM 名字 WHERE
378 MATCH(행운을빈다) AGAINST('"분은 어를"@2' IN BOOLEAN MODE);
379 SELECT * FROM 名字 WHERE
380 MATCH(e1) AGAINST('"row row"@1' IN BOOLEAN MODE);
381
382 EVAL CREATE TABLE tmp1 $table_row_format AS SELECT * FROM 名字 WHERE 1=2;
383 DROP TABLE 名字;
384 RENAME TABLE tmp1 TO 名字;
385 SHOW CREATE TABLE 名字;
386
387
388 -- echo # Test Case : CJK charset and collation with FTS + transactional statements
389 SELECT * FROM 名字;
390 ALTER TABLE 名字 ADD FULLTEXT INDEX con1 (c1) WITH PARSER ngram;
391 SHOW CREATE TABLE 名字;
392 ALTER TABLE 名字 ADD FULLTEXT INDEX con2 (さようなら,j3) WITH PARSER ngram;
393 SHOW CREATE TABLE 名字;
394 ALTER TABLE 名字 ADD FULLTEXT INDEX con3 (k2) WITH PARSER ngram;
395 SHOW CREATE TABLE 名字;
396 ALTER TABLE 名字 ADD FULLTEXT INDEX con4 (c3) WITH PARSER ngram;
397 SHOW CREATE TABLE 名字;
398 ALTER TABLE 名字 ADD FULLTEXT INDEX con5 (c2) WITH PARSER ngram;
399 SHOW CREATE TABLE 名字;
400 ALTER TABLE 名字 ADD FULLTEXT INDEX con6 (j2) WITH PARSER ngram;
401 SHOW CREATE TABLE 名字;
402 ALTER TABLE 名字 ADD FULLTEXT INDEX con7 (j3) WITH PARSER ngram;
403 SHOW CREATE TABLE 名字;
404 ALTER TABLE 名字 ADD FULLTEXT INDEX con8 (행운을빈다) WITH PARSER ngram;
405 SHOW CREATE TABLE 名字;
406
407 START TRANSACTION;
408 INSERT INTO 名字 (c1,c2,c3,さようなら,j2,j3,행운을빈다,k2,e1) VALUES
409 ('歡迎','你好','喂 ','日の長','言も言わずに','つやってみよう','제가 미국인입니다.','한국말을 공부합니다%','testing row'),
410 ('你好嗎?','我幾好,你呢','好耐冇見','左右','左右される','☆右折⇔左折','제 이름은 Charles입니다.','운동을 좋아해요*','row value'),
411 ('救命啊!','火啊! 你好','生日快樂 ','雨季','雨が降りそう つやってみよう','雨雲','저분은 영어를 잘 합니다','저는 서울에서 살고 있습니다.','recording test');
412
413 SELECT * FROM 名字 WHERE
414 MATCH(c1) AGAINST('命啊');
415 SELECT * FROM 名字 WHERE
416 MATCH(さようなら,j3) AGAINST('やっ');
417 SELECT * FROM 名字 WHERE
418 MATCH(행운을빈다) AGAINST('제가');
419 SELECT * FROM 名字 WHERE
420 MATCH(c2) AGAINST("-我幾 火啊" IN BOOLEAN MODE);
421 SELECT * FROM 名字 WHERE
422 MATCH(j2) AGAINST("やっ-(そう)" IN BOOLEAN MODE);
423 SELECT * FROM 名字 WHERE
424 MATCH(j3) AGAINST(">の長" IN BOOLEAN MODE);
425 SELECT * FROM 名字 WHERE
426 MATCH(행운을빈다) AGAINST("니다 -제가" IN BOOLEAN MODE);
427 SELECT * FROM 名字 WHERE
428 MATCH(c2) AGAINST('"我幾 火啊"@3' IN BOOLEAN MODE);
429 SELECT * FROM 名字 WHERE
430 MATCH(さようなら,j3) AGAINST('"降り やっ"@4' IN BOOLEAN MODE);
431 SELECT * FROM 名字 WHERE
432 MATCH(행운을빈다) AGAINST('"분은 어를"@2' IN BOOLEAN MODE);
433 COMMIT;
434
435 -- disable_result_log
436 ANALYZE TABLE 名字;
437 -- enable_result_log
438
439 SELECT * FROM 名字 WHERE
440 MATCH(c1) AGAINST('命啊');
441 SELECT * FROM 名字 WHERE
442 MATCH(さようなら,j3) AGAINST('やっ');
443 SELECT * FROM 名字 WHERE
444 MATCH(행운을빈다) AGAINST('제가');
445 SELECT * FROM 名字 WHERE
446 MATCH(c2) AGAINST("-我幾 火啊" IN BOOLEAN MODE);
447 SELECT * FROM 名字 WHERE
448 MATCH(j2) AGAINST("やっ-(そう)" IN BOOLEAN MODE);
449 SELECT * FROM 名字 WHERE
450 MATCH(j3) AGAINST(">の長" IN BOOLEAN MODE);
451 SELECT * FROM 名字 WHERE
452 MATCH(행운을빈다) AGAINST("니다 -제가" IN BOOLEAN MODE);
453 SELECT * FROM 名字 WHERE
454 MATCH(c2) AGAINST('"我幾 火啊"@3' IN BOOLEAN MODE);
455 SELECT * FROM 名字 WHERE
456 MATCH(さようなら,j3) AGAINST('"降り やっ"@4' IN BOOLEAN MODE);
457 SELECT * FROM 名字 WHERE
458 MATCH(행운을빈다) AGAINST('"분은 어를"@2' IN BOOLEAN MODE);
459 DELETE FROM 名字;
460 START TRANSACTION;
461 INSERT INTO 名字 (c1,c2,c3,さようなら,j2,j3,행운을빈다,k2,e1) VALUES
462 ('歡迎','你好','喂 ','日の長','言も言わずに','つやってみよう','제가 미국인입니다.','한국말을 공부합니다%','testing row');
463 SAVEPOINT A;
464 INSERT INTO 名字 (c1,c2,c3,さようなら,j2,j3,행운을빈다,k2,e1) VALUES
465 ('你好嗎?','我幾好,你呢','好耐冇見','左右','左右される','☆右折⇔左折','제 이름은 Charles입니다.','운동을 좋아해요*','row value');
466 SAVEPOINT B;
467 INSERT INTO 名字 (c1,c2,c3,さようなら,j2,j3,행운을빈다,k2,e1) VALUES
468 ('救命啊!','火啊! 你好','生日快樂 ','雨季','雨が降りそう つやってみよう','雨雲','저분은 영어를 잘 합니다','저는 서울에서 살고 있습니다.','recording test');
469
470 SELECT * FROM 名字 WHERE
471 MATCH(c1) AGAINST('命啊');
472 SELECT * FROM 名字 WHERE
473 MATCH(さようなら,j3) AGAINST('やっ');
474 SELECT * FROM 名字 WHERE
475 MATCH(행운을빈다) AGAINST('제가');
476 SELECT * FROM 名字 WHERE
477 MATCH(c2) AGAINST("-我幾 火啊" IN BOOLEAN MODE);
478 SELECT * FROM 名字 WHERE
479 MATCH(j2) AGAINST("やっ-(そう)" IN BOOLEAN MODE);
480 SELECT * FROM 名字 WHERE
481 MATCH(j3) AGAINST(">の長" IN BOOLEAN MODE);
482 SELECT * FROM 名字 WHERE
483 MATCH(행운을빈다) AGAINST("니다 -제가" IN BOOLEAN MODE);
484 SELECT * FROM 名字 WHERE
485 MATCH(c2) AGAINST('"我幾 火啊"@3' IN BOOLEAN MODE);
486 SELECT * FROM 名字 WHERE
487 MATCH(さようなら,j3) AGAINST('"降り やっ"@4' IN BOOLEAN MODE);
488 SELECT * FROM 名字 WHERE
489 MATCH(행운을빈다) AGAINST('"분은 어를"@2' IN BOOLEAN MODE);
490 ROLLBACK TO B;
491 COMMIT;
492
493 SELECT * FROM 名字 WHERE
494 MATCH(c1) AGAINST('啊! ');
495 SELECT * FROM 名字 WHERE
496 MATCH(さようなら,j3) AGAINST('やっ');
497 SELECT * FROM 名字 WHERE
498 MATCH(행운을빈다) AGAINST('제가');
499 SELECT * FROM 名字 WHERE
500 MATCH(c2) AGAINST("-我幾 火啊" IN BOOLEAN MODE);
501 SELECT * FROM 名字 WHERE
502 MATCH(j2) AGAINST("やっ-(そう)" IN BOOLEAN MODE);
503 SELECT * FROM 名字 WHERE
504 MATCH(j3) AGAINST(">の長" IN BOOLEAN MODE);
505 SELECT * FROM 名字 WHERE
506 MATCH(행운을빈다) AGAINST("니다 -제가" IN BOOLEAN MODE);
507 SELECT * FROM 名字 WHERE
508 MATCH(c2) AGAINST('"我幾 火啊"@3' IN BOOLEAN MODE);
509 SELECT * FROM 名字 WHERE
510 MATCH(さようなら,j3) AGAINST('"降り やっ"@4' IN BOOLEAN MODE);
511 SELECT * FROM 名字 WHERE
512 MATCH(행운을빈다) AGAINST('"분은 어를"@2' IN BOOLEAN MODE);
513
514 DELETE FROM 名字;
515 START TRANSACTION;
516 INSERT INTO 名字 (c1,c2,c3,さようなら,j2,j3,행운을빈다,k2,e1) VALUES
517 ('歡迎','你好','喂 ','日の長','言も言わずに','つやってみよう','제가 미국인입니다.','한국말을 공부합니다%','testing row');
518 SAVEPOINT A;
519 INSERT INTO 名字 (c1,c2,c3,さようなら,j2,j3,행운을빈다,k2,e1) VALUES
520 ('你好嗎?','我幾好,你呢','好耐冇見','左右','左右される','☆右折⇔左折','제 이름은 Charles입니다.','운동을 좋아해요*','row value');
521 SAVEPOINT B;
522 INSERT INTO 名字 (c1,c2,c3,さようなら,j2,j3,행운을빈다,k2,e1) VALUES
523 ('救命啊!','火啊! 你好','生日快樂 ','雨季','雨が降りそう つやってみよう','雨雲','저분은 영어를 잘 합니다','저는 서울에서 살고 있습니다.','recording test');
524 ROLLBACK;
525 COMMIT;
526
527 -- disable_result_log
528 ANALYZE TABLE 名字;
529 -- enable_result_log
530
531 SELECT * FROM 名字 WHERE
532 MATCH(c1) AGAINST('命啊');
533 SELECT * FROM 名字 WHERE
534 MATCH(さようなら,j3) AGAINST('やっ');
535 SELECT * FROM 名字 WHERE
536 MATCH(행운을빈다) AGAINST('제가');
537 SELECT * FROM 名字 WHERE
538 MATCH(c2) AGAINST("-我幾 火啊" IN BOOLEAN MODE);
539 SELECT * FROM 名字 WHERE
540 MATCH(j2) AGAINST("やっ-(そう)" IN BOOLEAN MODE);
541 SELECT * FROM 名字 WHERE
542 MATCH(j3) AGAINST(">の長" IN BOOLEAN MODE);
543 SELECT * FROM 名字 WHERE
544 MATCH(행운을빈다) AGAINST("니다 -제가" IN BOOLEAN MODE);
545 SELECT * FROM 名字 WHERE
546 MATCH(c2) AGAINST('"我幾 火啊"@3' IN BOOLEAN MODE);
547 SELECT * FROM 名字 WHERE
548 MATCH(さようなら,j3) AGAINST('"降り やっ"@4' IN BOOLEAN MODE);
549 SELECT * FROM 名字 WHERE
550 MATCH(행운을빈다) AGAINST('"분은 어를"@2' IN BOOLEAN MODE);
551
552 EVAL CREATE TABLE tmp1 $table_row_format AS SELECT * FROM 名字 WHERE 1=2;
553 DROP TABLE 名字;
554 RENAME TABLE tmp1 TO 名字;
555 SHOW CREATE TABLE 名字;
556
557 ALTER TABLE 名字 ADD FULLTEXT INDEX con1 (c1) WITH PARSER ngram;
558 SHOW CREATE TABLE 名字;
559 ALTER TABLE 名字 ADD FULLTEXT INDEX con2 (さようなら,j3) WITH PARSER ngram;
560 SHOW CREATE TABLE 名字;
561 ALTER TABLE 名字 ADD FULLTEXT INDEX con3 (k2) WITH PARSER ngram;
562 SHOW CREATE TABLE 名字;
563
564 SELECT * FROM 名字;
565 START TRANSACTION;
566 INSERT INTO 名字 (c1,c2,c3,さようなら,j2,j3,행운을빈다,k2,e1) VALUES
567 ('歡迎','你好','喂 ','日の長','言も言わずに','つやってみよう','제가 미국인입니다.','한국말을 공부합니다%','testing row');
568 SAVEPOINT A;
569 ALTER TABLE 名字 ADD FULLTEXT INDEX con5 (c2) WITH PARSER ngram;
570 INSERT INTO 名字 (c1,c2,c3,さようなら,j2,j3,행운을빈다,k2,e1) VALUES
571 ('你好嗎?','我幾好,你呢','好耐冇見','左右','左右される','☆右折⇔左折','제 이름은 Charles입니다.','운동을 좋아해요*','row value');
572 ALTER TABLE 名字 ADD FULLTEXT INDEX con4 (c3) WITH PARSER ngram;
573 ALTER TABLE 名字 ADD FULLTEXT INDEX con6 (j2) WITH PARSER ngram;
574 SAVEPOINT B;
575 INSERT INTO 名字 (c1,c2,c3,さようなら,j2,j3,행운을빈다,k2,e1) VALUES
576 ('救命啊!','火啊! 你好','生日快樂 ','雨季','雨が降りそう つやってみよう','雨雲','저분은 영어를 잘 합니다','저는 서울에서 살고 있습니다.','recording test');
577 ALTER TABLE 名字 ADD FULLTEXT INDEX con7 (j3) WITH PARSER ngram;
578 ALTER TABLE 名字 ADD FULLTEXT INDEX con8 (행운을빈다) WITH PARSER ngram;
579 COMMIT;
580
581 -- disable_result_log
582 ANALYZE TABLE 名字;
583 -- enable_result_log
584
585 SELECT * FROM 名字 WHERE
586 MATCH(c1) AGAINST('命啊');
587 SELECT * FROM 名字 WHERE
588 MATCH(さようなら,j3) AGAINST('やっ');
589 SELECT * FROM 名字 WHERE
590 MATCH(행운을빈다) AGAINST('제가');
591 SELECT * FROM 名字 WHERE
592 MATCH(c2) AGAINST("-我幾 火啊" IN BOOLEAN MODE);
593 SELECT * FROM 名字 WHERE
594 MATCH(j2) AGAINST("やっ-(そう)" IN BOOLEAN MODE);
595 SELECT * FROM 名字 WHERE
596 MATCH(j3) AGAINST(">の長" IN BOOLEAN MODE);
597 SELECT * FROM 名字 WHERE
598 MATCH(행운을빈다) AGAINST("니다 -제가" IN BOOLEAN MODE);
599 SELECT * FROM 名字 WHERE
600 MATCH(c2) AGAINST('"我幾 火啊"@3' IN BOOLEAN MODE);
601 SELECT * FROM 名字 WHERE
602 MATCH(さようなら,j3) AGAINST('"降り やっ"@4' IN BOOLEAN MODE);
603 SELECT * FROM 名字 WHERE
604 MATCH(행운을빈다) AGAINST('"분은 어를"@2' IN BOOLEAN MODE);
605
606 EVAL CREATE TABLE tmp1 $table_row_format AS SELECT * FROM 名字 WHERE 1=2;
607 DROP TABLE 名字;
608 RENAME TABLE tmp1 TO 名字;
609 SHOW CREATE TABLE 名字;
610
611 -- echo # Test Case : CJK charset and collation , FTS query in condition of update/delete
612
613 SELECT * FROM 名字;
614 ALTER TABLE 名字 ADD FULLTEXT INDEX con1 (c1) WITH PARSER ngram;
615 ALTER TABLE 名字 ADD FULLTEXT INDEX con2 (さようなら,j3) WITH PARSER ngram;
616 ALTER TABLE 名字 ADD FULLTEXT INDEX con3 (k2) WITH PARSER ngram;
617 ALTER TABLE 名字 ADD FULLTEXT INDEX con4 (c3) WITH PARSER ngram;
618 ALTER TABLE 名字 ADD FULLTEXT INDEX con5 (c2) WITH PARSER ngram;
619 ALTER TABLE 名字 ADD FULLTEXT INDEX con6 (j2) WITH PARSER ngram;
620 ALTER TABLE 名字 ADD FULLTEXT INDEX con7 (j3) WITH PARSER ngram;
621 ALTER TABLE 名字 ADD FULLTEXT INDEX con8 (행운을빈다) WITH PARSER ngram;
622
623 INSERT INTO 名字 (c1,c2,c3,さようなら,j2,j3,행운을빈다,k2,e1) VALUES
624 ('歡迎','你好','喂 ','日の長','言も言わずに','つやってみよう','제가 미국인입니다.','한국말을 공부합니다%','testing row'),
625 ('你好嗎?','我幾好,你呢','好耐冇見','左右','左右される','☆右折⇔左折','제 이름은 Charles입니다.','운동을 좋아해요*','row value'),
626 ('救命啊!','火啊! 你好','生日快樂 ','雨季','雨が降りそう つやってみよう','雨雲','저분은 영어를 잘 합니다','저는 서울에서 살고 있습니다.','recording test');
627
628 -- disable_result_log
629 ANALYZE TABLE 名字;
630 -- enable_result_log
631
632 SELECT * FROM 名字 WHERE
633 MATCH(c1) AGAINST('命啊');
634 UPDATE 名字 SET c1 = '你好嗎?' WHERE MATCH(c2) AGAINST('啊!');
635 SELECT * FROM 名字 WHERE
636 MATCH(c1) AGAINST('命啊');
637 DELETE FROM 名字 WHERE
638 MATCH(さようなら,j3) AGAINST("てみ -雨が" IN BOOLEAN MODE);
639 SELECT * FROM 名字 WHERE
640 MATCH(さようなら,j3) AGAINST('てみ');
641 SELECT * FROM 名字;
642 SELECT * FROM 名字 WHERE
643 MATCH (행운을빈다) AGAINST('니다');
644 DELETE FROM 名字 WHERE
645 MATCH (행운을빈다) AGAINST('니다');
646 SELECT * FROM 名字;
647
648 DROP TABLE 名字;