Karaktersets & collation (MySQL)

Uit De Vliegende Brigade
Ga naar: navigatie, zoeken
Collate in het Nederlands (2020): sortering of sorteren. In 2021 kreeg ik collatie als vertaling. Die term bestaat ook volgens Wiktionary
Coderingen die MySQL Workbench biedt bij aanmaak van een nieuwe database op m'n laptop in 2019 (1)
Coderingen die MySQL Workbench biedt bij aanmaak van een nieuwe database op m'n laptop in 2019 (2)

Mijn standaarden

  • Tekencodering: Altijd utf8mb4
  • Collatie - MySQL: utf8mb4_0900_as_ci
  • Collatie - MariaDB: utf8mb4_unicode_520_ci
  • Collatie - MySQL & MariaDB: utf8mb4_unicode_520_ci - Dus als een database in beide omgevingen moet functioneren
  • In databases tekencoderingen en collaties zo min mogelijk specificeren: Anders moet ik in dumps coderingen aanpassen bij export van MySQL naar MariaDB: Ze hebben helaas incompatibele sets van collaties.


Inleiding

Karaktersets?

Verschillende systemen (Amazon, Bol.com, WordPress, Google Shopping, Drupal, etc.) gebruiken verschillende karaktersets, oftewel systemen om karakters te coderen in bits and bytes. Amazon gebruikt bij import van productgegevens op sommige plekken zelfs karaktersets door elkaar heen. MySQL-databases kunnen hier rekening mee houden. Het is dus belangrijk om thuis te zijn in karaktersets, als je precies bent, en niet van verrassingen houdt.

Collation?

Collation (collatie in het Nederlands [1]) heeft betrekking op de sorteervolgorde binnen een taal. In verschillende talen worden woorden enigszins verschillend gesorteerd. Dat heeft te maken met hele interessante zaken, die ik echter nooit precies uit elkaar kan houden: glyphs, ligaturen, diacritics, graphemesdigraphs & trigraphs.

Waarom collatie voor mij relevant is: Er zijn allerlei handelingen (bv. een join) die je niet kunt verrichten met objecten met verschillende collatie.

MySQL behandelt karaktersets en collation vaak tezamen. Daarom ook in dit artikel.

Multi-level markeringen

In MySQL wordt de gebruikte karakterset op diverse plekken gespecificeerd:

  • Op database-niveau
  • Op tabelniveau
  • Per kolom.

Dat laatste geval kom ik helaas regelmatig tegen. Bv. als ik werk met databases die al een lange geschiedenis hebben. Dit maakt het verhelpen van collatie-fouten net een beetje irritanter dan verwacht: Ik moet soms per tabel per kolom de collatie controleren.

Beschikbare karaktersets

MySQL Server 8.0.25-0ubuntu0.20.04.1

M'n laptop - Sep. 2021:

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0,00 sec)

MariaDB 10.3.31-MariaDB-0ubuntu0.20.04.1

Nieuwe server dvb8 - Sep. 2021:

MariaDB [(none)]> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset       | binary              |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode            | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.001 sec)

Merk op dat MariaDB één rij minder heeft dan MySQL.

Ik gebruik karakterset UTF8mb4

Voor karaktersets gebruik ik standaard UTF8mb4. Dat is een multibyte UTF8-codering met maximaal 4 bytes per karakter. (Multibyte wil zeggen dat voor sommige karakters meerdere bytes nodig zijn ter codering).

Ik geloof dat er geen redelijke alternatief is: Als ik zou overschakelen op UTF8 zonder multibyte, dan raak ik informatie kwijt. En propriëtaire karaktersets (Bv. Latin-3) gebruik ik alleen als ik het nodig heb voor bv. een export.

Beschikbare collaties voor UTF8mb4

MySQL Server 8.0.25-0ubuntu0.20.04.1

Dit betreft m'n laptop - Sep. 2021:

