MySQL-String- und numerische Typkonvertierung: CAST vs CONVERT erklärt mit praktischen Beispielen

1. Einführung

Beim Betrieb einer Datenbank mit MySQL stoßen Sie häufig auf Situationen wie „Daten, die als Zahlen behandelt werden sollten, werden als Zeichenkettentyp gespeichert“ oder „Berechnungen und Aggregationen können nicht durchgeführt werden, solange die Daten eine Zeichenkette bleiben“. Beispielsweise werden Daten, die aus Excel‑ oder CSV‑Dateien importiert werden, häufig als VARCHAR oder CHAR gespeichert, obwohl sie numerische Werte darstellen. In solchen Fällen funktionieren Operationen wie SUMME, DURCHSCHNITT und numerischer Vergleich möglicherweise nicht wie erwartet, was das Schreiben korrekter SQL‑Abfragen erschwert.

Andererseits gibt es Szenarien, in denen numerische Daten als Zeichenketten behandelt werden müssen. Zum Beispiel beim Null‑Auffüllen einer ID oder eines Codes oder beim Verketten numerischer Daten mit anderen Spalten zu Anzeigezwecken. In solchen Fällen ist eine Umwandlung von numerischen Typen in Zeichenkettentypen erforderlich.

Auf diese Weise ist „Typkonvertierung zwischen Zeichenketten und Zahlen“ eine der grundlegenden Fähigkeiten, die erforderlich sind, um Daten in MySQL flexibel zu manipulieren. Durch den angemessenen Einsatz von Typkonvertierungen können Sie die Datenintegrität wahren und gleichzeitig flexible Aggregationen und Verarbeitungen ermöglichen.

In diesem Artikel erklären wir klar, wie man in MySQL zwischen Zeichenketten und Zahlen konvertiert, wie man diese Techniken effektiv einsetzt und welche häufigen Fallstricke sowie bewährten Vorgehensweisen aus der Praxis zu beachten sind. Egal, ob Sie Anfänger oder erfahrener SQL‑Entwickler sind, dieser Leitfaden bietet praktischen Nutzen.

2. Arten der Typkonvertierung in MySQL

MySQL bietet mehrere praktische Funktionen zur Konvertierung von Datentypen. In diesem Abschnitt erläutern wir zwei Hauptmuster: „explizite Typkonvertierung“ und „implizite Typkonvertierung“ sowie deren repräsentative Methoden.

2.1 Explizite Typkonvertierung

Explizite Typkonvertierung bedeutet, in SQL eindeutig anzugeben, dass „dieser Wert in einen bestimmten Typ konvertiert werden soll“. Die folgenden beiden Funktionen werden häufig verwendet.

CAST-Funktion
CAST() ist eine standardisierte SQL‑Funktion, die einen Wert in einen angegebenen Typ konvertiert. Sie wird in MySQL häufig verwendet.

SELECT CAST('123' AS SIGNED);

In diesem Beispiel wird die Zeichenkette '123' in eine vorzeichenbehaftete Ganzzahl (SIGNED) konvertiert. Weitere konvertierbare Typen sind UNSIGNED (vorzeichenlose Ganzzahl), DECIMAL (Festkommazahl), CHAR (Zeichenkette) und DATE (Datum).

CONVERT-Funktion
CONVERT() ist eine weitere Funktion, die zum Konvertieren von Werten in verschiedene Datentypen verwendet wird. Die Syntax lautet wie folgt.

SELECT CONVERT('456', UNSIGNED);

In diesem Beispiel wird die Zeichenkette '456' in eine vorzeichenlose Ganzzahl (UNSIGNED) konvertiert. Der Hauptunterschied zu CAST() besteht darin, dass CONVERT() auch für die Konvertierung von Zeichensätzen verwendet werden kann.

2.2 Implizite Typkonvertierung

