Trailing whitespace in varchar needs to be considered in comparison

Question

I am using a table with a varchar column. I did not realize that trailing whitespace is not considered in comparisons (and that, apparently, two values that differ only in amount of trailing whitespace will violate the uniqueness property, if specified).

I need to fix this in the table, preferably in place. Is there a recommended path to fixing a table like this in MySQL?

I am accessing the DB strictly through a program I control, so switching to a non-human readable format such as binary would be fine. But I am not sure how to do such a thing and don't want to destroy the table.

Answer 1

Could you run on empty query on the table field that has the whitespace?

RTRIM(str) 

Returns the string str with trailing space characters removed. mysql> SELECT RTRIM('barbar '); -> 'barbar' This function is multi-byte safe.