Edit Question - KnowledgeHub

Title Body

I am trying to achieve accent and case-insensitive sorting in MySQL. Following the instructions in the manual, this is supposed to work with the utf8 character set and utf8_general_ci collation. When I follow the example in the manual ( http://dev.mysql.com/doc/refman/5.1/en/charset-collation-implementations.html ) under "Collations for Unicode multi-byte character sets" I do not get the same results: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 679877 Server version: 5.1.41-log MySQL Community Server (GPL) by Remi Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SET NAMES 'utf8' COLLATE 'utf8_general_ci'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT 'a' = 'A', 'a' = 'À', 'a' = 'á'; +-----------+-----------+-----------+ | 'a' = 'A' | 'a' = 'À' | 'a' = 'á' | +-----------+-----------+-----------+ | 1 | 0 | 0 | +-----------+-----------+-----------+ 1 row in set (0.00 sec) mysql> In the example in the manual, those are all 1. It also fails to treat accented characters equally when I try to set the collation directly in a query. In this example, the table is using latin1 and I'm converting to utf8. mysql> select * from test; +----------+ | k | +----------+ | Cárdenas | | Cardozo | | Corbin | | Cabrero | +----------+ mysql> select k from test order by convert(k using utf8) collate utf8_general_ci ; +----------+ | k | +----------+ | Cabrero | | Cardozo | | Corbin | | Cárdenas | +----------+ 4 rows in set (0.00 sec) It should be ignoring the accent over the 'a' in the last entry and sorting it second. Any ideas what I'm doing wrong?

Tags (comma-separated) Cancel