character problem in sql

select * from table where key='çmyk'

when i run this query on table that have r开发者_运维技巧ow which's value is 'cmyk'.

the query returns me that row. but values are different. when i search 'çmyk' it returns 'cmyk'.

so what can i do?

MySQL charset: UTF-8 Unicode (utf8) MySQL connection collation: utf8_unicode_ci table collation: latin1_swedish_ci

The problem is that the latin1_swedish_ci collation is not only case insensitive, it is umlaut insensitive as well, so the following applies:

Ä = A
Ö = O
etc.

switching to a case sensitive collation in the WHERE clause should work, like so:

select * from table where key='çmyk' collate latin1_general_cs;

with the caveat that this is not good for performance.

mySQL Reference: 9.1.7.8. Examples of the Effect of Collation

Try running the command SET NAMES latin1; and then running your query.