Karaktersets & collation (MySQL): verschil tussen versies
(71 tussenliggende versies door dezelfde gebruiker niet weergegeven) | |||
Regel 3: | Regel 3: | ||
[[file:20190612-1053.png|thumb|Coderingen die MySQL Workbench biedt bij aanmaak van een nieuwe database op m'n laptop in 2019 (2)]] | [[file:20190612-1053.png|thumb|Coderingen die MySQL Workbench biedt bij aanmaak van een nieuwe database op m'n laptop in 2019 (2)]] | ||
− | ''' | + | '''Always use these settings''' |
− | * | + | * Character encoding: <code>utf8mb4</code> |
− | * | + | * Collation: <code>utf8mb4_unicode_520_ci</code>. |
+ | 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?''' | + | ''' 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. | 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? ''' |
''Collation'' (''collatie'' in het Nederlands [https://en.wiktionary.org/wiki/collatie]) 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: [https://en.wikipedia.org/wiki/Glyph glyphs], [https://en.wikipedia.org/wiki/Orthographic_ligature ligaturen], [https://en.wikipedia.org/wiki/Diacritic diacritics], [https://en.wikipedia.org/wiki/Grapheme graphemes] → [https://examples.yourdictionary.com/reference/examples/digraph-examples.html digraphs] & [https://en.wikipedia.org/wiki/Trigraph_(orthography) trigraphs]. | ''Collation'' (''collatie'' in het Nederlands [https://en.wiktionary.org/wiki/collatie]) 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: [https://en.wikipedia.org/wiki/Glyph glyphs], [https://en.wikipedia.org/wiki/Orthographic_ligature ligaturen], [https://en.wikipedia.org/wiki/Diacritic diacritics], [https://en.wikipedia.org/wiki/Grapheme graphemes] → [https://examples.yourdictionary.com/reference/examples/digraph-examples.html digraphs] & [https://en.wikipedia.org/wiki/Trigraph_(orthography) trigraphs]. | ||
− | Waarom collatie voor mij relevant is: Er zijn allerlei handelingen (bv. een join) die je niet kunt verrichten met objecten met verschillende collatie. | + | 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. | MySQL behandelt karaktersets en collation vaak tezamen. Daarom ook in dit artikel. | ||
− | ''' Multi-level markeringen''' | + | {| |
+ | |[[file:20211219-1540.png|thumb|Collatie in de praktijk (1): Deze lijst komt uit Nemo]] | ||
+ | |[[file:20211219-1541.png|thumb|Collatie in de praktijk (2): Dezelfde lijst, maar nu in Bash. Merk op dat de sorteervolgorde afwijkt]] | ||
+ | |} | ||
+ | |||
+ | ''' Multi-level markeringen? ''' | ||
In MySQL wordt de gebruikte karakterset op diverse plekken gespecificeerd: | In MySQL wordt de gebruikte karakterset op diverse plekken gespecificeerd: | ||
+ | * Op server-niveau | ||
* Op database-niveau | * Op database-niveau | ||
* Op tabelniveau | * Op tabelniveau | ||
Regel 33: | Regel 40: | ||
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. | 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 === |
− | M'n laptop - Sep. 2021 | + | '''MySQL Server 8.0.25-0ubuntu0.20.04.1 - M'n laptop - Sep. 2021''' |
<pre> | <pre> | ||
Regel 89: | Regel 96: | ||
</pre> | </pre> | ||
− | + | '''MariaDB 10.3.31-MariaDB-0ubuntu0.20.04.1 - Nieuwe server ''dvb8'' - Sep. 2021''' | |
− | |||
− | Nieuwe server ''dvb8'' - Sep. 2021 | ||
<pre> | <pre> | ||
Regel 144: | Regel 149: | ||
Merk op dat MariaDB één rij minder heeft dan MySQL. | 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). | 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). | ||
Regel 150: | Regel 155: | ||
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. | 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 === |
− | Dit betreft m'n laptop - Sep. 2021 | + | '''MySQL Server 8.0.25-0ubuntu0.20.04.1 - Dit betreft m'n laptop - Sep. 2021''' |
<pre> | <pre> | ||
Regel 240: | Regel 245: | ||
</pre> | </pre> | ||
− | + | ''' MariaDB 10.3.31-MariaDB-0ubuntu0.20.04.1 - Nieuwe server ''dvb8'' - Sep. 2021''' | |
− | |||
− | Nieuwe server ''dvb8'' - Sep. 2021 | ||
<pre> | <pre> | ||
Regel 286: | Regel 289: | ||
</pre> | </pre> | ||
− | == | + | === 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: | |
− | * | + | * <code>general</code>: Heel efficiënt, maar maakt daardoor fouten. Die efficiëntie speelt op moderne hardware geen rol. Slechte keuze |
− | * <code> | + | * <code>unicode</code>: Goed maar oud |
− | * <code> | + | * <code>unicode 520</code>: Beter - De aanduiding <code>unicode 520</code> heeft betrekking op de ''Unicode 5.2''-versie van het Unicode-sorteerprotocol |
− | * <code> | + | * <code>0900</code>: Best. Deze bestaat helaas niet voor MariaDB. Geen idee waarom. |
− | + | daarnaast is er een algoritme aangeduid met <code>bin</code>. Deze heeft betrekking op binaire vergelijking - Alleen relevant in sommige exotische situaties. Lijkt me niet relevant. | |
− | * | + | ''' ad & ai ''' |
− | * | + | |
+ | * <code>ad</code>: Er wordt onderscheid gemaakt tussen tekens met of zonder accent | ||
+ | * <code>ai</code>: ''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 ''' | ||
− | + | * <code>cs</code>: ''case sensitive'' | |
+ | * <code>ci</code>: ''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 ''' | |
− | + | * <code>PAD</code>: Trailing spaces worden niet meegenomen in sorteren | |
+ | * <code>PAD SPACE</code>: Trailing spaces worden meegenomen in sorteren. | ||
− | + | === Voorbeelden === | |
− | + | MySQL is rond 2017 overgestapt van <code>utf8mb4_general_ci</code> naar <code>utf8mb4_0900_ai_ci</code>. 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 [https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci] | |
+ | * Deze collatie bestaat niet op MariaDB. | ||
− | + | ''' utf8mb4_general_ci ''' | |
− | + | ||
+ | <code>utf8mb4_general_ci</code> is de standaard-collatie op m'n MariaDB-server. Volgens [https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci] is dat een beroerde implementatie van <code>utf8mb4_unicode_ci</code>. | ||
− | + | ''' utf8mb4_unicode_ci ''' | |
− | + | Dit is de officiële en goede implementatie van een oude sorteerstandaard van UTF. Het is beter dan <code>utf8mb4_general_ci</code>, maar er bestaan betere en nieuwere collaties [https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci]. | |
− | + | === Selectie === | |
− | |||
− | |||
− | |||
− | + | ''' MariaDB ''' | |
Ik sla land- of taalspecifieke collaties over. Blijft er over: | Ik sla land- of taalspecifieke collaties over. Blijft er over: | ||
Regel 353: | Regel 364: | ||
</pre> | </pre> | ||
− | + | Selectie, van goed naar minder goed: | |
# utf8mb4_unicode_520_ci ← Beste | # utf8mb4_unicode_520_ci ← Beste | ||
Regel 359: | Regel 370: | ||
# utf8mb4_general_ci | # utf8mb4_general_ci | ||
− | + | ''' MySQL ''' | |
+ | |||
+ | Eerste stap: Alleen land-onafhankelijke <code>0900</code>-collaties en geen gedoe met <code>bin</code>: | ||
+ | |||
+ | * utf8mb4_0900_ai_ci | ||
+ | * utf8mb4_0900_as_ci | ||
+ | * utf8mb4_0900_as_cs. | ||
− | + | Ik heb een voorkeur voor ''as'' en ''ci''. Blijft er over: | |
− | + | <code> | |
+ | utf8mb4_0900_as_ci | ||
+ | </code> | ||
− | + | ''' 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: | ||
<pre> | <pre> | ||
− | + | 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 | | ||
+ | +-------------------------------+----------------------------+ | ||
</pre> | </pre> | ||
Regel 391: | Regel 431: | ||
collate = utf8_general_ci; | collate = utf8_general_ci; | ||
</pre> | </pre> | ||
+ | |||
+ | == 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 == | == Codering database uitlezen == | ||
<pre> | <pre> | ||
− | SELECT default_character_set_name | + | SELECT |
− | FROM information_schema.SCHEMATA | + | default_character_set_name |
− | WHERE schema_name = " | + | FROM |
+ | information_schema.SCHEMATA | ||
+ | WHERE | ||
+ | schema_name = "mydatabase"; | ||
</pre> | </pre> | ||
Regel 460: | Regel 510: | ||
ALTER TABLE mytable CONVERT TO CHARACTER SET latin1 | ALTER TABLE mytable CONVERT TO CHARACTER SET latin1 | ||
− | == Waarschuwing: UTF8MB3-alias (zomer 2020) == | + | == Casus: Waarschuwing: UTF8MB3-alias (zomer 2020) == |
=== Het probleem === | === Het probleem === | ||
Regel 619: | Regel 669: | ||
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. | 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. | ||
+ | |||
+ | == Case: Collation error (2023.07) == | ||
+ | |||
+ | As before, except now concerning two tables (<code>device</code> and <code>device_product</code>) in the same database. Error: | ||
+ | |||
+ | <pre> | ||
+ | select device_product.device_id from device_product join device using (device_id) LIMIT 0, 5000 | ||
+ | |||
+ | Error Code: 1267. Illegal mix of collations | ||
+ | (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) | ||
+ | for operation '=' | ||
+ | </pre> | ||
+ | |||
+ | === Collation at database level === | ||
+ | |||
+ | <pre> | ||
+ | select | ||
+ | schema_name, | ||
+ | default_character_set_name, | ||
+ | default_collation_name | ||
+ | from | ||
+ | information_schema.schemata | ||
+ | where | ||
+ | schema_name="dwh"; | ||
+ | </pre> | ||
+ | |||
+ | So far so good: | ||
+ | |||
+ | <pre> | ||
+ | +-------------+----------------------------+------------------------+ | ||
+ | | schema_name | default_character_set_name | default_collation_name | | ||
+ | +-------------+----------------------------+------------------------+ | ||
+ | | dwh | utf8mb4 | utf8mb4_unicode_520_ci | | ||
+ | +-------------+----------------------------+------------------------+ | ||
+ | </pre> | ||
+ | |||
+ | === Collation at table level === | ||
+ | |||
+ | <pre> | ||
+ | select | ||
+ | table_schema, | ||
+ | table_name, | ||
+ | engine, | ||
+ | table_collation | ||
+ | from | ||
+ | information_schema.tables | ||
+ | where | ||
+ | table_schema = "dwh"; | ||
+ | </pre> | ||
+ | |||
+ | Result: A mixture of collations: | ||
+ | |||
+ | * <code>utf8mb4_unicode_520_ci</code> | ||
+ | * <code>utf8_general_ci</code> | ||
+ | * <code>latin1_swedish_ci</code>. | ||
+ | |||
+ | For these two tables specifically: | ||
+ | |||
+ | <pre> | ||
+ | select | ||
+ | table_schema, | ||
+ | table_name, | ||
+ | engine, | ||
+ | table_collation | ||
+ | from | ||
+ | information_schema.tables | ||
+ | where | ||
+ | table_schema = "dwh" | ||
+ | and | ||
+ | ( | ||
+ | table_name="device" | ||
+ | or | ||
+ | table_name="device_product" | ||
+ | ); | ||
+ | </pre> | ||
+ | |||
+ | Results: | ||
+ | |||
+ | <pre> | ||
+ | +--------------+----------------+--------+------------------------+ | ||
+ | | table_schema | table_name | engine | table_collation | | ||
+ | +--------------+----------------+--------+------------------------+ | ||
+ | | dwh | device | InnoDB | utf8mb4_unicode_520_ci | | ||
+ | | dwh | device_product | InnoDB | utf8mb4_unicode_520_ci | | ||
+ | +--------------+----------------+--------+------------------------+ | ||
+ | </pre> | ||
+ | |||
+ | Updating the collation of all tables: Use this code to generate the required code. I used to do this partially in an editor, but all code can be generated at once directly in SQL: | ||
+ | |||
+ | <pre> | ||
+ | # Generate code for adjusting all tables | ||
+ | ######################################## | ||
+ | # | ||
+ | # * Filter for "BASE TABLE": To exclude | ||
+ | # views | ||
+ | # * When altering a column, you need to | ||
+ | # include the field definition. You | ||
+ | # don't need to include anything when | ||
+ | # changing the collation of a table, | ||
+ | # as you can see here | ||
+ | # * "table_schema": Database name | ||
+ | # | ||
+ | select | ||
+ | concat | ||
+ | ( | ||
+ | "alter table ", table_schema, '.', table_name, | ||
+ | " collate utf8mb4_unicode_520_ci;" | ||
+ | ) | ||
+ | as execute_string | ||
+ | from | ||
+ | information_schema.tables | ||
+ | where | ||
+ | table_schema="dwh" | ||
+ | and | ||
+ | table_type="BASE TABLE"; | ||
+ | </pre> | ||
+ | |||
+ | === Collation at column level === | ||
+ | |||
+ | Finally: | ||
+ | <pre> | ||
+ | select | ||
+ | table_name, | ||
+ | column_name, | ||
+ | character_set_name, | ||
+ | collation_name | ||
+ | from | ||
+ | information_schema.columns | ||
+ | where | ||
+ | table_schema="dwh" | ||
+ | and | ||
+ | ( | ||
+ | table_name="device" | ||
+ | or | ||
+ | table_name="device_product" | ||
+ | ) | ||
+ | and | ||
+ | column_name="device_id" | ||
+ | </pre> | ||
+ | |||
+ | Output: | ||
+ | |||
+ | <pre> | ||
+ | +----------------+-------------+--------------------+------------------------+ | ||
+ | | table_name | column_name | character_set_name | collation_name | | ||
+ | +----------------+-------------+--------------------+------------------------+ | ||
+ | | device | device_id | utf8mb4 | utf8mb4_general_ci | | ||
+ | | device_product | device_id | utf8mb4 | utf8mb4_unicode_520_ci | | ||
+ | +----------------+-------------+--------------------+------------------------+ | ||
+ | </pre> | ||
+ | |||
+ | === Update collation for all columns === | ||
+ | |||
+ | This code generates the code for updating the collation of all columns in the given database (<code>dwh</code>). Execution of the resulting code, took about 45 minutes: | ||
+ | |||
+ | <pre> | ||
+ | # Generate code to update all columns | ||
+ | ######################################## | ||
+ | # | ||
+ | # * When changing a columnm, you have to | ||
+ | # include a field definition. Field | ||
+ | # "column_type" already has the current | ||
+ | # definition, so that can easily be | ||
+ | # reused | ||
+ | # * Only text fields have a collation. | ||
+ | # That's why there is a filter on | ||
+ | # "varchar" and "%text": To filter out | ||
+ | # numerical fields and data fields | ||
+ | # (No other kinds of fields are being | ||
+ | # used) | ||
+ | # | ||
+ | select | ||
+ | concat | ||
+ | ( | ||
+ | "alter table dwh.", table_name, | ||
+ | " change column ", column_name, " ", column_name, | ||
+ | " ", column_type, | ||
+ | " collate utf8mb4_unicode_520_ci;" | ||
+ | ) | ||
+ | as execute_string | ||
+ | from | ||
+ | information_schema.columns | ||
+ | where | ||
+ | table_schema="dwh" | ||
+ | and | ||
+ | ( | ||
+ | data_type = "varchar" | ||
+ | or | ||
+ | data_type like "%text" | ||
+ | ) | ||
+ | and | ||
+ | table_type="BASE TABLE"; | ||
+ | </pre> | ||
== Casus: Migratie MySQL naar Maria DB (sep. 2021) == | == 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 <code>utf8mb4_0900_ai_ci</code>. Het betrof een WordPress-database. Een drietal kolommen in tabel <code>wp_posts</code> bleken deze codering te hebben. Mbv. [[Sed | 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. | Bij het importeren van een MySQL-database-dump in MariaDB kreeg ik de foutmelding dat MariaDB niet overweg kan met collatie <code>utf8mb4_0900_ai_ci</code>. Het betrof een WordPress-database. Een drietal kolommen in tabel <code>wp_posts</code> bleken deze codering te hebben. Mbv. [[Sed | 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: [https://dba.stackexchange.com/questions/248904/mysql-to-mariadb-unknown-collation-utf8mb4-0900-ai-ci] | ||
+ | |||
+ | === Oplossing: Dumpfile aanpassen === | ||
+ | |||
+ | Met ''sed'' aanpassen in dumpfile. Zie [[Sed#Casus: String vervangen in MySQL-dump (sep. 2021) | sed » Casus: String vervangen in MySQL-dump (sep. 2021)]] voor details: | ||
+ | |||
+ | <pre> | ||
+ | sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_520_ci/' en_s1.sql | ||
+ | </pre> | ||
+ | |||
+ | === 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: | ||
+ | |||
+ | <pre> | ||
+ | ALTER TABLE `en_s1`.`wp_posts` | ||
+ | CHANGE COLUMN `nl` `nl` VARCHAR(255) COLLATE 'Default Collation' NULL DEFAULT NULL; | ||
+ | </pre> | ||
+ | |||
+ | Wat wél werkt: Het collatie-statement weglaten: | ||
+ | |||
+ | <pre> | ||
+ | ALTER TABLE `en_s1`.`wp_posts` | ||
+ | CHANGE COLUMN `nl` `nl` VARCHAR(255) NULL DEFAULT NULL; | ||
+ | </pre> | ||
+ | Nu krijgt deze rij <code>Default</code> voor tekecondering en collatie. | ||
== Zie ook == | == Zie ook == | ||
* [[Create database (MySQL)]] | * [[Create database (MySQL)]] | ||
+ | * [[MySQL vs. MariaDB]] | ||
* [[Strings, bytes, karakters & codering (MySQL)]] | * [[Strings, bytes, karakters & codering (MySQL)]] | ||
+ | * [[Systeemvariabelen (MySQL)]] | ||
* [[Tabel aanmaken (MySQL)]] | * [[Tabel aanmaken (MySQL)]] | ||
* [[Tekencodering achterhalen]] | * [[Tekencodering achterhalen]] | ||
Regel 642: | Regel 916: | ||
* http://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-mysql-database-table-column-is | * 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://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 | ||
+ | * https://stackoverflow.com/questions/10859966/how-to-convert-all-tables-in-database-to-one-collation |
Huidige versie van 19 jul 2023 om 13:02
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.
Case: Collation error (2023.07)
As before, except now concerning two tables (device
and device_product
) in the same database. Error:
select device_product.device_id from device_product join device using (device_id) LIMIT 0, 5000 Error Code: 1267. Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
Collation at database level
select schema_name, default_character_set_name, default_collation_name from information_schema.schemata where schema_name="dwh";
So far so good:
+-------------+----------------------------+------------------------+ | schema_name | default_character_set_name | default_collation_name | +-------------+----------------------------+------------------------+ | dwh | utf8mb4 | utf8mb4_unicode_520_ci | +-------------+----------------------------+------------------------+
Collation at table level
select table_schema, table_name, engine, table_collation from information_schema.tables where table_schema = "dwh";
Result: A mixture of collations:
utf8mb4_unicode_520_ci
utf8_general_ci
latin1_swedish_ci
.
For these two tables specifically:
select table_schema, table_name, engine, table_collation from information_schema.tables where table_schema = "dwh" and ( table_name="device" or table_name="device_product" );
Results:
+--------------+----------------+--------+------------------------+ | table_schema | table_name | engine | table_collation | +--------------+----------------+--------+------------------------+ | dwh | device | InnoDB | utf8mb4_unicode_520_ci | | dwh | device_product | InnoDB | utf8mb4_unicode_520_ci | +--------------+----------------+--------+------------------------+
Updating the collation of all tables: Use this code to generate the required code. I used to do this partially in an editor, but all code can be generated at once directly in SQL:
# Generate code for adjusting all tables ######################################## # # * Filter for "BASE TABLE": To exclude # views # * When altering a column, you need to # include the field definition. You # don't need to include anything when # changing the collation of a table, # as you can see here # * "table_schema": Database name # select concat ( "alter table ", table_schema, '.', table_name, " collate utf8mb4_unicode_520_ci;" ) as execute_string from information_schema.tables where table_schema="dwh" and table_type="BASE TABLE";
Collation at column level
Finally:
select table_name, column_name, character_set_name, collation_name from information_schema.columns where table_schema="dwh" and ( table_name="device" or table_name="device_product" ) and column_name="device_id"
Output:
+----------------+-------------+--------------------+------------------------+ | table_name | column_name | character_set_name | collation_name | +----------------+-------------+--------------------+------------------------+ | device | device_id | utf8mb4 | utf8mb4_general_ci | | device_product | device_id | utf8mb4 | utf8mb4_unicode_520_ci | +----------------+-------------+--------------------+------------------------+
Update collation for all columns
This code generates the code for updating the collation of all columns in the given database (dwh
). Execution of the resulting code, took about 45 minutes:
# Generate code to update all columns ######################################## # # * When changing a columnm, you have to # include a field definition. Field # "column_type" already has the current # definition, so that can easily be # reused # * Only text fields have a collation. # That's why there is a filter on # "varchar" and "%text": To filter out # numerical fields and data fields # (No other kinds of fields are being # used) # select concat ( "alter table dwh.", table_name, " change column ", column_name, " ", column_name, " ", column_type, " collate utf8mb4_unicode_520_ci;" ) as execute_string from information_schema.columns where table_schema="dwh" and ( data_type = "varchar" or data_type like "%text" ) and table_type="BASE TABLE";
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
- https://stackoverflow.com/questions/10859966/how-to-convert-all-tables-in-database-to-one-collation