# UTF8 vs Latin1 String comparison in MySQL?

**URL:** <https://www.ruby-forum.com/t/utf8-vs-latin1-string-comparison-in-mysql/168387>\
**Category:** Rails I18n\
**Created:** [May 20, 2009, 10:02am UTC](https://www.ruby-forum.com/t/utf8-vs-latin1-string-comparison-in-mysql/168387 "2009-05-20T10:02:19Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![wolframarnold](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/a88e57/32.png) [@wolframarnold](https://www.ruby-forum.com/u/wolframarnold)\
**Post date:** [May 20, 2009, 10:02am UTC](https://www.ruby-forum.com/t/utf8-vs-latin1-string-comparison-in-mysql/168387/1 "2009-05-20T10:02:19Z")

</div>

I’m running into a surprising twist in MySql string compare.

Try this:

select “América” = “America”;  
±-----------------------+  
| “América” = “America” |  
±-----------------------+  
| 1 |  
±-----------------------+

My db and client are in UTF8:  
show variables like “%character%”;  
±-------------------------±---------------------------+  
| Variable\_name | Value |  
±-------------------------±---------------------------+  
| character\_set\_client | utf8 |  
| character\_set\_connection | utf8 |  
| character\_set\_database | utf8 |  
| character\_set\_filesystem | binary |  
| character\_set\_results | utf8 |  
| character\_set\_server | utf8 |  
| character\_set\_system | utf8 |  
| character\_sets\_dir | /usr/share/mysql/charsets/ |  
±-------------------------±---------------------------+

MySql lets you force an encoding on a per string basis, if you try this:

\_utf8"xxx" the string is in UTF8, of  
\_latin1"xxx" then “xxx” is in Latin 1.

Here is what’s puzzling me:

select \_latin1"América" = \_latin1"America";  
±-------------------------------------+  
| \_latin1"América" = \_latin1"America" |  
±-------------------------------------+  
| 0 |  
±-------------------------------------+

vs

select \_utf8"América" = \_utf8"America";  
±---------------------------------+  
| \_utf8"América" = \_utf8"America" |  
±---------------------------------+  
| 1 |  
±---------------------------------+

Why does it think the UTF8 strings are the same when the Latin1 ones are  
not??? What am I not getting?

Thanks!

Wolf

---

<div class="post-metadata">

**Author:** ![wolframarnold](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/a88e57/32.png) [@wolframarnold](https://www.ruby-forum.com/u/wolframarnold)\
**Post date:** [May 20, 2009, 2:55pm UTC](https://www.ruby-forum.com/t/utf8-vs-latin1-string-comparison-in-mysql/168387/2 "2009-05-20T14:55:50Z")

</div>

Wolfram Arnold wrote:

> I’m running into a surprising twist in MySql string compare.
> 
> Try this:
> 
> select “América” = “America”;  
> ±-----------------------+  
> | “América” = “America” |  
> ±-----------------------+  
> | 1 |  
> ±-----------------------+

I looked into this a bit this morning. It seems that string comparison  
is affected by “collation” not charset. The default utf8 collation  
yields the unexpected result:

mysql\> SHOW VARIABLES LIKE ‘%collat%’;  
±---------------------±----------------+  
| Variable\_name | Value |  
±---------------------±----------------+  
| collation\_connection | utf8\_general\_ci |  
| collation\_database | utf8\_general\_ci |  
| collation\_server | utf8\_general\_ci |  
±---------------------±----------------+  
3 rows in set (0.00 sec)

mysql\> select “América” = “America”;  
±-----------------------+  
| “América” = “America” |  
±-----------------------+  
| 1 |  
±-----------------------+  
1 row in set (0.00 sec)

But if I change the collation to utf8\_bin, the strings compare as  
unequal:

mysql\> set collation=‘utf8\_bin’;  
Query OK, 0 rows affected (0.04 sec)

mysql\> set collation\_server=‘utf8\_bin’;  
Query OK, 0 rows affected (0.00 sec)

mysql\> set collation\_database=‘utf8\_bin’;  
Query OK, 0 rows affected (0.00 sec)

mysql\> SHOW VARIABLES LIKE ‘%collat%’;  
±---------------------±---------+  
| Variable\_name | Value |  
±---------------------±---------+  
| collation\_connection | utf8\_bin |  
| collation\_database | utf8\_bin |  
| collation\_server | utf8\_bin |  
±---------------------±---------+  
3 rows in set (0.00 sec)

mysql\> select “América” = “America”;;  
±-----------------------+  
| “América” = “America” |  
±-----------------------+  
| 0 |  
±-----------------------+  
1 row in set (0.00 sec)

I’m a bit confused myself about how utf8\_general\_ci could sort  
effectively if ‘é’ equals ‘e’ since users would expect all the like  
characters to group together and why this collation would be useful; nor  
do I understand if there are other implications to using utf8\_bin  
collation.

There is a nicely written report on Unicode collation here:  
[http://www.unicode.org/unicode/reports/tr10/](http://www.unicode.org/unicode/reports/tr10/)

Sarah