show collation like "utf8mb4%";
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci         | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs         | utf8mb4 | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin           | utf8mb4 | 309 |         | Yes      |       1 | NO PAD        |
| utf8mb4_bin                | utf8mb4 |  46 |         | Yes      |       1 | PAD SPACE     |
| utf8mb4_croatian_ci        | utf8mb4 | 245 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_cs_0900_ai_ci      | utf8mb4 | 266 |         | Yes      |       0 | NO PAD        |
| utf8mb4_cs_0900_as_cs      | utf8mb4 | 289 |         | Yes      |       0 | NO PAD        |
| utf8mb4_czech_ci           | utf8mb4 | 234 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_danish_ci          | utf8mb4 | 235 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_da_0900_ai_ci      | utf8mb4 | 267 |         | Yes      |       0 | NO PAD        |
| utf8mb4_da_0900_as_cs      | utf8mb4 | 290 |         | Yes      |       0 | NO PAD        |
| utf8mb4_de_pb_0900_ai_ci   | utf8mb4 | 256 |         | Yes      |       0 | NO PAD        |
| utf8mb4_de_pb_0900_as_cs   | utf8mb4 | 279 |         | Yes      |       0 | NO PAD        |
| utf8mb4_eo_0900_ai_ci      | utf8mb4 | 273 |         | Yes      |       0 | NO PAD        |
| utf8mb4_eo_0900_as_cs      | utf8mb4 | 296 |         | Yes      |       0 | NO PAD        |
| utf8mb4_esperanto_ci       | utf8mb4 | 241 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_estonian_ci        | utf8mb4 | 230 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_es_0900_ai_ci      | utf8mb4 | 263 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_0900_as_cs      | utf8mb4 | 286 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_trad_0900_ai_ci | utf8mb4 | 270 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_trad_0900_as_cs | utf8mb4 | 293 |         | Yes      |       0 | NO PAD        |
| utf8mb4_et_0900_ai_ci      | utf8mb4 | 262 |         | Yes      |       0 | NO PAD        |
| utf8mb4_et_0900_as_cs      | utf8mb4 | 285 |         | Yes      |       0 | NO PAD        |
| utf8mb4_general_ci         | utf8mb4 |  45 |         | Yes      |       1 | PAD SPACE     |
| utf8mb4_german2_ci         | utf8mb4 | 244 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_hr_0900_ai_ci      | utf8mb4 | 275 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hr_0900_as_cs      | utf8mb4 | 298 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hungarian_ci       | utf8mb4 | 242 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_hu_0900_ai_ci      | utf8mb4 | 274 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hu_0900_as_cs      | utf8mb4 | 297 |         | Yes      |       0 | NO PAD        |
| utf8mb4_icelandic_ci       | utf8mb4 | 225 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_is_0900_ai_ci      | utf8mb4 | 257 |         | Yes      |       0 | NO PAD        |
| utf8mb4_is_0900_as_cs      | utf8mb4 | 280 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ja_0900_as_cs      | utf8mb4 | 303 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ja_0900_as_cs_ks   | utf8mb4 | 304 |         | Yes      |      24 | NO PAD        |
| utf8mb4_latvian_ci         | utf8mb4 | 226 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_la_0900_ai_ci      | utf8mb4 | 271 |         | Yes      |       0 | NO PAD        |
| utf8mb4_la_0900_as_cs      | utf8mb4 | 294 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lithuanian_ci      | utf8mb4 | 236 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_lt_0900_ai_ci      | utf8mb4 | 268 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lt_0900_as_cs      | utf8mb4 | 291 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lv_0900_ai_ci      | utf8mb4 | 258 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lv_0900_as_cs      | utf8mb4 | 281 |         | Yes      |       0 | NO PAD        |
| utf8mb4_persian_ci         | utf8mb4 | 240 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_pl_0900_ai_ci      | utf8mb4 | 261 |         | Yes      |       0 | NO PAD        |
| utf8mb4_pl_0900_as_cs      | utf8mb4 | 284 |         | Yes      |       0 | NO PAD        |
| utf8mb4_polish_ci          | utf8mb4 | 229 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_romanian_ci        | utf8mb4 | 227 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_roman_ci           | utf8mb4 | 239 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_ro_0900_ai_ci      | utf8mb4 | 259 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ro_0900_as_cs      | utf8mb4 | 282 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ru_0900_ai_ci      | utf8mb4 | 306 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ru_0900_as_cs      | utf8mb4 | 307 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sinhala_ci         | utf8mb4 | 243 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sk_0900_ai_ci      | utf8mb4 | 269 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sk_0900_as_cs      | utf8mb4 | 292 |         | Yes      |       0 | NO PAD        |
| utf8mb4_slovak_ci          | utf8mb4 | 237 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_slovenian_ci       | utf8mb4 | 228 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sl_0900_ai_ci      | utf8mb4 | 260 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sl_0900_as_cs      | utf8mb4 | 283 |         | Yes      |       0 | NO PAD        |
| utf8mb4_spanish2_ci        | utf8mb4 | 238 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_spanish_ci         | utf8mb4 | 231 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sv_0900_ai_ci      | utf8mb4 | 264 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sv_0900_as_cs      | utf8mb4 | 287 |         | Yes      |       0 | NO PAD        |
| utf8mb4_swedish_ci         | utf8mb4 | 232 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_tr_0900_ai_ci      | utf8mb4 | 265 |         | Yes      |       0 | NO PAD        |
| utf8mb4_tr_0900_as_cs      | utf8mb4 | 288 |         | Yes      |       0 | NO PAD        |
| utf8mb4_turkish_ci         | utf8mb4 | 233 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_unicode_520_ci     | utf8mb4 | 246 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_unicode_ci         | utf8mb4 | 224 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_vietnamese_ci      | utf8mb4 | 247 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_vi_0900_ai_ci      | utf8mb4 | 277 |         | Yes      |       0 | NO PAD        |
| utf8mb4_vi_0900_as_cs      | utf8mb4 | 300 |         | Yes      |       0 | NO PAD        |
| utf8mb4_zh_0900_as_cs      | utf8mb4 | 308 |         | Yes      |       0 | NO PAD        |
+----------------------------+---------+-----+---------+----------+---------+---------------+
75 rows in set (0,00 sec)

