MySQL: Always Trim Your Data Before Loading Into MySQL

Here’s a situation. There are 2 columns in a table which are identical. Identical as in having the same data type and collation (varchar(100) + utf8_unicode_ci) and having the same content.

For example, let’s say the table has 2 columns:
a | b
=========
abcd|abcd
xyza|xyza
1234|1234

The Question

How could “select a,b from tableName where a=b” return 0 results?

Was there anything wrong with the query? Nope. Doing a “select a,b from tableName where a!=b” verified that and i can see all 3 results. But why doesn’t a=b? I mean, you and I can see visually that they are both the same, right?

Note: Always Trim Your Data

After a couple of unsuccessful attempts, I went back to the script that does the data loading of my data and see if my data was trim before it was loaded into MySQL. And there it was, the culprit:

mysql_escape_string($data)

which should be better written as:

mysql_escape_string(trim($data))

Why?

Without the trim, $data could have contained whitespaces before or after the value which we are actually interested in storing. Trimming gets rid of these whitespaces and other crap so only the useful value we want is stored into the database. In php, you can use trim while I’m sure other programming languages has their own equivalent too.