1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
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");
a b
Only MyISAM tables support collections
Full-text indexes are called collections
select * from t1 where MATCH(a,b) AGAINST ("indexes");
a b
Full-text indexes are called collections
select * from t1 where MATCH(a,b) AGAINST ("indexes collections");
a b
Full-text indexes are called collections
Only MyISAM tables support collections
select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE);
a b
MySQL has now support for full-text search
select * from t1 where MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE);
a b
MySQL has now support for full-text search
Full-text indexes are called collections
Only MyISAM tables support collections
select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE);
a b
Full-text indexes are called collections
Only MyISAM tables support collections
select * from t1 where MATCH(a,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
Full-text search in MySQL implements vector space model
select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
a b
Only MyISAM tables support collections
select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
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
select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
a b
MySQL has now support for full-text search
Full-text search in MySQL implements vector space model
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
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
delete from t1 where a like "MySQL%";
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');
id
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');
id
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');
id
3
show keys from t2;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Comment
t2 1 tig 1 ticket A NULL NULL NULL
t2 1 tix 1 inhalt A NULL 1 NULL FULLTEXT
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`ticket` int(11) default NULL,
`inhalt` text,
KEY `tig` (`ticket`),
FULLTEXT KEY `tix` (`inhalt`)
) TYPE=MyISAM
select * from t2 where MATCH inhalt AGAINST (NULL);
ticket inhalt
select * from t2 where MATCH inhalt AGAINST ('foobar');
ticket inhalt
3 foobar
select * from t2 having MATCH inhalt AGAINST ('foobar');
ticket inhalt
3 foobar
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;
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');
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
|