MariaDB 10.3.31-MariaDB-0ubuntu0.20.04.1

Nieuwe server dvb8 - Sep. 2021:

show collation like "utf8mb4%";
+------------------------------+---------+------+---------+----------+---------+
| Collation                    | Charset | Id   | Default | Compiled | Sortlen |
+------------------------------+---------+------+---------+----------+---------+
| utf8mb4_bin                  | utf8mb4 |   46 |         | Yes      |       1 |
| utf8mb4_croatian_ci          | utf8mb4 |  608 |         | Yes      |       8 |
| utf8mb4_croatian_mysql561_ci | utf8mb4 |  245 |         | Yes      |       8 |
| utf8mb4_czech_ci             | utf8mb4 |  234 |         | Yes      |       8 |
| utf8mb4_danish_ci            | utf8mb4 |  235 |         | Yes      |       8 |
| utf8mb4_esperanto_ci         | utf8mb4 |  241 |         | Yes      |       8 |
| utf8mb4_estonian_ci          | utf8mb4 |  230 |         | Yes      |       8 |
| utf8mb4_general_ci           | utf8mb4 |   45 | Yes     | Yes      |       1 |
| utf8mb4_general_nopad_ci     | utf8mb4 | 1069 |         | Yes      |       1 |
| utf8mb4_german2_ci           | utf8mb4 |  244 |         | Yes      |       8 |
| utf8mb4_hungarian_ci         | utf8mb4 |  242 |         | Yes      |       8 |
| utf8mb4_icelandic_ci         | utf8mb4 |  225 |         | Yes      |       8 |
| utf8mb4_latvian_ci           | utf8mb4 |  226 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci        | utf8mb4 |  236 |         | Yes      |       8 |
| utf8mb4_myanmar_ci           | utf8mb4 |  609 |         | Yes      |       8 |
| utf8mb4_nopad_bin            | utf8mb4 | 1070 |         | Yes      |       1 |
| utf8mb4_persian_ci           | utf8mb4 |  240 |         | Yes      |       8 |
| utf8mb4_polish_ci            | utf8mb4 |  229 |         | Yes      |       8 |
| utf8mb4_roman_ci             | utf8mb4 |  239 |         | Yes      |       8 |
| utf8mb4_romanian_ci          | utf8mb4 |  227 |         | Yes      |       8 |
| utf8mb4_sinhala_ci           | utf8mb4 |  243 |         | Yes      |       8 |
| utf8mb4_slovak_ci            | utf8mb4 |  237 |         | Yes      |       8 |
| utf8mb4_slovenian_ci         | utf8mb4 |  228 |         | Yes      |       8 |
| utf8mb4_spanish2_ci          | utf8mb4 |  238 |         | Yes      |       8 |
| utf8mb4_spanish_ci           | utf8mb4 |  231 |         | Yes      |       8 |
| utf8mb4_swedish_ci           | utf8mb4 |  232 |         | Yes      |       8 |
| utf8mb4_thai_520_w2          | utf8mb4 |  610 |         | Yes      |       4 |
| utf8mb4_turkish_ci           | utf8mb4 |  233 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci       | utf8mb4 |  246 |         | Yes      |       8 |
| utf8mb4_unicode_520_nopad_ci | utf8mb4 | 1270 |         | Yes      |       8 |
| utf8mb4_unicode_ci           | utf8mb4 |  224 |         | Yes      |       8 |
| utf8mb4_unicode_nopad_ci     | utf8mb4 | 1248 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci        | utf8mb4 |  247 |         | Yes      |       8 |
+------------------------------+---------+------+---------+----------+---------+
33 rows in set (0.001 sec)