Implizite Typkonvertierung ist ein Mechanismus, bei dem MySQL Datentypen automatisch konvertiert, wenn Operationen oder Vergleiche durchgeführt werden.

Beispielsweise konvertiert MySQL beim Addieren einer Zahl und einer Zeichenkette die Zeichenkette automatisch in einen numerischen Wert.

SELECT 1 + '2';
-- Result: 3

Ähnlich beim Verketten numerischer Werte als Zeichenketten:

SELECT CONCAT(10, ' apples');
-- Result: '10 apples'

Implizite Typkonvertierung ist praktisch, kann jedoch zu unerwarteten Ergebnissen führen. Daher wird für komplexe Logik oder kritische Prozesse die Verwendung von expliziter Typkonvertierung dringend empfohlen.

3. Praktische Beispiele: Konvertieren von Zeichenketten zu Zahlen

In MySQL, wenn numerische Werte als Zeichenketten (wie CHAR oder VARCHAR) gespeichert sind, können Sie Berechnungen oder numerische Vergleiche nicht zuverlässig durchführen. Um solche Daten korrekt zu aggregieren und zu analysieren, müssen Sie die Zeichenketten in numerische Typen konvertieren. Dieser Abschnitt stellt häufig verwendete Konvertierungsmethoden und wichtige Vorsichtsmaßnahmen vor.

3.1 Konvertierung mit der CAST-Funktion

Der grundlegendste Ansatz ist die Verwendung der CAST()‑Funktion. Um beispielsweise die Zeichenkette '100' in eine Ganzzahl zu konvertieren, schreiben Sie Folgendes:

SELECT CAST('100' AS SIGNED) AS numeric_result;
-- Result: 100 (integer)

Use SIGNED für signierte Ganzzahlen und UNSIGNED für unsignierte Ganzzahlen. Für Dezimaldaten können Sie auch DECIMAL oder FLOAT verwenden.

SELECT CAST('123.45' AS DECIMAL(10,2)) AS decimal_result;
-- Result: 123.45

3.2 Konvertieren mit der CONVERT-Funktion

Die CONVERT()-Funktion kann auf fast dieselbe Weise verwendet werden:

SELECT CONVERT('200', SIGNED) AS converted_result;
-- Result: 200

Beide erzeugen dasselbe Ergebnis, aber da CAST() Standard-SQL ist und bessere Portabilität bietet, wird es im Zweifelsfall allgemein empfohlen.

3.3 Implizite Konvertierung über Arithmetik

Wenn Sie Arithmetik mit numerischen und String-Typen in SQL-Ausdrücken durchführen, konvertiert MySQL den String automatisch zu einer Zahl. Zum Beispiel:

SELECT '50' + 25 AS total;
-- Result: 75

Sie können dieses Verhalten auch auf Aggregationsfunktionen anwenden. Zum Beispiel, wenn Sie eine Spalte vom String-Typ an SUM() übergeben, versucht MySQL eine numerische Konvertierung automatisch und summiert die Werte:

SELECT SUM(amount) FROM sales_data;
-- Even if the amount column is VARCHAR, MySQL will attempt numeric summation

3.4 Achten Sie auf nullgepolsterte Strings und nicht-numerische Werte

Nullgepolsterte Strings (z. B. '000100') können ebenfalls zu Zahlen konvertiert werden:

SELECT CAST('000100' AS SIGNED) AS converted_result;
-- Result: 100

Allerdings seien Sie vorsichtig, wenn der String nicht-numerische Zeichen enthält. Wenn Sie etwas wie CAST('abc123' AS SIGNED) konvertieren und es keine numerischen Zeichen am Anfang gibt, gibt MySQL 0 zurück. Abhängig von der Datenqualität ist es wichtig, vor der Konvertierung eine Eingabevalidierung durchzuführen.

3.5 Häufige reale Anwendungsfälle

