Debian Codegraph @

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

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