List:General Discussion« Previous MessageNext Message »
From:Anders Karlsson Date:September 26 2013 10:48pm
Subject:Re: Why is mySQL not respecting foreign characters as different
View as plain text  
That is because of the collation. It's the collations that determines 
character equality. I can't tell what the collation is in your case for 
the columns us, es, de, es and fr. Also, that you match character sets 
in different columns is usually not a good idea, unless you have a good 
reason for it (and there are exception to this rule).

Try specifying the utf8_bin collation instead and that will work. For a 
more complete explanation of all this, read my blog on this subject: 
http://karlssonondatabases.blogspot.nl/2012/11/character-sets-collations-utf-8-and-all.html

/Karlsson
Daevid Vincent skrev 2013-09-26 23:44:
> How come MySQL is not differentiating between these characters?
>
> SELECT text_id, us, de, es, fr
>    FROM texts
>    WHERE us = fr;
>
> Results in matching here. Notice the difference in the "scene" vs "scène"
>
> text_id         us      es      de         fr
> --------------  ------  ------  ---------  --------
> all_page_scene  scene   escena  Filmszene  scène
>
>
> I wold expect this NOT to match.
>
> Do I have to add something to my query to tell MySQL to respect other
> character sets as different?
>
> CREATE TABLE `texts` (
>    `text_id` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci NOT
> NULL DEFAULT '',
>    `us` text,
>    `es` text,
>    `de` text,
>    `fr` text,
>    PRIMARY KEY (`text_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
>
> Field    Type         Collation          Null    Key     Default  Extra
> Privileges                       Comment
> -------  -----------  -----------------  ------  ------  -------  ------
> -------------------------------  ---------
> text_id  varchar(50)  latin1_general_ci  NO      PRI
> select,insert,update,references
> us       text         utf8_general_ci    YES             (NULL)
> select,insert,update,references
> es       text         utf8_general_ci    YES             (NULL)
> select,insert,update,references
> de       text         utf8_general_ci    YES             (NULL)
> select,insert,update,references
> fr       text         utf8_general_ci    YES             (NULL)
> select,insert,update,references
>
>


-- 

Anders Karlsson, Senior Sales Engineer
SkySQL | t: +46 708-608-121 | Skype: drdatabase

Thread
Why is mySQL not respecting foreign characters as differentDaevid Vincent26 Sep
  • Re: Why is mySQL not respecting foreign characters as differentClaudio Nanni26 Sep
  • Re: Why is mySQL not respecting foreign characters as differentAnders Karlsson26 Sep
    • Re: Why is mySQL not respecting foreign characters as differentHartmut Holzgraefe27 Sep