• Aggregieren von Verkaufs- oder Betragsdaten, die aufgrund von Excel/CSV-Importen zu Strings geworden sind
• Sortieren von als Strings gespeicherten IDs (obwohl sie Zahlen darstellen) in numerischer Reihenfolge
• Sortieren von Datums-Strings, die im YYYYMMDD-Format gespeichert sind, nach Datumsreihenfolge (später erklärt)

4. Praktische Beispiele: Konvertieren von Zahlen zu Strings

In MySQL gibt es viele Fälle, in denen Sie numerische Daten als Strings behandeln möchten. Typische Beispiele umfassen das Anzeigen von IDs oder Codes mit Nullauffüllung oder das Erstellen von Nachrichten durch Verkettung numerischer Werte mit anderem Text. Dieser Abschnitt stellt repräsentative Methoden und praktische Anwendungsfälle vor.

4.1 Konvertieren mit der CAST-Funktion

Um einen numerischen Typ (wie INT oder DECIMAL) explizit in einen String-Typ zu konvertieren, verwenden Sie CAST():

SELECT CAST(123 AS CHAR) AS string_result;
-- Result: '123'

Mit dieser Methode können Sie numerische Spalten leicht mit anderen Strings verkettet.

4.2 Konvertieren mit der CONVERT-Funktion

Sie können dasselbe auch mit CONVERT() tun:

SELECT CONVERT(456, CHAR) AS converted_result;
-- Result: '456'

Es gibt wenig Unterschied zu CAST(), aber aus Sicht des Standard-SQL wird CAST() leicht bevorzugt.

4.3 Implizite Konvertierung über Verkettung

Beim Verkettet von Zahlen und Strings mit Funktionen wie CONCAT() konvertiert MySQL Zahlen automatisch in Strings.

SELECT CONCAT(2024, ' year') AS fiscal_year_display;
-- Result: '2024 year'

Eine solche implizite Konvertierung wird häufig für routinemäßige Berichterstattungsausgaben und Datenformatierung verwendet.

4.4 Häufige reale Anwendungsfälle

• Generieren nullgepolsterter IDs Um eine numerische ID in 5 Stellen anzuzeigen, kombinieren Sie sie mit LPAD() wie folgt: SELECT LPAD(CAST(id AS CHAR), 5, '0') AS zero_padded_id FROM users; -- If id=7, the result is '00007'
• Verketten von Daten oder Beträgen in Strings SELECT CONCAT('The total amount is ', CAST(total AS CHAR), ' yen.') AS message FROM orders; -- If total=1500, the result is 'The total amount is 1500 yen.'

4.5 Hinweise

Auch wenn der konvertierte String visuell gleich aussieht, ändert sich das Verhalten von „Sortieren“ und „Vergleichsoperationen“.
Zum Beispiel kommt bei der Sortierung als Strings '20' vor '100' (lexikografische Reihenfolge). Es ist wichtig, den Ansatz je nach Zweck zu wählen.

5. Erweiterte Anwendungsfälle mit Typumwandlung

Typumwandlung ist nicht auf einfache numerische oder Zeichenketten‑Transformationen beschränkt. Sie kann auch in verschiedenen praktischen Szenarien in realen Systemen angewendet werden. Dieser Abschnitt stellt häufig genutzte erweiterte Fälle und deren zentrale Überlegungen vor.

5.1 Vergleich und Umwandlung datumsähnlicher Zeichenketten

Wenn Ihre Datenbank Daten in Formaten wie YYYYMMDD als numerische oder Zeichenketten‑Typen speichert, kann ein einfacher Zeichenkettenvergleich nicht immer das erwartete Verhalten zeigen.

In solchen Fällen ermöglicht die Umwandlung des Wertes in einen numerischen Typ mittels CAST() eine korrekte chronologische Sortierung und den Vergleich.

SELECT *
FROM events
ORDER BY CAST(event_date AS UNSIGNED);
-- Values such as '20240501', '20240502', etc. are sorted in date order

