guoxin
/
test-example-projects
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
用于EagleEye3.0 规则集漏报和误报测试的示例项目，项目收集于github和gitee
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
3 Commits
1 Branch
0 Tags
722 MiB
Tag: Branch: Tree: master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
test-example-projects/c++_projects/280w-mysql-8.0.18/mysql-test/r/fulltext.result

862 lines
36 KiB
Raw Permalink Normal View History Unescape

first commit
5 months ago
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;