Question: I'd like to be able to do queries that normalize accented characters, so that for example:

```
é, è, and ê

```

are all treated as 'e', in queries using '=' and 'like'. I have a row with username field set to '**rené**', and I'd like to be able to match on it with both '**rene**' and '**rené**'.

I'm attempting to do this with the 'collate' clause in MySQL 5.0.8. I get the following error:

```
mysql> select * from User where username = 'rené' collate utf8_general_ci;
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

```

FWIW, my table was created with:

```
CREATE TABLE `User` (
  `id` bigint(19) NOT NULL auto_increment,
  `username` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uniqueUsername` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=56790 DEFAULT CHARSET=utf8

```

Answer: 
The reason for the error is not the table but the characterset of your input, i.e. the 'rené' in your query. The behaviour depends on the [character\_set\_connection](http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_character_set_connection) variable:

> 
> The character set used for literals that do not have a character set introducer and for number-to-string conversion. 
> 
> 
> 

Using the MySQL Client, change it using `SET NAMES`:

> 
> A SET NAMES 'charset\_name' statement is equivalent to these three statements:
> 
> 
> 

```
SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

```

(from <http://dev.mysql.com/doc/refman/5.5/en/charset-connection.html>)

Example output:

```
mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from User where username = 'rené' collate utf8_general_ci;
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from User where username = 'rené' collate utf8_general_ci;
Empty set (0.00 sec)

```

Altenatively, use can explicitly set the character set using a 'character set introducer':

```
mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from User where username = _utf8'rené' collate utf8_general_ci;
Empty set (0.00 sec)

```

*I know this question is pretty old but since Google led me here for a related question, I though it still deserves an answer :)*
Does a search using *English* characters return results with foreign characters?
I wrote the following script to compare collations in MySQL 5.7 (Should also work for MariaDB 10.2+):

```
        $db->query('CREATE TABLE IF NOT EXISTS test (name varchar(20))
         Engine=InnoDB character set utf8mb4 collate utf8mb4_unicode_520_ci');

        $db->query('CREATE TABLE IF NOT EXISTS test2 (name varchar(20))
         Engine=InnoDB character set utf8mb4 collate utf8mb4_unicode_ci');

        $db->query("insert into test values('Łove 520')");
        $db->query("insert into test2 values('Łove 520')");

        $types = ['utf8mb4_unicode_520_ci', 'utf8mb4_unicode_ci'];
        $tables = ['test' => 'utf8mb4_unicode_520_ci', 'test2' => 'utf8mb4_unicode_ci'];
        foreach($types as $n)
        {
            foreach($tables as $ta => $tc)
            {
                $db->query("SET NAMES 'utf8mb4' COLLATE '$n'");
                $res = $db->query("Select * from $ta where name like 'Love%'"); // Ł equal
                echo "\ntable $ta($tc), names($n): ".$res->fetchColumn(0);
            }
        }

```

Here are the results:

```
table test(utf8mb4_unicode_520_ci), names(utf8mb4_unicode_520_ci): ┼üove 520
table test2(utf8mb4_unicode_ci), names(utf8mb4_unicode_520_ci):
table test(utf8mb4_unicode_520_ci), names(utf8mb4_unicode_ci): ┼üove 520
table test2(utf8mb4_unicode_ci), names(utf8mb4_unicode_ci):

```

(Note: I ran the script from the command line, so it appears as ┼üove 520 instead of Łove 520)

It appears that L == Ł when the table collation is utf8mb4\_unicode\_**520**\_ci, regardless of the connection collation. However, it is **not** equivalent if you only use utf8mb4\_unicode\_ci.