Practical MySQL & MariaDB

MySQL-Collation erklärt: So setzen, ändern und optimieren Sie die Zeichensortierung

Serverkonfiguration & Administration
目次

1. Einführung

MySQL ist eines der am weitesten verbreiteten Datenbankverwaltungssysteme, und darin ist „Collation“ (Sortierung) eine wichtige Einstellung, die beeinflusst, wie Zeichenketten verglichen und sortiert werden.

Die Bedeutung von Collation

Wenn keine geeignete Collation konfiguriert ist, können Suchergebnisse von den Erwartungen abweichen und die Datenbankleistung kann nachlassen. Besonders in mehrsprachigen Systemen wird die Genauigkeit des Zeichenvergleichs und des Suchverhaltens erheblich beeinflusst.

Dieser Artikel liefert eine ausführliche Erklärung zu Collations in MySQL, einschließlich Konfigurationsmethoden, Unterschieden zwischen den Typen und wichtigen Überlegungen. Durch das Verständnis dieser Konzepte können Sie eine effektivere Datenbankverwaltung erreichen.

2. Was ist Collation?

Collation bezeichnet die Regeln, die MySQL beim Vergleichen und Sortieren von Zeichenkettenwerten anwendet.

Die Rolle von Collation

  • String Sorting: Bestimmt die Reihenfolge von Zeichenkettendaten.
  • String Comparison: Definiert Vergleichskriterien wie WHERE name = 'Sagawa'.
  • Search Accuracy: Beeinflusst die mehrsprachige Unterstützung und die Präzision von Übereinstimmungen.

Beziehung zu Zeichensätzen

Collation steht in engem Zusammenhang mit dem Zeichensatz. Zum Beispiel enthält der Zeichensatz utf8 die folgenden Collations:

  • utf8_general_ci : Groß-/Kleinschreibung ignorierender Vergleich.
  • utf8_bin : Binärer Vergleich.

Benennungskonvention für Collation

character_set_comparison_type

Beispiel:

  • utf8_general_ci : Groß-/Kleinschreibung ignorierender Vergleich (ci: case insensitive).
  • utf8_bin : Binärer Vergleich.

3. Collation-Konfigurationsstufen in MySQL

In MySQL können Collations auf den folgenden fünf Ebenen konfiguriert werden:

Server-Ebene

SHOW VARIABLES LIKE 'collation_server';

Um die Einstellung zu ändern, fügen Sie das Folgende zu my.cnf hinzu und starten den Server neu.

[mysqld]
collation_server=utf8mb4_unicode_ci

Datenbank-Ebene

ALTER DATABASE database_name DEFAULT COLLATE utf8mb4_unicode_ci;

Tabellen-Ebene

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Spalten-Ebene

ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(255) COLLATE utf8mb4_unicode_ci;

Zeichenketten-Literal-Ebene

SELECT * FROM table_name WHERE column_name = 'value' COLLATE utf8mb4_bin;

4. Haupt-Collation-Typen und ihre Eigenschaften

utf8_general_ci

  • Features: Groß-/Kleinschreibung ignorierender und schneller Vergleich.
  • Note: Geringere Genauigkeit und nicht vollständig konform mit dem Unicode-Standard.

utf8_unicode_ci

  • Features: Hochgenauer Vergleich basierend auf dem Unicode-Standard.
  • Note: Etwas langsamer als utf8_general_ci.

utf8_bin

  • Features: Groß-/Kleinschreibung beachtend und erfordert exakte Übereinstimmungen.
  • Use Case: Passwörter und Identifier-Vergleiche.

utf8mb4_unicode_ci

  • Features: Konform mit modernen Unicode-Standards und geeignet für mehrsprachige Systeme.
  • Use Case: Anwendungen, die Emojis und Sonderzeichen verarbeiten.

5. Wie man Collation prüft und ändert

In MySQL können Sie Collation-Einstellungen auf Datenbank-, Tabellen- und Spaltenebene prüfen und ändern.

Wie man Collation prüft

Datenbank-Collation prüfen

SELECT SCHEMA_NAME, DEFAULT_COLLATION_NAME 
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'database_name';

Tabellen-Collation prüfen

SHOW TABLE STATUS WHERE Name = 'table_name';

Spalten-Collation prüfen

SHOW FULL COLUMNS FROM table_name;

