CODEWITHSUNDEEP
mysqlsql-like
user3783243
user3783243

Reputation: 5223

SQL Like vs. = Function Differently Without Wildcards

I have a query where changing from like to = results in a match. My understanding was the = and like functioned the same unless wildcards were present. Neither _ nor % are in my string. Characters present in my string are:

acdeijknoprtuy4@-.

My query was:

SELECT * FROM UserEmails WHERE email like ?

which returned 0 results, I changed it to:

SELECT * FROM UserEmails WHERE email = ?

and I got the 1 expected result returned. This is running on percona version of mysql 5.6.41.

Upvotes: 1

Views: 57

Answers (1)

slaakso
slaakso

Reputation: 9080

Your string, or matching database value, most likely have trailing spaces.

See documentation for MySQL 5.6:

All MySQL collations are of type PAD SPACE. This means that all CHAR, VARCHAR, and TEXT values are compared without regard to any trailing spaces. “Comparison” in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant.

In MySQL 8.0:

Most MySQL collations have a pad attribute of PAD SPACE. The exceptions are Unicode collations based on UCA 9.0.0 and higher, which have a pad attribute of NO PAD.

Upvotes: 1

Related Questions