mysql doesn't take accents into account validating uniqueness of index key

Question

I have a table with just a single field named "tag", which is a varchar(250)

the field is defined with collation 'latin1_spanish_ci', because I'm planning to save words in spanish...

the problem is that the following query

select * from test where tag = 'unó'

gives me exactly the same result as

select * from test where tag = 'uno'

that is, for mysql, 'uno' = 'unó'... and 'unò', and 'unö'... etc...

this field is supossed to have a unique key...

I tried with others collations, all the "bin" collations seem to work, and also latin1_general_ci too...

I'd just like to know if this would be the appropiate collation to choose for every field in the database, or if I might face any trouble choosing it... take into account that I'm planing to store spanish data in that db...

saludos

sas

ps: anyway, it seems really odd that in a spanish collation accents could be considered meaningless...

--

edit: I did a couple of tests, I entered data with á Á é É ñ Ñ, etc, and it seems like mysql can really handle them ok...

Answer 1

You can use a UTF-8 collation to differentiate the value.

Answer 2

You could use latin1_general_ci as your default database/table/column collation and specify latin1_spanish_ci on a per-select basis where needed:

select * from test order by tag collate latin1_spanish_ci;
select * from test where tag = 'uno' collate latin1_spanish_ci;

Because 'o', 'ó' etc. are considered equal in latin1_spanish_ci, the latter statement returns multiple results if the table contains e.g. 'uno', 'unó' and 'unò'.