MySQL different counts between "where =" and "where like"

Question

1. select count(*) from tableX where code = "XYZ";
2. select count(*) from tableX where code like "%XYZ";

Result for query 1 is 18734. <== Not Correct

Result for query 2 is 93003. <== Correct

We know that query 2's count is correct based on independent verification.

We expect these two queries to have the exact same count for each because we know that no rows in tableX have a code that ends with "XYZ", so the wildcard at the beginning shouldn't affect the query.

Why would these queries produce different counts?

We have already researched the differences between "=" comparison and "like" string comparison, but based on all our verification checks, we still don't understand why this would give us different counts

We have confirmed the following:

• There are no leading or trailing characters in the "code" field
• There are no hidden characters (tried all found here: How can I find non-ASCII characters in MySQL?)
• The collation is "utf8_unicode_ci"

We are using MySQL version 5.5.40-0ubuntu0.12.04.1.

Answer 1

Try this in order to get your answer:

SELECT code
FROM tableX 
WHERE code LIKE "%XYZ"
AND code <> "XYZ"
LIMIT 10

My guess is that some of your codes end with a lowercase xyz, and since LIKE is case-insensitive, it matched these where = did not.