Thomas Wiedmann https://twiedmann.de
• Sie befinden sich hier: Startseite > SQL-Backstube > MySQL Encoding Check

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

13.11.2010: MySQL Encoding Check

Problemstellung

Durch die Globalisierung werden immer häufiger die Daten UTF-8 kodiert gespeichert. Das früher häufig verwendete Latin1 wird seltener. Das Problem ist nun der "mischmasch" von Zeichensätzen. Jeder kennt wahrscheinlich das T-Shirt mit dem "Schei? Encoding" Spruch. Ein Klassiker!


Um Probleme wie diese "Warning: #1366 Incorrect string value ..." zu vermeiden, hilft (hoffentlich) folgender Encoding-Check.


Getestet mit folgender Datenbank:

* MySQL 5.1.30


MYSQL SERVER KONFIGURATION

Schauen wir uns die Server-System-Variablen an. Achtung, die System-Variablen existieren als SESSION und GLOBAL Version. Beide müssen geprüft werden. Beginnen wir mit dem CHARSET:


SHOW VARIABLES 'char%'

Listing 1:

  1. mysql> show session variables like 'char%';
  2. +--------------------------+----------------------------------------------+
  3. | Variable_name | Value |
  4. +--------------------------+----------------------------------------------+
  5. | character_set_client | latin1 |
  6. | character_set_connection | latin1 |
  7. | character_set_database | latin1 |
  8. | character_set_filesystem | binary |
  9. | character_set_results | latin1 |
  10. | character_set_server | latin1 |
  11. | character_set_system | utf8 |
  12. | character_sets_dir | G:\MySQL\MySql-Server-5.1.30\share\charsets\ |
  13. +--------------------------+----------------------------------------------+
  14. 8 rows in set (0.01 sec)
  15. mysql> show global variables like 'char%';
  16. +--------------------------+----------------------------------------------+
  17. | Variable_name | Value |
  18. +--------------------------+----------------------------------------------+
  19. | character_set_client | latin1 |
  20. | character_set_connection | latin1 |
  21. | character_set_database | latin1 |
  22. | character_set_filesystem | binary |
  23. | character_set_results | latin1 |
  24. | character_set_server | latin1 |
  25. | character_set_system | utf8 |
  26. | character_sets_dir | G:\MySQL\MySql-Server-5.1.30\share\charsets\ |
  27. +--------------------------+----------------------------------------------+
  28. 8 rows in set (0.01 sec)

SHOW VARIABLES 'coll%'

Neben dem CHARSET gibt es auch noch die Sortierung (Collation).


Listing 2:

  1. mysql> show session variables like 'coll%';
  2. +----------------------+-------------------+
  3. | Variable_name | Value |
  4. +----------------------+-------------------+
  5. | collation_connection | latin1_swedish_ci |
  6. | collation_database | latin1_swedish_ci |
  7. | collation_server | latin1_swedish_ci |
  8. +----------------------+-------------------+
  9. 3 rows in set (0.00 sec)
  10. mysql> show global variables like 'coll%';
  11. +----------------------+-------------------+
  12. | Variable_name | Value |
  13. +----------------------+-------------------+
  14. | collation_connection | latin1_swedish_ci |
  15. | collation_database | latin1_swedish_ci |
  16. | collation_server | latin1_swedish_ci |
  17. +----------------------+-------------------+
  18. 3 rows in set (0.00 sec)

Die MySQL Konfiguration haben wir nun geprüft. Aber neben der Server-Konfiguration können auch einzelne Tabellenspalten ein spezielles Character Set zugewiesen bekommen. Wie kann dies validiert werden?


MySQL INFORMATION_SCHEMA

Hinter dem INFORMATION_SCHEMA verstecken sich die Metadaten der MySQL-Datenbank. Hier sind die Tabellenstrukturen und vieles mehr hinterlegt. Das schönste dabei ist, das Information_schema basiert selbst auf Tabellen, die mit SQL abgefragt werden können.

Listing 3:

  1. /**
  2. * Welche CHARSET sind auf Spaltenebene definiert?
  3. */
  4. SELECT character_set_name, COUNT(*) AS anzahl
  5. FROM information_schema.columns
  6. WHERE character_set_name IS NOT NULL
  7. GROUP BY character_set_name;
  8. +--------------------+--------+
  9. | character_set_name | anzahl |
  10. +--------------------+--------+
  11. | latin1 | 720 |
  12. | utf8 | 514 |
  13. +--------------------+--------+
  14. 2 rows in set (3.83 sec)

Also bei vielen Spalten ist explizit der Charset latin1 bzw. utf8 angegeben. In welchen Tabellen und Spalten ist nun latin1 definiert?

Listing 4:

  1. /**
  2. * Welcher Tabellenspalte ist explizit 'latin1' zugewiesen?
  3. */
  4. SELECT table_catalog, table_schema, table_name, column_name
  5. FROM information_schema.columns
  6. WHERE character_set_name = 'latin1'
  7. ORDER BY table_catalog, table_schema, table_name, column_name;
  8. +---------------+--------------+------------------+-------------+
  9. | table_catalog | table_schema | table_name | column_name |
  10. +---------------+--------------+------------------+-------------+
  11. | NULL | lager | wx1 | comment |
  12. | NULL | lager | wx1 | link |
  13. | NULL | lager | wx1 | node |
  14. ...
  15. 720 rows in set (0.17 sec)

In diesen Tabellen und Spalten hat sich somit noch ein "latin1" versteckt.


Zeichensatz konvertieren

Ab MySQL Version 5 kann dies beispielweise so erfolgen.


Listing 5:

  1. mysql> ALTER TABLE wx1 CONVERT TO CHARACTER SET utf8;

Detailierte Informationen (englisch) was beim Umstellen des Zeichensatzes zu beachten ist, gibt es hier: MySQL Performance Blog




Sitemap - Inhaltsverzeichnis

© 2002-2013 by Thomas Wiedmann : (Stand : 01.05.2012). 
Powered by Zend Framework and "Yahoo! User Interface" (YUI)