Karaktersets & collation (MySQL)

Amazon en Bol.com gebruiken uitsluitend ISO-8850-1 (Latin-1)-karaktersets voor importbestanden. Ik hoef dus enkel in die karakterset te exporteren.

In MySQL wordt de gebruikte karakterset op diverse plekken gespecificeerd:

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

Het is goed mogelijk om in een tabel verschillende kolommen in verschillende tekencoderingen te hebben. Sterker nog: Dat overkomt mij voortdurend.

De oplossing - Wat

• Intern met UTF-8 werken, want open source. Drupal gebruikt dit ook - utf8 - default collation om precies te zijn
• Tijdens exportprocedures wordt meestal een tabel aangemaakt zoals amazon_export. Als ik direct na aanmaken van deze tabel zorg dat-ie 100% Latin 1 is, ben ik klaar.

De oplossing - Hoe

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

Zie ook

• Strings, bytes, karakters & codering (MySQL)
• Tekencodering achterhalen

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