Sie können dies auch mit der REPLACE()‑Funktion kombinieren, um durch Bindestriche getrennte Daten wie '2024-05-01' in Ganzzahlen umzuwandeln.

SELECT CAST(REPLACE('2024-05-01', '-', '') AS UNSIGNED);
-- Result: 20240501

5.2 ENUM‑Typen oder Code‑Werte numerisch sortieren

Für ENUM‑Typen oder Code‑Werte, die eine numerische Bedeutung haben, möchten Sie sie möglicherweise in numerischer Reihenfolge statt in lexikografischer Reihenfolge sortieren. Durch die Umwandlung in numerische Typen mit CAST() vor dem Sortieren erhalten Sie eine intuitive Reihenfolge.

SELECT *
FROM products
ORDER BY CAST(product_code AS UNSIGNED);

5.3 Verwendung in Aggregation und Datenformatierung

Zum Beispiel, wenn Verkaufsbeträge als VARCHAR gespeichert sind, können Sie durch explizite Umwandlung innerhalb von SUM() eine genaue Gesamtsumme erhalten.

SELECT SUM(CAST(sales_amount AS SIGNED)) AS total_sales
FROM sales_data;

5.4 Vermeidung von Problemen mit Präzision und Typauswahl

Für dezimale oder große numerische Werte hilft die Umwandlung in den DECIMAL‑Typ, die Präzision zu erhalten.

Die Wahl zwischen SIGNED und UNSIGNED ist ebenfalls wichtig, abhängig davon, ob negative Werte in Ihren Daten vorkommen können.

SELECT CAST('1234.567' AS DECIMAL(10, 3));
-- Result: 1234.567

5.5 Erkennen von Fehlern oder ungültigen Daten

Wenn die Umwandlung unerwartete NULL‑ oder 0‑Werte ergibt, kann dies auf ungültige Daten hinweisen.

Zum Beispiel, wenn eine Zeichenkette nicht in eine Zahl umgewandelt werden kann, kann CAST() je nach SQL‑Modus 0 oder NULL zurückgeben.

In solchen Fällen kann das Überprüfen der Umwandlungsergebnisse helfen, Anforderungen an Datenbereinigung oder -validierung zu erkennen.

SELECT original, CAST(original AS SIGNED) AS converted
FROM test_data
WHERE CAST(original AS SIGNED) = 0 AND original <> '0';
-- Extract only data that cannot be properly converted to numeric values

6. Unterschiede zwischen CAST und CONVERT

Bei der Typumwandlung in MySQL verwenden Sie typischerweise entweder die CAST‑Funktion oder die CONVERT‑Funktion. Obwohl sie ähnlich erscheinen, gibt es Unterschiede in der Verwendung und den Eigenschaften. Dieser Abschnitt erklärt diese Unterschiede und wie Sie zwischen ihnen wählen.

6.1 Grundlegende Unterschiede

• CAST-Funktion Verwendet die Syntax CAST(value AS type), um einen Wert explizit in den angegebenen Typ zu konvertieren. Sie ist im Standard‑SQL definiert und funktioniert ähnlich in vielen Datenbanksystemen. SELECT CAST('123' AS SIGNED);
• CONVERT-Funktion Verwendet die Syntax CONVERT(value, type), um einen Wert in den angegebenen Typ zu konvertieren. In MySQL kann sie zudem für die Zeichensatzkonvertierung verwendet werden. SELECT CONVERT('123', SIGNED); -- Typumwandlung SELECT CONVERT('hello' USING utf8mb4); -- Zeichensatzumwandlung

6.2 Kompatibilität mit Standard‑SQL

Da CAST Teil des internationalen SQL‑Standards ist, bietet es eine bessere Portabilität zu anderen Datenbanken (wie PostgreSQL, SQL Server und Oracle). Andererseits enthält CONVERT MySQL‑spezifische Erweiterungen, insbesondere für Zeichensatzkonvertierung, mit einer Syntax wie CONVERT(expr USING charset_name).

