MySQL matching unicode characters with ascii version

by

You specified the name column as text CHARACTER SET utf8 COLLATE utf8_unicode_ci which tells MySQL to consider e and é as equivalent in matching and sorting. That collation and utf8_general_ci both make a lot of things equivalent.

http://www.collation-charts.org/ is a great resource once you learn how to read the charts, which is pretty easy.

If you want e and é etc. to be considered different then you must choose a different collation. To find out what collations are on your server (assuming you’re limited to UTF-8 encoding):

mysql> show collation like 'utf8%';

And choose using the collation charts as a reference.

One more special collation is utf8_bin in which there are no equivalencies, it’s a binary match.

The only MySQL Unicode collations I’m aware of that are not language specific are utf8_unicode_ci, utf8_general_ci and utf8_bin. They are rather weird. The real purpose of a collation is to make the computer match and sort as a person from somewhere would expect. Hungarian and Turkish dictionaries have their entries ordered according to different rules. Specifying a collation allows you to sort and match according to such local rules.

For example, it seems Danes consider e and é equivalent but Icelanders don’t:

mysql> select _utf8'e' collate utf8_danish_ci
    -> = _utf8'é' collate utf8_danish_ci as equal;
+-------+
| equal |
+-------+
|     1 |
+-------+

mysql> select _utf8'e' collate utf8_icelandic_ci
    -> = _utf8'é' collate utf8_icelandic_ci as equal;
+-------+
| equal |
+-------+
|     0 |
+-------+

Another handy trick is to fill a one column table with a bunch of characters you’re interested in (it’s easier from a script) and then MySQL can tell you the equivalencies:

mysql> create table t (c char(1) character set utf8);
mysql> insert into t values ('a'), ('ä'), ('á');
mysql> select group_concat(c) from t group by c collate utf8_icelandic_ci;
+-----------------+
| group_concat(c) |
+-----------------+
| a               |
| á               |
| ä               |
+-----------------+

mysql> select group_concat(c) from t group by c collate utf8_danish_ci;
+-----------------+
| group_concat(c) |
+-----------------+
| a,á             |
| ä               |
+-----------------+

mysql> select group_concat(c) from t group by c collate utf8_general_ci;
+-----------------+
| group_concat(c) |
+-----------------+
| a,ä,á           |
+-----------------+

More Related Contents:

  1. What is better subquery vs literal on IN clause in mysql
  2. Trouble with UTF-8 characters; what I see is not what I stored
  3. SQL query return data from multiple tables
  4. Select all columns except one in MySQL?
  5. What’s the difference between utf8_general_ci and utf8_unicode_ci?
  6. MySQL SELECT only not null values
  7. How to display UTF-8 characters in phpMyAdmin?
  8. What is MySQL row order for “SELECT * FROM table_name;”?
  9. How to store unicode in MySQL?
  10. MySQL SELECT increment counter
  11. mysql select from n last rows
  12. MySQL VARCHAR Lengths and UTF-8
  13. MySQL select with CONCAT condition
  14. How does MySQL’s ORDER BY RAND() work?
  15. Sort by order of values in a select statement “in” clause in mysql
  16. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  17. MySQL INSERT INTO … VALUES and SELECT
  18. Mysql select distinct
  19. How to select one row randomly taking into account a weight?
  20. Compare only day and month with date field in mysql
  21. Sum results of a few queries and then find top 5 in SQL
  22. MySQL – SQL_BIG_SELECTS
  23. MySQL INNER JOIN select only one row from second table
  24. What is the default MySQL JOIN behaviour, INNER or OUTER?
  25. MySQL : retrieve a large select by chunks
  26. Insert data into table with result from another select query
  27. How to SELECT by MAX(date)?
  28. How to use Table output from stored MYSQL Procedure
  29. MySQL: Querying for unicode entities
  30. mySQL returns all rows when field=0

Leave a Comment