How to string-compare for a single space

Question

I want to check whether a column has any values that are a single space character.

I initially thought that

WHERE my_column = ' '

would be sensible. But no. That will also match columns which have multiple spaces for some reason:

SELECT '    ' = ' '           => true

So I can use a regular express or hex encoding to test:

WHERE HEX(my_column) = '20'
WHERE my_column REGEXP '^\ $'

Both work. But I suspect both (certainly the latter) will be quite inefficient.

Is there a better way?

Answer 1

A BINARY comparison of the two strings is required for an exact match

Under normal circumstances, trailing whitespace is not regarded in the comparison, but the BINARY operator forces it to be:

BINARY also causes trailing spaces to be significant.

mysql> SELECT BINARY '   ' = ' ';
+--------------------+
| BINARY '   ' = ' ' |
+--------------------+
|                  0 |
+--------------------+

Incidentally, it isn't just whitespace-only comparisons that are affected by the trailing whitespace issue:

mysql> SELECT 'abc   ' = 'abc';
+------------------+
| 'abc   ' = 'abc' |
+------------------+
|                1 |
+------------------+

...but...

mysql> SELECT BINARY 'abc   ' = 'abc';
+-------------------------+
| BINARY 'abc   ' = 'abc' |
+-------------------------+
|                       0 |
+-------------------------+

...and even more confusingly, leading whitespace is significant:

mysql> SELECT ' abc   ' = 'abc';
+-------------------+
| ' abc   ' = 'abc' |
+-------------------+
|                 0 |
+-------------------+

Regarding indexing:

BINARY will prevent an index from being used on the character column. However, a note on the docs suggests that the index will be used if the BINARY operator is applied to the string literal side of the comparison as in:

SELECT * FROM `tbl` WHERE `col` = BINARY 'string   '

Answer 2

where my_column = ' ' and LENGTH(my_column) = 1