fulltext.result 6.31 KB
Newer Older
unknown's avatar
unknown committed
1 2 3 4 5 6 7 8
drop table if exists t1,t2,t3;
CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),
('Full-text indexes', 'are called collections'),
('Only MyISAM tables','support collections'),
('Function MATCH ... AGAINST()','is used to do a search'),
('Full-text search in MySQL', 'implements vector space model');
select * from t1 where MATCH(a,b) AGAINST ("collections");
9 10 11
a	b
Only MyISAM tables	support collections
Full-text indexes	are called collections
unknown's avatar
unknown committed
12
select * from t1 where MATCH(a,b) AGAINST ("indexes");
13 14
a	b
Full-text indexes	are called collections
unknown's avatar
unknown committed
15
select * from t1 where MATCH(a,b) AGAINST ("indexes collections");
16 17 18
a	b
Full-text indexes	are called collections
Only MyISAM tables	support collections
19 20 21 22 23
select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes");
a	b
Only MyISAM tables	support collections
Full-text indexes	are called collections
Full-text indexes	are called collections
unknown's avatar
unknown committed
24
select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE);
25 26
a	b
MySQL has now support	for full-text search
unknown's avatar
unknown committed
27
select * from t1 where MATCH(a,b) AGAINST("support  collections" IN BOOLEAN MODE);
28 29 30 31
a	b
MySQL has now support	for full-text search
Full-text indexes	are called collections
Only MyISAM tables	support collections
unknown's avatar
unknown committed
32
select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE);
33 34 35
a	b
Full-text indexes	are called collections
Only MyISAM tables	support collections
unknown's avatar
unknown committed
36
select * from t1 where MATCH(a,b) AGAINST("sear*" IN BOOLEAN MODE);
37 38 39 40
a	b
MySQL has now support	for full-text search
Function MATCH ... AGAINST()	is used to do a search
Full-text search in MySQL	implements vector space model
unknown's avatar
unknown committed
41
select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
42 43
a	b
Only MyISAM tables	support collections
unknown's avatar
unknown committed
44
select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
45 46 47 48
a	b
MySQL has now support	for full-text search
Function MATCH ... AGAINST()	is used to do a search
Full-text search in MySQL	implements vector space model
unknown's avatar
unknown committed
49
select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
50 51 52
a	b
MySQL has now support	for full-text search
Full-text search in MySQL	implements vector space model
unknown's avatar
unknown committed
53 54 55
select * from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
a	b
Function MATCH ... AGAINST()	is used to do a search
56 57 58 59 60 61 62 63
select *, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
a	b	x
MySQL has now support	for full-text search	1
Full-text indexes	are called collections	1
Only MyISAM tables	support collections	2
Function MATCH ... AGAINST()	is used to do a search	0
Full-text search in MySQL	implements vector space model	0
select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
unknown's avatar
unknown committed
64 65 66 67 68 69
a	b	x
MySQL has now support	for full-text search	1
Full-text indexes	are called collections	1
Only MyISAM tables	support collections	2
Function MATCH ... AGAINST()	is used to do a search	0
Full-text search in MySQL	implements vector space model	0
unknown's avatar
unknown committed
70 71 72
select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
unknown's avatar
unknown committed
73
select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE);
unknown's avatar
unknown committed
74 75
a	b
Full-text search in MySQL	implements vector space model
unknown's avatar
unknown committed
76 77 78 79
select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
Function MATCH ... AGAINST()	is used to do a search
unknown's avatar
unknown committed
80
delete from t1 where a like "MySQL%";
81 82 83 84 85 86 87
update t1 set a='some test foobar' where MATCH a,b AGAINST ('model');
delete from t1 where MATCH(a,b) AGAINST ("indexes");
select * from t1;
a	b
Only MyISAM tables	support collections
Function MATCH ... AGAINST()	is used to do a search
some test foobar	implements vector space model
unknown's avatar
unknown committed
88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105
drop table t1;
CREATE TABLE t1 (
id int(11),
ticket int(11),
KEY ti (id),
KEY tit (ticket)
);
INSERT INTO t1 VALUES (2,3),(1,2);
CREATE TABLE t2 (
ticket int(11),
inhalt text,
KEY tig (ticket),
fulltext index tix (inhalt)
);
INSERT INTO t2 VALUES (1,'foo'),(2,'bar'),(3,'foobar');
select t1.id FROM t2 as ttxt,t1,t1 as ticket2
WHERE ticket2.id = ttxt.ticket AND t1.id = ticket2.ticket and
match(ttxt.inhalt) against ('foobar');
106
id
unknown's avatar
unknown committed
107 108 109
select t1.id FROM t2 as ttxt,t1 INNER JOIN t1 as ticket2 ON
ticket2.id = ttxt.ticket
WHERE t1.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar');
110
id
unknown's avatar
unknown committed
111 112 113 114 115
INSERT INTO t1 VALUES (3,3);
select t1.id FROM t2 as ttxt,t1
INNER JOIN t1 as ticket2 ON ticket2.id = ttxt.ticket
WHERE t1.id = ticket2.ticket and
match(ttxt.inhalt) against ('foobar');
116 117
id
3
unknown's avatar
unknown committed
118
show keys from t2;
119 120 121
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t2	1	tig	1	ticket	A	NULL	NULL	NULL	YES	BTREE	
t2	1	tix	1	inhalt	A	NULL	1	NULL	YES	FULLTEXT	
unknown's avatar
unknown committed
122
show create table t2;
123 124 125 126
Table	Create Table
t2	CREATE TABLE `t2` (
  `ticket` int(11) default NULL,
  `inhalt` text,
127 128
  KEY `tig` (`ticket`),
  FULLTEXT KEY `tix` (`inhalt`)
129
) TYPE=MyISAM
unknown's avatar
unknown committed
130
select * from t2 where MATCH inhalt AGAINST (NULL);
131
ticket	inhalt
unknown's avatar
unknown committed
132
select * from t2 where  MATCH inhalt AGAINST ('foobar');
unknown's avatar
unknown committed
133 134
ticket	inhalt
3	foobar
unknown's avatar
unknown committed
135
select * from t2 having MATCH inhalt AGAINST ('foobar');
unknown's avatar
unknown committed
136 137
ticket	inhalt
3	foobar
unknown's avatar
unknown committed
138 139 140 141 142 143 144 145 146 147 148 149 150
CREATE TABLE t3 (
ticket int(11),
inhalt text,
KEY tig (ticket),
fulltext index tix (inhalt)
);
select * from t2 where MATCH inhalt AGAINST (t2.inhalt);
Wrong arguments to AGAINST
select * from t2 where MATCH ticket AGAINST ('foobar');
Can't find FULLTEXT index matching the column list
select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar');
Wrong arguments to MATCH
drop table t1,t2,t3;
unknown's avatar
unknown committed
151 152 153 154 155 156 157 158
CREATE TABLE t1 (
id int(11)  auto_increment,
title varchar(100)  default '',
PRIMARY KEY  (id),
KEY ind5 (title),
FULLTEXT KEY FT1 (title)
) TYPE=MyISAM;
insert into t1 (title) values ('this is a test');
unknown's avatar
unknown committed
159 160 161
select * from t1 where match title against ('test' in boolean mode);
id	title
1	this is a test
unknown's avatar
unknown committed
162 163 164 165 166 167 168 169
update t1 set title='this is A test' where id=1;
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
update t1 set title='this test once revealed a bug' where id=1;
select * from t1;
id	title
1	this test once revealed a bug
170
update t1 set title=NULL where id=1;
unknown's avatar
unknown committed
171 172 173 174 175 176
drop table t1;
CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) TYPE=MyISAM;
insert into t1 values (1,"I wonder why the fulltext index doesnt work?");
SELECT * from t1 where MATCH (b) AGAINST ('apples');
a	b
drop table t1;