|
1
by brian
clean slate |
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'); |
|
8 |
SHOW INDEX FROM t1; |
|
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"); |
|
13 |
a b |
|
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 |
|
19 |
Warnings: |
|
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"); |
|
22 |
a b |
|
23 |
Full-text indexes are called collections |
|
24 |
select * from t1 where MATCH(a,b) AGAINST ("indexes collections"); |
|
25 |
a b |
|
26 |
Full-text indexes are called collections |
|
27 |
Only MyISAM tables support collections |
|
28 |
select * from t1 where MATCH(a,b) AGAINST ("only"); |
|
29 |
a b |
|
30 |
select * from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION); |
|
31 |
a b |
|
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); |
|
36 |
a b |
|
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); |
|
40 |
a b |
|
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); |
|
45 |
a b |
|
46 |
Full-text indexes are called collections |
|
47 |
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION); |
|
48 |
a b |
|
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); |
|
84 |
a b |
|
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 |
|
89 |
Warnings: |
|
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); |
|
92 |
a b |
|
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); |
|
97 |
a b |
|
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); |
|
101 |
a b |
|
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); |
|
106 |
a b |
|
107 |
Only MyISAM tables support collections |
|
108 |
select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); |
|
109 |
a b |
|
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); |
|
114 |
a b |
|
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); |
|
118 |
a b |
|
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; |
|
121 |
a b x |
|
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; |
|
128 |
a b x |
|
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); |
|
135 |
a b |
|
136 |
Full-text indexes are called collections |
|
137 |
select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE); |
|
138 |
a b |
|
139 |
select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE); |
|
140 |
a b |
|
141 |
MySQL has now support for full-text search |
|
142 |
select * from t1 where MATCH a,b AGAINST ('"now support"' IN BOOLEAN MODE); |
|
143 |
a b |
|
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); |
|
146 |
a b |
|
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); |
|
150 |
a b |
|
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); |
|
153 |
a b |
|
154 |
MySQL has now support for full-text search |
|
155 |
select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE); |
|
156 |
a b |
|
157 |
select * from t1 where MATCH a,b AGAINST ('"xt indexes"' IN BOOLEAN MODE); |
|
158 |
a b |
|
159 |
select * from t1 where MATCH a,b AGAINST ('+(support collections) +foobar*' IN BOOLEAN MODE); |
|
160 |
a b |
|
161 |
select * from t1 where MATCH a,b AGAINST ('+(+(support collections)) +foobar*' IN BOOLEAN MODE); |
|
162 |
a b |
|
163 |
select * from t1 where MATCH a,b AGAINST ('+collections -supp* -foobar*' IN BOOLEAN MODE); |
|
164 |
a b |
|
165 |
Full-text indexes are called collections |
|
166 |
select * from t1 where MATCH a,b AGAINST('"space model' IN BOOLEAN MODE); |
|
167 |
a b |
|
168 |
Full-text search in MySQL implements vector space model |
|
169 |
select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE); |
|
170 |
a b |
|
171 |
Full-text search in MySQL implements vector space model |
|
172 |
select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE); |
|
173 |
a b |
|
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"); |
|
177 |
a b |
|
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"); |
|
184 |
select * from t1; |
|
185 |
a b |
|
186 |
Only MyISAM tables support collections |
|
187 |
Function MATCH ... AGAINST() is used to do a search |
|
188 |
some test foobar implements vector space model |
|
189 |
drop table t1; |
|
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); |
|
193 |
a
|
|
194 |
aaa30 bbb10 |
|
195 |
aaa20 bbb15 |
|
196 |
aaa10 bbb20 |
|
197 |
select * from t1 where match a against ("+aaa* +bbb1*" in boolean mode); |
|
198 |
a
|
|
199 |
aaa30 bbb10 |
|
200 |
aaa20 bbb15 |
|
201 |
select * from t1 where match a against ("+aaa* +ccc*" in boolean mode); |
|
202 |
a
|
|
203 |
select * from t1 where match a against ("+aaa10 +(bbb*)" in boolean mode); |
|
204 |
a
|
|
205 |
aaa10 bbb20 |
|
206 |
select * from t1 where match a against ("+(+aaa* +bbb1*)" in boolean mode); |
|
207 |
a
|
|
208 |
aaa30 bbb10 |
|
209 |
aaa20 bbb15 |
|
210 |
select * from t1 where match a against ("(+aaa* +bbb1*)" in boolean mode); |
|
211 |
a
|
|
212 |
aaa30 bbb10 |
|
213 |
aaa20 bbb15 |
|
214 |
drop table t1; |
|
215 |
CREATE TABLE t1 ( |
|
216 |
id int(11), |
|
217 |
ticket int(11), |
|
218 |
KEY ti (id), |
|
219 |
KEY tit (ticket) |
|
220 |
);
|
|
221 |
INSERT INTO t1 VALUES (2,3),(1,2); |
|
222 |
CREATE TABLE t2 ( |
|
223 |
ticket int(11), |
|
224 |
inhalt text, |
|
225 |
KEY tig (ticket), |
|
226 |
fulltext index tix (inhalt) |
|
227 |
);
|
|
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'); |
|
232 |
id
|
|
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'); |
|
236 |
id
|
|
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'); |
|
242 |
id
|
|
243 |
3
|
|
244 |
show keys from t2; |
|
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; |
|
249 |
Table Create Table |
|
250 |
t2 CREATE TABLE `t2` ( |
|
251 |
`ticket` int(11) DEFAULT NULL, |
|
252 |
`inhalt` text, |
|
253 |
KEY `tig` (`ticket`), |
|
254 |
FULLTEXT KEY `tix` (`inhalt`) |
|
255 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
|
256 |
select * from t2 where MATCH inhalt AGAINST (NULL); |
|
257 |
ticket inhalt |
|
258 |
select * from t2 where MATCH inhalt AGAINST ('foobar'); |
|
259 |
ticket inhalt |
|
260 |
3 foobar |
|
261 |
select * from t2 having MATCH inhalt AGAINST ('foobar'); |
|
262 |
ticket inhalt |
|
263 |
3 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, |
|
268 |
fulltext tix (i,j)); |
|
269 |
ERROR HY000: Column 'j' cannot be part of FULLTEXT index |
|
270 |
CREATE TABLE t3 ( |
|
271 |
ticket int(11), |
|
272 |
inhalt text, |
|
273 |
KEY tig (ticket), |
|
274 |
fulltext index tix (inhalt) |
|
275 |
);
|
|
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
|
|
282 |
drop table t1,t2,t3;
|
|
283 |
CREATE TABLE t1 (
|
|
284 |
id int(11) auto_increment,
|
|
285 |
title varchar(100) default '', |
|
286 |
PRIMARY KEY (id),
|
|
287 |
KEY ind5 (title)
|
|
288 |
) ENGINE=MyISAM;
|
|
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); |
|
292 |
id title
|
|
293 |
1 this is a test
|
|
294 |
update t1 set title='this is A test' where id=1; |
|
295 |
check table t1;
|
|
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; |
|
299 |
select * from t1;
|
|
300 |
id title
|
|
301 |
1 this test once revealed a bug
|
|
302 |
update t1 set title=NULL where id=1;
|
|
303 |
drop table t1;
|
|
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'); |
|
307 |
a b
|
|
308 |
insert into t1 values (2,"fullaaa fullzzz");
|
|
309 |
select * from t1 where match b against ('full*' in boolean mode); |
|
310 |
a b
|
|
311 |
2 fullaaa fullzzz
|
|
312 |
1 I wonder why the fulltext index doesnt work?
|
|
313 |
drop table t1;
|
|
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'); |
|
316 |
select 8 from t1;
|
|
317 |
8
|
|
318 |
8
|
|
319 |
8
|
|
320 |
8
|
|
321 |
8
|
|
322 |
drop table t1;
|
|
323 |
create table t1 (a text, fulltext key (a));
|
|
324 |
insert into t1 values ('aaaa'); |
|
325 |
repair table t1;
|
|
326 |
Table Op Msg_type Msg_text
|
|
327 |
test.t1 repair status OK
|
|
328 |
select * from t1 where match (a) against ('aaaa'); |
|
329 |
a
|
|
330 |
drop table t1;
|
|
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); |
|
334 |
ref_mag
|
|
335 |
test
|
|
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); |
|
338 |
ref_mag
|
|
339 |
test
|
|
340 |
drop table t1;
|
|
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
|
|
350 |
1 data1 1 xxfoo
|
|
351 |
select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode); |
|
352 |
t2_id t1_id name
|
|
353 |
drop table t1,t2;
|
|
354 |
create table t1 (a text, fulltext key (a));
|
|
355 |
insert into t1 select "xxxx yyyy zzzz";
|
|
356 |
drop table t1;
|
|
357 |
SET NAMES latin1;
|
|
358 |
CREATE TABLE t1 (t text character set utf8 not null, fulltext(t));
|
|
359 |
INSERT t1 VALUES ('Mit freundlichem Grüß'), ('aus Osnabrück'); |
|
360 |
SET NAMES koi8r;
|
|
361 |
INSERT t1 VALUES ("üÔÏ ÍÙ - ÏÐÉÌËÉ"),("ïÔÌÅÚØ, ÇÎÉÄÁ!"),
|
|
362 |
("îÅ ×ÌÅÚÁÊ, ÕÂØÅÔ!"),("É ÂÕÄÅÔ ÐÒÁ×!");
|
|
363 |
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('ïðéìëé'); |
|
364 |
t collation(t)
|
|
365 |
üÔÏ ÍÙ - ÏÐÉÌËÉ utf8_general_ci
|
|
366 |
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('ðÒá*' IN BOOLEAN MODE); |
|
367 |
t collation(t)
|
|
368 |
É ÂÕÄÅÔ ÐÒÁ×! utf8_general_ci
|
|
369 |
SELECT * FROM t1 WHERE MATCH t AGAINST ('ÜÔÏ' IN BOOLEAN MODE); |
|
370 |
t
|
|
371 |
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrück'); |
|
372 |
t collation(t)
|
|
373 |
SET NAMES latin1;
|
|
374 |
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrück'); |
|
375 |
t collation(t)
|
|
376 |
aus Osnabrück utf8_general_ci
|
|
377 |
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck'); |
|
378 |
t collation(t)
|
|
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;
|
|
383 |
Warnings:
|
|
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'); |
|
389 |
t collation(t)
|
|
390 |
aus Osnabrück latin1_german2_ci
|
|
391 |
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck'); |
|
392 |
t collation(t)
|
|
393 |
aus Osnabrück latin1_german2_ci
|
|
394 |
DROP TABLE t1;
|
|
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); |
|
398 |
s
|
|
399 |
pära para para
|
|
400 |
para para para
|
|
401 |
select * from t1 where match(s) against('par*' in boolean mode); |
|
402 |
s
|
|
403 |
pära para para
|
|
404 |
para para para
|
|
405 |
DROP TABLE t1;
|
|
406 |
CREATE TABLE t1 (h text, FULLTEXT (h));
|
|
407 |
INSERT INTO t1 VALUES ('Jesses Hasse Ling and his syncopators of Swing'); |
|
408 |
REPAIR TABLE t1;
|
|
409 |
Table Op Msg_type Msg_text
|
|
410 |
test.t1 repair status OK
|
|
411 |
select count(*) from t1;
|
|
412 |
count(*)
|
|
413 |
1
|
|
414 |
drop table 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') |
|
419 |
0
|
|
420 |
DROP TABLE t1;
|
|
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;
|
|
426 |
drop table t1, t2;
|
|
427 |
CREATE TABLE t1 (t VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci, FULLTEXT (t));
|
|
428 |
SET NAMES latin1;
|
|
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); |
|
431 |
COUNT(*)
|
|
432 |
1
|
|
433 |
DROP TABLE t1;
|
|
434 |
CREATE TABLE t1 (a VARCHAR(30), FULLTEXT(a));
|
|
435 |
INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); |
|
436 |
SET myisam_repair_threads=2;
|
|
437 |
REPAIR TABLE t1;
|
|
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); |
|
443 |
a
|
|
444 |
testword'' |
|
445 |
SELECT a FROM t1 WHERE MATCH a AGAINST('testword\'\'' IN BOOLEAN MODE); |
|
446 |
a
|
|
447 |
testword'' |
|
448 |
INSERT INTO t1 VALUES('test\'s'); |
|
449 |
SELECT a FROM t1 WHERE MATCH a AGAINST('test' IN BOOLEAN MODE); |
|
450 |
a
|
|
451 |
test's |
|
452 |
DROP TABLE t1;
|
|
453 |
CREATE TABLE t1 (a VARCHAR(10000), FULLTEXT(a));
|
|
454 |
SHOW CREATE TABLE t1;
|
|
455 |
Table Create Table
|
|
456 |
t1 CREATE TABLE `t1` (
|
|
457 |
`a` varchar(10000) DEFAULT NULL,
|
|
458 |
FULLTEXT KEY `a` (`a`)
|
|
459 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1
|
|
460 |
DROP TABLE t1;
|
|
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')"; |
|
464 |
EXECUTE stmt;
|
|
465 |
a FORMAT(MATCH(a) AGAINST('test1 test'),6) |
|
466 |
test1 0.685267
|
|
467 |
EXECUTE stmt;
|
|
468 |
a FORMAT(MATCH(a) AGAINST('test1 test'),6) |
|
469 |
test1 0.685267
|
|
470 |
DEALLOCATE PREPARE stmt;
|
|
471 |
DROP TABLE t1;
|
|
472 |
CREATE TABLE t1 (a VARCHAR(255), FULLTEXT(a));
|
|
473 |
SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test'); |
|
474 |
a
|
|
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 |
|
478 |
DROP TABLE t1; |
|
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); |
|
482 |
a
|
|
483 |
aaaaa aaaa |
|
484 |
DROP TABLE t1; |
|
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); |
|
488 |
a
|
|
489 |
City Of God |
|
490 |
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of*)' IN BOOLEAN MODE); |
|
491 |
a
|
|
492 |
City Of God |
|
493 |
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city* of*' IN BOOLEAN MODE); |
|
494 |
a
|
|
495 |
City Of God |
|
496 |
DROP TABLE t1; |