• 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)
16. mysql> show global variables like 'char%';
17. +--------------------------+----------------------------------------------+
18. | Variable_name | Value |
19. +--------------------------+----------------------------------------------+
20. | character_set_client | latin1 |
21. | character_set_connection | latin1 |
22. | character_set_database | latin1 |
23. | character_set_filesystem | binary |
24. | character_set_results | latin1 |
25. | character_set_server | latin1 |
26. | character_set_system | utf8 |
27. | character_sets_dir | G:\MySQL\MySql-Server-5.1.30\share\charsets\ |
28. +--------------------------+----------------------------------------------+
29. 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)
11. mysql> show global variables like 'coll%';
12. +----------------------+-------------------+
13. | Variable_name | Value |
14. +----------------------+-------------------+
15. | collation_connection | latin1_swedish_ci |
16. | collation_database | latin1_swedish_ci |
17. | collation_server | latin1_swedish_ci |
18. +----------------------+-------------------+
19. 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

https://twiedmann.de