Karaktersets & collation (MySQL)
Ik gebruik deze parameters
- Tekencodering:
utf8mb4
- Collatie:
utf8mb4_general_ci
- andersgeneral_ci
.
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, 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.
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)
Wat ik gebruik
In MySQL werk ik met deze instellingen:
- Tekencodering:
utf8mb4
- andersutf8
- Collation:
utf8mb4_general_ci
- andersgeneral_ci
.
SQL:
create schema blub default character set utf8mb4 collate 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;
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) [2]
- Ondersteund geen additionele planes (Supplementary planes)
- Multibyte characters: Max. 3 bytes/character.
De essentie
utf8mb3
is deprecated. Gebruik voortaan alleen nogutf8mb4
[3].- 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.
Zie ook
- Create database (MySQL)
- Strings, bytes, karakters & codering (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