1
drop table if exists t1,t2,t3;
2
CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
3
INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),
4
('Full-text indexes', 'are called collections'),
5
('Only MyISAM tables','support collections'),
6
('Function MATCH ... AGAINST()','is used to do a search'),
7
('Full-text search in MySQL', 'implements vector space model');
9
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
10
t1 1 a 1 a NULL NULL NULL NULL YES FULLTEXT
11
t1 1 a 2 b NULL NULL NULL NULL YES FULLTEXT
12
select * from t1 where MATCH(a,b) AGAINST ("collections");
14
Only MyISAM tables support collections
15
Full-text indexes are called collections
16
explain extended select * from t1 where MATCH(a,b) AGAINST ("collections");
17
id select_type table type possible_keys key key_len ref rows filtered Extra
18
1 SIMPLE t1 fulltext a a 0 1 100.00 Using where
20
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections'))
21
select * from t1 where MATCH(a,b) AGAINST ("indexes");
23
Full-text indexes are called collections
24
select * from t1 where MATCH(a,b) AGAINST ("indexes collections");
26
Full-text indexes are called collections
27
Only MyISAM tables support collections
28
select * from t1 where MATCH(a,b) AGAINST ("only");
30
select * from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
32
Only MyISAM tables support collections
33
Full-text indexes are called collections
34
MySQL has now support for full-text search
35
select * from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION);
37
Full-text indexes are called collections
38
Only MyISAM tables support collections
39
select * from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION);
41
Full-text indexes are called collections
42
Only MyISAM tables support collections
43
MySQL has now support for full-text search
44
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE);
46
Full-text indexes are called collections
47
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
49
Full-text indexes are called collections
50
Only MyISAM tables support collections
51
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN BOOLEAN MODE WITH QUERY EXPANSION);
52
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'QUERY EXPANSION)' at line 1
53
explain select * from t1 where MATCH(a,b) AGAINST ("collections");
54
id select_type table type possible_keys key key_len ref rows Extra
55
1 SIMPLE t1 fulltext a a 0 1 Using where
56
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0;
57
id select_type table type possible_keys key key_len ref rows Extra
58
1 SIMPLE t1 fulltext a a 0 1 Using where
59
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>1;
60
id select_type table type possible_keys key key_len ref rows Extra
61
1 SIMPLE t1 fulltext a a 0 1 Using where
62
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=0;
63
id select_type table type possible_keys key key_len ref rows Extra
64
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
65
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=1;
66
id select_type table type possible_keys key key_len ref rows Extra
67
1 SIMPLE t1 fulltext a a 0 1 Using where
68
explain select * from t1 where 0<MATCH(a,b) AGAINST ("collections");
69
id select_type table type possible_keys key key_len ref rows Extra
70
1 SIMPLE t1 fulltext a a 0 1 Using where
71
explain select * from t1 where 1<MATCH(a,b) AGAINST ("collections");
72
id select_type table type possible_keys key key_len ref rows Extra
73
1 SIMPLE t1 fulltext a a 0 1 Using where
74
explain select * from t1 where 0<=MATCH(a,b) AGAINST ("collections");
75
id select_type table type possible_keys key key_len ref rows Extra
76
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
77
explain select * from t1 where 1<=MATCH(a,b) AGAINST ("collections");
78
id select_type table type possible_keys key key_len ref rows Extra
79
1 SIMPLE t1 fulltext a a 0 1 Using where
80
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0 and a like '%ll%';
81
id select_type table type possible_keys key key_len ref rows Extra
82
1 SIMPLE t1 fulltext a a 0 1 Using where
83
select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE);
85
MySQL has now support for full-text search
86
explain extended select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE);
87
id select_type table type possible_keys key key_len ref rows filtered Extra
88
1 SIMPLE t1 fulltext a a 0 1 100.00 Using where
90
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('support -collections' in boolean mode))
91
select * from t1 where MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE);
93
MySQL has now support for full-text search
94
Full-text indexes are called collections
95
Only MyISAM tables support collections
96
select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE);
98
Full-text indexes are called collections
99
Only MyISAM tables support collections
100
select * from t1 where MATCH(a,b) AGAINST("sear*" IN BOOLEAN MODE);
102
MySQL has now support for full-text search
103
Function MATCH ... AGAINST() is used to do a search
104
Full-text search in MySQL implements vector space model
105
select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
107
Only MyISAM tables support collections
108
select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
110
MySQL has now support for full-text search
111
Function MATCH ... AGAINST() is used to do a search
112
Full-text search in MySQL implements vector space model
113
select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
115
MySQL has now support for full-text search
116
Full-text search in MySQL implements vector space model
117
select * from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
119
Function MATCH ... AGAINST() is used to do a search
120
select *, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
122
MySQL has now support for full-text search 1
123
Full-text indexes are called collections 1
124
Only MyISAM tables support collections 2
125
Function MATCH ... AGAINST() is used to do a search 0
126
Full-text search in MySQL implements vector space model 0
127
select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
129
MySQL has now support for full-text search 1
130
Full-text indexes are called collections 1
131
Only MyISAM tables support collections 2
132
Function MATCH ... AGAINST() is used to do a search 0
133
Full-text search in MySQL implements vector space model 0
134
select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
136
Full-text indexes are called collections
137
select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
139
select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
141
MySQL has now support for full-text search
142
select * from t1 where MATCH a,b AGAINST ('"now support"' IN BOOLEAN MODE);
144
MySQL has now support for full-text search
145
select * from t1 where MATCH a,b AGAINST ('"text search" "now support"' IN BOOLEAN MODE);
147
MySQL has now support for full-text search
148
Full-text search in MySQL implements vector space model
149
select * from t1 where MATCH a,b AGAINST ('"text search" -"now support"' IN BOOLEAN MODE);
151
Full-text search in MySQL implements vector space model
152
select * from t1 where MATCH a,b AGAINST ('"text search" +"now support"' IN BOOLEAN MODE);
154
MySQL has now support for full-text search
155
select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE);
157
select * from t1 where MATCH a,b AGAINST ('"xt indexes"' IN BOOLEAN MODE);
159
select * from t1 where MATCH a,b AGAINST ('+(support collections) +foobar*' IN BOOLEAN MODE);
161
select * from t1 where MATCH a,b AGAINST ('+(+(support collections)) +foobar*' IN BOOLEAN MODE);
163
select * from t1 where MATCH a,b AGAINST ('+collections -supp* -foobar*' IN BOOLEAN MODE);
165
Full-text indexes are called collections
166
select * from t1 where MATCH a,b AGAINST('"space model' IN BOOLEAN MODE);
168
Full-text search in MySQL implements vector space model
169
select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE);
171
Full-text search in MySQL implements vector space model
172
select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE);
174
MySQL has now support for full-text search
175
Function MATCH ... AGAINST() is used to do a search
176
select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes");
178
Only MyISAM tables support collections
179
Full-text indexes are called collections
180
Full-text indexes are called collections
181
delete from t1 where a like "MySQL%";
182
update t1 set a='some test foobar' where MATCH a,b AGAINST ('model');
183
delete from t1 where MATCH(a,b) AGAINST ("indexes");
186
Only MyISAM tables support collections
187
Function MATCH ... AGAINST() is used to do a search
188
some test foobar implements vector space model
190
create table t1 (a varchar(200) not null, fulltext (a));
191
insert t1 values ("aaa10 bbb20"), ("aaa20 bbb15"), ("aaa30 bbb10");
192
select * from t1 where match a against ("+aaa* +bbb*" in boolean mode);
197
select * from t1 where match a against ("+aaa* +bbb1*" in boolean mode);
201
select * from t1 where match a against ("+aaa* +ccc*" in boolean mode);
203
select * from t1 where match a against ("+aaa10 +(bbb*)" in boolean mode);
206
select * from t1 where match a against ("+(+aaa* +bbb1*)" in boolean mode);
210
select * from t1 where match a against ("(+aaa* +bbb1*)" in boolean mode);
221
INSERT INTO t1 VALUES (2,3),(1,2);
226
fulltext index tix (inhalt)
228
INSERT INTO t2 VALUES (1,'foo'),(2,'bar'),(3,'foobar');
229
select t1.id FROM t2 as ttxt,t1,t1 as ticket2
230
WHERE ticket2.id = ttxt.ticket AND t1.id = ticket2.ticket and
231
match(ttxt.inhalt) against ('foobar');
233
select ticket2.id FROM t2 as ttxt,t2 INNER JOIN t1 as ticket2 ON
234
ticket2.id = t2.ticket
235
WHERE ticket2.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar');
237
INSERT INTO t1 VALUES (3,3);
238
select ticket2.id FROM t2 as ttxt,t2
239
INNER JOIN t1 as ticket2 ON ticket2.id = t2.ticket
240
WHERE ticket2.id = ticket2.ticket and
241
match(ttxt.inhalt) against ('foobar');
245
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
246
t2 1 tig 1 ticket A NULL NULL NULL YES BTREE
247
t2 1 tix 1 inhalt NULL NULL NULL NULL YES FULLTEXT
248
show create table t2;
250
t2 CREATE TABLE `t2` (
251
`ticket` int(11) DEFAULT NULL,
253
KEY `tig` (`ticket`),
254
FULLTEXT KEY `tix` (`inhalt`)
255
) ENGINE=MyISAM DEFAULT CHARSET=latin1
256
select * from t2 where MATCH inhalt AGAINST (NULL);
258
select * from t2 where MATCH inhalt AGAINST ('foobar');
261
select * from t2 having MATCH inhalt AGAINST ('foobar');
264
CREATE TABLE t3 (t int(11),i text,fulltext tix (t,i));
265
ERROR HY000: Column 't' cannot be part of FULLTEXT index
266
CREATE TABLE t3 (t int(11),i text,
267
j varchar(200) CHARACTER SET latin2,
269
ERROR HY000: Column 'j' cannot be part of FULLTEXT index
274
fulltext index tix (inhalt)
276
select * from t2 where MATCH inhalt AGAINST (t2.inhalt);
277
ERROR HY000: Incorrect arguments to AGAINST
278
select * from t2 where MATCH ticket AGAINST ('foobar');
279
ERROR HY000: Can't find FULLTEXT index matching the column list
280
select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar');
281
ERROR HY000: Incorrect arguments to MATCH
284
id int(11) auto_increment,
285
title varchar(100) default '',
289
CREATE FULLTEXT INDEX ft1 ON t1(title);
290
insert into t1 (title) values ('this is a test');
291
select * from t1 where match title against ('test' in boolean mode);
294
update t1 set title='this is A test' where id=1;
296
Table Op Msg_type Msg_text
297
test.t1 check status OK
298
update t1 set title='this test once revealed a bug' where id=1;
301
1 this test once revealed a bug
302
update t1 set title=NULL where id=1;
304
CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) ENGINE=MyISAM;
305
insert into t1 values (1,"I wonder why the fulltext index doesnt work?");
306
SELECT * from t1 where MATCH (b) AGAINST ('apples');
308
insert into t1 values (2,"fullaaa fullzzz");
309
select * from t1 where match b against ('full*' in boolean mode);
312
1 I wonder why the fulltext index doesnt work?
314
CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) ENGINE=MyISAM;
315
INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so funny'),(4,'MySQL Tutorial');
323
create table t1 (a text, fulltext key (a));
324
insert into t1 values ('aaaa');
326
Table Op Msg_type Msg_text
327
test.t1 repair status OK
328
select * from t1 where match (a) against ('aaaa');
331
create table t1 ( ref_mag text not null, fulltext (ref_mag));
332
insert into t1 values ('test');
333
select ref_mag from t1 where match ref_mag against ('+test' in boolean mode);
336
alter table t1 change ref_mag ref_mag char (255) not null;
337
select ref_mag from t1 where match ref_mag against ('+test' in boolean mode);
341
create table t1 (t1_id int(11) primary key, name varchar(32));
342
insert into t1 values (1, 'data1');
343
insert into t1 values (2, 'data2');
344
create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32));
345
insert into t2 values (1, 1, 'xxfoo');
346
insert into t2 values (2, 1, 'xxbar');
347
insert into t2 values (3, 1, 'xxbuz');
348
select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode);
349
t1_id name t2_id name
351
select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode);
354
create table t1 (a text, fulltext key (a));
355
insert into t1 select "xxxx yyyy zzzz";
358
CREATE TABLE t1 (t text character set utf8 not null, fulltext(t));
359
INSERT t1 VALUES ('Mit freundlichem Gr��'), ('aus Osnabr�ck');
361
INSERT t1 VALUES ("��� �� - ������"),("������, �����!"),
362
("�� ������, �����!"),("� ����� ����!");
363
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('������');
365
��� �� - ������ utf8_general_ci
366
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('���*' IN BOOLEAN MODE);
368
� ����� ����! utf8_general_ci
369
SELECT * FROM t1 WHERE MATCH t AGAINST ('���' IN BOOLEAN MODE);
371
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabr�ck');
374
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabr�ck');
376
aus Osnabr�ck utf8_general_ci
377
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
379
SELECT t, collation(t),FORMAT(MATCH t AGAINST ('Osnabruck'),6) FROM t1 WHERE MATCH t AGAINST ('Osnabruck');
380
t collation(t) FORMAT(MATCH t AGAINST ('Osnabruck'),6)
381
aus Osnabr�ck utf8_general_ci 1.591140
382
alter table t1 modify t varchar(200) collate latin1_german2_ci not null;
384
Warning 1366 Incorrect string value: '\xD0\xAD\xD1\x82\xD0\xBE...' for column 't' at row 3
385
Warning 1366 Incorrect string value: '\xD0\x9E\xD1\x82\xD0\xBB...' for column 't' at row 4
386
Warning 1366 Incorrect string value: '\xD0\x9D\xD0\xB5 \xD0...' for column 't' at row 5
387
Warning 1366 Incorrect string value: '\xD0\xB8 \xD0\xB1\xD1...' for column 't' at row 6
388
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabr�ck');
390
aus Osnabr�ck latin1_german2_ci
391
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
393
aus Osnabr�ck latin1_german2_ci
395
CREATE TABLE t1 (s varchar(255), FULLTEXT (s)) DEFAULT CHARSET=utf8;
396
insert into t1 (s) values ('p�ra para para'),('para para para');
397
select * from t1 where match(s) against('para' in boolean mode);
401
select * from t1 where match(s) against('par*' in boolean mode);
406
CREATE TABLE t1 (h text, FULLTEXT (h));
407
INSERT INTO t1 VALUES ('Jesses Hasse Ling and his syncopators of Swing');
409
Table Op Msg_type Msg_text
410
test.t1 repair status OK
411
select count(*) from t1;
415
CREATE TABLE t1 ( a TEXT, FULLTEXT (a) );
416
INSERT INTO t1 VALUES ('testing ft_nlq_find_relevance');
417
SELECT MATCH(a) AGAINST ('nosuchword') FROM t1;
418
MATCH(a) AGAINST ('nosuchword')
421
create table t1 (a int primary key, b text, fulltext(b));
422
create table t2 (a int, b text);
423
insert t1 values (1, "aaaa"), (2, "bbbb");
424
insert t2 values (10, "aaaa"), (2, "cccc");
425
replace t1 select * from t2;
427
CREATE TABLE t1 (t VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci, FULLTEXT (t));
429
INSERT INTO t1 VALUES('Mit freundlichem Gr�� aus Osnabr�ck');
430
SELECT COUNT(*) FROM t1 WHERE MATCH(t) AGAINST ('"osnabr�ck"' IN BOOLEAN MODE);
434
CREATE TABLE t1 (a VARCHAR(30), FULLTEXT(a));
435
INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
436
SET myisam_repair_threads=2;
438
Table Op Msg_type Msg_text
439
test.t1 repair status OK
440
SET myisam_repair_threads=@@global.myisam_repair_threads;
441
INSERT INTO t1 VALUES('testword\'\'');
442
SELECT a FROM t1 WHERE MATCH a AGAINST('testword' IN BOOLEAN MODE);
445
SELECT a FROM t1 WHERE MATCH a AGAINST('testword\'\'' IN BOOLEAN MODE);
448
INSERT INTO t1 VALUES('test\'s');
449
SELECT a FROM t1 WHERE MATCH a AGAINST('test' IN BOOLEAN MODE);
453
CREATE TABLE t1 (a VARCHAR(10000), FULLTEXT(a));
454
SHOW CREATE TABLE t1;
456
t1 CREATE TABLE `t1` (
457
`a` varchar(10000) DEFAULT NULL,
458
FULLTEXT KEY `a` (`a`)
459
) ENGINE=MyISAM DEFAULT CHARSET=latin1
461
CREATE TABLE t1 (a TEXT, FULLTEXT KEY(a));
462
INSERT INTO t1 VALUES('test'),('test1'),('test');
463
PREPARE stmt from "SELECT a, FORMAT(MATCH(a) AGAINST('test1 test'),6) FROM t1 WHERE MATCH(a) AGAINST('test1 test')";
465
a FORMAT(MATCH(a) AGAINST('test1 test'),6)
468
a FORMAT(MATCH(a) AGAINST('test1 test'),6)
470
DEALLOCATE PREPARE stmt;
472
CREATE TABLE t1 (a VARCHAR(255), FULLTEXT(a));
473
SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test');
475
ALTER TABLE t1 DISABLE KEYS;
476
SELECT * FROM t1 WHERE MATCH(a) AGAINST('test');
477
ERROR HY000: Can't find FULLTEXT index matching the column list
479
CREATE TABLE t1(a TEXT);
480
INSERT INTO t1 VALUES(' aaaaa aaaa');
481
SELECT * FROM t1 WHERE MATCH(a) AGAINST ('"aaaa"' IN BOOLEAN MODE);
485
CREATE TABLE t1(a VARCHAR(20), FULLTEXT(a));
486
INSERT INTO t1 VALUES('Offside'),('City Of God');
487
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city of*' IN BOOLEAN MODE);
490
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of*)' IN BOOLEAN MODE);
493
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city* of*' IN BOOLEAN MODE);
added
removed
mysql-test/r/fulltext.result
