|
|
drop table if exists t1,t2,t3;
|
|
|
CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) charset utf8mb4;
|
|
|
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');
|
|
|
ANALYZE TABLE t1;
|
|
|
Table Op Msg_type Msg_text
|
|
|
test.t1 analyze status OK
|
|
|
SHOW INDEX FROM t1;
|
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
|
|
|
t1 1 a 1 a NULL 5 NULL NULL YES FULLTEXT YES NULL
|
|
|
t1 1 a 2 b NULL 5 NULL NULL YES FULLTEXT YES NULL
|
|
|
EXPLAIN FORMAT=tree select * from t1 where MATCH(a,b) AGAINST ("collections");
|
|
|
EXPLAIN
|
|
|
-> Filter: (match t1.a,t1.b against ('collections')) (cost=0.35 rows=1)
|
|
|
-> Indexed full text search on t1 using a (a='collections') (cost=0.35 rows=1)
|
|
|
|
|
|
select * from t1 where MATCH(a,b) AGAINST ("collections");
|
|
|
a b
|
|
|
Only MyISAM tables support collections
|
|
|
Full-text indexes are called collections
|
|
|
explain select * from t1 where MATCH(a,b) AGAINST ("collections");
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL fulltext a a 0 const 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('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 ("only");
|
|
|
a b
|
|
|
select * from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
|
|
|
a b
|
|
|
Only MyISAM tables support collections
|
|
|
Full-text indexes are called collections
|
|
|
MySQL has now support for full-text search
|
|
|
select * from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION);
|
|
|
a b
|
|
|
Full-text indexes are called collections
|
|
|
Only MyISAM tables support collections
|
|
|
select * from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION);
|
|
|
a b
|
|
|
Full-text indexes are called collections
|
|
|
Only MyISAM tables support collections
|
|
|
MySQL has now support for full-text search
|
|
|
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE);
|
|
|
a b
|
|
|
Full-text indexes are called collections
|
|
|
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
|
|
|
a b
|
|
|
Full-text indexes are called collections
|
|
|
Only MyISAM tables support collections
|
|
|
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN BOOLEAN MODE WITH QUERY EXPANSION);
|
|
|
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 'WITH QUERY EXPANSION)' at line 1
|
|
|
explain select * from t1 where MATCH(a,b) AGAINST ("collections");
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL fulltext a a 0 const 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections'))
|
|
|
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL fulltext a a 0 const 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')) > 0)
|
|
|
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>1;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL fulltext a a 0 const 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')) > 1)
|
|
|
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=0;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')) >= 0)
|
|
|
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=1;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL fulltext a a 0 const 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')) >= 1)
|
|
|
explain select * from t1 where 0<MATCH(a,b) AGAINST ("collections");
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL fulltext a a 0 const 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (0 < (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')))
|
|
|
explain select * from t1 where 1<MATCH(a,b) AGAINST ("collections");
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL fulltext a a 0 const 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (1 < (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')))
|
|
|
explain select * from t1 where 0<=MATCH(a,b) AGAINST ("collections");
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (0 <= (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')))
|
|
|
explain select * from t1 where 1<=MATCH(a,b) AGAINST ("collections");
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL fulltext a a 0 const 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (1 <= (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')))
|
|
|
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0 and a like '%ll%';
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL fulltext a a 0 const 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')) > 0) and (`test`.`t1`.`a` like '%ll%'))
|
|
|
select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE);
|
|
|
a b
|
|
|
MySQL has now support for full-text search
|
|
|
explain select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE);
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL fulltext a a 0 const 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ 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))
|
|
|
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
|
|
|
select *, MATCH(a,b) AGAINST("collections support" 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 * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
|
|
|
a b
|
|
|
Full-text indexes are called collections
|
|
|
select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
|
|
|
a b
|
|
|
select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
|
|
|
a b
|
|
|
MySQL has now support for full-text search
|
|
|
select * from t1 where MATCH a,b AGAINST ('"now support"' IN BOOLEAN MODE);
|
|
|
a b
|
|
|
MySQL has now support for full-text search
|
|
|
select * from t1 where MATCH a,b AGAINST ('"text search" "now support"' 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 ('"text search" -"now support"' IN BOOLEAN MODE);
|
|
|
a b
|
|
|
Full-text search in MySQL implements vector space model
|
|
|
select * from t1 where MATCH a,b AGAINST ('"text search" +"now support"' IN BOOLEAN MODE);
|
|
|
a b
|
|
|
MySQL has now support for full-text search
|
|
|
select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE);
|
|
|
a b
|
|
|
select * from t1 where MATCH a,b AGAINST ('"xt indexes"' IN BOOLEAN MODE);
|
|
|
a b
|
|
|
select * from t1 where MATCH a,b AGAINST ('+(support collections) +foobar*' IN BOOLEAN MODE);
|
|
|
a b
|
|
|
select * from t1 where MATCH a,b AGAINST ('+(+(support collections)) +foobar*' IN BOOLEAN MODE);
|
|
|
a b
|
|
|
select * from t1 where MATCH a,b AGAINST ('+collections -supp* -foobar*' IN BOOLEAN MODE);
|
|
|
a b
|
|
|
Full-text indexes are called collections
|
|
|
select * from t1 where MATCH a,b AGAINST('"space model' IN BOOLEAN MODE);
|
|
|
a b
|
|
|
Full-text search in MySQL implements vector space model
|
|
|
select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE);
|
|
|
a b
|
|
|
Full-text search in MySQL implements vector space model
|
|
|
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
|
|
|
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
|
|
|
delete from t1 where a like "MySQL%";
|
|
|
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
|
|
|
drop table t1;
|
|
|
create table t1 (a varchar(200) not null, fulltext (a));
|
|
|
insert t1 values ("aaa10 bbb20"), ("aaa20 bbb15"), ("aaa30 bbb10");
|
|
|
select * from t1 where match a against ("+aaa* +bbb*" in boolean mode);
|
|
|
a
|
|
|
aaa30 bbb10
|
|
|
aaa20 bbb15
|
|
|
aaa10 bbb20
|
|
|
select * from t1 where match a against ("+aaa* +bbb1*" in boolean mode);
|
|
|
a
|
|
|
aaa30 bbb10
|
|
|
aaa20 bbb15
|
|
|
select * from t1 where match a against ("+aaa* +ccc*" in boolean mode);
|
|
|
a
|
|
|
select * from t1 where match a against ("+aaa10 +(bbb*)" in boolean mode);
|
|
|
a
|
|
|
aaa10 bbb20
|
|
|
select * from t1 where match a against ("+(+aaa* +bbb1*)" in boolean mode);
|
|
|
a
|
|
|
aaa30 bbb10
|
|
|
aaa20 bbb15
|
|
|
select * from t1 where match a against ("(+aaa* +bbb1*)" in boolean mode);
|
|
|
a
|
|
|
aaa30 bbb10
|
|
|
aaa20 bbb15
|
|
|
drop table t1;
|
|
|
CREATE TABLE t1 (
|
|
|
id int(11),
|
|
|
ticket int(11),
|
|
|
KEY ti (id),
|
|
|
KEY tit (ticket)
|
|
|
) charset utf8mb4;
|
|
|
Warnings:
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
INSERT INTO t1 VALUES (2,3),(1,2);
|
|
|
CREATE TABLE t2 (
|
|
|
ticket int(11),
|
|
|
inhalt text,
|
|
|
KEY tig (ticket),
|
|
|
fulltext index tix (inhalt)
|
|
|
) charset utf8mb4;
|
|
|
Warnings:
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
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 ticket2.id FROM t2 as ttxt,t2 INNER JOIN t1 as ticket2 ON
|
|
|
ticket2.id = t2.ticket
|
|
|
WHERE ticket2.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar');
|
|
|
id
|
|
|
INSERT INTO t1 VALUES (3,3);
|
|
|
select ticket2.id FROM t2 as ttxt,t2
|
|
|
INNER JOIN t1 as ticket2 ON ticket2.id = t2.ticket
|
|
|
WHERE ticket2.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 Null Index_type Comment Index_comment Visible Expression
|
|
|
t2 1 tig 1 ticket A NULL NULL NULL YES BTREE YES NULL
|
|
|
t2 1 tix 1 inhalt NULL NULL NULL NULL YES FULLTEXT YES NULL
|
|
|
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`)
|
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
|
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 (t int(11),i text,fulltext tix (t,i));
|
|
|
ERROR HY000: Column 't' cannot be part of FULLTEXT index
|
|
|
CREATE TABLE t3 (t int(11),i text,
|
|
|
j varchar(200) CHARACTER SET latin2,
|
|
|
fulltext tix (i,j));
|
|
|
ERROR HY000: Column 'j' cannot be part of FULLTEXT index
|
|
|
CREATE TABLE t3 (
|
|
|
ticket int(11),
|
|
|
inhalt text,
|
|
|
KEY tig (ticket),
|
|
|
fulltext index tix (inhalt)
|
|
|
);
|
|
|
Warnings:
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
select * from t2 where MATCH inhalt AGAINST (t2.inhalt);
|
|
|
ERROR HY000: Incorrect arguments to AGAINST
|
|
|
select * from t2 where MATCH ticket AGAINST ('foobar');
|
|
|
ERROR HY000: Can't find FULLTEXT index matching the column list
|
|
|
select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar');
|
|
|
ERROR HY000: Incorrect 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)
|
|
|
) ENGINE=MyISAM;
|
|
|
Warnings:
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
CREATE FULLTEXT INDEX ft1 ON t1(title);
|
|
|
insert into t1 (title) values ('this is a test');
|
|
|
select * from t1 where match title against ('test' in boolean mode);
|
|
|
id title
|
|
|
1 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
|
|
|
update t1 set title=NULL where id=1;
|
|
|
drop table t1;
|
|
|
CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) ENGINE=MyISAM;
|
|
|
Warnings:
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
insert into t1 values (1,"I wonder why the fulltext index doesnt work?");
|
|
|
SELECT * from t1 where MATCH (b) AGAINST ('apples');
|
|
|
a b
|
|
|
insert into t1 values (2,"fullaaa fullzzz");
|
|
|
select * from t1 where match b against ('full*' in boolean mode);
|
|
|
a b
|
|
|
2 fullaaa fullzzz
|
|
|
1 I wonder why the fulltext index doesnt work?
|
|
|
drop table t1;
|
|
|
CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) ENGINE=MyISAM;
|
|
|
Warnings:
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so funny'),(4,'MySQL Tutorial');
|
|
|
select 8 from t1;
|
|
|
8
|
|
|
8
|
|
|
8
|
|
|
8
|
|
|
8
|
|
|
drop table t1;
|
|
|
create table t1 (a text, fulltext key (a));
|
|
|
insert into t1 values ('aaaa');
|
|
|
repair table t1;
|
|
|
Table Op Msg_type Msg_text
|
|
|
test.t1 repair status OK
|
|
|
select * from t1 where match (a) against ('aaaa');
|
|
|
a
|
|
|
drop table t1;
|
|
|
create table t1 ( ref_mag text not null, fulltext (ref_mag));
|
|
|
insert into t1 values ('test');
|
|
|
select ref_mag from t1 where match ref_mag against ('+test' in boolean mode);
|
|
|
ref_mag
|
|
|
test
|
|
|
alter table t1 change ref_mag ref_mag char (255) not null;
|
|
|
select ref_mag from t1 where match ref_mag against ('+test' in boolean mode);
|
|
|
ref_mag
|
|
|
test
|
|
|
drop table t1;
|
|
|
create table t1 (t1_id int(11) primary key, name varchar(32));
|
|
|
Warnings:
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
insert into t1 values (1, 'data1');
|
|
|
insert into t1 values (2, 'data2');
|
|
|
create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32));
|
|
|
Warnings:
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
insert into t2 values (1, 1, 'xxfoo');
|
|
|
insert into t2 values (2, 1, 'xxbar');
|
|
|
insert into t2 values (3, 1, 'xxbuz');
|
|
|
select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode);
|
|
|
t1_id name t2_id name
|
|
|
1 data1 1 xxfoo
|
|
|
select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode);
|
|
|
t2_id t1_id name
|
|
|
drop table t1,t2;
|
|
|
create table t1 (a text, fulltext key (a));
|
|
|
insert into t1 select "xxxx yyyy zzzz";
|
|
|
drop table t1;
|
|
|
SET NAMES latin1;
|
|
|
CREATE TABLE t1 (t text character set utf8 not null, fulltext(t));
|
|
|
Warnings:
|
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
|
INSERT t1 VALUES ('Mit freundlichem Gr<EFBFBD><EFBFBD>'), ('aus Osnabr<EFBFBD>ck');
|
|
|
SET NAMES koi8r;
|
|
|
INSERT t1 VALUES ("<EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD> - <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>"),("<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>, <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>!"),
|
|
|
("<EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>, <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>!"),("<EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD>!");
|
|
|
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>');
|
|
|
t collation(t)
|
|
|
<EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD> - <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> utf8_general_ci
|
|
|
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('<EFBFBD><EFBFBD><EFBFBD>*' IN BOOLEAN MODE);
|
|
|
t collation(t)
|
|
|
<EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD>! utf8_general_ci
|
|
|
SELECT * FROM t1 WHERE MATCH t AGAINST ('<EFBFBD><EFBFBD><EFBFBD>' IN BOOLEAN MODE);
|
|
|
t
|
|
|
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabr<EFBFBD>ck');
|
|
|
t collation(t)
|
|
|
SET NAMES latin1;
|
|
|
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabr<EFBFBD>ck');
|
|
|
t collation(t)
|
|
|
aus Osnabr<EFBFBD>ck utf8_general_ci
|
|
|
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
|
|
|
t collation(t)
|
|
|
SELECT t, collation(t),FORMAT(MATCH t AGAINST ('Osnabruck'),6) FROM t1 WHERE MATCH t AGAINST ('Osnabruck');
|
|
|
t collation(t) FORMAT(MATCH t AGAINST ('Osnabruck'),6)
|
|
|
aus Osnabr<EFBFBD>ck utf8_general_ci 1.591140
|
|
|
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
|
|
|
alter table t1 modify t varchar(200) collate latin1_german2_ci not null;
|
|
|
Warnings:
|
|
|
Warning 1366 Incorrect string value: '\xD0\xAD\xD1\x82\xD0\xBE...' for column 't' at row 3
|
|
|
Warning 1366 Incorrect string value: '\xD0\x9E\xD1\x82\xD0\xBB...' for column 't' at row 4
|
|
|
Warning 1366 Incorrect string value: '\xD0\x9D\xD0\xB5 \xD0...' for column 't' at row 5
|
|
|
Warning 1366 Incorrect string value: '\xD0\xB8 \xD0\xB1\xD1...' for column 't' at row 6
|
|
|
SET sql_mode = default;
|
|
|
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabr<EFBFBD>ck');
|
|
|
t collation(t)
|
|
|
aus Osnabr<EFBFBD>ck latin1_german2_ci
|
|
|
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
|
|
|
t collation(t)
|
|
|
aus Osnabr<EFBFBD>ck latin1_german2_ci
|
|
|
DROP TABLE t1;
|
|
|
CREATE TABLE t1 (s varchar(255), FULLTEXT (s)) DEFAULT CHARSET=utf8;
|
|
|
Warnings:
|
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
|
insert into t1 (s) values ('p<EFBFBD>ra para para'),('para para para');
|
|
|
select * from t1 where match(s) against('para' in boolean mode);
|
|
|
s
|
|
|
p<EFBFBD>ra para para
|
|
|
para para para
|
|
|
select * from t1 where match(s) against('par*' in boolean mode);
|
|
|
s
|
|
|
p<EFBFBD>ra para para
|
|
|
para para para
|
|
|
DROP TABLE t1;
|
|
|
CREATE TABLE t1 (h text, FULLTEXT (h));
|
|
|
INSERT INTO t1 VALUES ('Jesses Hasse Ling and his syncopators of Swing');
|
|
|
REPAIR TABLE t1;
|
|
|
Table Op Msg_type Msg_text
|
|
|
test.t1 repair status OK
|
|
|
select count(*) from t1;
|
|
|
count(*)
|
|
|
1
|
|
|
drop table t1;
|
|
|
CREATE TABLE t1 ( a TEXT, FULLTEXT (a) );
|
|
|
INSERT INTO t1 VALUES ('testing ft_nlq_find_relevance');
|
|
|
SELECT MATCH(a) AGAINST ('nosuchword') FROM t1;
|
|
|
MATCH(a) AGAINST ('nosuchword')
|
|
|
0
|
|
|
DROP TABLE t1;
|
|
|
create table t1 (a int primary key, b text, fulltext(b));
|
|
|
create table t2 (a int, b text);
|
|
|
insert t1 values (1, "aaaa"), (2, "bbbb");
|
|
|
insert t2 values (10, "aaaa"), (2, "cccc");
|
|
|
replace t1 select * from t2;
|
|
|
drop table t1, t2;
|
|
|
CREATE TABLE t1 (t VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci, FULLTEXT (t));
|
|
|
Warnings:
|
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
|
Warning 3778 'utf8_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
|
|
|
SET NAMES latin1;
|
|
|
INSERT INTO t1 VALUES('Mit freundlichem Gr<EFBFBD><EFBFBD> aus Osnabr<EFBFBD>ck');
|
|
|
SELECT COUNT(*) FROM t1 WHERE MATCH(t) AGAINST ('"osnabr<EFBFBD>ck"' IN BOOLEAN MODE);
|
|
|
COUNT(*)
|
|
|
1
|
|
|
DROP TABLE t1;
|
|
|
CREATE TABLE t1 (a VARCHAR(30), FULLTEXT(a));
|
|
|
INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
|
|
|
SET myisam_repair_threads=2;
|
|
|
REPAIR TABLE t1;
|
|
|
Table Op Msg_type Msg_text
|
|
|
test.t1 repair status OK
|
|
|
SET myisam_repair_threads=@@global.myisam_repair_threads;
|
|
|
INSERT INTO t1 VALUES('testword\'\'');
|
|
|
SELECT a FROM t1 WHERE MATCH a AGAINST('testword' IN BOOLEAN MODE);
|
|
|
a
|
|
|
testword''
|
|
|
SELECT a FROM t1 WHERE MATCH a AGAINST('testword\'\'' IN BOOLEAN MODE);
|
|
|
a
|
|
|
testword''
|
|
|
INSERT INTO t1 VALUES('test\'s');
|
|
|
SELECT a FROM t1 WHERE MATCH a AGAINST('test' IN BOOLEAN MODE);
|
|
|
a
|
|
|
test's
|
|
|
DROP TABLE t1;
|
|
|
CREATE TABLE t1 (a VARCHAR(10000), FULLTEXT(a)) charset utf8mb4;
|
|
|
SHOW CREATE TABLE t1;
|
|
|
Table Create Table
|
|
|
t1 CREATE TABLE `t1` (
|
|
|
`a` varchar(10000) DEFAULT NULL,
|
|
|
FULLTEXT KEY `a` (`a`)
|
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
|
DROP TABLE t1;
|
|
|
CREATE TABLE t1 (a TEXT, FULLTEXT KEY(a));
|
|
|
INSERT INTO t1 VALUES('test'),('test1'),('test');
|
|
|
PREPARE stmt from "SELECT a, FORMAT(MATCH(a) AGAINST('test1 test'),6) FROM t1 WHERE MATCH(a) AGAINST('test1 test')";
|
|
|
EXECUTE stmt;
|
|
|
a FORMAT(MATCH(a) AGAINST('test1 test'),6)
|
|
|
test1 0.685267
|
|
|
EXECUTE stmt;
|
|
|
a FORMAT(MATCH(a) AGAINST('test1 test'),6)
|
|
|
test1 0.685267
|
|
|
DEALLOCATE PREPARE stmt;
|
|
|
DROP TABLE t1;
|
|
|
CREATE TABLE t1 (a VARCHAR(255), FULLTEXT(a));
|
|
|
SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test');
|
|
|
a
|
|
|
ALTER TABLE t1 DISABLE KEYS;
|
|
|
SELECT * FROM t1 WHERE MATCH(a) AGAINST('test');
|
|
|
ERROR HY000: Can't find FULLTEXT index matching the column list
|
|
|
DROP TABLE t1;
|
|
|
CREATE TABLE t1(a TEXT);
|
|
|
INSERT INTO t1 VALUES(' aaaaa aaaa');
|
|
|
SELECT * FROM t1 WHERE MATCH(a) AGAINST ('"aaaa"' IN BOOLEAN MODE);
|
|
|
a
|
|
|
aaaaa aaaa
|
|
|
DROP TABLE t1;
|
|
|
CREATE TABLE t1(a VARCHAR(20), FULLTEXT(a));
|
|
|
INSERT INTO t1 VALUES('Offside'),('City Of God');
|
|
|
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city of*' IN BOOLEAN MODE);
|
|
|
a
|
|
|
City Of God
|
|
|
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of*)' IN BOOLEAN MODE);
|
|
|
a
|
|
|
City Of God
|
|
|
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city* of*' IN BOOLEAN MODE);
|
|
|
a
|
|
|
City Of God
|
|
|
DROP TABLE t1;
|
|
|
create table t1(a text,b date,fulltext index(a))engine=myisam;
|
|
|
insert into t1 set a='water',b='2008-08-04';
|
|
|
select 1 from t1 where match(a) against ('water' in boolean mode) and b>='2008-08-01';
|
|
|
1
|
|
|
1
|
|
|
drop table t1;
|
|
|
show warnings;
|
|
|
Level Code Message
|
|
|
CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b));
|
|
|
INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1),
|
|
|
('test', 1),('test', 2),('test', 3),('test', 4);
|
|
|
EXPLAIN SELECT * FROM t1
|
|
|
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL fulltext b,a a 0 const 1 50.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 1) and (match `test`.`t1`.`a` against ('test' in boolean mode)))
|
|
|
EXPLAIN SELECT * FROM t1 USE INDEX(a)
|
|
|
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL fulltext a a 0 const 1 12.50 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX (`a`) where ((`test`.`t1`.`b` = 1) and (match `test`.`t1`.`a` against ('test' in boolean mode)))
|
|
|
EXPLAIN SELECT * FROM t1 FORCE INDEX(a)
|
|
|
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL fulltext a a 0 const 1 12.50 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` FORCE INDEX (`a`) where ((`test`.`t1`.`b` = 1) and (match `test`.`t1`.`a` against ('test' in boolean mode)))
|
|
|
EXPLAIN SELECT * FROM t1 IGNORE INDEX(a)
|
|
|
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ref b b 5 const 4 12.50 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` IGNORE INDEX (`a`) where ((`test`.`t1`.`b` = 1) and (match `test`.`t1`.`a` against ('test' in boolean mode)))
|
|
|
EXPLAIN SELECT * FROM t1 USE INDEX(b)
|
|
|
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ref b b 5 const 4 12.50 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX (`b`) where ((`test`.`t1`.`b` = 1) and (match `test`.`t1`.`a` against ('test' in boolean mode)))
|
|
|
EXPLAIN SELECT * FROM t1 FORCE INDEX(b)
|
|
|
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ref b b 5 const 4 12.50 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` FORCE INDEX (`b`) where ((`test`.`t1`.`b` = 1) and (match `test`.`t1`.`a` against ('test' in boolean mode)))
|
|
|
DROP TABLE t1;
|
|
|
CREATE TABLE t1(a CHAR(10));
|
|
|
INSERT INTO t1 VALUES('aaa15');
|
|
|
SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1;
|
|
|
MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE)
|
|
|
1
|
|
|
SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) FROM t1;
|
|
|
MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE)
|
|
|
2
|
|
|
DROP TABLE t1;
|
|
|
CREATE TABLE t1(a TEXT);
|
|
|
SELECT GROUP_CONCAT(a) AS st FROM t1 HAVING MATCH(st) AGAINST('test' IN BOOLEAN MODE);
|
|
|
ERROR HY000: Incorrect arguments to MATCH
|
|
|
DROP TABLE t1;
|
|
|
CREATE TABLE t1(a VARCHAR(64), FULLTEXT(a));
|
|
|
INSERT INTO t1 VALUES('awrd bwrd cwrd'),('awrd bwrd cwrd'),('awrd bwrd cwrd');
|
|
|
SELECT * FROM t1 WHERE MATCH(a) AGAINST('+awrd bwrd* +cwrd*' IN BOOLEAN MODE);
|
|
|
a
|
|
|
awrd bwrd cwrd
|
|
|
awrd bwrd cwrd
|
|
|
awrd bwrd cwrd
|
|
|
DROP TABLE t1;
|
|
|
CREATE TABLE t1 (col text, FULLTEXT KEY full_text (col));
|
|
|
PREPARE s FROM
|
|
|
"SELECT MATCH (col) AGAINST('findme') FROM t1 ORDER BY MATCH (col) AGAINST('findme')"
|
|
|
;
|
|
|
EXECUTE s;
|
|
|
MATCH (col) AGAINST('findme')
|
|
|
DEALLOCATE PREPARE s;
|
|
|
DROP TABLE t1;
|
|
|
#
|
|
|
# Bug #49250 : spatial btree index corruption and crash
|
|
|
# Part two : fulltext syntax check
|
|
|
#
|
|
|
CREATE TABLE t1(col1 TEXT,
|
|
|
FULLTEXT INDEX USING BTREE (col1));
|
|
|
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 'USING BTREE (col1))' at line 2
|
|
|
CREATE TABLE t2(col1 TEXT);
|
|
|
CREATE FULLTEXT INDEX USING BTREE ON t2(col);
|
|
|
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 'USING BTREE ON t2(col)' at line 1
|
|
|
ALTER TABLE t2 ADD FULLTEXT INDEX USING BTREE (col1);
|
|
|
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 'USING BTREE (col1)' at line 1
|
|
|
DROP TABLE t2;
|
|
|
End of 5.0 tests
|
|
|
#
|
|
|
# Bug #47930: MATCH IN BOOLEAN MODE returns too many results
|
|
|
# inside subquery
|
|
|
#
|
|
|
CREATE TABLE t1 (a int);
|
|
|
INSERT INTO t1 VALUES (1), (2);
|
|
|
CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2));
|
|
|
INSERT INTO t2 VALUES (1,'Scargill');
|
|
|
CREATE TABLE t3 (a int, b int);
|
|
|
INSERT INTO t3 VALUES (1,1), (2,1);
|
|
|
# t2 should use full text index
|
|
|
EXPLAIN
|
|
|
SELECT count(*) FROM t1 WHERE
|
|
|
not exists(
|
|
|
SELECT 1 FROM t2, t3
|
|
|
WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
|
|
|
);
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Not exists
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
|
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` anti join (`test`.`t2` join `test`.`t3`) on(((`test`.`t3`.`a` = `test`.`t1`.`a`) and (match `test`.`t2`.`b2` against ('scargill' in boolean mode)))) where true
|
|
|
# should return 0
|
|
|
SELECT count(*) FROM t1 WHERE
|
|
|
not exists(
|
|
|
SELECT 1 FROM t2, t3
|
|
|
WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
|
|
|
);
|
|
|
count(*)
|
|
|
0
|
|
|
# should return 0
|
|
|
SELECT count(*) FROM t1 WHERE
|
|
|
not exists(
|
|
|
SELECT 1 FROM t2 IGNORE INDEX (b2), t3
|
|
|
WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
|
|
|
);
|
|
|
count(*)
|
|
|
0
|
|
|
DROP TABLE t1,t2,t3;
|
|
|
CREATE TABLE t1 (a VARCHAR(4), FULLTEXT(a));
|
|
|
INSERT INTO t1 VALUES
|
|
|
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
|
|
|
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
|
|
|
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
|
|
|
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
|
|
|
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
|
|
|
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
|
|
|
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
|
|
|
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
|
|
|
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
|
|
|
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
|
|
|
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
|
|
|
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
|
|
|
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('awrd'),('cwrd'),
|
|
|
('awrd');
|
|
|
SELECT COUNT(*) FROM t1 WHERE MATCH(a) AGAINST("+awrd bwrd* +cwrd*" IN BOOLEAN MODE);
|
|
|
COUNT(*)
|
|
|
0
|
|
|
DROP TABLE t1;
|
|
|
#
|
|
|
# Bug #49445: Assertion failed: 0, file .\item_row.cc, line 55 with
|
|
|
# fulltext search and row op
|
|
|
#
|
|
|
CREATE TABLE t1(a CHAR(1),FULLTEXT(a));
|
|
|
SELECT 1 FROM t1 WHERE MATCH(a) AGAINST ('') AND ROW(a,a) > ROW(1,1);
|
|
|
1
|
|
|
DROP TABLE t1;
|
|
|
#
|
|
|
# BUG#51866 - crash with repair by sort and fulltext keys
|
|
|
#
|
|
|
CREATE TABLE t1(a CHAR(4), FULLTEXT(a));
|
|
|
INSERT INTO t1 VALUES('aaaa');
|
|
|
SET myisam_sort_buffer_size=4;
|
|
|
Warnings:
|
|
|
Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '4'
|
|
|
REPAIR TABLE t1;
|
|
|
Table Op Msg_type Msg_text
|
|
|
test.t1 repair status OK
|
|
|
SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size;
|
|
|
DROP TABLE t1;
|
|
|
#
|
|
|
# Bug#54484 explain + prepared statement: crash and Got error -1 from storage engine
|
|
|
#
|
|
|
CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, FULLTEXT KEY(f1), UNIQUE(f1));
|
|
|
INSERT INTO t1 VALUES ('test');
|
|
|
SELECT 1 FROM t1 WHERE 1 >
|
|
|
ALL((SELECT 1 FROM t1 JOIN t1 a
|
|
|
ON (MATCH(t1.f1) against (""))
|
|
|
WHERE t1.f1 GROUP BY t1.f1)) xor f1;
|
|
|
1
|
|
|
1
|
|
|
PREPARE stmt FROM
|
|
|
'SELECT 1 FROM t1 WHERE 1 >
|
|
|
ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a
|
|
|
ON (MATCH(t1.f1) against (""))
|
|
|
WHERE t1.f1 GROUP BY t1.f1)) xor f1';
|
|
|
EXECUTE stmt;
|
|
|
1
|
|
|
1
|
|
|
EXECUTE stmt;
|
|
|
1
|
|
|
1
|
|
|
DEALLOCATE PREPARE stmt;
|
|
|
PREPARE stmt FROM
|
|
|
'SELECT 1 FROM t1 WHERE 1 >
|
|
|
ALL((SELECT 1 FROM t1 JOIN t1 a
|
|
|
ON (MATCH(t1.f1) against (""))
|
|
|
WHERE t1.f1 GROUP BY t1.f1))';
|
|
|
EXECUTE stmt;
|
|
|
1
|
|
|
1
|
|
|
EXECUTE stmt;
|
|
|
1
|
|
|
1
|
|
|
DEALLOCATE PREPARE stmt;
|
|
|
DROP TABLE t1;
|
|
|
End of 5.1 tests
|
|
|
# Bug#21140111: Explain ... match against: Assertion failed: ret ...
|
|
|
CREATE TABLE z(a INTEGER) engine=innodb;
|
|
|
CREATE TABLE q(b TEXT CHARSET latin1, fulltext(b)) engine=innodb;
|
|
|
explain format=json SELECT 1 FROM q
|
|
|
WHERE (SELECT MATCH(b) AGAINST ('*') FROM z);
|
|
|
ERROR HY000: Incorrect arguments to MATCH
|
|
|
DROP TABLE z, q;
|
|
|
#
|
|
|
# Bug#26271244: ASSERT FAILS WHEN SHOW CREATE TABLE CALLED ON A FILE
|
|
|
# CREATED FOR FULLTEXT INDEX
|
|
|
#
|
|
|
# Create Innodb table with full text index
|
|
|
CREATE TABLE t1(id INT, c CHAR(100) , FULLTEXT KEY `msg_idx` (`c`))
|
|
|
ENGINE INNODB;
|
|
|
CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2));
|
|
|
# Find name of first FTS index "table"
|
|
|
# SHOW CREATE TABLE on an FTS index table should give error
|
|
|
# (not crash). Query log and error message suppressed as the name of
|
|
|
# FTS index table is not stable.
|
|
|
Got one of the listed errors
|
|
|
# Msg_type Error ER_NO_SUCH_TABLE,ER_NO_SUCH_TABLE
|
|
|
# Msg_type Error ER_NO_SUCH_TABLE,ER_NO_SUCH_TABLE
|
|
|
Got one of the listed errors
|
|
|
Got one of the listed errors
|
|
|
Got one of the listed errors
|
|
|
Got one of the listed errors
|
|
|
Got one of the listed errors
|
|
|
DROP TABLE t1, t2;
|
|
|
#
|
|
|
# BUG#21625016: INNODB FULL TEXT CASE SENSITIVE NOT WORKING
|
|
|
#
|
|
|
CREATE TABLE t1(fld1 VARCHAR(10) COLLATE 'latin1_bin', FULLTEXT INDEX (fld1))
|
|
|
ENGINE=InnoDB;
|
|
|
INSERT INTO t1 VALUES ('abCD'),('ABCD');
|
|
|
# With the patch, case sensitive comparison is performed since
|
|
|
# binary collation is used.
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN BOOLEAN MODE);
|
|
|
fld1
|
|
|
abCD
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN BOOLEAN MODE);
|
|
|
fld1
|
|
|
ABCD
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN NATURAL LANGUAGE MODE);
|
|
|
fld1
|
|
|
abCD
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE);
|
|
|
fld1
|
|
|
ABCD
|
|
|
# Since binary collation is not used, case insensitive comparison is
|
|
|
# performed.
|
|
|
ALTER TABLE t1 MODIFY fld1 VARCHAR(10) COLLATE 'latin1_general_cs';
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN BOOLEAN MODE);
|
|
|
fld1
|
|
|
abCD
|
|
|
ABCD
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN BOOLEAN MODE);
|
|
|
fld1
|
|
|
abCD
|
|
|
ABCD
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN NATURAL LANGUAGE MODE);
|
|
|
fld1
|
|
|
abCD
|
|
|
ABCD
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE);
|
|
|
fld1
|
|
|
abCD
|
|
|
ABCD
|
|
|
# With the patch, case sensitive comparison is performed.
|
|
|
ALTER TABLE t1 MODIFY fld1 VARCHAR(10) COLLATE 'utf8mb4_bin';
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN BOOLEAN MODE);
|
|
|
fld1
|
|
|
abCD
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN BOOLEAN MODE);
|
|
|
fld1
|
|
|
ABCD
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN NATURAL LANGUAGE MODE);
|
|
|
fld1
|
|
|
abCD
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE);
|
|
|
fld1
|
|
|
ABCD
|
|
|
# Test(using case insensitive collation) added for coverage
|
|
|
ALTER TABLE t1 MODIFY fld1 VARCHAR(10) COLLATE 'utf8mb4_general_ci';
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN BOOLEAN MODE);
|
|
|
fld1
|
|
|
abCD
|
|
|
ABCD
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN BOOLEAN MODE);
|
|
|
fld1
|
|
|
abCD
|
|
|
ABCD
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN NATURAL LANGUAGE MODE);
|
|
|
fld1
|
|
|
abCD
|
|
|
ABCD
|
|
|
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE);
|
|
|
fld1
|
|
|
abCD
|
|
|
ABCD
|
|
|
DROP TABLE t1;
|
|
|
|
