Debian Codegraph @

mysql-test/suite/innodb_fts/r/innodb_fts_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=latin1 COLLATE=latin1_swedish_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=latin1 COLLATE=latin1_swedish_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=latin1 COLLATE=latin1_swedish_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=latin1 COLLATE=latin1_swedish_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 set names utf8;
412 "----------Test1---------"
413 create table t50 (s1 varchar(60) character set utf8 collate utf8_bin) engine = innodb;
414 create fulltext index i on t50 (s1);
415 insert into t50 values ('ABCDE'),('FGHIJ'),('KLMNO'),('VÐƷWİ');
416 select * from t50 where match(s1) against ('VÐƷWİ');
417 s1
418 VÐƷWİ
419 drop table t50;
420 "----------Test2---------"
421 create table t50 (s1 int unsigned primary key auto_increment, s2
422 varchar(60) character set utf8) engine = innodb;
423 create fulltext index i on t50 (s2);
424 insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCDE');
425 select * from t50 order by s2;
426 s1 s2
427 4 ABCDE
428 1 FGHIJ
429 2 KLMNO
430 3 VÐƷWİ
431 drop table t50;
432 "----------Test3---------"
433 create table t50 (id int unsigned primary key auto_increment, s2
434 varchar(60) character set utf8) engine = innodb;
435 create fulltext index i on t50 (s2);
436 insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCDE');
437 set @@autocommit=0;
438 update t50 set s2 = lower(s2);
439 update t50 set s2 = upper(s2);
440 commit;
441 select * from t50 where match(s2) against ('VÐƷWİ FGHIJ KLMNO ABCDE' in boolean mode);
442 id s2
443 1 FGHIJ
444 2 KLMNO
445 3 VÐƷWI
446 4 ABCDE
447 select * from t50;
448 id s2
449 1 FGHIJ
450 2 KLMNO
451 3 VÐƷWI
452 4 ABCDE
453 drop table t50;
454 set @@autocommit=1;
455 "----------Test4---------"
456 create table t50 (id int unsigned primary key auto_increment, s2
457 varchar(60) character set utf8) engine = innodb;
458 create fulltext index i on t50 (s2);
459 insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCD*');
460 select * from t50 where match(s2) against ('abcd*' in natural language
461 mode);
462 id s2
463 4 ABCD*
464 select * from t50 where match(s2) against ('abcd*' in boolean mode);
465 id s2
466 4 ABCD*
467 drop table t50;
468 "----------Test5---------"
469 create table t50 (s1 int, s2 varchar(200), fulltext key(s2)) engine = innodb;
470 set @@autocommit=0;
471 insert into t50 values (1,'Sunshine'),(2,'Lollipops');
472 select * from t50 where match(s2) against('Rainbows');
473 s1 s2
474 rollback;
475 select * from t50;
476 s1 s2
477 drop table t50;
478 set @@autocommit=1;
479 "----------Test6---------"
480 CREATE TABLE t1 (
481 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
482 a VARCHAR(200),
483 b TEXT
484 ) ENGINE = InnoDB;
485 INSERT INTO t1 (a,b) VALUES
486 ('aab` MySQL Tutorial','DBMS stands for DataBase ...') ,
487 ('aas How To Use MySQL Well','After you went through a ...'),
488 ('aac Optimizing MySQL','In this tutorial we will show ...');
489 INSERT INTO t1 (a,b) VALUES
490 ('aac 1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
491 ('aab MySQL vs. YourSQL','In the following database comparison ...'),
492 ('aaa MySQL Security','When configured properly, MySQL ...');
493 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
494 SELECT * FROM t1 ORDER BY MATCH(a,b) AGAINST ('aac') DESC;
495 id a b
496 3 aac Optimizing MySQL In this tutorial we will show ...
497 4 aac 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
498 1 aab` MySQL Tutorial DBMS stands for DataBase ...
499 2 aas How To Use MySQL Well After you went through a ...
500 5 aab MySQL vs. YourSQL In the following database comparison ...
501 6 aaa MySQL Security When configured properly, MySQL ...
502 SELECT * FROM t1 ORDER BY MATCH(a,b) AGAINST ('aab') DESC;
503 id a b
504 1 aab` MySQL Tutorial DBMS stands for DataBase ...
505 5 aab MySQL vs. YourSQL In the following database comparison ...
506 2 aas How To Use MySQL Well After you went through a ...
507 3 aac Optimizing MySQL In this tutorial we will show ...
508 4 aac 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
509 6 aaa MySQL Security When configured properly, MySQL ...
510 "----------Test7---------"
511 select * from t1 where match(a,b) against ('aaa')
512 union select * from t1 where match(a,b) against ('aab')
513 union select * from t1 where match(a,b) against ('aac');
514 id a b
515 6 aaa MySQL Security When configured properly, MySQL ...
516 1 aab` MySQL Tutorial DBMS stands for DataBase ...
517 5 aab MySQL vs. YourSQL In the following database comparison ...
518 3 aac Optimizing MySQL In this tutorial we will show ...
519 4 aac 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
520 select * from t1 where match(a,b) against ('aaa')
521 or match(a,b) against ('aab')
522 or match(a,b) against ('aac');
523 id a b
524 1 aab` MySQL Tutorial DBMS stands for DataBase ...
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 5 aab MySQL vs. YourSQL In the following database comparison ...
528 6 aaa MySQL Security When configured properly, MySQL ...
529 DROP TABLE t1;
530 "----------Test8---------"
531 CREATE TABLE t1 (
532 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
533 a VARCHAR(200),
534 b TEXT
535 ) ENGINE = InnoDB;
536 INSERT INTO t1 (a,b) VALUES
537 ('MySQL Tutorial','DBMS stands for DataBase ... abcd') ,
538 ('How To Use MySQL Well','After you went through a q ...abdd'),
539 ('Optimizing MySQL','In this tutorial we will show ...abed');
540 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
541 SHOW CREATE TABLE t1;
542 Table Create Table
543 t1 CREATE TABLE `t1` (
544 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
545 `a` varchar(200) DEFAULT NULL,
546 `b` text DEFAULT NULL,
547 PRIMARY KEY (`id`),
548 FULLTEXT KEY `idx` (`a`,`b`)
549 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
550 INSERT INTO t1 (a,b) VALUES
551 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. q ...'),
552 ('MySQL vs. YourSQL use','In the following database comparison ...'),
553 ('MySQL Security','When run configured properly, MySQL ...');
554 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run');
555 id a b
556 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. q ...
557 6 MySQL Security When run configured properly, MySQL ...
558 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('use');
559 id a b
560 2 How To Use MySQL Well After you went through a q ...abdd
561 5 MySQL vs. YourSQL use In the following database comparison ...
562 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('went');
563 id a b
564 2 How To Use MySQL Well After you went through a q ...abdd
565 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run') AND NOT MATCH(a,b) AGAINST ('q');
566 id a b
567 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. q ...
568 6 MySQL Security When run configured properly, MySQL ...
569 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('use') AND NOT MATCH(a,b) AGAINST ('q');
570 id a b
571 2 How To Use MySQL Well After you went through a q ...abdd
572 5 MySQL vs. YourSQL use In the following database comparison ...
573 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('went') AND NOT MATCH(a,b) AGAINST ('q');
574 id a b
575 2 How To Use MySQL Well After you went through a q ...abdd
576 "----------Test9---------"
577 CREATE TABLE t2 AS SELECT * FROM t1;
578 ALTER TABLE t2 ENGINE=MYISAM;
579 CREATE FULLTEXT INDEX i ON t2 (a,b);
580 SET @x = (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('run'));
581 SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('use'));
582 SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('went'));
583 SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('run'));
584 SET @x2 = (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('run'));
585 SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('use'));
586 SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('went'));
587 SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('run'));
588 SELECT @x, @x2;
589 @x @x2
590 7 0
591 DROP TABLE t2;
592 "----------Test10---------"
593 CREATE TABLE t2 AS SELECT * FROM t1;
594 ALTER TABLE t2 ENGINE=MYISAM;
595 CREATE FULLTEXT INDEX i ON t2 (a,b);
596 SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE);
597 COUNT(*)
598 1
599 SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE);
600 COUNT(*)
601 1
602 DROP TABLE t2;
603 "----------Test11---------"
604 CREATE TABLE t2 AS SELECT * FROM t1;
605 ALTER TABLE t2 ENGINE = MYISAM;
606 CREATE FULLTEXT INDEX i ON t2 (a,b);
607 ALTER TABLE t2 ENGINE=InnoDB;
608 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run');
609 id a b
610 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. q ...
611 6 MySQL Security When run configured properly, MySQL ...
612 SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE);
613 COUNT(*)
614 1
615 DROP TABLE t2,t1;
616 "----------Test13---------"
617 set names utf8;
618 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8_SPANISH_CI) ENGINE = InnoDB;
619 CREATE FULLTEXT INDEX i ON t1 (s2);
620 INSERT INTO t1 VALUES (1,'aaCen'),(2,'aaCha'),(3,'aaCio'),(4,'aaçen'),(5,'aaçha'),(6,'aaçio');
621 SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('aach*' IN BOOLEAN MODE);
622 s1 s2
623 2 aaCha
624 5 aaçha
625 SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('aaC*' IN BOOLEAN MODE);
626 s1 s2
627 1 aaCen
628 2 aaCha
629 3 aaCio
630 4 aaçen
631 5 aaçha
632 6 aaçio
633 DROP TABLE t1;
634 "----------Test14---------"
635 CREATE TABLE t1(s1 INT , s2 VARCHAR(100) CHARACTER SET sjis) ENGINE = InnoDB;
636 CREATE FULLTEXT INDEX i ON t1 (s2);
637 INSERT INTO t1 VALUES (1,'ペペペ'),(2,'テテテ'),(3,'ルルル'),(4,'グググ');
638 DROP TABLE t1;
639 "----------Test15---------"
640 CREATE TABLE t1 (s1 VARCHAR (60) CHARACTER SET UTF8 COLLATE UTF8_UNICODE_520_CI) ENGINE = MyISAM;
641 CREATE FULLTEXT INDEX i ON t1 (s1);
642 INSERT INTO t1 VALUES
643 ('a'),('b'),('c'),('d'),('ŁŁŁŁ'),('LLLL'),(NULL),('ŁŁŁŁ ŁŁŁŁ'),('LLLLLLLL');
644 SELECT * FROM t1 WHERE MATCH(s1) AGAINST ('LLLL' COLLATE UTF8_UNICODE_520_CI);
645 s1
646 ŁŁŁŁ
647 LLLL
648 ŁŁŁŁ ŁŁŁŁ
649 CREATE TABLE t2 (s1 VARCHAR(60) CHARACTER SET UTF8 COLLATE UTF8_POLISH_CI) ENGINE = InnoDB;
650 CREATE FULLTEXT INDEX i ON t2 ( s1);
651 INSERT INTO t2 VALUES
652 ('a'),('b'),('c'),('d'),('ŁŁŁŁ'),('LLLL'),(NULL),('ŁŁŁŁ ŁŁŁŁ'),('LLLLLLLL');
653 SELECT * FROM t2 WHERE MATCH(s1) AGAINST ('LLLL' COLLATE UTF8_UNICODE_520_CI);
654 s1
655 LLLL
656 DROP TABLE t1,t2;
657 "----------Test16---------"
658 CREATE TABLE t1 (s1 INT, s2 VARCHAR(50) CHARACTER SET UTF8) ENGINE = InnoDB;
659 CREATE FULLTEXT INDEX i ON t1(s2);
660 INSERT INTO t1 VALUES (2, 'ğė Daśi p ');
661 SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('+p +"ğė Daśi*"' IN BOOLEAN MODE);
662 s1 s2
663 DROP TABLE t1;
664 "----------Test19---------"
665 CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8) ENGINE = InnoDB;
666 INSERT INTO t1 VALUES (1,'İóëɠ');
667 CREATE FULLTEXT INDEX i ON t1 (char_column);
668 SELECT * FROM t1 WHERE MATCH(char_column) AGAINST ('"İóëɠ"' IN BOOLEAN MODE);
669 id char_column
670 1 İóëɠ
671 DROP TABLE t1;
672 "----------Test20---------"
673 CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF32, char_column2 VARCHAR(60) character set utf8) ENGINE = InnoDB;
674 INSERT INTO t1 (char_column) VALUES ('abcde'),('fghij'),('klmno'),('qrstu');
675 UPDATE t1 SET char_column2 = char_column;
676 CREATE FULLTEXT INDEX i ON t1 (char_column2);
677 SELECT * FROM t1 WHERE MATCH(char_column) AGAINST ('abc*' IN BOOLEAN MODE);
678 ERROR HY000: Can't find FULLTEXT index matching the column list
679 DROP TABLE t1;
680 "----------Test22---------"
681 CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8) ENGINE = InnoDB;
682 INSERT INTO t1 VALUES (1,'aaa'),(2,'bbb'),(3,'ccc');
683 CREATE FULLTEXT INDEX i ON t1 (char_column);
684 HANDLER t1 OPEN;
685 HANDLER t1 READ i = ('aaa');
686 ERROR HY000: FULLTEXT index `i` does not support this operation
687 DROP TABLE t1;
688 "----------Test25---------"
689 CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8 COLLATE UTF8_CROATIAN_CI) ENGINE=InnoDB;
690 INSERT INTO t1 VALUES (1,'LJin'),(2,'ljin'),(3,'lmin'),(4,'LJLJLJLJLJ');
691 CREATE FULLTEXT INDEX i ON t1 (char_column);
692 SELECT count(*) FROM t1 WHERE MATCH (char_column) AGAINST ('lj*' IN BOOLEAN MODE);
693 count(*)
694 3
695 DROP TABLE t1;
696 "----------Test27---------"
697 CREATE TABLE t1 (id INT,char_column VARCHAR(60));
698 CREATE TABLE t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, a TEXT)ENGINE=InnoDB;
699 ALTER TABLE t2 DROP a;
700 CREATE FULLTEXT INDEX i ON t1 (char_column);
701 INSERT INTO t1 values (1,'aaa');
702 CREATE TABLE mdev20987_1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=InnoDB;
703 CREATE TABLE mdev20987_2(f1 INT NOT NULL, f2 CHAR(100),
704 FULLTEXT(f2),
705 FOREIGN KEY(f1) REFERENCES mdev20987_1(f1))ENGINE=InnoDB;
706 INSERT INTO mdev20987_1 VALUES(1);
707 INSERT INTO mdev20987_2 VALUES(1, 'mariadb');
708 CREATE TABLE mdev22358 (a INT, b TEXT, FULLTEXT KEY ftidx (b)) ENGINE=InnoDB;
709 ALTER TABLE mdev22358 DROP KEY ftidx;
710 INSERT INTO mdev22358 (a) VALUES (2),(2);
711 ALTER TABLE mdev22358 ADD UNIQUE KEY uidx (a), ADD FULLTEXT KEY ftidx (b);
712 ERROR 23000: Duplicate entry '2' for key 'uidx'
713 # restart
714 SHOW CREATE TABLE t2;
715 Table Create Table
716 t2 CREATE TABLE `t2` (
717 `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
718 PRIMARY KEY (`FTS_DOC_ID`)
719 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
720 DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb');
721 DROP TABLE t1, t2, mdev20987_2, mdev20987_1, mdev22358;
722 "----------Test28---------"
723 create table `fts_test`(`a` text,fulltext key(`a`))engine=innodb;
724 set session autocommit=0;
725 insert into `fts_test` values ('');
726 savepoint `b`;
727 savepoint `b`;
728 set session autocommit=1;
729 DROP TABLE fts_test;
730 "----------Test29---------"
731 CREATE TABLE articles (
732 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
733 title VARCHAR(200),
734 body TEXT,
735 FULLTEXT (title,body)
736 ) ENGINE=InnoDB;
737 INSERT INTO articles (title,body) VALUES
738 ('MySQL Tutorial','DBMS stands for DataBase ...');
739 start transaction;
740 INSERT INTO articles (title,body) VALUES
741 ('How To Use MySQL Well','After you went through a ...');
742 savepoint `a1`;
743 INSERT INTO articles (title,body) VALUES
744 ('Optimizing MySQL','In this tutorial we will show ...');
745 savepoint `a2`;
746 INSERT INTO articles (title,body) VALUES
747 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...');
748 savepoint `a3`;
749 INSERT INTO articles (title,body) VALUES
750 ('MySQL vs. YourSQL','In the following database comparison ...');
751 savepoint `a4`;
752 SELECT * FROM articles
753 WHERE MATCH (title,body)
754 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
755 id title body
756 1 MySQL Tutorial DBMS stands for DataBase ...
757 rollback to savepoint a3;
758 select title, body from articles;
759 title body
760 MySQL Tutorial DBMS stands for DataBase ...
761 How To Use MySQL Well After you went through a ...
762 Optimizing MySQL In this tutorial we will show ...
763 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
764 INSERT INTO articles (title,body) VALUES
765 ('MySQL Security','When configured properly, MySQL ...');
766 savepoint `a5`;
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 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
773 MySQL Security When configured properly, MySQL ...
774 rollback to savepoint a2;
775 select title, body from articles;
776 title body
777 MySQL Tutorial DBMS stands for DataBase ...
778 How To Use MySQL Well After you went through a ...
779 Optimizing MySQL In this tutorial we will show ...
780 commit;
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 SELECT * FROM articles
787 WHERE MATCH (title,body)
788 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
789 id title body
790 1 MySQL Tutorial DBMS stands for DataBase ...
791 3 Optimizing MySQL In this tutorial we will show ...
792 DROP TABLE articles;
793 "----------Test30---------"
794 CREATE TABLE articles (
795 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
796 title VARCHAR(200),
797 body TEXT,
798 FULLTEXT (title,body)
799 ) ENGINE=InnoDB;
800 INSERT INTO articles (title,body) VALUES
801 ('MySQL Tutorial','DBMS stands for DataBase ...');
802 start transaction;
803 INSERT INTO articles (title,body) VALUES
804 ('How To Use MySQL Well','After you went through a ...');
805 savepoint `a1`;
806 INSERT INTO articles (title,body) VALUES
807 ('Optimizing MySQL','In this tutorial we will show ...');
808 savepoint `a2`;
809 INSERT INTO articles (title,body) VALUES
810 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...');
811 savepoint `a3`;
812 INSERT INTO articles (title,body) VALUES
813 ('MySQL vs. YourSQL','In the following database comparison ...');
814 savepoint `a4`;
815 SELECT * FROM articles
816 WHERE MATCH (title,body)
817 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
818 id title body
819 1 MySQL Tutorial DBMS stands for DataBase ...
820 rollback to savepoint a3;
821 select title, body from articles;
822 title body
823 MySQL Tutorial DBMS stands for DataBase ...
824 How To Use MySQL Well After you went through a ...
825 Optimizing MySQL In this tutorial we will show ...
826 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
827 INSERT INTO articles (title,body) VALUES
828 ('MySQL Security','When configured properly, MySQL ...');
829 savepoint `a5`;
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 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
836 MySQL Security When configured properly, MySQL ...
837 rollback to savepoint a2;
838 select title, body from articles;
839 title body
840 MySQL Tutorial DBMS stands for DataBase ...
841 How To Use MySQL Well After you went through a ...
842 Optimizing MySQL In this tutorial we will show ...
843 rollback;
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 SELECT * FROM articles
850 WHERE MATCH (title,body)
851 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
852 id title body
853 1 MySQL Tutorial DBMS stands for DataBase ...
854 DROP TABLE articles;
855 CREATE TABLE articles (
856 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
857 title VARCHAR(200),
858 body TEXT,
859 FULLTEXT (title,body)
860 ) ENGINE=InnoDB;
861 INSERT INTO articles (title,body) VALUES
862 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
863 ('How To Use MySQL Well','After you went through a ...'),
864 ('Optimizing MySQL','In this tutorial we will show ...'),
865 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
866 ('MySQL vs. YourSQL','In the following database comparison ...'),
867 ('MySQL Security','When configured properly, MySQL ...');
868 ANALYZE TABLE articles;
869 SELECT *, MATCH(title, body) AGAINST ('-database +MySQL' 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 *, MATCH(title, body) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score FROM articles;
878 id title body score
879 1 MySQL Tutorial DBMS stands for DataBase ... 0
880 2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186
881 3 Optimizing MySQL In this tutorial we will show ... 0.000000001885928302414186
882 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0.000000001885928302414186
883 5 MySQL vs. YourSQL In the following database comparison ... 0
884 6 MySQL Security When configured properly, MySQL ... 0.000000003771856604828372
885 SELECT * FROM articles where MATCH(title, body) AGAINST ('MySQL - (database - tutorial)' IN BOOLEAN MODE);
886 id title body
887 6 MySQL Security When configured properly, MySQL ...
888 1 MySQL Tutorial DBMS stands for DataBase ...
889 2 How To Use MySQL Well After you went through a ...
890 3 Optimizing MySQL In this tutorial we will show ...
891 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
892 SELECT * FROM articles where MATCH(title, body) AGAINST ('MySQL - (- tutorial database)' IN BOOLEAN MODE);
893 id title body
894 6 MySQL Security When configured properly, MySQL ...
895 1 MySQL Tutorial DBMS stands for DataBase ...
896 2 How To Use MySQL Well After you went through a ...
897 3 Optimizing MySQL In this tutorial we will show ...
898 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
899 SELECT * FROM articles where MATCH(title, body) AGAINST ('MySQL - (- tutorial database) -Tricks' IN BOOLEAN MODE);
900 id title body
901 6 MySQL Security When configured properly, MySQL ...
902 1 MySQL Tutorial DBMS stands for DataBase ...
903 2 How To Use MySQL Well After you went through a ...
904 3 Optimizing MySQL In this tutorial we will show ...
905 SELECT * FROM articles where MATCH(title, body) AGAINST ('-Tricks MySQL - (- tutorial database)' IN BOOLEAN MODE);
906 id title body
907 6 MySQL Security When configured properly, MySQL ...
908 1 MySQL Tutorial DBMS stands for DataBase ...
909 2 How To Use MySQL Well After you went through a ...
910 3 Optimizing MySQL In this tutorial we will show ...
911 DROP TABLE articles;
912 create table t1 (FTS_DOC_ID bigint unsigned auto_increment not null primary key,
913 title varchar(200),body text,fulltext(title,body)) engine=innodb;
914 insert into t1 set body='test';
915 select * from t1 where match(title,body) against('%test');
916 FTS_DOC_ID title body
917 1 NULL test
918 select * from t1 where match(title,body) against('%');
919 FTS_DOC_ID title body
920 select * from t1 where match(title,body) against('%%%%');
921 FTS_DOC_ID title body
922 drop table t1;
923 CREATE DATABASE `benu database`;
924 USE `benu database`;
925 CREATE TABLE t1 (
926 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
927 a VARCHAR(200),
928 b TEXT
929 ) ENGINE = InnoDB;
930 INSERT INTO t1 (a,b) VALUES
931 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
932 ('How To Use MySQL Well','After you went through a ...'),
933 ('Optimizing MySQL','In this tutorial we will show ...');
934 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
935 SHOW CREATE TABLE t1;
936 Table Create Table
937 t1 CREATE TABLE `t1` (
938 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
939 `a` varchar(200) DEFAULT NULL,
940 `b` text DEFAULT NULL,
941 PRIMARY KEY (`id`),
942 FULLTEXT KEY `idx` (`a`,`b`)
943 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
944 INSERT INTO t1 (a,b) VALUES
945 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
946 ('MySQL vs. YourSQL','In the following database comparison ...'),
947 ('MySQL Security','When configured properly, MySQL ...');
948 SELECT id FROM t1 WHERE MATCH (a,b)
949 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
950 id
951 1
952 3
953 select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
954 id
955 select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
956 id
957 select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
958 id
959 select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
960 id
961 select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
962 id x
963 1 0
964 2 0
965 3 0
966 4 0
967 5 0
968 6 0
969 select id, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
970 id x
971 1 0
972 2 0
973 3 0
974 4 0
975 5 0
976 6 0
977 select id from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
978 id
979 select id from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
980 id
981 select id from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
982 id
983 DROP DATABASE `benu database`;
984 USE test;
985 CREATE TABLE `t21` (`a` text, `b` int not null,
986 fulltext key (`a`), fulltext key (`a`)
987 ) ENGINE=INNODB DEFAULT CHARSET=LATIN1;
988 Warnings:
989 Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release
990 ALTER TABLE `t21` ADD UNIQUE INDEX (`b`), ALGORITHM=INPLACE;
991 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY
992 ALTER TABLE `t21` ADD UNIQUE INDEX (`b`);
993 DROP TABLE t21;
994 CREATE TABLE `t21` (`a` text, `b` int not null,
995 fulltext key (`a`)) ENGINE=INNODB DEFAULT CHARSET=LATIN1;
996 ALTER TABLE `t21` ADD UNIQUE INDEX (`b`);
997 DROP TABLE t21;
998 CREATE TABLE t1 (
999 id INT NOT NULL,
1000 a VARCHAR(200),
1001 b TEXT
1002 ) ENGINE = InnoDB;
1003 INSERT INTO t1 VALUES
1004 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
1005 (2, 'How To Use MySQL Well','After you went through a ...'),
1006 (3, 'Optimizing MySQL','In this tutorial we will show ...');
1007 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
1008 ALTER TABLE t1 ADD UNIQUE INDEX (`id`);
1009 SELECT id FROM t1 WHERE MATCH (a,b)
1010 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
1011 id
1012 1
1013 3
1014 select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
1015 id
1016 select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
1017 id
1018 select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
1019 id
1020 select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
1021 id
1022 select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
1023 id x
1024 1 0
1025 2 0
1026 3 0
1027 DROP TABLE t1;
1028 CREATE TABLE t1 (
1029 id INT NOT NULL,
1030 a VARCHAR(200),
1031 b TEXT
1032 ) ENGINE = InnoDB;
1033 INSERT INTO t1 VALUES
1034 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
1035 (2, 'How To Use MySQL Well','After you went through a ...'),
1036 (3, 'Optimizing MySQL','In this tutorial we will show ...');
1037 ALTER TABLE t1 ADD UNIQUE INDEX (`id`), ADD FULLTEXT INDEX idx (a,b);
1038 SELECT id FROM t1 WHERE MATCH (a,b)
1039 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
1040 id
1041 1
1042 3
1043 select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
1044 id
1045 select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
1046 id
1047 select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
1048 id
1049 select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
1050 id
1051 DROP TABLE t1;
1052 CREATE TABLE t1 (
1053 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
1054 a VARCHAR(200),
1055 b TEXT
1056 ) ENGINE = InnoDB;
1057 INSERT INTO t1 VALUES
1058 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
1059 (2, 'How To Use MySQL Well','After you went through a ...'),
1060 (3, 'Optimizing MySQL','In this tutorial we will show ...');
1061 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
1062 ALTER TABLE t1 ADD UNIQUE INDEX (`FTS_DOC_ID`);
1063 SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b)
1064 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
1065 FTS_DOC_ID
1066 1
1067 3
1068 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
1069 FTS_DOC_ID
1070 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
1071 FTS_DOC_ID
1072 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
1073 FTS_DOC_ID
1074 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
1075 FTS_DOC_ID
1076 select FTS_DOC_ID, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
1077 FTS_DOC_ID x
1078 1 0
1079 2 0
1080 3 0
1081 DROP TABLE t1;
1082 CREATE TABLE t1 (
1083 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
1084 a VARCHAR(200),
1085 b TEXT
1086 ) ENGINE = InnoDB;
1087 INSERT INTO t1 VALUES
1088 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
1089 (2, 'How To Use MySQL Well','After you went through a ...'),
1090 (3, 'Optimizing MySQL','In this tutorial we will show ...');
1091 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b), ADD UNIQUE INDEX FTS_DOC_ID_INDEX (FTS_DOC_ID);
1092 SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b)
1093 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
1094 FTS_DOC_ID
1095 1
1096 3
1097 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
1098 FTS_DOC_ID
1099 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
1100 FTS_DOC_ID
1101 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
1102 FTS_DOC_ID
1103 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
1104 FTS_DOC_ID
1105 select FTS_DOC_ID, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
1106 FTS_DOC_ID x
1107 1 0
1108 2 0
1109 3 0
1110 DROP TABLE t1;
1111 CREATE TABLE t2 (`b` char(2),fulltext(`b`)) ENGINE=INNODB
1112 DEFAULT CHARSET=LATIN1;
1113 CREATE TABLE t3 LIKE t2;
1114 INSERT INTO `t2` VALUES();
1115 COMMIT WORK AND CHAIN;
1116 INSERT INTO `t3` VALUES ();
1117 UPDATE `t2` SET `b` = 'a';
1118 SAVEPOINT BATCH1;
1119 DROP TABLE t2;
1120 DROP TABLE t3;
1121 CREATE TABLE t1 (
1122 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1123 a VARCHAR(200),
1124 b TEXT
1125 ) ENGINE = InnoDB;
1126 INSERT INTO t1 (a,b) VALUES
1127 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
1128 ('How To Use MySQL Well','After you went through a ...'),
1129 ('Optimizing MySQL','In this tutorial we will show ...');
1130 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
1131 COMMIT WORK AND CHAIN;
1132 INSERT INTO t1 (a,b) VALUES
1133 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
1134 ('MySQL vs. YourSQL','In the following database comparison ...'),
1135 ('MySQL Security','When configured properly, MySQL ...');
1136 SAVEPOINT BATCH1;
1137 SELECT id FROM t1 WHERE MATCH (a,b)
1138 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
1139 id
1140 1
1141 2
1142 3
1143 INSERT INTO t1 (a,b) VALUES
1144 ('1002 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
1145 ('MySQL vs. YourSQL','In the following database comparison ...'),
1146 ('MySQL Security','When configured properly, MySQL ...');
1147 ROLLBACK TO SAVEPOINT BATCH1;
1148 COMMIT;
1149 SELECT id FROM t1 WHERE MATCH (a,b)
1150 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
1151 id
1152 6
1153 1
1154 2
1155 3
1156 4
1157 5
1158 DROP TABLE t1;
1159 CREATE TABLE `t` (`a` char(20) character set utf8 default null,
1160 fulltext key (`a`)) ENGINE=INNODB;
1161 INSERT INTO `t` VALUES ('a');
1162 INSERT INTO `t` VALUES ('aaa');
1163 SELECT MATCH(`a`) AGAINST (0x22dd22) FROM `t`;
1164 MATCH(`a`) AGAINST (0x22dd22)
1165 0
1166 0
1167 SELECT MATCH(`a`) AGAINST (0x2222) FROM `t`;
1168 MATCH(`a`) AGAINST (0x2222)
1169 0
1170 0
1171 SELECT MATCH(`a`) AGAINST (0x22) FROM `t`;
1172 MATCH(`a`) AGAINST (0x22)
1173 0
1174 0
1175 SELECT MATCH(`a`) AGAINST (0x2261616122) FROM `t`;
1176 MATCH(`a`) AGAINST (0x2261616122)
1177 0
1178 0.0906190574169159
1179 SELECT MATCH(`a`) AGAINST (0x2261dd6122) FROM `t`;
1180 MATCH(`a`) AGAINST (0x2261dd6122)
1181 0
1182 0
1183 SELECT MATCH(`a`) AGAINST (0x2261dd612222226122) FROM `t`;
1184 MATCH(`a`) AGAINST (0x2261dd612222226122)
1185 0
1186 0
1187 DROP TABLE t;
1188 CREATE TABLE t(a CHAR(1),FULLTEXT KEY(a)) ENGINE=INNODB;
1189 HANDLER t OPEN;
1190 HANDLER t READ a NEXT;
1191 ERROR HY000: FULLTEXT index `a` does not support this operation
1192 HANDLER t READ a PREV;
1193 ERROR HY000: FULLTEXT index `a` does not support this operation
1194 DROP TABLE t;
1195 CREATE TABLE `%`(a TEXT, FULLTEXT INDEX(a)) ENGINE=INNODB;
1196 CREATE TABLE `A B`(a TEXT, FULLTEXT INDEX(a)) ENGINE=INNODB;
1197 DROP TABLE `%`;
1198 DROP TABLE `A B`;
1199 CREATE TABLE `t-26`(a VARCHAR(10),FULLTEXT KEY(a)) ENGINE=INNODB;
1200 INSERT INTO `t-26` VALUES('117');
1201 DROP TABLE `t-26`;
1202 CREATE TABLE `t1` (
1203 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1204 `content` TEXT NOT NULL,
1205 PRIMARY KEY (`id`),
1206 FULLTEXT INDEX `IDX_CONTEXT_FULLTEXT`(`content`)
1207 )
1208 ENGINE = InnoDB;
1209 insert into t1 (content)
1210 values
1211 ('This is a story which has has a complicated phrase structure here in the
1212 middle'),
1213 ('This is a story which doesn''t have that text'),
1214 ('This is a story that has complicated the phrase structure');
1215 select * from t1
1216 where match(content) against('"complicated phrase structure"' in boolean
1217 mode);
1218 id content
1219 1 This is a story which has has a complicated phrase structure here in the
1220 middle
1221 select * from t1
1222 where match(content) against('+"complicated phrase structure"' in boolean
1223 mode);
1224 id content
1225 1 This is a story which has has a complicated phrase structure here in the
1226 middle
1227 select * from t1
1228 where match(content) against('"complicated the phrase structure"' in boolean
1229 mode);
1230 id content
1231 3 This is a story that has complicated the phrase structure
1232 select * from t1 where match(content) against('+"this is a story which" +"complicated the phrase structure"' in boolean mode);
1233 id content
1234 select * from t1 where match(content) against('"the complicated the phrase structure"' in boolean mode);
1235 id content
1236 3 This is a story that has complicated the phrase structure
1237 select * from t1 where match(content) against('"complicated a phrase structure"' in boolean mode);
1238 id content
1239 DROP TABLE t1;
1240 CREATE TABLE my (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
1241 c VARCHAR(32), FULLTEXT(c)) ENGINE = INNODB;
1242 INSERT INTO my (c) VALUES ('green-iguana');
1243 SELECT * FROM my WHERE MATCH(c) AGAINST ('green-iguana');
1244 id c
1245 1 green-iguana
1246 DROP TABLE my;
1247 CREATE TABLE ift (
1248 `a` int(11) NOT NULL,
1249 `b` text,
1250 PRIMARY KEY (`a`),
1251 FULLTEXT KEY `b` (`b`)
1252 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1253 INSERT INTO ift values (1, "skip");
1254 INSERT INTO ift values (2, "skip and networking");
1255 INSERT INTO ift values (3, "--skip-networking");
1256 INSERT INTO ift values (4, "-donot--skip-networking");
1257 SELECT * FROM ift WHERE MATCH (b) AGAINST ('--skip-networking');
1258 a b
1259 2 skip and networking
1260 3 --skip-networking
1261 4 -donot--skip-networking
1262 1 skip
1263 SELECT * FROM ift WHERE MATCH (b) AGAINST ('skip-networking');
1264 a b
1265 2 skip and networking
1266 3 --skip-networking
1267 4 -donot--skip-networking
1268 1 skip
1269 SELECT * FROM ift WHERE MATCH (b) AGAINST ('----');
1270 a b
1271 SELECT * FROM ift WHERE MATCH (b) AGAINST ('-donot--skip-networking');
1272 a b
1273 4 -donot--skip-networking
1274 2 skip and networking
1275 3 --skip-networking
1276 1 skip
1277 DROP TABLE ift;
1278 CREATE TABLE articles (
1279 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1280 title VARCHAR(200),
1281 body TEXT,
1282 FULLTEXT (title,body)
1283 ) ENGINE=InnoDB;
1284 INSERT INTO articles (title,body) VALUES
1285 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
1286 ('How To Use MySQL Well','After you went through a ...'),
1287 ('Optimizing MySQL','In this tutorial we will show ...'),
1288 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
1289 ('MySQL vs. YourSQL','In the following database comparison ...'),
1290 ('( that''s me )','When configured properly, MySQL ...');
1291 SELECT * FROM articles WHERE MATCH (title,body)
1292 AGAINST ('( yours''s* )' IN BOOLEAN MODE);
1293 id title body
1294 5 MySQL vs. YourSQL In the following database comparison ...
1295 SELECT * FROM articles WHERE MATCH (title,body)
1296 AGAINST ('s*' IN BOOLEAN MODE);
1297 id title body
1298 1 MySQL Tutorial DBMS stands for DataBase ...
1299 3 Optimizing MySQL In this tutorial we will show ...
1300 SELECT * FROM articles WHERE MATCH (title,body)
1301 AGAINST ('stands\'] | * | show[@database' IN NATURAL LANGUAGE MODE);
1302 id title body
1303 1 MySQL Tutorial DBMS stands for DataBase ...
1304 3 Optimizing MySQL In this tutorial we will show ...
1305 5 MySQL vs. YourSQL In the following database comparison ...
1306 DROP TABLE articles;
1307 CREATE TABLE t1(a TEXT CHARACTER SET LATIN1, FULLTEXT INDEX(a)) ENGINE=INNODB;
1308 SELECT * FROM t1 WHERE MATCH(a) AGAINST("*");
1309 ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'
1310 DROP TABLE t1;
1311 CREATE TABLE t1 (
1312 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1313 a VARCHAR(200),
1314 FULLTEXT (a)
1315 ) ENGINE= InnoDB;
1316 INSERT INTO t1 (a) VALUES
1317 ('Do you know MySQL is a good database'),
1318 ('How to build a good database'),
1319 ('Do you know'),
1320 ('Do you know MySQL'),
1321 ('How to use MySQL'),
1322 ('Do you feel good'),
1323 ('MySQL is good'),
1324 ('MySQL is good to know'),
1325 ('What is database');
1326 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know mysql"' IN BOOLEAN MODE);
1327 id a
1328 1 Do you know MySQL is a good database
1329 4 Do you know MySQL
1330 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+("know mysql")' IN BOOLEAN MODE);
1331 id a
1332 1 Do you know MySQL is a good database
1333 4 Do you know MySQL
1334 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('("know mysql" good)' IN BOOLEAN MODE);
1335 id a
1336 1 Do you know MySQL is a good database
1337 4 Do you know MySQL
1338 2 How to build a good database
1339 6 Do you feel good
1340 7 MySQL is good
1341 8 MySQL is good to know
1342 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+("know mysql" good)' IN BOOLEAN MODE);
1343 id a
1344 1 Do you know MySQL is a good database
1345 4 Do you know MySQL
1346 2 How to build a good database
1347 6 Do you feel good
1348 7 MySQL is good
1349 8 MySQL is good to know
1350 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('(good "know mysql")' IN BOOLEAN MODE);
1351 id a
1352 1 Do you know MySQL is a good database
1353 4 Do you know MySQL
1354 2 How to build a good database
1355 6 Do you feel good
1356 7 MySQL is good
1357 8 MySQL is good to know
1358 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+(good "know mysql")' IN BOOLEAN MODE);
1359 id a
1360 1 Do you know MySQL is a good database
1361 4 Do you know MySQL
1362 2 How to build a good database
1363 6 Do you feel good
1364 7 MySQL is good
1365 8 MySQL is good to know
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 2 How to build a good database
1370 4 Do you know MySQL
1371 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know mysql" +"good database"' IN BOOLEAN MODE);
1372 id a
1373 1 Do you know MySQL is a good database
1374 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know database"@4' IN BOOLEAN MODE);
1375 id a
1376 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know database"@8' IN BOOLEAN MODE);
1377 id a
1378 1 Do you know MySQL is a good database
1379 DROP TABLE t1;
1380 CREATE TABLE t1 (
1381 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1382 a VARCHAR(200),
1383 FULLTEXT (a)
1384 ) ENGINE= InnoDB;
1385 INSERT INTO t1 (a) VALUES
1386 ('know mysql good database');
1387 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"good database"' IN BOOLEAN MODE);
1388 id a
1389 1 know mysql good database
1390 DROP TABLE t1;
1391 CREATE TABLE t1(f1 TEXT, FULLTEXT KEY(f1))ENGINE=InnoDB;
1392 INSERT INTO t1 VALUES(repeat("this is the test case", 500));
1393 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
1394 ALTER TABLE t1 KEY_BLOCK_SIZE=0;
1395 DROP TABLE t1;