MySQL 5.5 will lose trailing spaces in a query

Question

I am creating a database in which the final space is important for the results. When I request the result, I find that

SELECT * where `field` = 'a ' 

Returns the result when there is a field whose value is equal to 'a'. I want the final space to matter in the result set. I tried using Char, Varchar, Text and Blob. I note that this field is the index of my table.

Can someone show me how to query the query in such a way as to count spaces (and / or leading)? Do I need to format the table in any special way to make this work?

Answer 1

This design behavior is not unique to MySQL.

You can get around it in comparison with BINARY :

 mysql> select version(), 'a' = 'a ', BINARY 'a' = BINARY 'a '; +-------------+------------+--------------------------+ | version() | 'a' = 'a ' | BINARY 'a' = BINARY 'a ' | +-------------+------------+--------------------------+ | 5.5.25a-log | 1 | 0 | +-------------+------------+--------------------------+ 1 row in set (0.00 sec) 

but not much more. This will help you with SELECT if spaces appear, for example. when a user enters a search; but if you want to actually enter information conveyed as spaces, this will be a problem (you cannot have an index with "a" and "a").

see also

Tracing spaces in varchar should be considered in comparison

You could possibly change the rows in this column and undo them back when they are displayed. Of course, this will ruin any ordering based on this column, but if you only check for the existence of equality or substring, it might work. Leading spaces are considered.

To search for equality, you can also preserve the base64 encoding of the string, which must support lexicographic order (i.e. the order between a and b must be maintained between base64 (a) and base64 (b)). Or you can add a terminator to the string ("\ n" may succeed and not appear in the search results).

Finally, but it is dangerous because people cannot tell the difference, you can replace the spaces with UTF8 char (49824):

 mysql> select concat ('\'a', char(49824),'\'') AS tricked, concat ('\'a', ' ' ,'\'') as honest, concat ('\'a', char(49824),'\'') = concat ('\'a', ' ' ,'\'') as equals; +---------+--------+--------+ | tricked | honest | equals | +---------+--------+--------+ | 'a ' | 'a ' | 0 | +---------+--------+--------+ 1 row in set (0.00 sec) 

The lines seem to be equal, but it is not. Note that in HTML, space is space, and 49824 is   (inextricable space). This affects the functions that convert HTML and HTML, and nbsp is actually UTF8's code notation that the honest string is two bytes, but the length of the cheated string is actually three.

Finally, you can declare a VARBINARY column instead of VARCHAR , thus completely hiding what is happening. This seems like the easiest solution, but I'm afraid it might bite you in a few weeks or months.

Answer 2

I have had success doing the following, but I'm not sure if this is an unsustainable approach.

 CREATE TEMPORARY TABLE test ( PRIMARY KEY(id), id INT AUTO_INCREMENT, val VARCHAR(20) ); INSERT INTO test VALUES (NULL, 'a'), (NULL, 'a '), (NULL, 'a '), (NULL, 'a '); SELECT * FROM test WHERE val LIKE 'a '; 

Exit

 id val 2 'a ' 

Using WHERE val = 'a', select all the records without considering spaces, but LIKE works for me.