Wie man Collation ändert

Datenbank-Collation ändern

ALTER DATABASE database_name
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

Tabellen-Collation ändern

ALTER TABLE table_name
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Spalten-Collation ändern

ALTER TABLE table_name
MODIFY COLUMN column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Temporäres Ändern der Collation

When comparing columns with different collations, you can use the COLLATE clause to prevent errors.

SELECT * FROM table_name
WHERE column1 COLLATE utf8mb4_unicode_ci = column2;

Always back up your data before making changes and verify compatibility with your application.

6. Wichtige Überlegungen und bewährte Verfahren für Collation

When configuring collation in MySQL, it is important to understand key considerations and follow optimal operational practices. Below are important notes and best practices for effectively managing collation.

Wichtige Überlegungen

Vorgänge zwischen unterschiedlichen Collations

Comparing or joining columns with different collations may result in errors.

  • Beispiel-Fehler:
    ERROR 1267 (HY000): Illegal mix of collations for operation '='
  • Lösung: Use the COLLATE clause to unify the collation.
    SELECT * FROM table_name
WHERE column1 COLLATE utf8mb4_unicode_ci = column2;

Ändern der Collation und vorhandene Daten

Changing collation may affect existing data. Since comparison and search behavior can change, careful validation is required.

Auswirkungen auf die Leistung

  • High-precision collations such as utf8mb4_unicode_ci may be slower than utf8_general_ci .
  • When handling large datasets, collation selection can significantly affect performance and must be chosen carefully.

Migrationsprobleme

When changing collation, ensure compatibility with applications and external systems.

Bewährte Verfahren

1. Collation basierend auf Anforderungen auswählen

Consider the following when selecting a collation:

  • Genauigkeitspriorität: Use utf8_unicode_ci or utf8mb4_unicode_ci when precise comparisons are required.
  • Leistungspriorität: Choose utf8_general_ci if slightly lower accuracy is acceptable.
  • Mehrsprachige Unterstützung: Use utf8mb4 -based collations when handling emojis and special characters.

2. Collation standardisieren

Using a unified collation across the database prevents errors and inconsistencies.

  • Apply the same collation at the database, table, and column levels.
  • Use the COLLATE clause for temporary adjustments when needed.

3. Vor Änderungen testen und sichern

Before making changes:

  • Verify behavior in a test environment.
  • Create a full backup of production data.

4. Leistung optimieren

To minimize the impact of collation on query performance:

  • Use high-performance collations for frequently queried columns (e.g., indexed columns).
  • Temporarily adjust collation in specific queries when necessary.

5. Neueste Unicode-Unterstützung nutzen

For multilingual systems, using utf8mb4_0900_ai_ci based on the latest Unicode standard improves accuracy and compatibility.

7. Fazit

MySQL collation is a critical factor that determines how string data is compared and sorted within a database. This article covered everything from the basic concept of collation to configuration methods, considerations, and best practices.

Wichtigste Erkenntnisse

  1. Was ist Collation? Collation defines how strings are compared and ordered. Selecting the appropriate collation improves both database accuracy and performance.
  2. Flexible Konfigurationsebenen MySQL allows collation to be configured at multiple levels: server, database, table, column, and literal. This flexibility enables optimal configuration at each layer.
  3. Hauptmerkmale der Collation
  • utf8_general_ci : Fast but less precise.
  • utf8_unicode_ci : More precise but slightly slower.
  • utf8mb4_unicode_ci : Unicode-compliant and supports emojis and special characters.
  1. Praktische Überprüfung und Modifikation Dieser Artikel stellte SQL‑Befehle zum Überprüfen und Ändern der Kollation vor, zusammen mit praktischen Implementierungsanleitungen.
  2. Überlegungen und bewährte Vorgehensweisen Verstehen Sie die wichtigsten Vorsichtsmaßnahmen beim Ändern der Kollation und führen Sie stets vorher Tests und Backups durch.

Kollation effektiv nutzen

Da die Kollation die Zeichenkettenvergleichs‑ und Sortieroperationen erheblich beeinflusst, ist eine korrekte Konfiguration unerlässlich. Nutzen Sie die in diesem Artikel vorgestellten Methoden und bewährten Verfahren, um die optimale Konfiguration für Ihr System auszuwählen.