Comparison with trailing spaces in MySQL

Question

This SQL query:

select c1 from table where c1='';

returns rows that have c1=' ' (one empty space) in MySQL.

Is this intended or a bug?

EDIT: please check SQL Fiddle link here, and the number of spaces in SELECT query doesn't matter.

Answer 1

It's all stated there in the documentation. I've quoted the important points here. But I would suggest to go through the full documentation

VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL.

On the other hand, CHAR values are padded when they are stored but trailing spaces are ignored when retrieved.

All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL 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.

Explanation: Trailing spaces are ignored while comparing strings using comparison operator ('='). But trailing spaces are significant for LIKE (pattern matching operator)

Answer 2

This is documented behaviour.

The MySQL documentation for LIKE mentions

trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator:

SQL Server works the same way.