When you have some experience with databases, you probably know that the „collation“ is a setting related to the language und character set that is relevant for sorting strings. But it is far more than that. So, let’s do some hands-on experiments:
create database collation_demo;
use collation_demo;
Latin-1
Sort order
Back in the days before UTF-8 when latin1 was the most frequently used character set, MySQL was well known for the fact that the Swedish sort order was the default order for latin1 - „Ä“ and „Ö“ come after „Z“. Which felt always a bit like a bug in applications with German strings.
It is still like this today, a historical reminder of the Swedish origin of MySQL.
show character set like 'latin1%';
+---------+----------------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+----------------------+-------------------+--------+
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
+---------+----------------------+-------------------+--------+
create table cities(id int auto_increment primary key, name varchar(20)) character set latin1;
insert into cities(name) values ("Aachen");
insert into cities(name) values ("Zürich");
insert into cities(name) values ("Öhringen");
select name from cities order by name;
+-----------+
| name |
+-----------+
| Aachen |
| Zürich |
| Öhringen |
+-----------+
So let’s just change the table collation to latin1_german1_ci and this will fix the sort order, won’t it?
Well, no:
alter table cities character set latin1 collate latin1_german1_ci;
select name from cities order by name;
+-----------+
| name |
+-----------+
| Aachen |
| Zürich |
| Öhringen |
+-----------+
This is already a new thing that I learned while writing this article:
- Changing the database collation will only affect new tables
- Changing the table collation will only affect new columns
So let’s just change the column collation:
alter table cities modify name varchar(20) character set latin1 collate latin1_german1_ci;
select name from cities order by name;
+-----------+
| name |
+-----------+
| Aachen |
| Öhringen |
| Zürich |
+-----------+
Sort order options for German
There is not only a german1, but also a german2, what’s the difference? We’ll explain it now, because the different collations are also available with UTF-8 character sets. „German 1“ is dictionary sort order („Ä“ is treated like „A“), whereas „German 2“ is phonebook sort order („Ä“ is treated like „AE“). There seem to be DIN norms for this. Let’s try it:
insert into cities(name) values ("Oer-Erkenschwick");
select name from cities order by name;
+------------------+
| name |
+------------------+
| Aachen |
| Oer-Erkenschwick |
| Öhringen |
| Zürich |
+------------------+
alter table cities modify name varchar(20) character set latin1 collate latin1_german2_ci;
select name from cities order by name;
+------------------+
| name |
+------------------+
| Aachen |
| Öhringen |
| Oer-Erkenschwick |
| Zürich |
+------------------+
But now, let’s look at collations for UTF-8 character sets, because this is the default today.
UTF-8
alter table cities modify name varchar(20) character set utf8mb4;
(we still have to use utf8mb4, because utf8 will give us only 3-byte UTF-8 characters, which won’t allow emojis 😥 - but this is an entirely different story worth a separate blog article)
This will give as us the same sort order as german1:
select name from cities order by name;
+------------------+
| name |
+------------------+
| Aachen |
| Oer-Erkenschwick |
| Öhringen |
| Zürich |
+------------------+
Why? Because the default collation for utf8mb4 is utf8mb4_0900_ai_ci
show character set like 'utf8mb4';
+---------+---------------+--------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+--------------------+--------+
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
+---------+---------------+--------------------+--------+
The _ai_ means „accent insensitive“ and the _ci_ „case insensitive“. „Accent insensitive“ means that „ü“ is equivalent to „u“.
And this also relevant to finding data and the definition of „equality“ - I wasn’t fully aware of this until yesterday!
Equality
select * from cities where name = "Zurich";
+----+---------+
| id | name |
+----+---------+
| 2 | Zürich |
+----+---------+
Which has further effects, of course, for things like unique indices:
alter table cities add unique index unique_name(name);
insert into cities(name) values ("Zurich");
ERROR 1062 (23000): Duplicate entry 'Zurich' for key 'cities.unique_name'
We can avoid this by changing the collation to an „accent sensitive“ one:
alter table cities modify name varchar(20) character set utf8mb4 collate utf8mb4_0900_as_ci;
insert into cities(name) values ("Zurich");
select name from cities order by name;
+------------------+
| name |
+------------------+
| Aachen |
| Oer-Erkenschwick |
| Öhringen |
| Zurich |
| Zürich |
+------------------+
select * from cities where name = "Zurich";
+----+--------+
| id | name |
+----+--------+
| 6 | Zurich |
+----+--------+
Now we can’t change back to the accent-insensitive collation because this would violate the unique constraint:
alter table cities modify name varchar(20) character set utf8mb4 collate utf8mb4_0900_ai_ci;
ERROR 1062 (23000): Duplicate entry 'Zurich' for key 'cities.unique_name'
Is there also a Unicode collation in which „ue“ and „ü“ are equivalent? Yes, but it is German-specific:
utf8mb4_german2_ci will use the „phonebook“ sort order and consider „ü“ and „ue“ as equal when finding strings, but not „ü“ and „u“
alter table cities modify name varchar(20) character set utf8mb4 collate utf8mb4_german2_ci;
select name from cities order by name;
+------------------+
| name |
+------------------+
| Aachen |
| Öhringen |
| Oer-Erkenschwick |
| Zürich |
| Zurich |
+------------------+
select * from cities where name = "Zurich";
+----+--------+
| id | name |
+----+--------+
| 6 | Zurich |
+----+--------+
insert into cities(name) values ("Oehringen");
ERROR 1062 (23000): Duplicate entry 'Oehringen' for key 'cities.unique_name'
select * from cities where name = "Zuerich";
+----+---------+
| id | name |
+----+---------+
| 2 | Zürich |
+----+---------+
The utf8mb4_de_pb_0900_ai_ci collation will still use „phonebook“ properties for „ä“, „ö“ and „ü“ (I guess that’s the _pb_), but „accent insensitivity“ for other letters like „è“ - that’s really interesting:
alter table cities modify name varchar(20) character set utf8mb4 collate utf8mb4_de_pb_0900_ai_ci;
select * from cities where name = "Zuerich";
+----+---------+
| id | name |
+----+---------+
| 2 | Zürich |
+----+---------+
select * from cities where name = "Ohringen";
Empty set (0,000 sec)
insert into cities(name) values ("Ohringen");
Query OK, 1 row affected (0,009 sec)
insert into cities(name) values ("Sélestat");
select * from cities where name = "Selestat";
+----+-----------+
| id | name |
+----+-----------+
| 9 | Sélestat |
+----+-----------+
insert into cities(name) values ("Selestat");
ERROR 1062 (23000): Duplicate entry 'Selestat' for key 'cities.unique_name'
Language-specific quirks
By the way, there is no collation with a „phonebook“ property for all languages at once (at least not in MySQL and probably MariaDB)
Let’s try Norwegian, where „aa“ is considered equivalent to „å“, and its utf8mb4_nb_0900_ai_ci collation:
create table players(id int auto_increment primary key, first_name varchar(20), last_name varchar(20))
charset utf8mb4 collate utf8mb4_nb_0900_ai_ci;
insert into players(first_name, last_name) values ("Erling", "Håland");
insert into players(first_name, last_name) values ("Thomas", "Müller");
Then we will we find „Haaland“, but not „Mueller“, and vice versa with the German collation:
select * from players where last_name = "Haaland";
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 1 | Erling | Håland |
+----+------------+-----------+
select * from players where last_name = "Mueller";
Empty set (0,001 sec)
alter table players modify last_name varchar(20) character set utf8mb4 collate utf8mb4_german2_ci;
select * from players where last_name = "Haaland";
Empty set (0,001 sec)
select * from players where last_name = "Mueller";
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 2 | Thomas | Müller |
+----+------------+-----------+
UTF-8 character quirks
One last experiment: There is more than one way to write a „ü“ in UTF-8. You can write it as a single codepoint „ü“ or as a „u“ combined with a combining diacritical mark (the dots) - this is "u\u0308" when written as a Java string literal.
I discovered that this kind of „ü“ is considered different to a normal „ü“ even with an accent-insensitive collation. The „Müller“ in the insert statement contains the „special“ „ü“. The different hash values show that the binary column value is indeed different.
alter table players modify last_name varchar(20) character set utf8mb4 collate utf8mb4_de_pb_0900_ai_ci;
insert into players(first_name, last_name) values ("Thomas", "Müller");
select id, last_name, sha2(last_name, 256) from players;
+----+-----------+------------------------------------------------------------------+
| id | last_name | sha2(last_name, 256) |
+----+-----------+------------------------------------------------------------------+
| 1 | Håland | cfe11ffd6ad90f7f5bb7ecb0ea31f08c22fd427552c4f7bf7cfbbc397d36a1ff |
| 2 | Müller | a78429899bb825ce667d25d92e2cde488f8a7d6871bb2be7365601cad6b0a8f2 |
| 5 | Müller | fa6a2313ed2910a7ea010814b90a5e6d4815d667f30132010deb6c0ddf524a07 |
+----+-----------+------------------------------------------------------------------+
select * from players where last_name = "Müller";
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 2 | Thomas | Müller |
+----+------------+-----------+
However, it is considered equivalent to „u“!
select * from players where last_name = "Muller";
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 5 | Thomas | Müller |
+----+------------+-----------+
This means that you can have „Müller“ (visually) twice in the database even with a unique constraint on the column. I’m just wondering whether this can be exploited security-wise in some way in some applications…