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
|
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
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;
|