6.3 Wie man zwischen ihnen wählt

• Für Typkonvertierung (Zahlen, Zeichenketten, Datumsangaben usw.) In den meisten Fällen ist die Verwendung von CAST die sicherste Wahl. Es ist Standard‑SQL, hoch portabel und für zukünftige Datenbankmigrationen geeignet.
• Für Zeichensatzkonvertierung (z. B. sjis zu utf8mb4) Sie müssen CONVERT(expr USING charset_name) verwenden.
• Für MySQL‑spezifische oder Sonderfälle CONVERT kann zusätzliche Flexibilität bieten, aber für allgemeine Zwecke beginnen Sie mit CAST und verwenden CONVERT nur bei Bedarf.

6.4 Beispielvergleich

-- Type conversion using CAST (convert to integer)
SELECT CAST('456' AS SIGNED);

-- Type conversion using CONVERT
SELECT CONVERT('456', SIGNED);

-- Character set conversion using CONVERT
SELECT CONVERT('Hello' USING utf8mb4);

6.5 Wichtige Hinweise

• Wenn die Konvertierung fehlschlägt, können beide Funktionen NULL oder 0 zurückgeben.
• Zeichensatzkonvertierung kann nicht mit CAST durchgeführt werden.
• Das Verhalten kann je nach SQL‑Modus oder MySQL‑Version variieren, daher sollten Sie immer sowohl in Entwicklungs‑ als auch in Produktionsumgebungen testen.

7. Wichtige Hinweise und bewährte Vorgehensweisen

Bei der Verwendung von Zeichenketten‑ und numerischen Typkonvertierungen in MySQL gibt es mehrere potenzielle Fallstricke. Dieser Abschnitt stellt bewährte Vorgehensweisen vor, um Probleme zu vermeiden und eine sichere sowie genaue Nutzung zu gewährleisten.

7.1 Fehler oder unerwartetes NULL/0 bei ungültigen Konvertierungen

Bei der Durchführung von Typkonvertierungen kann MySQL, wenn der Quellwert nicht im richtigen Format vorliegt, unerwartete NULL‑ oder 0‑Werte zurückgeben.

SELECT CAST('abc' AS SIGNED) AS result;
-- Result: 0 (default MySQL behavior)

Wie oben gezeigt, führt die Umwandlung einer nicht‑numerischen Zeichenkette in einen numerischen Typ zu 0. Daher sollten Sie stets prüfen, dass die Originaldaten keine ungültigen Werte enthalten, bevor Sie konvertieren.

Abhängig von den SQL‑Modus‑Einstellungen kann die Konvertierung stattdessen einen Fehler oder NULL erzeugen. Überprüfen Sie stets die Konfiguration Ihrer Produktionsumgebung.

7.2 Auswahl von Präzision und Vorzeichen‑/Unsigned‑Typen

• Für Werte mit Dezimalstellen konvertieren Sie zu DECIMAL oder FLOAT.
• Wenn negative Werte möglich sind, verwenden Sie SIGNED; wenn Werte ausschließlich positive ganze Zahlen sind, verwenden Sie UNSIGNED.

Wählen Sie stets den passenden Typ entsprechend den Merkmalen Ihrer Daten aus.

7.3 Auswirkungen auf Indizes

Die Verwendung von Typkonvertierungsfunktionen wie CAST oder CONVERT in WHERE‑ oder ORDER BY‑Klauseln kann verhindern, dass Indizes genutzt werden, was zu Leistungseinbußen führt.

SELECT * FROM users WHERE CAST(user_id AS SIGNED) = 1000;
-- Even if user_id has an index, it is often not used

Bei großen Datensätzen oder leistungskritischen Abfragen ist es am besten, die Spaltentypen im Schema zu vereinheitlichen, um unnötige Konvertierungen zu vermeiden.

