drop table if exists test_innodb, test; CREATE TABLE `test_innodb` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `str` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_general_cs', PRIMARY KEY (`id`), FULLTEXT INDEX `str` (`str`) ) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB; INSERT INTO test_innodb(str) VALUES ('abcd'),('ABCD'); SELECT * FROM test_innodb where match(str) AGAINST ('abcd' IN BOOLEAN MODE); id str 1 abcd SELECT * FROM test_innodb where match(str) AGAINST ('ABCD' IN BOOLEAN MODE); id str 2 ABCD SELECT * FROM test_innodb where match(str) AGAINST ('abcd' IN NATURAL LANGUAGE MODE); id str 1 abcd SELECT * FROM test_innodb where match(str) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE); id str 2 ABCD alter table test_innodb modify `str` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_general_ci'; SELECT * FROM test_innodb where match(str) AGAINST ('abcd' IN BOOLEAN MODE); id str 1 abcd 2 ABCD SELECT * FROM test_innodb where match(str) AGAINST ('ABCD' IN BOOLEAN MODE); id str 1 abcd 2 ABCD SELECT * FROM test_innodb where match(str) AGAINST ('abcd' IN NATURAL LANGUAGE MODE); id str 1 abcd 2 ABCD SELECT * FROM test_innodb where match(str) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE); id str 1 abcd 2 ABCD alter table test_innodb modify `str` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_bin'; SELECT * FROM test_innodb where match(str) AGAINST ('abcd' IN BOOLEAN MODE); id str 1 abcd SELECT * FROM test_innodb where match(str) AGAINST ('ABCD' IN BOOLEAN MODE); id str 2 ABCD SELECT * FROM test_innodb where match(str) AGAINST ('abcd' IN NATURAL LANGUAGE MODE); id str 1 abcd SELECT * FROM test_innodb where match(str) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE); id str 2 ABCD CREATE TABLE `test` ( `seq` int(11) NOT NULL AUTO_INCREMENT, `cont` text COLLATE utf8mb4_bin, PRIMARY KEY (`seq`), FULLTEXT KEY `fx_txts` (`cont`) /*!50100 WITH PARSER `ngram` */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; insert into test (cont) values ('aBc!efg'); insert into test (cont) values ('aBc@efg'); insert into test (cont) values ('aBc#efg'); insert into test (cont) values ('aBc$efg'); insert into test (cont) values ('aBc%efg'); insert into test (cont) values ('abc^efg'); insert into test (cont) values ('abc&efg'); insert into test (cont) values ('abc*efg'); insert into test (cont) values ('abc(efg'); insert into test (cont) values ('abc)efg'); select * from test where match(cont) against ('Bc' in boolean mode); seq cont 1 aBc!efg 2 aBc@efg 3 aBc#efg 4 aBc$efg 5 aBc%efg select * from test where match(cont) against ('bc' in boolean mode); seq cont 6 abc^efg 7 abc&efg 8 abc*efg 9 abc(efg 10 abc)efg select * from test where match(cont) against ('Bc' in natural language mode); seq cont 1 aBc!efg 2 aBc@efg 3 aBc#efg 4 aBc$efg 5 aBc%efg select * from test where match(cont) against ('bc' in natural language mode); seq cont 6 abc^efg 7 abc&efg 8 abc*efg 9 abc(efg 10 abc)efg alter table test modify `cont` text COLLATE utf8mb4_general_ci; select * from test where match(cont) against ('Bc' in boolean mode); seq cont 1 aBc!efg 2 aBc@efg 3 aBc#efg 4 aBc$efg 5 aBc%efg 6 abc^efg 7 abc&efg 8 abc*efg 9 abc(efg 10 abc)efg select * from test where match(cont) against ('bc' in boolean mode); seq cont 1 aBc!efg 2 aBc@efg 3 aBc#efg 4 aBc$efg 5 aBc%efg 6 abc^efg 7 abc&efg 8 abc*efg 9 abc(efg 10 abc)efg select * from test where match(cont) against ('Bc' in natural language mode); seq cont 1 aBc!efg 2 aBc@efg 3 aBc#efg 4 aBc$efg 5 aBc%efg 6 abc^efg 7 abc&efg 8 abc*efg 9 abc(efg 10 abc)efg select * from test where match(cont) against ('bc' in natural language mode); seq cont 1 aBc!efg 2 aBc@efg 3 aBc#efg 4 aBc$efg 5 aBc%efg 6 abc^efg 7 abc&efg 8 abc*efg 9 abc(efg 10 abc)efg alter table test modify `cont` text COLLATE utf8mb4_bin; select * from test where match(cont) against ('Bc' in boolean mode); seq cont 1 aBc!efg 2 aBc@efg 3 aBc#efg 4 aBc$efg 5 aBc%efg select * from test where match(cont) against ('bc' in boolean mode); seq cont 6 abc^efg 7 abc&efg 8 abc*efg 9 abc(efg 10 abc)efg select * from test where match(cont) against ('Bc' in natural language mode); seq cont 1 aBc!efg 2 aBc@efg 3 aBc#efg 4 aBc$efg 5 aBc%efg select * from test where match(cont) against ('bc' in natural language mode); seq cont 6 abc^efg 7 abc&efg 8 abc*efg 9 abc(efg 10 abc)efg drop table test_innodb; drop table test;