character - mysql varbinary vs varchar

Question

We use varchar(255) for storing "keywords" in mysql. We are facing a problem that mysql ignores all trailing spaces for comparison purposes in "=". It does respect trailing spaces in "like" comparison, but it does not let us store same word with and without trailing spaces in a varchar column if it has a "UNIQUE" index over it.

So, we are considering switching to varbinary. Can anybody suggest what could be the implications when there are multi-byte characters in column values?

Answer 1

Andomar,

We use version 5.0.5. All mysql versions ignore trailing spaces for comparison. From the manual:

All MySQL collations are of type PADSPACE. This means that all CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces. This is true for all MySQL versions, and it makes no difference whether your version trims trailing spaces from VARCHAR values before storing them

Moreover mysql considers texts with/without trailing spaces duplicate in indexes:

For those cases where trailing pad characters are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting into the column values that differ only in number of trailing pad characters will result in a duplicate-key error. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error.

And, we absolutely need an index on keywords. So, I guess we have two options: varbinary or text. We shall evaluate the performance of "text", and multibyte functionality for varbinary.