7.4 Verlassen Sie sich nicht zu sehr auf implizite Konvertierung

Implizite Typkonvertierung in MySQL ist praktisch, kann jedoch zu unbeabsichtigtem Verhalten führen. Für wichtige Logik verwenden Sie stets explizite CAST‑ oder CONVERT‑Anweisungen.

SELECT '100a' + 20;
-- Result: 100 (only the leading numeric portion is used)

Um subtile Fehler und Dateninkonsistenzen zu vermeiden, sollten Sie explizite Konvertierungen zur Gewohnheit machen.

7.5 Validieren Sie Typen bereits beim Dateneingang

Beim Entwurf Ihrer Datenbank sollten numerische Daten als numerische Typen und Zeichenketten als Zeichenkettentypen gespeichert werden. Durch ein korrektes Schema‑Design den Bedarf an Konvertierungen zu minimieren, ist einer der effektivsten Wege, Probleme zu verhindern.

8. Zusammenfassung

Typkonvertierung zwischen Zeichenketten und Zahlen in MySQL ist ein unvermeidliches Thema bei der täglichen Datenverarbeitung, Aggregation und Systemoperationen. In diesem Artikel haben wir alles von der Grundnutzung bis zu fortgeschrittenen Techniken und wichtigen Vorsichtsmaßnahmen behandelt.

Für die Konvertierung von Zeichenketten zu Zahlen stehen explizite Methoden wie CAST und CONVERT zur Verfügung. Obwohl implizite Konvertierung in der Praxis häufig verwendet wird, erhöht die Priorisierung von expliziter Konvertierung die Zuverlässigkeit und Datenqualität. Umgekehrt wird die Konvertierung von Zahlen zu Zeichenketten ebenfalls häufig zum Formatieren von IDs, Codes und zum Erzeugen von Ausgabemeldungen eingesetzt.

In addition, techniques such as date-related applications, aggregation and sorting, and error detection demonstrate how powerful type conversion can be in real‑world scenarios. However, conversion also carries potential pitfalls and performance impacts. Always follow the best practices discussed above.

By mastering type conversion properly, you can significantly expand your ability to manipulate data effectively in MySQL.
We hope this article supports your daily work and learning.

9. FAQ (Häufig gestellte Fragen)

Q1. What happens if I CAST a string like ‚abc‘ to a number?

In MySQL, when attempting to convert a string to a numeric type (such as SIGNED or UNSIGNED), MySQL returns the leading numeric portion if present; otherwise, it returns 0.

SELECT CAST('abc' AS SIGNED); -- Result: 0
SELECT CAST('123abc' AS SIGNED); -- Result: 123

However, depending on SQL mode (for example, STRICT_TRANS_TABLES), the result may be an error or NULL.

Q2. Should I use CAST or CONVERT?

For general type conversion (numbers, strings, dates, etc.), using CAST is recommended because it conforms to standard SQL. For character set conversion, you must use the CONVERT function with the USING clause. Choose based on your use case.

Q3. Is implicit type conversion sufficient?

Implicit conversion may work for small queries or testing, but for critical aggregation and production system development, explicit conversion (CAST or CONVERT) is strongly recommended. Writing explicit conversions helps prevent unintended behavior and bugs.

Q4. Does using type conversion in WHERE or ORDER BY disable indexes?

Yes. Applying functions such as CAST or CONVERT to a column may prevent MySQL from using indexes defined on that column.
If high‑performance querying of large datasets is required, unify column types in advance or consider using subqueries or generated columns.

Q5. Is type conversion safe for decimals or large numbers?

Using DECIMAL or FLOAT can preserve precision, but rounding errors, precision loss, or truncation may still occur. Always specify sufficient precision and scale for your requirements.

Q6. What is the difference between sorting as a string and sorting as a number?

When sorted as strings, values like '10' may come before '2' due to lexicographical ordering. If you want numeric order, always perform type conversion before sorting.