Enkele collaties

MySQL is rond 2017 overgestapt van utf8mb4_general_ci naar utf8mb4_0900_ai_ci. Dat verklaar waarom ik ogenschijnlijk totaal verschillende standaard-collaties hanteer.

utf8mb4_0900_ai_ci

Wat utf8mb4_0900_ai_ci inhoudt:

  • De achterliggende karakterset is utf8mb4 - Zie eerder in dit artikel voor details. Collatie hangt af van de karakterset
  • 0900: De naam van het Unicode sorteer-algoritme
  • ai: accent indifference: Bv. e, è en é worden qua collatie als dezelfde letter beschouwd
  • ci: case insensitive: Bv. A en a worden qua collatie als dezelfde letter beschouwd.

Verder:

  • Dit schijnt een prima keuze te zijn op MySQL [2]
  • Deze collatie bestaat niet op m'n MariaDB-server. Daar bestaat het onderscheid tussen cs/ci en ai/ad niet - Alles is ci.

utf8mb4_general_ci

utf8mb4_general_ci is de standaard-collatie op m'n MariaDB-server. Volgens [3] is dat een beroerde implementatie van utf8mb4_unicode_ci.

utf8mb4_unicode_ci

Dit is de officiële en goede implementatie van een oude sorteerstandaard van UTF te zijn. Het is dus beter dan utf8mb4_general_ci, maar er bestaan betere en nieuwere collaties [4].

utf8mb4_bin

bin heeft betrekking op binaire vergelijking - Alleen relevant in sommige exotische situaties.

PAD vs. NO PAD

  • PAD: Trailing spaces worden niet meegenomen in sorteren
  • NO PAD: Trailing spaces worden meegenomen in sorteren.

520

De aanduiding 520 heeft betrekking op de Unicode 5.2-versie van het Unicode-sorteerprotocol. Alle relevante versies bij elkaar:

  1. general: Slecht! Heel efficiënt, maar maakt daardoor fouten. Die efficiëntie speelt op moderne hardware geen rol
  2. unicode: Goed maar oud
  3. 520: Beter
  4. 0900: Best.

Mijn keuze - MariaDB

Ik sla land- of taalspecifieke collaties over. Blijft er over:

utf8mb4_general_ci
utf8mb4_bin
utf8mb4_unicode_ci
utf8mb4_unicode_520_ci
utf8mb4_general_nopad_ci
utf8mb4_nopad_bin
utf8mb4_unicode_nopad_ci
utf8mb4_unicode_520_nopad_ci

Ik heb geen zin in bin of geneuzel rondom padding. Blijft er over:

utf8mb4_general_ci
utf8mb4_unicode_ci
utf8mb4_unicode_520_ci

Dus is de keuze:

  1. utf8mb4_unicode_520_ci ← Beste
  2. utf8mb4_unicode_ci
  3. utf8mb4_general_ci

