mysql varchar varbinary_mysql varbinary vs varchar

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?

解决方案

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.