Debian Codegraph @

mysql-test/suite/innodb_fts/r/misc.result

raw
1 CREATE TABLE t1 (
2 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
3 a VARCHAR(200),
4 b TEXT
5 ) ENGINE = InnoDB;
6 INSERT INTO t1 (a,b) VALUES
7 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
8 ('How To Use MySQL Well','After you went through a ...'),
9 ('Optimizing MySQL','In this tutorial we will show ...');
10 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
11 SHOW CREATE TABLE t1;
12 Table Create Table
13 t1 CREATE TABLE `t1` (
14 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
15 `a` varchar(200) DEFAULT NULL,
16 `b` text DEFAULT NULL,
17 PRIMARY KEY (`id`),
18 FULLTEXT KEY `idx` (`a`,`b`)
19 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
20 INSERT INTO t1 (a,b) VALUES
21 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
22 ('MySQL vs. YourSQL','In the following database comparison ...'),
23 ('MySQL Security','When configured properly, MySQL ...');
24 SELECT id FROM t1 WHERE MATCH (a,b)
25 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
26 id
27 1
28 3
29 select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
30 id
31 select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
32 id
33 select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
34 id
35 select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
36 id
37 select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
38 id x
39 1 0
40 2 0
41 3 0
42 4 0
43 5 0
44 6 0
45 select id, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
46 id x
47 1 0
48 2 0
49 3 0
50 4 0
51 5 0
52 6 0
53 select id from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
54 id
55 select id from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
56 id
57 select id from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
58 id
59 select id from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
60 id
61 select id from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION);
62 id
63 select id from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION);
64 id
65 ALTER TABLE t1 DROP INDEX idx;
66 CREATE FULLTEXT INDEX idx on t1 (a,b);
67 SELECT id FROM t1 WHERE MATCH (a,b)
68 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
69 id
70 1
71 3
72 select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
73 id
74 select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
75 id
76 select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
77 id
78 select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
79 id
80 select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
81 id x
82 1 0
83 2 0
84 3 0
85 4 0
86 5 0
87 6 0
88 select id, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
89 id x
90 1 0
91 2 0
92 3 0
93 4 0
94 5 0
95 6 0
96 select id from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
97 id
98 select id from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
99 id
100 select id from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
101 id
102 select id from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
103 id
104 select id from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION);
105 id
106 select id from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION);
107 id
108 INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...');
109 INSERT INTO t1 (a,b) VALUES
110 ('test proximity search, test, proximity and phrase',
111 'search, with proximity innodb');
112 INSERT INTO t1 (a,b) VALUES
113 ('test proximity fts search, test, proximity and phrase',
114 'search, with proximity innodb');
115 INSERT INTO t1 (a,b) VALUES
116 ('test more proximity fts search, test, more proximity and phrase',
117 'search, with proximity innodb');
118 SELECT id FROM t1
119 WHERE MATCH (a,b)
120 AGAINST ('"proximity search"@2' IN BOOLEAN MODE);
121 id
122 8
123 SELECT id FROM t1
124 WHERE MATCH (a,b)
125 AGAINST ('"proximity search"@1' IN BOOLEAN MODE);
126 id
127 SELECT id FROM t1
128 WHERE MATCH (a,b)
129 AGAINST ('"proximity search"@3' IN BOOLEAN MODE);
130 id
131 8
132 9
133 10
134 SELECT id FROM t1
135 WHERE MATCH (a,b)
136 AGAINST ('"test proximity"@3' IN BOOLEAN MODE);
137 id
138 8
139 9
140 10
141 SELECT id FROM t1
142 WHERE MATCH (a,b)
143 AGAINST ('"more test proximity"@3' IN BOOLEAN MODE);
144 id
145 10
146 SELECT id FROM t1
147 WHERE MATCH (a,b)
148 AGAINST ('"more test proximity"@2' IN BOOLEAN MODE);
149 id
150 SELECT id FROM t1
151 WHERE MATCH (a,b)
152 AGAINST ('"more fts proximity"@02' IN BOOLEAN MODE);
153 id
154 DROP TABLE t1;
155 CREATE TABLE t1 (
156 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
157 a VARCHAR(200),
158 b TEXT
159 ) ENGINE = InnoDB;
160 CREATE FULLTEXT INDEX idx on t1 (a,b);
161 INSERT INTO t1 (a,b) VALUES
162 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
163 ('How To Use MySQL Well','After you went through a ...'),
164 ('Optimizing MySQL','In this tutorial we will show ...');
165 INSERT INTO t1 (a,b) VALUES
166 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
167 ('MySQL vs. YourSQL','In the following database comparison ...'),
168 ('MySQL Security','When configured properly, MySQL ...');
169 SELECT id FROM t1 WHERE MATCH (a,b)
170 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
171 id
172 1
173 3
174 SELECT id FROM t1 WHERE id = (SELECT MAX(id) FROM t1 WHERE MATCH (a,b)
175 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE));
176 id
177 3
178 SELECT id FROM t1 WHERE id = (SELECT MIN(id) FROM t1 WHERE MATCH (a,b)
179 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE));
180 id
181 1
182 SELECT id FROM t1 WHERE id = (SELECT MIN(id) FROM t1 WHERE MATCH (a,b)
183 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) OR id = 3 ;
184 id
185 1
186 3
187 SELECT id FROM t1 WHERE CONCAT(t1.a,t1.b) IN (
188 SELECT CONCAT(a,b) FROM t1 AS t2 WHERE
189 MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
190 ) OR t1.id = 3 ;
191 id
192 1
193 3
194 SELECT id FROM t1 WHERE CONCAT(t1.a,t1.b) IN (
195 SELECT CONCAT(a,b) FROM t1 AS t2
196 WHERE MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
197 AND t2.id != 3) ;
198 id
199 1
200 SELECT id FROM t1 WHERE id IN (SELECT MIN(id) FROM t1 WHERE
201 MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) OR id = 3 ;
202 id
203 1
204 3
205 SELECT id FROM t1 WHERE id NOT IN (SELECT MIN(id) FROM t1
206 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) ;
207 id
208 2
209 3
210 4
211 5
212 6
213 SELECT id FROM t1 WHERE EXISTS (SELECT t2.id FROM t1 AS t2 WHERE
214 MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
215 AND t1.id = t2.id) ;
216 id
217 1
218 3
219 SELECT id FROM t1 WHERE NOT EXISTS (SELECT t2.id FROM t1 AS t2 WHERE
220 MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
221 AND t1.id = t2.id) ;
222 id
223 2
224 4
225 5
226 6
227 DROP TABLE t1;
228 CREATE TABLE t1 (
229 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
230 a VARCHAR(200),
231 b TEXT ,
232 FULLTEXT (a,b)
233 ) ENGINE = InnoDB;
234 INSERT INTO t1(a,b) VALUES('MySQL has now support', 'for full-text search'),
235 ('Full-text indexes', 'are called collections'),
236 ('Only MyISAM tables','support collections'),
237 ('Function MATCH ... AGAINST()','is used to do a search'),
238 ('Full-text search in MySQL', 'implements vector space model');
239 SELECT id FROM t1 WHERE t1.id = (SELECT MAX(t2.id) FROM t1 AS t2 WHERE
240 MATCH(t2.a,t2.b) AGAINST("+support +collections" IN BOOLEAN MODE));
241 id
242 3
243 SELECT id FROM t1 WHERE t1.id != (SELECT MIN(t2.id) FROM t1 AS t2 WHERE
244 MATCH(t2.a,t2.b) AGAINST("+search" IN BOOLEAN MODE));
245 id
246 2
247 3
248 4
249 5
250 SELECT id FROM t1 WHERE t1.id IN (SELECT t2.id FROM t1 AS t2 WHERE
251 MATCH (t2.a,t2.b) AGAINST ("+call* +coll*" IN BOOLEAN MODE));
252 id
253 2
254 SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE
255 MATCH t2.a,t2.b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE) AND t2.id=t1.id);
256 id
257 1
258 INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...');
259 INSERT INTO t1 (a,b) VALUES
260 ('test proximity search, test, proximity and phrase',
261 'search, with proximity innodb');
262 INSERT INTO t1 (a,b) VALUES
263 ('test proximity fts search, test, proximity and phrase',
264 'search, with proximity innodb');
265 INSERT INTO t1 (a,b) VALUES
266 ('test more proximity fts search, test, more proximity and phrase',
267 'search, with proximity innodb');
268 SELECT id FROM t1 WHERE t1.id = (SELECT MAX(t2.id) FROM t1 AS t2 WHERE
269 MATCH(t2.a,t2.b) AGAINST ('"proximity search"@2' IN BOOLEAN MODE));
270 id
271 7
272 SELECT id FROM t1 WHERE t1.id > (SELECT MIN(t2.id) FROM t1 AS t2 WHERE
273 MATCH(t2.a,t2.b) AGAINST ('"proximity search"@2' IN BOOLEAN MODE));
274 id
275 8
276 9
277 SELECT id FROM t1 WHERE t1.id IN (SELECT t2.id FROM t1 AS t2 WHERE
278 MATCH (t2.a,t2.b) AGAINST ('"proximity search"@2' IN BOOLEAN MODE));
279 id
280 7
281 SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE
282 MATCH t2.a,t2.b AGAINST ('"proximity search"@2' IN BOOLEAN MODE)
283 AND t2.id=t1.id);
284 id
285 7
286 SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE
287 MATCH t2.a,t2.b AGAINST ('"more test proximity"@3' IN BOOLEAN MODE)
288 AND t2.id=t1.id);
289 id
290 9
291 SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE
292 MATCH t2.a,t2.b AGAINST ('"more test proximity"@2' IN BOOLEAN MODE)
293 AND t2.id=t1.id);
294 id
295 CREATE TABLE t2 ENGINE = InnoDB AS SELECT id FROM t1 WHERE
296 MATCH a,b AGAINST ('support') ;
297 SHOW CREATE TABLE t2;
298 Table Create Table
299 t2 CREATE TABLE `t2` (
300 `id` int(10) unsigned NOT NULL DEFAULT 0
301 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
302 SELECT id FROM t2;
303 id
304 1
305 3
306 DROP TABLE t2;
307 CREATE TABLE t2 ENGINE = InnoDB AS SELECT id FROM t1 WHERE
308 MATCH a,b AGAINST("+support +collections" IN BOOLEAN MODE);
309 SHOW CREATE TABLE t2;
310 Table Create Table
311 t2 CREATE TABLE `t2` (
312 `id` int(10) unsigned NOT NULL DEFAULT 0
313 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
314 SELECT id FROM t2;
315 id
316 3
317 DROP TABLE t2;
318 CREATE TABLE t2 ENGINE = InnoDB AS SELECT id FROM t1 WHERE
319 MATCH a,b AGAINST ('"proximity search"@10' IN BOOLEAN MODE);
320 SHOW CREATE TABLE t2;
321 Table Create Table
322 t2 CREATE TABLE `t2` (
323 `id` int(10) unsigned NOT NULL DEFAULT 0
324 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
325 SELECT id FROM t2;
326 id
327 7
328 8
329 9
330 DROP TABLE t2;
331 DROP TABLE t1;
332 CREATE TABLE t1 (
333 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
334 a VARCHAR(200),
335 b TEXT
336 ) ENGINE = InnoDB;
337 CREATE FULLTEXT INDEX idx on t1 (a,b);
338 INSERT INTO t1 (a,b) VALUES
339 ('MySQL from Tutorial','DBMS stands for DataBase ...');
340 INSERT INTO t1 (a,b) VALUES
341 ('when To Use MySQL Well','After that you went through a ...');
342 INSERT INTO t1 (a,b) VALUES
343 ('where will Optimizing MySQL','what In this tutorial we will show ...');
344 INSERT INTO t1 (a,b) VALUES
345 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
346 ('MySQL vs. YourSQL','In the following database comparison ...'),
347 ('MySQL Security','When configured properly, MySQL null...');
348 SELECT COUNT(*) FROM t1;
349 COUNT(*)
350 106
351 SELECT COUNT(*) FROM t1 WHERE a IS NULL;
352 COUNT(*)
353 100
354 SELECT COUNT(*) FROM t1 WHERE b IS NOT NULL;
355 COUNT(*)
356 6
357 SELECT id FROM t1
358 WHERE MATCH (a,b)
359 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
360 id
361 1
362 103
363 SELECT id FROM t1
364 WHERE MATCH (a,b)
365 AGAINST (NULL IN NATURAL LANGUAGE MODE);
366 id
367 SELECT id FROM t1
368 WHERE MATCH (a,b)
369 AGAINST (NULL WITH QUERY EXPANSION);
370 id
371 SELECT id FROM t1
372 WHERE MATCH (a,b)
373 AGAINST ('null' IN NATURAL LANGUAGE MODE);
374 id
375 106
376 SELECT id FROM t1 WHERE MATCH (a,b)
377 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
378 id
379 106
380 1
381 52
382 103
383 104
384 SELECT id FROM t1 WHERE MATCH (a,b)
385 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE) AND (a IS NOT NULL OR b IS NOT NULL);
386 id
387 106
388 1
389 52
390 103
391 104
392 SELECT id FROM t1 WHERE MATCH (a,b)
393 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE) AND (a IS NULL AND b IS NOT NULL);
394 id
395 SELECT id FROM t1 WHERE MATCH (a,b)
396 AGAINST ('DBMS Security' IN BOOLEAN MODE);
397 id
398 1
399 106
400 SELECT COUNT(*) FROM t1
401 WHERE MATCH (a,b)
402 AGAINST ('database' WITH QUERY EXPANSION);
403 COUNT(*)
404 6
405 SELECT id FROM t1
406 WHERE MATCH (a,b)
407 AGAINST ('"following database"@10' IN BOOLEAN MODE);
408 id
409 105
410 DROP TABLE t1;
411 drop table if exists t50;
412 set names utf8;
413 "----------Test1---------"
414 create table t50 (s1 varchar(60) character set utf8 collate utf8_bin) engine = innodb;
415 create fulltext index i on t50 (s1);
416 insert into t50 values ('ABCDE'),('FGHIJ'),('KLMNO'),('VÐƷWİ');
417 select * from t50 where match(s1) against ('VÐƷWİ');
418 s1
419 VÐƷWİ
420 drop table t50;
421 "----------Test2---------"
422 create table t50 (s1 int unsigned primary key auto_increment, s2
423 varchar(60) character set utf8) engine = innodb;
424 create fulltext index i on t50 (s2);
425 insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCDE');
426 select * from t50 order by s2;
427 s1 s2
428 4 ABCDE
429 1 FGHIJ
430 2 KLMNO
431 3 VÐƷWİ
432 drop table t50;
433 "----------Test3---------"
434 create table t50 (id int unsigned primary key auto_increment, s2
435 varchar(60) character set utf8) engine = innodb;
436 create fulltext index i on t50 (s2);
437 insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCDE');
438 set @@autocommit=0;
439 update t50 set s2 = lower(s2);
440 update t50 set s2 = upper(s2);
441 commit;
442 select * from t50 where match(s2) against ('VÐƷWİ FGHIJ KLMNO ABCDE' in boolean mode);
443 id s2
444 1 FGHIJ
445 2 KLMNO
446 3 VÐƷWI
447 4 ABCDE
448 select * from t50;
449 id s2
450 1 FGHIJ
451 2 KLMNO
452 3 VÐƷWI
453 4 ABCDE
454 drop table t50;
455 set @@autocommit=1;
456 "----------Test4---------"
457 create table t50 (id int unsigned primary key auto_increment, s2
458 varchar(60) character set utf8) engine = innodb;
459 create fulltext index i on t50 (s2);
460 insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCD*');
461 select * from t50 where match(s2) against ('abcd*' in natural language
462 mode);
463 id s2
464 4 ABCD*
465 select * from t50 where match(s2) against ('abcd*' in boolean mode);
466 id s2
467 4 ABCD*
468 drop table t50;
469 "----------Test5---------"
470 create table t50 (s1 int, s2 varchar(200), fulltext key(s2)) engine = innodb;
471 set @@autocommit=0;
472 insert into t50 values (1,'Sunshine'),(2,'Lollipops');
473 select * from t50 where match(s2) against('Rainbows');
474 s1 s2
475 rollback;
476 select * from t50;
477 s1 s2
478 drop table t50;
479 set @@autocommit=1;
480 "----------Test6---------"
481 CREATE TABLE t1 (
482 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
483 a VARCHAR(200),
484 b TEXT
485 ) ENGINE = InnoDB;
486 INSERT INTO t1 (a,b) VALUES
487 ('aab` MySQL Tutorial','DBMS stands for DataBase ...') ,
488 ('aas How To Use MySQL Well','After you went through a ...'),
489 ('aac Optimizing MySQL','In this tutorial we will show ...');
490 INSERT INTO t1 (a,b) VALUES
491 ('aac 1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
492 ('aab MySQL vs. YourSQL','In the following database comparison ...'),
493 ('aaa MySQL Security','When configured properly, MySQL ...');
494 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
495 SELECT * FROM t1 ORDER BY MATCH(a,b) AGAINST ('aac') DESC;
496 id a b
497 3 aac Optimizing MySQL In this tutorial we will show ...
498 4 aac 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
499 1 aab` MySQL Tutorial DBMS stands for DataBase ...
500 2 aas How To Use MySQL Well After you went through a ...
501 5 aab MySQL vs. YourSQL In the following database comparison ...
502 6 aaa MySQL Security When configured properly, MySQL ...
503 SELECT * FROM t1 ORDER BY MATCH(a,b) AGAINST ('aab') DESC;
504 id a b
505 1 aab` MySQL Tutorial DBMS stands for DataBase ...
506 5 aab MySQL vs. YourSQL In the following database comparison ...
507 2 aas How To Use MySQL Well After you went through a ...
508 3 aac Optimizing MySQL In this tutorial we will show ...
509 4 aac 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
510 6 aaa MySQL Security When configured properly, MySQL ...
511 "----------Test7---------"
512 select * from t1 where match(a,b) against ('aaa')
513 union select * from t1 where match(a,b) against ('aab')
514 union select * from t1 where match(a,b) against ('aac');
515 id a b
516 6 aaa MySQL Security When configured properly, MySQL ...
517 1 aab` MySQL Tutorial DBMS stands for DataBase ...
518 5 aab MySQL vs. YourSQL In the following database comparison ...
519 3 aac Optimizing MySQL In this tutorial we will show ...
520 4 aac 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
521 select * from t1 where match(a,b) against ('aaa')
522 or match(a,b) against ('aab')
523 or match(a,b) against ('aac');
524 id a b
525 1 aab` MySQL Tutorial DBMS stands for DataBase ...
526 3 aac Optimizing MySQL In this tutorial we will show ...
527 4 aac 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
528 5 aab MySQL vs. YourSQL In the following database comparison ...
529 6 aaa MySQL Security When configured properly, MySQL ...
530 DROP TABLE t1;
531 "----------Test8---------"
532 CREATE TABLE t1 (
533 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
534 a VARCHAR(200),
535 b TEXT
536 ) ENGINE = InnoDB;
537 INSERT INTO t1 (a,b) VALUES
538 ('MySQL Tutorial','DBMS stands for DataBase ... abcd') ,
539 ('How To Use MySQL Well','After you went through a q ...abdd'),
540 ('Optimizing MySQL','In this tutorial we will show ...abed');
541 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
542 SHOW CREATE TABLE t1;
543 Table Create Table
544 t1 CREATE TABLE `t1` (
545 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
546 `a` varchar(200) DEFAULT NULL,
547 `b` text DEFAULT NULL,
548 PRIMARY KEY (`id`),
549 FULLTEXT KEY `idx` (`a`,`b`)
550 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
551 INSERT INTO t1 (a,b) VALUES
552 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. q ...'),
553 ('MySQL vs. YourSQL use','In the following database comparison ...'),
554 ('MySQL Security','When run configured properly, MySQL ...');
555 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run');
556 id a b
557 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. q ...
558 6 MySQL Security When run configured properly, MySQL ...
559 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('use');
560 id a b
561 2 How To Use MySQL Well After you went through a q ...abdd
562 5 MySQL vs. YourSQL use In the following database comparison ...
563 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('went');
564 id a b
565 2 How To Use MySQL Well After you went through a q ...abdd
566 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run') AND NOT MATCH(a,b) AGAINST ('q');
567 id a b
568 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. q ...
569 6 MySQL Security When run configured properly, MySQL ...
570 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('use') AND NOT MATCH(a,b) AGAINST ('q');
571 id a b
572 2 How To Use MySQL Well After you went through a q ...abdd
573 5 MySQL vs. YourSQL use In the following database comparison ...
574 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('went') AND NOT MATCH(a,b) AGAINST ('q');
575 id a b
576 2 How To Use MySQL Well After you went through a q ...abdd
577 "----------Test9---------"
578 CREATE TABLE t2 AS SELECT * FROM t1;
579 ALTER TABLE t2 ENGINE=MYISAM;
580 CREATE FULLTEXT INDEX i ON t2 (a,b);
581 SET @x = (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('run'));
582 SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('use'));
583 SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('went'));
584 SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('run'));
585 SET @x2 = (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('run'));
586 SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('use'));
587 SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('went'));
588 SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('run'));
589 SELECT @x, @x2;
590 @x @x2
591 7 0
592 DROP TABLE t2;
593 "----------Test10---------"
594 CREATE TABLE t2 AS SELECT * FROM t1;
595 ALTER TABLE t2 ENGINE=MYISAM;
596 CREATE FULLTEXT INDEX i ON t2 (a,b);
597 SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE);
598 COUNT(*)
599 1
600 SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE);
601 COUNT(*)
602 1
603 DROP TABLE t2;
604 "----------Test11---------"
605 CREATE TABLE t2 AS SELECT * FROM t1;
606 ALTER TABLE t2 ENGINE = MYISAM;
607 CREATE FULLTEXT INDEX i ON t2 (a,b);
608 ALTER TABLE t2 ENGINE=InnoDB;
609 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run');
610 id a b
611 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. q ...
612 6 MySQL Security When run configured properly, MySQL ...
613 SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE);
614 COUNT(*)
615 1
616 DROP TABLE t2,t1;
617 "----------Test13---------"
618 set names utf8;
619 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8_SPANISH_CI) ENGINE = InnoDB;
620 CREATE FULLTEXT INDEX i ON t1 (s2);
621 INSERT INTO t1 VALUES (1,'aaCen'),(2,'aaCha'),(3,'aaCio'),(4,'aaçen'),(5,'aaçha'),(6,'aaçio');
622 SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('aach*' IN BOOLEAN MODE);
623 s1 s2
624 2 aaCha
625 5 aaçha
626 SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('aaC*' IN BOOLEAN MODE);
627 s1 s2
628 1 aaCen
629 2 aaCha
630 3 aaCio
631 4 aaçen
632 5 aaçha
633 6 aaçio
634 DROP TABLE t1;
635 "----------Test14---------"
636 CREATE TABLE t1(s1 INT , s2 VARCHAR(100) CHARACTER SET sjis) ENGINE = InnoDB;
637 CREATE FULLTEXT INDEX i ON t1 (s2);
638 INSERT INTO t1 VALUES (1,'ペペペ'),(2,'テテテ'),(3,'ルルル'),(4,'グググ');
639 DROP TABLE t1;
640 "----------Test15---------"
641 CREATE TABLE t1 (s1 VARCHAR (60) CHARACTER SET UTF8 COLLATE UTF8_UNICODE_520_CI) ENGINE = MyISAM;
642 CREATE FULLTEXT INDEX i ON t1 (s1);
643 INSERT INTO t1 VALUES
644 ('a'),('b'),('c'),('d'),('ŁŁŁŁ'),('LLLL'),(NULL),('ŁŁŁŁ ŁŁŁŁ'),('LLLLLLLL');
645 SELECT * FROM t1 WHERE MATCH(s1) AGAINST ('LLLL' COLLATE UTF8_UNICODE_520_CI);
646 s1
647 ŁŁŁŁ
648 LLLL
649 ŁŁŁŁ ŁŁŁŁ
650 DROP TABLE if EXISTS t2;
651 Warnings:
652 Note 1051 Unknown table 'test.t2'
653 CREATE TABLE t2 (s1 VARCHAR(60) CHARACTER SET UTF8 COLLATE UTF8_POLISH_CI) ENGINE = InnoDB;
654 CREATE FULLTEXT INDEX i ON t2 ( s1);
655 INSERT INTO t2 VALUES
656 ('a'),('b'),('c'),('d'),('ŁŁŁŁ'),('LLLL'),(NULL),('ŁŁŁŁ ŁŁŁŁ'),('LLLLLLLL');
657 SELECT * FROM t2 WHERE MATCH(s1) AGAINST ('LLLL' COLLATE UTF8_UNICODE_520_CI);
658 s1
659 LLLL
660 DROP TABLE t1,t2;
661 "----------Test16---------"
662 CREATE TABLE t1 (s1 INT, s2 VARCHAR(50) CHARACTER SET UTF8) ENGINE = InnoDB;
663 CREATE FULLTEXT INDEX i ON t1(s2);
664 INSERT INTO t1 VALUES (2, 'ğė Daśi p ');
665 SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('+p +"ğė Daśi*"' IN BOOLEAN MODE);
666 s1 s2
667 DROP TABLE t1;
668 "----------Test19---------"
669 CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8) ENGINE = InnoDB;
670 INSERT INTO t1 VALUES (1,'İóëɠ');
671 CREATE FULLTEXT INDEX i ON t1 (char_column);
672 SELECT * FROM t1 WHERE MATCH(char_column) AGAINST ('"İóëɠ"' IN BOOLEAN MODE);
673 id char_column
674 1 İóëɠ
675 DROP TABLE t1;
676 "----------Test20---------"
677 CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF32, char_column2 VARCHAR(60) character set utf8) ENGINE = InnoDB;
678 INSERT INTO t1 (char_column) VALUES ('abcde'),('fghij'),('klmno'),('qrstu');
679 UPDATE t1 SET char_column2 = char_column;
680 CREATE FULLTEXT INDEX i ON t1 (char_column2);
681 SELECT * FROM t1 WHERE MATCH(char_column) AGAINST ('abc*' IN BOOLEAN MODE);
682 ERROR HY000: Can't find FULLTEXT index matching the column list
683 DROP TABLE t1;
684 "----------Test22---------"
685 CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8) ENGINE = InnoDB;
686 INSERT INTO t1 VALUES (1,'aaa'),(2,'bbb'),(3,'ccc');
687 CREATE FULLTEXT INDEX i ON t1 (char_column);
688 HANDLER t1 OPEN;
689 HANDLER t1 READ i = ('aaa');
690 ERROR HY000: FULLTEXT index `i` does not support this operation
691 DROP TABLE t1;
692 "----------Test25---------"
693 CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8 COLLATE UTF8_CROATIAN_CI) ENGINE=InnoDB;
694 INSERT INTO t1 VALUES (1,'LJin'),(2,'ljin'),(3,'lmin'),(4,'LJLJLJLJLJ');
695 CREATE FULLTEXT INDEX i ON t1 (char_column);
696 SELECT count(*) FROM t1 WHERE MATCH (char_column) AGAINST ('lj*' IN BOOLEAN MODE);
697 count(*)
698 3
699 DROP TABLE t1;
700 "----------Test27---------"
701 CREATE TABLE t1 (id INT,char_column VARCHAR(60)) ENGINE=InnoDB;
702 SET @@autocommit=0;
703 CREATE FULLTEXT INDEX i ON t1 (char_column);
704 INSERT INTO t1 values (1,'aaa');
705 "restart server..."
706 # Restart the server
707 --source include/restart_mysqld.inc
708 DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb')
709 SET @@autocommit=1;
710 DROP TABLE t1;
711 "----------Test28---------"
712 drop table if exists `fts_test`;
713 Warnings:
714 Note 1051 Unknown table 'test.fts_test'
715 create table `fts_test`(`a` text,fulltext key(`a`))engine=innodb;
716 set session autocommit=0;
717 insert into `fts_test` values ('');
718 savepoint `b`;
719 savepoint `b`;
720 set session autocommit=1;
721 DROP TABLE fts_test;
722 "----------Test29---------"
723 CREATE TABLE articles (
724 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
725 title VARCHAR(200),
726 body TEXT,
727 FULLTEXT (title,body)
728 ) ENGINE=InnoDB;
729 INSERT INTO articles (title,body) VALUES
730 ('MySQL Tutorial','DBMS stands for DataBase ...');
731 start transaction;
732 INSERT INTO articles (title,body) VALUES
733 ('How To Use MySQL Well','After you went through a ...');
734 savepoint `a1`;
735 INSERT INTO articles (title,body) VALUES
736 ('Optimizing MySQL','In this tutorial we will show ...');
737 savepoint `a2`;
738 INSERT INTO articles (title,body) VALUES
739 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...');
740 savepoint `a3`;
741 INSERT INTO articles (title,body) VALUES
742 ('MySQL vs. YourSQL','In the following database comparison ...');
743 savepoint `a4`;
744 SELECT * FROM articles
745 WHERE MATCH (title,body)
746 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
747 id title body
748 1 MySQL Tutorial DBMS stands for DataBase ...
749 rollback to savepoint a3;
750 select title, body from articles;
751 title body
752 MySQL Tutorial DBMS stands for DataBase ...
753 How To Use MySQL Well After you went through a ...
754 Optimizing MySQL In this tutorial we will show ...
755 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
756 INSERT INTO articles (title,body) VALUES
757 ('MySQL Security','When configured properly, MySQL ...');
758 savepoint `a5`;
759 select title, body from articles;
760 title body
761 MySQL Tutorial DBMS stands for DataBase ...
762 How To Use MySQL Well After you went through a ...
763 Optimizing MySQL In this tutorial we will show ...
764 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
765 MySQL Security When configured properly, MySQL ...
766 rollback to savepoint a2;
767 select title, body from articles;
768 title body
769 MySQL Tutorial DBMS stands for DataBase ...
770 How To Use MySQL Well After you went through a ...
771 Optimizing MySQL In this tutorial we will show ...
772 commit;
773 SELECT * FROM articles
774 WHERE MATCH (title,body)
775 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
776 id title body
777 1 MySQL Tutorial DBMS stands for DataBase ...
778 SELECT * FROM articles
779 WHERE MATCH (title,body)
780 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
781 id title body
782 1 MySQL Tutorial DBMS stands for DataBase ...
783 3 Optimizing MySQL In this tutorial we will show ...
784 DROP TABLE articles;
785 "----------Test30---------"
786 CREATE TABLE articles (
787 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
788 title VARCHAR(200),
789 body TEXT,
790 FULLTEXT (title,body)
791 ) ENGINE=InnoDB;
792 INSERT INTO articles (title,body) VALUES
793 ('MySQL Tutorial','DBMS stands for DataBase ...');
794 start transaction;
795 INSERT INTO articles (title,body) VALUES
796 ('How To Use MySQL Well','After you went through a ...');
797 savepoint `a1`;
798 INSERT INTO articles (title,body) VALUES
799 ('Optimizing MySQL','In this tutorial we will show ...');
800 savepoint `a2`;
801 INSERT INTO articles (title,body) VALUES
802 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...');
803 savepoint `a3`;
804 INSERT INTO articles (title,body) VALUES
805 ('MySQL vs. YourSQL','In the following database comparison ...');
806 savepoint `a4`;
807 SELECT * FROM articles
808 WHERE MATCH (title,body)
809 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
810 id title body
811 1 MySQL Tutorial DBMS stands for DataBase ...
812 rollback to savepoint a3;
813 select title, body from articles;
814 title body
815 MySQL Tutorial DBMS stands for DataBase ...
816 How To Use MySQL Well After you went through a ...
817 Optimizing MySQL In this tutorial we will show ...
818 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
819 INSERT INTO articles (title,body) VALUES
820 ('MySQL Security','When configured properly, MySQL ...');
821 savepoint `a5`;
822 select title, body from articles;
823 title body
824 MySQL Tutorial DBMS stands for DataBase ...
825 How To Use MySQL Well After you went through a ...
826 Optimizing MySQL In this tutorial we will show ...
827 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
828 MySQL Security When configured properly, MySQL ...
829 rollback to savepoint a2;
830 select title, body from articles;
831 title body
832 MySQL Tutorial DBMS stands for DataBase ...
833 How To Use MySQL Well After you went through a ...
834 Optimizing MySQL In this tutorial we will show ...
835 rollback;
836 SELECT * FROM articles
837 WHERE MATCH (title,body)
838 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
839 id title body
840 1 MySQL Tutorial DBMS stands for DataBase ...
841 SELECT * FROM articles
842 WHERE MATCH (title,body)
843 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
844 id title body
845 1 MySQL Tutorial DBMS stands for DataBase ...
846 DROP TABLE articles;
847 CREATE TABLE articles (
848 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
849 title VARCHAR(200),
850 body TEXT,
851 FULLTEXT (title,body)
852 ) ENGINE=InnoDB;
853 INSERT INTO articles (title,body) VALUES
854 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
855 ('How To Use MySQL Well','After you went through a ...'),
856 ('Optimizing MySQL','In this tutorial we will show ...'),
857 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
858 ('MySQL vs. YourSQL','In the following database comparison ...'),
859 ('MySQL Security','When configured properly, MySQL ...');
860 ANALYZE TABLE articles;
861 SELECT *, MATCH(title, body) AGAINST ('-database +MySQL' IN BOOLEAN MODE) AS score from articles;
862 id title body score
863 1 MySQL Tutorial DBMS stands for DataBase ... 0
864 2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186
865 3 Optimizing MySQL In this tutorial we will show ... 0.000000001885928302414186
866 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0.000000001885928302414186
867 5 MySQL vs. YourSQL In the following database comparison ... 0
868 6 MySQL Security When configured properly, MySQL ... 0.000000003771856604828372
869 SELECT *, MATCH(title, body) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score FROM articles;
870 id title body score
871 1 MySQL Tutorial DBMS stands for DataBase ... 0
872 2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186
873 3 Optimizing MySQL In this tutorial we will show ... 0.000000001885928302414186
874 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0.000000001885928302414186
875 5 MySQL vs. YourSQL In the following database comparison ... 0
876 6 MySQL Security When configured properly, MySQL ... 0.000000003771856604828372
877 SELECT * FROM articles where MATCH(title, body) AGAINST ('MySQL - (database - tutorial)' IN BOOLEAN MODE);
878 id title body
879 6 MySQL Security When configured properly, MySQL ...
880 1 MySQL Tutorial DBMS stands for DataBase ...
881 2 How To Use MySQL Well After you went through a ...
882 3 Optimizing MySQL In this tutorial we will show ...
883 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
884 SELECT * FROM articles where MATCH(title, body) AGAINST ('MySQL - (- tutorial database)' IN BOOLEAN MODE);
885 id title body
886 6 MySQL Security When configured properly, MySQL ...
887 1 MySQL Tutorial DBMS stands for DataBase ...
888 2 How To Use MySQL Well After you went through a ...
889 3 Optimizing MySQL In this tutorial we will show ...
890 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
891 SELECT * FROM articles where MATCH(title, body) AGAINST ('MySQL - (- tutorial database) -Tricks' IN BOOLEAN MODE);
892 id title body
893 6 MySQL Security When configured properly, MySQL ...
894 1 MySQL Tutorial DBMS stands for DataBase ...
895 2 How To Use MySQL Well After you went through a ...
896 3 Optimizing MySQL In this tutorial we will show ...
897 SELECT * FROM articles where MATCH(title, body) AGAINST ('-Tricks MySQL - (- tutorial database)' IN BOOLEAN MODE);
898 id title body
899 6 MySQL Security When configured properly, MySQL ...
900 1 MySQL Tutorial DBMS stands for DataBase ...
901 2 How To Use MySQL Well After you went through a ...
902 3 Optimizing MySQL In this tutorial we will show ...
903 DROP TABLE articles;
904 drop table if exists t1;
905 Warnings:
906 Note 1051 Unknown table 'test.t1'
907 create table t1 (FTS_DOC_ID bigint unsigned auto_increment not null primary key,
908 title varchar(200),body text,fulltext(title,body)) engine=innodb;
909 insert into t1 set body='test';
910 select * from t1 where match(title,body) against('%test');
911 FTS_DOC_ID title body
912 1 NULL test
913 select * from t1 where match(title,body) against('%');
914 FTS_DOC_ID title body
915 select * from t1 where match(title,body) against('%%%%');
916 FTS_DOC_ID title body
917 drop table t1;
918 CREATE DATABASE `benu database`;
919 USE `benu database`;
920 CREATE TABLE t1 (
921 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
922 a VARCHAR(200),
923 b TEXT
924 ) ENGINE = InnoDB;
925 INSERT INTO t1 (a,b) VALUES
926 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
927 ('How To Use MySQL Well','After you went through a ...'),
928 ('Optimizing MySQL','In this tutorial we will show ...');
929 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
930 SHOW CREATE TABLE t1;
931 Table Create Table
932 t1 CREATE TABLE `t1` (
933 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
934 `a` varchar(200) DEFAULT NULL,
935 `b` text DEFAULT NULL,
936 PRIMARY KEY (`id`),
937 FULLTEXT KEY `idx` (`a`,`b`)
938 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
939 INSERT INTO t1 (a,b) VALUES
940 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
941 ('MySQL vs. YourSQL','In the following database comparison ...'),
942 ('MySQL Security','When configured properly, MySQL ...');
943 SELECT id FROM t1 WHERE MATCH (a,b)
944 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
945 id
946 1
947 3
948 select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
949 id
950 select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
951 id
952 select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
953 id
954 select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
955 id
956 select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
957 id x
958 1 0
959 2 0
960 3 0
961 4 0
962 5 0
963 6 0
964 select id, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
965 id x
966 1 0
967 2 0
968 3 0
969 4 0
970 5 0
971 6 0
972 select id from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
973 id
974 select id from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
975 id
976 select id from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
977 id
978 DROP DATABASE `benu database`;
979 USE test;
980 CREATE TABLE `t21` (`a` text, `b` int not null,
981 fulltext key (`a`), fulltext key (`a`)
982 ) ENGINE=INNODB DEFAULT CHARSET=LATIN1;
983 Warnings:
984 Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release
985 ALTER TABLE `t21` ADD UNIQUE INDEX (`b`), ALGORITHM=INPLACE;
986 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY
987 ALTER TABLE `t21` ADD UNIQUE INDEX (`b`);
988 DROP TABLE t21;
989 CREATE TABLE `t21` (`a` text, `b` int not null,
990 fulltext key (`a`)) ENGINE=INNODB DEFAULT CHARSET=LATIN1;
991 ALTER TABLE `t21` ADD UNIQUE INDEX (`b`);
992 DROP TABLE t21;
993 CREATE TABLE t1 (
994 id INT NOT NULL,
995 a VARCHAR(200),
996 b TEXT
997 ) ENGINE = InnoDB;
998 INSERT INTO t1 VALUES
999 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
1000 (2, 'How To Use MySQL Well','After you went through a ...'),
1001 (3, 'Optimizing MySQL','In this tutorial we will show ...');
1002 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
1003 ALTER TABLE t1 ADD UNIQUE INDEX (`id`);
1004 SELECT id FROM t1 WHERE MATCH (a,b)
1005 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
1006 id
1007 1
1008 3
1009 select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
1010 id
1011 select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
1012 id
1013 select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
1014 id
1015 select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
1016 id
1017 select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
1018 id x
1019 1 0
1020 2 0
1021 3 0
1022 DROP TABLE t1;
1023 CREATE TABLE t1 (
1024 id INT NOT NULL,
1025 a VARCHAR(200),
1026 b TEXT
1027 ) ENGINE = InnoDB;
1028 INSERT INTO t1 VALUES
1029 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
1030 (2, 'How To Use MySQL Well','After you went through a ...'),
1031 (3, 'Optimizing MySQL','In this tutorial we will show ...');
1032 ALTER TABLE t1 ADD UNIQUE INDEX (`id`), ADD FULLTEXT INDEX idx (a,b);
1033 SELECT id FROM t1 WHERE MATCH (a,b)
1034 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
1035 id
1036 1
1037 3
1038 select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
1039 id
1040 select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
1041 id
1042 select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
1043 id
1044 select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
1045 id
1046 DROP TABLE t1;
1047 CREATE TABLE t1 (
1048 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
1049 a VARCHAR(200),
1050 b TEXT
1051 ) ENGINE = InnoDB;
1052 INSERT INTO t1 VALUES
1053 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
1054 (2, 'How To Use MySQL Well','After you went through a ...'),
1055 (3, 'Optimizing MySQL','In this tutorial we will show ...');
1056 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
1057 ALTER TABLE t1 ADD UNIQUE INDEX (`FTS_DOC_ID`);
1058 SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b)
1059 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
1060 FTS_DOC_ID
1061 1
1062 3
1063 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
1064 FTS_DOC_ID
1065 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
1066 FTS_DOC_ID
1067 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
1068 FTS_DOC_ID
1069 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
1070 FTS_DOC_ID
1071 select FTS_DOC_ID, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
1072 FTS_DOC_ID x
1073 1 0
1074 2 0
1075 3 0
1076 DROP TABLE t1;
1077 CREATE TABLE t1 (
1078 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
1079 a VARCHAR(200),
1080 b TEXT
1081 ) ENGINE = InnoDB;
1082 INSERT INTO t1 VALUES
1083 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
1084 (2, 'How To Use MySQL Well','After you went through a ...'),
1085 (3, 'Optimizing MySQL','In this tutorial we will show ...');
1086 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b), ADD UNIQUE INDEX FTS_DOC_ID_INDEX (FTS_DOC_ID);
1087 SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b)
1088 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
1089 FTS_DOC_ID
1090 1
1091 3
1092 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
1093 FTS_DOC_ID
1094 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
1095 FTS_DOC_ID
1096 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
1097 FTS_DOC_ID
1098 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
1099 FTS_DOC_ID
1100 select FTS_DOC_ID, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
1101 FTS_DOC_ID x
1102 1 0
1103 2 0
1104 3 0
1105 DROP TABLE t1;
1106 CREATE TABLE t2 (`b` char(2),fulltext(`b`)) ENGINE=INNODB
1107 DEFAULT CHARSET=LATIN1;
1108 CREATE TABLE t3 LIKE t2;
1109 INSERT INTO `t2` VALUES();
1110 COMMIT WORK AND CHAIN;
1111 INSERT INTO `t3` VALUES ();
1112 UPDATE `t2` SET `b` = 'a';
1113 SAVEPOINT BATCH1;
1114 DROP TABLE t2;
1115 DROP TABLE t3;
1116 CREATE TABLE t1 (
1117 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1118 a VARCHAR(200),
1119 b TEXT
1120 ) ENGINE = InnoDB;
1121 INSERT INTO t1 (a,b) VALUES
1122 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
1123 ('How To Use MySQL Well','After you went through a ...'),
1124 ('Optimizing MySQL','In this tutorial we will show ...');
1125 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
1126 COMMIT WORK AND CHAIN;
1127 INSERT INTO t1 (a,b) VALUES
1128 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
1129 ('MySQL vs. YourSQL','In the following database comparison ...'),
1130 ('MySQL Security','When configured properly, MySQL ...');
1131 SAVEPOINT BATCH1;
1132 SELECT id FROM t1 WHERE MATCH (a,b)
1133 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
1134 id
1135 1
1136 2
1137 3
1138 INSERT INTO t1 (a,b) VALUES
1139 ('1002 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
1140 ('MySQL vs. YourSQL','In the following database comparison ...'),
1141 ('MySQL Security','When configured properly, MySQL ...');
1142 ROLLBACK TO SAVEPOINT BATCH1;
1143 COMMIT;
1144 SELECT id FROM t1 WHERE MATCH (a,b)
1145 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
1146 id
1147 6
1148 1
1149 2
1150 3
1151 4
1152 5
1153 DROP TABLE t1;
1154 CREATE TABLE `t` (`a` char(20) character set utf8 default null,
1155 fulltext key (`a`)) ENGINE=INNODB;
1156 INSERT INTO `t` VALUES ('a');
1157 INSERT INTO `t` VALUES ('aaa');
1158 SELECT MATCH(`a`) AGAINST (0x22dd22) FROM `t`;
1159 MATCH(`a`) AGAINST (0x22dd22)
1160 0
1161 0
1162 SELECT MATCH(`a`) AGAINST (0x2222) FROM `t`;
1163 MATCH(`a`) AGAINST (0x2222)
1164 0
1165 0
1166 SELECT MATCH(`a`) AGAINST (0x22) FROM `t`;
1167 MATCH(`a`) AGAINST (0x22)
1168 0
1169 0
1170 SELECT MATCH(`a`) AGAINST (0x2261616122) FROM `t`;
1171 MATCH(`a`) AGAINST (0x2261616122)
1172 0
1173 0.0906190574169159
1174 SELECT MATCH(`a`) AGAINST (0x2261dd6122) FROM `t`;
1175 MATCH(`a`) AGAINST (0x2261dd6122)
1176 0
1177 0
1178 SELECT MATCH(`a`) AGAINST (0x2261dd612222226122) FROM `t`;
1179 MATCH(`a`) AGAINST (0x2261dd612222226122)
1180 0
1181 0
1182 DROP TABLE t;
1183 CREATE TABLE t(a CHAR(1),FULLTEXT KEY(a)) ENGINE=INNODB;
1184 HANDLER t OPEN;
1185 HANDLER t READ a NEXT;
1186 ERROR HY000: FULLTEXT index `a` does not support this operation
1187 HANDLER t READ a PREV;
1188 ERROR HY000: FULLTEXT index `a` does not support this operation
1189 DROP TABLE t;
1190 CREATE TABLE `%`(a TEXT, FULLTEXT INDEX(a)) ENGINE=INNODB;
1191 CREATE TABLE `A B`(a TEXT, FULLTEXT INDEX(a)) ENGINE=INNODB;
1192 DROP TABLE `%`;
1193 DROP TABLE `A B`;
1194 CREATE TABLE `t-26`(a VARCHAR(10),FULLTEXT KEY(a)) ENGINE=INNODB;
1195 INSERT INTO `t-26` VALUES('117');
1196 DROP TABLE `t-26`;
1197 CREATE TABLE `t1` (
1198 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1199 `content` TEXT NOT NULL,
1200 PRIMARY KEY (`id`),
1201 FULLTEXT INDEX `IDX_CONTEXT_FULLTEXT`(`content`)
1202 )
1203 ENGINE = InnoDB;
1204 insert into t1 (content)
1205 values
1206 ('This is a story which has has a complicated phrase structure here in the
1207 middle'),
1208 ('This is a story which doesn''t have that text'),
1209 ('This is a story that has complicated the phrase structure');
1210 select * from t1
1211 where match(content) against('"complicated phrase structure"' in boolean
1212 mode);
1213 id content
1214 1 This is a story which has has a complicated phrase structure here in the
1215 middle
1216 select * from t1
1217 where match(content) against('+"complicated phrase structure"' in boolean
1218 mode);
1219 id content
1220 1 This is a story which has has a complicated phrase structure here in the
1221 middle
1222 select * from t1
1223 where match(content) against('"complicated the phrase structure"' in boolean
1224 mode);
1225 id content
1226 3 This is a story that has complicated the phrase structure
1227 select * from t1 where match(content) against('+"this is a story which" +"complicated the phrase structure"' in boolean mode);
1228 id content
1229 select * from t1 where match(content) against('"the complicated the phrase structure"' in boolean mode);
1230 id content
1231 3 This is a story that has complicated the phrase structure
1232 select * from t1 where match(content) against('"complicated a phrase structure"' in boolean mode);
1233 id content
1234 DROP TABLE t1;
1235 CREATE TABLE my (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
1236 c VARCHAR(32), FULLTEXT(c)) ENGINE = INNODB;
1237 INSERT INTO my (c) VALUES ('green-iguana');
1238 SELECT * FROM my WHERE MATCH(c) AGAINST ('green-iguana');
1239 id c
1240 1 green-iguana
1241 DROP TABLE my;
1242 CREATE TABLE ift (
1243 `a` int(11) NOT NULL,
1244 `b` text,
1245 PRIMARY KEY (`a`),
1246 FULLTEXT KEY `b` (`b`)
1247 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1248 INSERT INTO ift values (1, "skip");
1249 INSERT INTO ift values (2, "skip and networking");
1250 INSERT INTO ift values (3, "--skip-networking");
1251 INSERT INTO ift values (4, "-donot--skip-networking");
1252 SELECT * FROM ift WHERE MATCH (b) AGAINST ('--skip-networking');
1253 a b
1254 2 skip and networking
1255 3 --skip-networking
1256 4 -donot--skip-networking
1257 1 skip
1258 SELECT * FROM ift WHERE MATCH (b) AGAINST ('skip-networking');
1259 a b
1260 2 skip and networking
1261 3 --skip-networking
1262 4 -donot--skip-networking
1263 1 skip
1264 SELECT * FROM ift WHERE MATCH (b) AGAINST ('----');
1265 a b
1266 SELECT * FROM ift WHERE MATCH (b) AGAINST ('-donot--skip-networking');
1267 a b
1268 4 -donot--skip-networking
1269 2 skip and networking
1270 3 --skip-networking
1271 1 skip
1272 DROP TABLE ift;
1273 CREATE TABLE articles (
1274 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1275 title VARCHAR(200),
1276 body TEXT,
1277 FULLTEXT (title,body)
1278 ) ENGINE=InnoDB;
1279 INSERT INTO articles (title,body) VALUES
1280 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
1281 ('How To Use MySQL Well','After you went through a ...'),
1282 ('Optimizing MySQL','In this tutorial we will show ...'),
1283 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
1284 ('MySQL vs. YourSQL','In the following database comparison ...'),
1285 ('( that''s me )','When configured properly, MySQL ...');
1286 SELECT * FROM articles WHERE MATCH (title,body)
1287 AGAINST ('( yours''s* )' IN BOOLEAN MODE);
1288 id title body
1289 5 MySQL vs. YourSQL In the following database comparison ...
1290 SELECT * FROM articles WHERE MATCH (title,body)
1291 AGAINST ('s*' IN BOOLEAN MODE);
1292 id title body
1293 1 MySQL Tutorial DBMS stands for DataBase ...
1294 3 Optimizing MySQL In this tutorial we will show ...
1295 SELECT * FROM articles WHERE MATCH (title,body)
1296 AGAINST ('stands\'] | * | show[@database' IN NATURAL LANGUAGE MODE);
1297 id title body
1298 1 MySQL Tutorial DBMS stands for DataBase ...
1299 3 Optimizing MySQL In this tutorial we will show ...
1300 5 MySQL vs. YourSQL In the following database comparison ...
1301 DROP TABLE articles;
1302 CREATE TABLE t1(a TEXT CHARACTER SET LATIN1, FULLTEXT INDEX(a)) ENGINE=INNODB;
1303 SELECT * FROM t1 WHERE MATCH(a) AGAINST("*");
1304 ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'
1305 DROP TABLE t1;
1306 CREATE TABLE t1 (
1307 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1308 a VARCHAR(200),
1309 FULLTEXT (a)
1310 ) ENGINE= InnoDB;
1311 INSERT INTO t1 (a) VALUES
1312 ('Do you know MySQL is a good database'),
1313 ('How to build a good database'),
1314 ('Do you know'),
1315 ('Do you know MySQL'),
1316 ('How to use MySQL'),
1317 ('Do you feel good'),
1318 ('MySQL is good'),
1319 ('MySQL is good to know'),
1320 ('What is database');
1321 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know mysql"' IN BOOLEAN MODE);
1322 id a
1323 1 Do you know MySQL is a good database
1324 4 Do you know MySQL
1325 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+("know mysql")' IN BOOLEAN MODE);
1326 id a
1327 1 Do you know MySQL is a good database
1328 4 Do you know MySQL
1329 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('("know mysql" good)' IN BOOLEAN MODE);
1330 id a
1331 1 Do you know MySQL is a good database
1332 4 Do you know MySQL
1333 2 How to build a good database
1334 6 Do you feel good
1335 7 MySQL is good
1336 8 MySQL is good to know
1337 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+("know mysql" good)' IN BOOLEAN MODE);
1338 id a
1339 1 Do you know MySQL is a good database
1340 4 Do you know MySQL
1341 2 How to build a good database
1342 6 Do you feel good
1343 7 MySQL is good
1344 8 MySQL is good to know
1345 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('(good "know mysql")' IN BOOLEAN MODE);
1346 id a
1347 1 Do you know MySQL is a good database
1348 4 Do you know MySQL
1349 2 How to build a good database
1350 6 Do you feel good
1351 7 MySQL is good
1352 8 MySQL is good to know
1353 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+(good "know mysql")' IN BOOLEAN MODE);
1354 id a
1355 1 Do you know MySQL is a good database
1356 4 Do you know MySQL
1357 2 How to build a good database
1358 6 Do you feel good
1359 7 MySQL is good
1360 8 MySQL is good to know
1361 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+("know mysql" "good database")' IN BOOLEAN MODE);
1362 id a
1363 1 Do you know MySQL is a good database
1364 2 How to build a good database
1365 4 Do you know MySQL
1366 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know mysql" +"good database"' IN BOOLEAN MODE);
1367 id a
1368 1 Do you know MySQL is a good database
1369 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know database"@4' IN BOOLEAN MODE);
1370 id a
1371 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know database"@8' IN BOOLEAN MODE);
1372 id a
1373 1 Do you know MySQL is a good database
1374 DROP TABLE t1;
1375 CREATE TABLE t1 (
1376 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1377 a VARCHAR(200),
1378 FULLTEXT (a)
1379 ) ENGINE= InnoDB;
1380 INSERT INTO t1 (a) VALUES
1381 ('know mysql good database');
1382 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"good database"' IN BOOLEAN MODE);
1383 id a
1384 1 know mysql good database
1385 DROP TABLE t1;
1386 CREATE TABLE articles (
1387 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1388 title VARCHAR(200),
1389 body TEXT,
1390 FULLTEXT (title,body)
1391 ) ENGINE=InnoDB;
1392 INSERT INTO articles (title,body) VALUES ('Test Article','blah blah
1393 blah'),("Matt's Noise",'this is noisy'),('February Weather','It was terrible
1394 this year.'),('Peter Pan','Tis a kids story.'),('Test1','nada'),('Database
1395 database database','foo database database database'),('Database article
1396 title','body with lots of words.'),('myfulltext database', 'my test fulltext
1397 database');
1398 SELECT id, title, body FROM articles ORDER BY MATCH (title,body) AGAINST ('database' IN BOOLEAN MODE) DESC;
1399 id title body
1400 6 Database
1401 database database foo database database database
1402 8 myfulltext database my test fulltext
1403 database
1404 7 Database article
1405 title body with lots of words.
1406 1 Test Article blah blah
1407 blah
1408 2 Matt's Noise this is noisy
1409 3 February Weather It was terrible
1410 this year.
1411 4 Peter Pan Tis a kids story.
1412 5 Test1 nada
1413 DELETE from articles WHERE title like "myfulltext database";
1414 INSERT INTO articles (title,body) VALUES ('myfulltext database', 'my test fulltext database');
1415 SELECT id, title, body FROM articles ORDER BY MATCH (title,body) AGAINST ('database' IN BOOLEAN MODE) DESC;
1416 id title body
1417 6 Database
1418 database database foo database database database
1419 9 myfulltext database my test fulltext database
1420 7 Database article
1421 title body with lots of words.
1422 1 Test Article blah blah
1423 blah
1424 2 Matt's Noise this is noisy
1425 3 February Weather It was terrible
1426 this year.
1427 4 Peter Pan Tis a kids story.
1428 5 Test1 nada
1429 DELETE from articles WHERE title like "myfulltext database";
1430 INSERT INTO articles (title,body) VALUES ('myfulltext database', 'my test fulltext database');
1431 SELECT id, title, body FROM articles ORDER BY MATCH (title,body) AGAINST ('database' IN BOOLEAN MODE) DESC;
1432 id title body
1433 6 Database
1434 database database foo database database database
1435 10 myfulltext database my test fulltext database
1436 7 Database article
1437 title body with lots of words.
1438 1 Test Article blah blah
1439 blah
1440 2 Matt's Noise this is noisy
1441 3 February Weather It was terrible
1442 this year.
1443 4 Peter Pan Tis a kids story.
1444 5 Test1 nada
1445 DROP TABLE articles;
1446 CREATE TABLE t1(
1447 a TEXT CHARSET ujis COLLATE ujis_japanese_ci,
1448 b TEXT CHARSET utf8mb4 COLLATE utf8mb4_turkish_ci,
1449 c TEXT CHARSET eucjpms COLLATE eucjpms_bin,
1450 d TEXT CHARSET utf8mb4,
1451 FULLTEXT INDEX(a),
1452 FULLTEXT INDEX(b),
1453 FULLTEXT INDEX(c),
1454 FULLTEXT INDEX(d)
1455 ) ENGINE = InnoDB;
1456 INSERT INTO t1 VALUES
1457 ('myisam', 'myisam', 'myisam', 'myisam'),
1458 ('innodb', 'innodb', 'innodb', 'innodb'),
1459 ('innodb myisam', 'innodb myisam', 'innodb myisam', 'innodb myisam'),
1460 ('memory', 'memory', 'memory', 'memory'),
1461 ('archive', 'archive', 'archive', 'archive'),
1462 ('federated', 'federated', 'federated', 'federated'),
1463 ('storage engine innodb', 'storage engine innodb', 'storage engine innodb', 'storage engine innodb'),
1464 ('storage engine myisam', 'storage engine myisam', 'storage engine myisam', 'storage engine myisam'),
1465 ('innobase', 'innobase', 'innobase', 'innobase'),
1466 ('myisam innodb', 'myisam innodb', 'myisam innodb', 'myisam innodb'),
1467 ('innodb myisam engines', 'innodb myisam engines', 'innodb myisam engines', 'innodb myisam engines');
1468 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"'));
1469 a
1470 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00));
1471 a
1472 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', '"'));
1473 a
1474 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', ' ', '"'));
1475 a
1476 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, '"'));
1477 a
1478 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 0x00, '"', 0x00));
1479 a
1480 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', '&', 0x00, '"'));
1481 a
1482 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, '&', '"'));
1483 a
1484 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', '%', '"'));
1485 a
1486 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"'));
1487 a
1488 innodb
1489 innodb myisam
1490 storage engine innodb
1491 myisam innodb
1492 innodb myisam engines
1493 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"'));
1494 a
1495 innodb
1496 innodb myisam
1497 storage engine innodb
1498 myisam innodb
1499 innodb myisam engines
1500 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, 0x00, 0x00, '"'));
1501 a
1502 innodb
1503 innodb myisam
1504 storage engine innodb
1505 myisam innodb
1506 innodb myisam engines
1507 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00));
1508 a
1509 innodb myisam
1510 myisam innodb
1511 innodb myisam engines
1512 myisam
1513 innodb
1514 storage engine innodb
1515 storage engine myisam
1516 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00));
1517 a
1518 innodb myisam
1519 myisam innodb
1520 innodb myisam engines
1521 myisam
1522 innodb
1523 storage engine innodb
1524 storage engine myisam
1525 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00, '"'));
1526 a
1527 innodb myisam
1528 innodb myisam engines
1529 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', '(', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', ')'));
1530 a
1531 innodb myisam
1532 myisam innodb
1533 innodb myisam engines
1534 myisam
1535 innodb
1536 storage engine innodb
1537 storage engine myisam
1538 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', 'm', 'y', 'i', 's', 'a', 'm'));
1539 a
1540 innodb myisam
1541 myisam innodb
1542 innodb myisam engines
1543 myisam
1544 innodb
1545 storage engine innodb
1546 storage engine myisam
1547 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', 'm', 'y', 'i', 's', 'a', 'm', '"'));
1548 a
1549 innodb myisam
1550 innodb myisam engines
1551 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00, '"','@', '2') IN BOOLEAN MODE);
1552 a
1553 innodb myisam
1554 myisam innodb
1555 innodb myisam engines
1556 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 'm', 'y', 'i', 's', 'a', 'm', '"','@', '4') IN BOOLEAN MODE);
1557 a
1558 innodb myisam
1559 myisam innodb
1560 innodb myisam engines
1561 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 's', 't', 'o', 'r', 'a', 'g', 'e', '"','@', '4', 0x00) IN BOOLEAN MODE);
1562 ERROR 42000: syntax error, unexpected FTS_TERM, expecting FTS_NUMB
1563 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"'));
1564 b
1565 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT(0x00));
1566 b
1567 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', '"'));
1568 b
1569 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', ' ', '"'));
1570 b
1571 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', 0x00, '"'));
1572 b
1573 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT(0x00, '"', 0x00, '"', 0x00));
1574 b
1575 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', '&', 0x00, '"'));
1576 b
1577 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', 0x00, '&', '"'));
1578 b
1579 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', '%', '"'));
1580 b
1581 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"'));
1582 b
1583 innodb
1584 innodb myisam
1585 storage engine innodb
1586 myisam innodb
1587 innodb myisam engines
1588 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"'));
1589 b
1590 innodb
1591 innodb myisam
1592 storage engine innodb
1593 myisam innodb
1594 innodb myisam engines
1595 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, 0x00, 0x00, '"'));
1596 b
1597 innodb
1598 innodb myisam
1599 storage engine innodb
1600 myisam innodb
1601 innodb myisam engines
1602 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00));
1603 b
1604 innodb myisam
1605 myisam innodb
1606 innodb myisam engines
1607 myisam
1608 innodb
1609 storage engine innodb
1610 storage engine myisam
1611 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00, '"','@', '2') IN BOOLEAN MODE);
1612 b
1613 innodb myisam
1614 myisam innodb
1615 innodb myisam engines
1616 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 'm', 'y', 'i', 's', 'a', 'm', '"','@', '4') IN BOOLEAN MODE);
1617 b
1618 innodb myisam
1619 myisam innodb
1620 innodb myisam engines
1621 SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT(0x00, '"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 's', 't', 'o', 'r', 'a', 'g', 'e', '"','@', '4', 0x00) IN BOOLEAN MODE);
1622 ERROR 42000: syntax error, unexpected FTS_TERM, expecting FTS_NUMB
1623 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"'));
1624 c
1625 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT(0x00));
1626 c
1627 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', '"'));
1628 c
1629 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', ' ', '"'));
1630 c
1631 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 0x00, '"'));
1632 c
1633 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT(0x00, '"', 0x00, '"', 0x00));
1634 c
1635 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', '&', 0x00, '"'));
1636 c
1637 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 0x00, '&', '"'));
1638 c
1639 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', '%', '"'));
1640 c
1641 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"'));
1642 c
1643 innodb
1644 innodb myisam
1645 storage engine innodb
1646 myisam innodb
1647 innodb myisam engines
1648 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"'));
1649 c
1650 innodb
1651 innodb myisam
1652 storage engine innodb
1653 myisam innodb
1654 innodb myisam engines
1655 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, 0x00, 0x00, '"'));
1656 c
1657 innodb
1658 innodb myisam
1659 storage engine innodb
1660 myisam innodb
1661 innodb myisam engines
1662 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00));
1663 c
1664 innodb myisam
1665 myisam innodb
1666 innodb myisam engines
1667 myisam
1668 innodb
1669 storage engine innodb
1670 storage engine myisam
1671 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00));
1672 c
1673 innodb myisam
1674 myisam innodb
1675 innodb myisam engines
1676 myisam
1677 innodb
1678 storage engine innodb
1679 storage engine myisam
1680 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00, '"'));
1681 c
1682 innodb myisam
1683 innodb myisam engines
1684 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', '(', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', ')'));
1685 c
1686 innodb myisam
1687 myisam innodb
1688 innodb myisam engines
1689 myisam
1690 innodb
1691 storage engine innodb
1692 storage engine myisam
1693 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', 'm', 'y', 'i', 's', 'a', 'm'));
1694 c
1695 innodb myisam
1696 myisam innodb
1697 innodb myisam engines
1698 myisam
1699 innodb
1700 storage engine innodb
1701 storage engine myisam
1702 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', 'm', 'y', 'i', 's', 'a', 'm', '"'));
1703 c
1704 innodb myisam
1705 innodb myisam engines
1706 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00, '"','@', '2') IN BOOLEAN MODE);
1707 c
1708 innodb myisam
1709 myisam innodb
1710 innodb myisam engines
1711 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 'm', 'y', 'i', 's', 'a', 'm', '"','@', '4') IN BOOLEAN MODE);
1712 c
1713 innodb myisam
1714 myisam innodb
1715 innodb myisam engines
1716 SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT(0x00, '"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 's', 't', 'o', 'r', 'a', 'g', 'e', '"','@', '4', 0x00) IN BOOLEAN MODE);
1717 ERROR 42000: syntax error, unexpected FTS_TERM, expecting FTS_NUMB
1718 ALTER TABLE t1 ENGINE = MyISAM;
1719 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"'));
1720 a
1721 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00));
1722 a
1723 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', '"'));
1724 a
1725 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', ' ', '"'));
1726 a
1727 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, '"'));
1728 a
1729 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 0x00, '"', 0x00));
1730 a
1731 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', '&', 0x00, '"'));
1732 a
1733 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, '&', '"'));
1734 a
1735 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', '%', '"'));
1736 a
1737 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"'));
1738 a
1739 innodb
1740 innodb myisam
1741 myisam innodb
1742 storage engine innodb
1743 innodb myisam engines
1744 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"'));
1745 a
1746 innodb
1747 innodb myisam
1748 myisam innodb
1749 storage engine innodb
1750 innodb myisam engines
1751 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, 0x00, 0x00, '"'));
1752 a
1753 innodb
1754 innodb myisam
1755 myisam innodb
1756 storage engine innodb
1757 innodb myisam engines
1758 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00));
1759 a
1760 innodb myisam
1761 myisam innodb
1762 innodb myisam engines
1763 myisam
1764 innodb
1765 storage engine innodb
1766 storage engine myisam
1767 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00));
1768 a
1769 innodb myisam
1770 myisam innodb
1771 innodb myisam engines
1772 myisam
1773 innodb
1774 storage engine innodb
1775 storage engine myisam
1776 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00, '"'));
1777 a
1778 innodb myisam
1779 myisam innodb
1780 innodb myisam engines
1781 myisam
1782 innodb
1783 storage engine innodb
1784 storage engine myisam
1785 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', '(', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', ')'));
1786 a
1787 innodb myisam
1788 myisam innodb
1789 innodb myisam engines
1790 myisam
1791 innodb
1792 storage engine innodb
1793 storage engine myisam
1794 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', 'm', 'y', 'i', 's', 'a', 'm'));
1795 a
1796 innodb myisam
1797 myisam innodb
1798 innodb myisam engines
1799 myisam
1800 innodb
1801 storage engine innodb
1802 storage engine myisam
1803 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', 'm', 'y', 'i', 's', 'a', 'm', '"'));
1804 a
1805 innodb myisam
1806 myisam innodb
1807 innodb myisam engines
1808 myisam
1809 innodb
1810 storage engine innodb
1811 storage engine myisam
1812 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00, '"','@', '2') IN BOOLEAN MODE);
1813 a
1814 innodb myisam
1815 innodb myisam engines
1816 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 'm', 'y', 'i', 's', 'a', 'm', '"','@', '4') IN BOOLEAN MODE);
1817 a
1818 innodb myisam
1819 innodb myisam engines
1820 SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 's', 't', 'o', 'r', 'a', 'g', 'e', '"','@', '4', 0x00) IN BOOLEAN MODE);
1821 a
1822 DROP TABLE t1;
1823 CREATE TABLE t1 (
1824 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1825 a VARCHAR(200),
1826 FULLTEXT (a)
1827 ) ENGINE= InnoDB;
1828 INSERT INTO t1 (a) VALUES
1829 ('know database'),('good database'), ('gmail email'), ('ghome windows');
1830 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('g *' IN NATURAL LANGUAGE MODE);
1831 id a
1832 2 good database
1833 3 gmail email
1834 4 ghome windows
1835 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('g *' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
1836 id a
1837 3 gmail email
1838 4 ghome windows
1839 2 good database
1840 1 know database
1841 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('g * k *' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
1842 id a
1843 1 know database
1844 3 gmail email
1845 4 ghome windows
1846 2 good database
1847 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('g * k * d *' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
1848 id a
1849 1 know database
1850 3 gmail email
1851 4 ghome windows
1852 2 good database
1853 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('g * go *' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
1854 id a
1855 2 good database
1856 3 gmail email
1857 4 ghome windows
1858 1 know database
1859 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('g * good' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
1860 id a
1861 2 good database
1862 3 gmail email
1863 4 ghome windows
1864 1 know database
1865 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('gm * go *' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
1866 id a
1867 3 gmail email
1868 2 good database
1869 1 know database
1870 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('good *' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
1871 id a
1872 2 good database
1873 1 know database
1874 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('g* database' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
1875 id a
1876 1 know database
1877 2 good database
1878 DROP TABLE t1;