Mijn keuze - MySQL

Eerste stap: Alleen land-onafhankelijke 0900-collaties en geen gedoe met bin:

  • utf8mb4_0900_ai_ci
  • utf8mb4_0900_as_ci
  • utf8mb4_0900_as_cs.

Ik vind accent sensitivity (as) en case insensitivity (ci) belangrijk. Dus het wordt

utf8mb4_0900_as_ci

Tekencodering exportbestanden

Bij exports, kun je bepalen met welke codering het exportbestand moet worden aangemaakt. En anders kan ik de betreffende tabel omzetten naar bv. latin1. Voorbeeld:

alter table
   blub_tmp
   character set = utf8,
   collate = utf8_general_ci;

Codering database uitlezen

SELECT default_character_set_name 
FROM information_schema.SCHEMATA 
WHERE schema_name = "schemaname";

Wellicht eenvoudiger:

show variables like "character_set_database";

Codering tabellen uitlezen

Alle tabellen (geloof ik):

SELECT CCSA.character_set_name 
FROM 

   information_schema.`TABLES` T,
   information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA

WHERE 

   CCSA.collation_name = T.table_collation
  AND T.table_schema = "schemaname"
  AND T.table_name = "tablename";

Voorbeeld mbt. een specifieke tabel:

select table_collation from information_schema.tables
where table_schema="webwinkels" and table_name="superquery_export";

De waardes die ik op m'n ontwikkelmachine met zo'n 30 databases tegenkom:

binary
latin1_swedish_ci
utf_bin
utf8_general_ci
utf8mb4_general_ci
utf8mb4_unicode_ci

Codering kolommen uitlezen

SELECT character_set_name 
FROM information_schema.`COLUMNS` 
WHERE table_schema = "schemaname"
  AND table_name = "tablename"
  AND column_name = "columnname";

Bv.:

SELECT collation_name FROM information_schema.COLUMNS 
where table_schema="webwinkels" and TABLE_NAME="superquery_export";

Kolom-codering aanpassen

Je kunt niet zomaar schrijven naar information_schema, maar dat hoeft ook niet:

ALTER TABLE mytable CONVERT TO CHARACTER SET latin1

Waarschuwing: UTF8MB3-alias (zomer 2020)

Het probleem

Waarschuwing bij het aanmaken van een tabel:

1 warning(s): 3719 
'utf8' is currently an alias for the character set UTF8MB3, 
but will be an alias for UTF8MB4 in a future release. 
Please consider using UTF8MB4 in order to be unambiguous.

Aanvullende gegevens

utf8mb3:

  • Identiek aan codering ucs2
  • Ondersteund enkel BMP (Basic Multilingual Plan; Plane 0; 0000-ffff (0-65.536); Eerste pagina van 2^16 tekens) [5]
  • Ondersteund geen additionele planes (Supplementary planes)
  • Multibyte characters: Max. 3 bytes/character.

De essentie

  • utf8mb3 is deprecated. Gebruik voortaan alleen nog utf8mb4 [6].
  • Specificeer expliciet utf8mb4
  • Collation: collate utf8mb4_0900_ai_ci - Uitleg volgt nog.

Bronnen

Casus: Collation-foutmelding (april 2021)

Foutmelding

De mooiste dingen in het leven beginnen met een foutmelding:

Error Code: 1267. Illegal mix of collations 
(utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) 
for operation '='

Collation op database-niveau

De tabellen in deze query, lijken allemaal de standaard-instellingen van de bijbehorende databases te volgen. Dit betreffen queries uit twee verschillende databases. Waarschijnlijk hebben die databases verschillende instellingen voor collation (collatie, sorteervolgorde?)

select
   schema_name,
   default_character_set_name,
   default_collation_name
from
   information_schema.schemata;

Verrassend: Alle eigen databases lijken dezelfde collation te hebben:

schema_name          default_character_set_name   default_collation_name
------------------   --------------------------   ----------------------
mysql	             utf8mb4	                  utf8mb4_0900_ai_ci
information_schema   utf8	                  utf8_general_ci
performance_schema   utf8mb4	                  utf8mb4_0900_ai_ci
sys	             utf8mb4	                  utf8mb4_0900_ai_ci
example_cm	     utf8mb4	                  utf8mb4_0900_ai_ci
...       	     utf8mb4	                  utf8mb4_0900_ai_ci

