MySQL Full Text "Exact Phrase" that Actually Works without Relevancy false hits when no match is found

Question

PHP Version 5.8.8

I'm was trying to create a Case Insensitive "Exact Match" query that only returns exact match results with no relevancy leftovers like the results in Query 2 and 3 below.

Cut straight to the chase, Query 6 works!!

I created a table with some test data:

Tablename = test_boolean_mode, column name = fulltext (utf8_bin)

123 The Black Cat 456
The Black Cat
98 The Black Cat
The Black Cat 76
543 the 456
black cat
hgs cat
gres black jgfx
black 99

Query 1 (case sensitive):

SELECT *
FROM `test_boolean_mode`
WHERE (MATCH `fulltext` AGAINST ('The Black Cat' IN BOOLEAN MODE))

Returns:

123 The Black Cat 456
The Black Cat
98 The Black Cat
The Black Cat 76

Query 2 (results are what I call leftovers or false hits):

SELECT *
FROM `test_boolean_mode`
WHERE (MATCH `fulltext` AGAINST ('The black cat' IN BOOLEAN MODE))

Returns:

black cat
gres black jgfx
black 99

Query 3 (results are what I call leftovers or false hits)

SELECT *
FROM `test_boolean_mode`
WHERE (MATCH `fulltext` AGAINST ('the black cat' IN BOOLEAN MODE))

Returns:

black cat
gres black jgfx
black 99

Here are the tests with the LIKE Operator

Query 4 (Returns nothing which is better than binary mode however still case sensitive):

SELECT *
FROM `test_boolean_mode`
WHERE (`fulltext`  LIKE '%the black cat%')

Returns Nothing

Query 5 (This is correct but still case sensitive):

SELECT * 
FROM `test_boolean_mode`
WHERE (`fulltext`  LIKE '%The Black Cat%')

Returns:

123 The Black Cat 456
The Black Cat
98 The Black Cat
The Black Cat 76

Query 6 (This is correct and works perfect, returns nothinng if no match and is case insensitive!!!):

SELECT *
FROM `test_boolean_mode`
WHERE (`fulltext` COLLATE UTF8_GENERAL_CI LIKE '%the black cat%')

Returns:

123 The Black Cat 456
The Black Cat
98 The Black Cat
The Black Cat 76

Answer 1

I meant to post this as information not a question but could not find a place to do so. Since I spend so much time figuring this out I thought it would be useful to someone out there and could not find the answer anywhere.