Karaktersets & collation (MySQL)
![](/images/thumb/8/80/20200620-1834.png/300px-20200620-1834.png)
Always use these settings
- Character encoding:
utf8mb4
- Collation:
utf8mb4_unicode_520_ci
.
I use these settings since I moved from MySQL to MariaDB, somewhere half 2021. These are the best settings that are compatible between these two RDBMSs. See further in the article for details.
I try to specify encoding & collation as little as possible in databases: Otherwise I have to change these in sql-dumps, before importing an MySQL-database into MariaDB, as they mostly seem to have incompatible sets of collation. Changes are, that they don't need to be specified as all.
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, graphemes → digraphs & trigraphs.
Waarom collatie voor mij relevant is: Er zijn allerlei handelingen (bv. een join) die je niet kunt verrichten met objecten met verschillende collatie. Dit is overigens de enige reden waarom ik me ben gaan verdiepen in deze materie.
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 server-niveau
- 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.
Karaktersets
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.
Gebruik altijd 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.
Collaties
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)
Collatie-parameters
Er zijn een handjevol parameters die bij elkaar een collatie vormen:
Sorteeralgoritme
De volgende sorteeralgoritmes zijn in omploop, in volgorde van slechtst naar best:
general
: Heel efficiënt, maar maakt daardoor fouten. Die efficiëntie speelt op moderne hardware geen rol. Slechte keuzeunicode
: Goed maar oudunicode 520
: Beter - De aanduidingunicode 520
heeft betrekking op de Unicode 5.2-versie van het Unicode-sorteerprotocol0900
: Best. Deze bestaat helaas niet voor MariaDB. Geen idee waarom.
daarnaast is er een algoritme aangeduid met bin
. Deze heeft betrekking op binaire vergelijking - Alleen relevant in sommige exotische situaties. Lijkt me niet relevant.
ad & ai
ad
: Er wordt onderscheid gemaakt tussen tekens met of zonder accentai
: accent indifference: Bv. e, è en é worden qua collatie niet als dezelfde letter beschouwd- Ik denk dat ik een lichte voorkeur heb voor accent difference.
cs & ci
cs
: case sensitiveci
: case insensitive: Bv. A en a worden qua collatie als dezelfde letter beschouwd- Dit onderscheid bestaat op MySQL, maar niet op MariaDB: Daar is alles ci
- Voorkeur: ci.
PAD & PAD SPACE
PAD
: Trailing spaces worden niet meegenomen in sorterenPAD SPACE
: Trailing spaces worden meegenomen in sorteren.
Voorbeelden
MySQL is rond 2017 overgestapt van utf8mb4_general_ci
naar utf8mb4_0900_ai_ci
. Dat verklaar waarom ik ogenschijnlijk totaal verschillende standaard-collaties hanteer. Enkele collaties die ik in de praktijk tegenkom:
utf8mb4_0900_ai_ci
- Dit schijnt een prima keuze te zijn op MySQL [2]
- Deze collatie bestaat niet op MariaDB.
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. Het is beter dan utf8mb4_general_ci
, maar er bestaan betere en nieuwere collaties [4].
Selectie
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
Selectie, van goed naar minder goed:
- utf8mb4_unicode_520_ci ← Beste
- utf8mb4_unicode_ci
- utf8mb4_general_ci
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 heb een voorkeur voor as en ci. Blijft er over:
utf8mb4_0900_as_ci
MariaDB + MySQL
Voor databases die in beide omgevingen moeten kunnen functioneren:
- utf8mb4_unicode_520_ci ← Beste
- utf8mb4_unicode_ci
- utf8mb4_general_ci
Coderingen uitlezen
Achterhalen van de huidige instellingen. Aan de namen kun je zien of ze betrekking hebben op de server, client, of wat dan ook:
show variables where variable_name like "%character\_set%" or variable_name like "%collation%"; +-------------------------------+----------------------------+ | Variable_name | Value | +-------------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8mb3 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | utf8mb4_0900_ai_ci | | collation_database | utf8mb4_0900_ai_ci | | collation_server | utf8mb4_0900_ai_ci | | default_collation_for_utf8mb4 | utf8mb4_0900_ai_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;
Instellingen op server-niveau
Waarschijnlijk handig om de juiste teken- & collatie-codering op server-niveau in te stellen, zodat alle onderliggende databases en -objecten automatisch de juiste standaard-instellingen krijgen
- https://www.mysqltutorial.org/mysql-collation
- https://stackoverflow.com/questions/3513773/change-mysql-default-character-set-to-utf-8-in-my-cnf
Codering database uitlezen
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "mydatabase";
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
Casus: 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 nogutf8mb4
[6].- Specificeer expliciet
utf8mb4
- Collation:
collate utf8mb4_0900_ai_ci
- Uitleg volgt nog.
Bronnen
- https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html
- https://en.wikipedia.org/wiki/Plane_(Unicode)
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
- Create database (MySQL)
- MySQL vs. MariaDB
- Strings, bytes, karakters & codering (MySQL)
- Systeemvariabelen (MySQL)
- Tabel aanmaken (MySQL)
- Tekencodering achterhalen
- Tekencodering (MySQL)
Bronnen
- http://mysql.rjweb.org/doc.php/charcoll → Leuk achtergrondartikel + practisch, maar beetje oud
- https://docs.python.org/2/howto/unicode.html → Leuk achtergrondartikel
- http://www.whitesmith.co/blog/latin1-to-utf8/ → Leuk geschreven en mooie vormgeving
- http://www.garethsprice.com/blog/2011/fix-mysql-latin1-utf-character-encoding/
- http://blog.codekills.net/2012/03/20/in-mysql-latin1-isnt-actually-latin1/ → Oei, moeilijk!
- http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html
- http://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-mysql-database-table-column-is
- https://www.monolune.com/what-is-the-utf8mb4_0900_ai_ci-collation/
- https://mariadb.com/kb/en/supported-character-sets-and-collations/
- https://dba.stackexchange.com/questions/248904/mysql-to-mariadb-unknown-collation-utf8mb4-0900-ai-ci
- https://stackoverflow.com/questions/47566730/force-mariadb-clients-to-use-utf8mb4
- https://stackoverflow.com/questions/16082480/what-is-the-purpose-of-character-set-connection