Collation op tabel-niveau

Volgende stap: Wat is de collision voor de verschillende tabellen? Query

select
   table_schema,
   table_name,
   engine,
   table_collation
from 
   information_schema.tables
where
   (
      table_schema = "knl_s1"
      or
      table_schema = "dwh_org"
   )
   and
   (
      table_name = "wp_posts"
      or
      table_name = "wp_postmeta"
      or
      table_name = "oem_bal"
   );

Levert op:

table_schema   table_name   engine   table_collation
------------   ----------   ------   ---------------
dwh_org        oem_bal      InnoDB   utf8mb4_0900_ai_ci
knl_s1         wp_postmeta  InnoDB   utf8mb4_unicode_520_ci
knl_s1         wp_posts     InnoDB   utf8mb4_unicode_520_ci

Oplossing:

alter table
   knl_s1.wp_postmeta
   collate = utf8mb4_0900_ai_ci;
    
alter table
   knl_s1.wp_posts
   collate = utf8mb4_0900_ai_ci;

Collation op kolom-niveau

Argh, de foutmelding is er nog steeds. Misschien dat dit op veld-niveau gefixet moet worden. Nieuw voor mij: Dit kun je gemakkelijk achterhalen in MySQL Workbench door met de rechtermuistoets op een entiteit (db, tabel) te klikken en te kiezen ... inspector:

knl_s1.wp_postmeta.meta_value   utf8mb4_unicode_520_ci
knl_s1.wp_posts.meta_value      utf8mb4_unicode_520_ci

Oplossing:

alter table
   knl_s1.wp_postmeta
   change column
      meta_key
      meta_key
         VARCHAR(255) 
         CHARACTER SET 'utf8mb4' 
         COLLATE 'utf8mb4_0900_ai_ci' 
         NULL 
         DEFAULT NULL;

alter table
   knl_s1.wp_postmeta
   change column
      meta_value
      meta_value
         LONGTEXT 
         CHARACTER SET 'utf8mb4' 
         COLLATE 'utf8mb4_0900_ai_ci' 
         NULL 
         DEFAULT NULL;

P.s.

Ongetwijfeld bestaat er een manier om in één keer de collation van alle velden in een database aan te passen. Lijkt me een prima vraagstuk, volgende keer als ik deze foutmelding krijg.

Casus: Migratie MySQL naar Maria DB (sep. 2021)

Bij het importeren van een MySQL-database-dump in MariaDB kreeg ik de foutmelding dat MariaDB niet overweg kan met collatie utf8mb4_0900_ai_ci. Het betrof een WordPress-database. Een drietal kolommen in tabel wp_posts bleken deze codering te hebben. Mbv. sed heb ik dit aangepast in het SQL-bestand. Maar dat moet grondiger kunnen. Daarnaast roept het de vraag op, welke collaties ik wil aanhouden, want deze casus lijkt niet gedekt te zijn door het statement aan het begin van dit artikel.

Ik ben niet de enige met dit probleem: [7]

Oplossing: Dumpfile aanpassen

Met sed aanpassen in dumpfile. Zie sed » Casus: String vervangen in MySQL-dump (sep. 2021) voor details:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_520_ci/' en_s1.sql

Oplossing: Tabel-definitie aanpassen

Heel gemakkelijk: In de betreffende bron-database de collatie-specificatie weglaten in de definitie van de betreffende rijen.

Dat lukt niet via MySQL Workbench. Dan krijg je foutieve code zoals:

ALTER TABLE `en_s1`.`wp_posts` 
CHANGE COLUMN `nl` `nl` VARCHAR(255) COLLATE 'Default Collation' NULL DEFAULT NULL;

Wat wél werkt: Het collatie-statement weglaten:

ALTER TABLE `en_s1`.`wp_posts` 
CHANGE COLUMN `nl` `nl` VARCHAR(255) NULL DEFAULT NULL;

Nu krijgt deze rij Default voor tekecondering en collatie.

Zie ook

Bronnen