The Ultimate Guide to High Availability with MariaDB

How do I set accent insensitivity

You are viewing an old version of this question. View the current version here.

I would like to have a primary key with accent insensitivity. We have a MySQL table with _0900_ai_ci which I would like to migrate to our new infrastructure. With _520 or any other collate it prints a "duplicated primary key".

Answer

MariaDB 10.10.0 includes a preview that includes UCA-14 collations (5 versions ahead of MySQL's 0900 collations, that also includes accent insensitive collations:

MariaDB [(none)]> show collation;
+--------------------------------+----------+------+---------+----------+---------+
| Collation                      | Charset  | Id   | Default | Compiled | Sortlen |
+--------------------------------+----------+------+---------+----------+---------+
...
| uca1400_ai_ci                  | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_ai_cs                  | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_as_ci                  | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_as_cs                  | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_nopad_ai_ci            | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_nopad_ai_cs            | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_nopad_as_ci            | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_nopad_as_cs            | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_icelandic_ai_ci        | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_icelandic_ai_cs        | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_icelandic_as_ci        | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_icelandic_as_cs        | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_icelandic_nopad_ai_ci  | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_icelandic_nopad_ai_cs  | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_icelandic_nopad_as_ci  | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_icelandic_nopad_as_cs  | NULL     | NULL | NULL    | Yes      |       8 |
...
| uca1400_croatian_ai_ci         | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_croatian_ai_cs         | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_croatian_as_ci         | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_croatian_as_cs         | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_croatian_nopad_ai_ci   | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_croatian_nopad_ai_cs   | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_croatian_nopad_as_ci   | NULL     | NULL | NULL    | Yes      |       8 |
| uca1400_croatian_nopad_as_cs   | NULL     | NULL | NULL    | Yes      |       8 |
...

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.