Převod řetězcových a číselných typů v MySQL: CAST vs CONVERT vysvětleno s praktickými příklady

1. Úvod

Při práci s databází v MySQL často narazíte na situace, jako je „data, která by měla být považována za čísla, jsou uložena jako typ řetězce“ nebo „výpočty a agregace nelze provést, pokud data zůstávají řetězcem“. Například data importovaná z Excelu nebo CSV souborů jsou často uložena jako VARCHAR nebo CHAR, přestože představují číselné hodnoty. V takových případech operace jako součet, průměr a číselné porovnání nemusí fungovat podle očekávání, což ztěžuje psaní správných SQL dotazů.

Na druhé straně existují scénáře, kdy je nutné číselná data zpracovávat jako řetězce. Například při doplňování nul do ID nebo kódu, nebo při spojování číselných dat s jinými sloupci pro účely zobrazení. V takových případech je nutné převést číselné typy na typy řetězců.

Tímto způsobem je „převod typů mezi řetězci a čísly“ jednou z základních dovedností potřebných k flexibilní manipulaci s daty v MySQL. Pomocí vhodného převodu typů můžete udržet integritu dat a zároveň umožnit flexibilní agregaci a zpracování.

V tomto článku jasně vysvětlíme, jak převádět mezi řetězci a čísly v MySQL, jak tyto techniky efektivně používat, a běžné pasti a osvědčené postupy na základě reálných zkušeností. Ať jste začátečník nebo zkušený vývojář SQL, tento průvodce vám poskytne praktickou hodnotu.

2. Typy převodu typů v MySQL

MySQL poskytuje několik pohodlných funkcí pro převod datových typů. V této sekci vysvětlíme dva hlavní vzorce: „explicitní převod typu“ a „implicitní převod typu“, spolu s jejich reprezentativními metodami.

2.1 Explicitní převod typu

Explicitní převod typu znamená jasné specifikování v SQL, že „tato hodnota by měla být převedena na specifický typ“. Následující dvě funkce se běžně používají.

Funkce CAST
CAST() je standardní SQL funkce, která převádí hodnotu na určený typ. Je široce používána v MySQL.

SELECT CAST('123' AS SIGNED);

V tomto příkladu je řetězec '123' převeden na celé číslo se znaménkem (SIGNED). Mezi další převoditelné typy patří UNSIGNED (necely celé číslo), DECIMAL (desetinné číslo s pevnou čárkou), CHAR (řetězec) a DATE (datum).

Funkce CONVERT
CONVERT() je další funkce používaná k převodu hodnot na různé datové typy. Syntaxe je následující.

SELECT CONVERT('456', UNSIGNED);

V tomto příkladu je řetězec '456' převeden na necely celé číslo (UNSIGNED). Hlavní rozdíl oproti CAST() spočívá v tom, že CONVERT() lze použít i pro převod znakové sady.

2.2 Implicitní převod typu

Implicitní převod typu je mechanismus, kdy MySQL automaticky převádí datové typy při provádění operací nebo porovnání.

Například při sčítání čísla a řetězce MySQL automaticky převede řetězec na číselnou hodnotu.

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

Podobně při spojování číselných hodnot jako řetězců:

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

Implicitní převod typu je pohodlný, ale může vést k neočekávaným výsledkům. Proto se pro složitou logiku nebo kritické procesy důrazně doporučuje používat explicitní převod typu.

3. Praktické příklady: Převod řetězců na čísla

V MySQL, když jsou číselné hodnoty uloženy jako řetězce (jako CHAR nebo VARCHAR), nelze spolehlivě provádět výpočty nebo číselná porovnání tak, jak jsou. Aby bylo možné taková data správně agregovat a analyzovat, je nutné převést řetězce na číselné typy. Tato sekce představuje běžně používané metody převodu a klíčová opatření.

3.1 Převod pomocí funkce CAST

Nejzákladnější přístup je použití funkce CAST(). Například pro převod řetězce '100' na celé číslo napište následující:

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

Use SIGNED for signed integers and UNSIGNED for unsigned integers. For decimal data, you can use DECIMAL or FLOAT as well.

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

3.2 Převod pomocí funkce CONVERT

The CONVERT() function can be used in almost the same way:

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

Both produce the same result, but since CAST() is standard SQL and offers better portability, it is generally recommended when in doubt.

3.3 Implicitní převod pomocí aritmetiky

When you perform arithmetic with numeric and string types in SQL expressions, MySQL automatically converts the string to a number. For example:

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

You can also apply this behavior to aggregation functions. For instance, if you pass a string-type column to SUM(), MySQL attempts numeric conversion automatically and sums the values:

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

3.4 Dejte pozor na nulou vyplněné řetězce a nečíselné hodnoty

Zero-padded strings (e.g., '000100') can also be converted to numbers:

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

However, be careful if the string contains non-numeric characters. If you convert something like CAST('abc123' AS SIGNED), and there are no numeric characters at the start, MySQL returns 0. Depending on data quality, it is important to perform input validation before conversion.

3.5 Běžné reálné příklady použití

• Agregace prodejních nebo částkových dat, která se kvůli importu z Excelu/CSV stala řetězci
• Řazení ID uložených jako řetězce (i když představují čísla) v číselném pořadí
• Řazení datových řetězců uložených ve formátu YYYYMMDD podle data (vysvětleno později)

4. Praktické příklady: Převod čísel na řetězce

In MySQL, there are many cases where you want to treat numeric data as strings. Typical examples include displaying IDs or codes with zero padding, or building messages by concatenating numeric values with other text. This section introduces representative methods and practical use cases.

4.1 Převod pomocí funkce CAST

To explicitly convert a numeric type (such as INT or DECIMAL) into a string type, use CAST():

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

With this method, you can easily concatenate numeric columns with other strings.

4.2 Převod pomocí funkce CONVERT

You can also do the same with CONVERT():

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

There is little difference from CAST(), but from a standard SQL perspective, CAST() is slightly preferred.

4.3 Implicitní převod pomocí konkatenace

When concatenating numbers and strings using functions like CONCAT(), MySQL automatically converts numbers into strings.

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

Such implicit conversion is commonly used for routine reporting output and data formatting.

4.4 Běžné reálné příklady použití

• Generování nulou vyplněných ID Pro zobrazení číselného ID v 5 číslicích jej kombinujte s LPAD() následovně: SELECT LPAD(CAST(id AS CHAR), 5, '0') AS zero_padded_id FROM users; -- Pokud je id=7, výsledek je '00007'
• Konkatenace dat nebo částek do řetězců SELECT CONCAT('The total amount is ', CAST(total AS CHAR), ' yen.') AS message FROM orders; -- Pokud je total=1500, výsledek je 'The total amount is 1500 yen.'

4.5 Poznámky

Even if the converted string looks the same visually, the behavior of “sorting” and “comparison operations” changes.
For example, when sorting as strings, '20' may come before '100' (lexicographical order). It is important to choose the approach depending on your purpose.

5. Pokročilé případy použití s konverzí typů

Převod typů není omezen na jednoduché numerické nebo řetězcové transformace. Lze jej také aplikovat v různých praktických scénářích v reálných systémech. Tato sekce představuje běžně používané pokročilé případy a jejich klíčové úvahy.

5.1 Porovnávání a konverze řetězců podobných datům

Pokud vaše databáze ukládá data ve formátech jako YYYYMMDD jako numerické nebo řetězcové typy, jednoduché porovnávání řetězců nemusí vždy chovat podle očekávání.

V takových případech umožňuje převod hodnoty na numerický typ pomocí CAST() správné chronologické řazení a porovnávání.

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

Můžete to také kombinovat s funkcí REPLACE() k převodu dat oddělených pomlčkami, jako je '2024-05-01', na celá čísla.

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

5.2 Řazení typů ENUM nebo kódových hodnot numericky

Pro typy ENUM nebo kódové hodnoty, které představují numerický význam, můžete chtít řadit numericky místo lexikografického pořadí. Převodem na numerické typy pomocí CAST() před řazením dosáhnete intuitivního pořadí.

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

5.3 Použití v agregaci a formátování dat

Například pokud jsou částky prodeje uloženy jako VARCHAR, můžete získat přesný součet explicitním převodem uvnitř SUM().

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

5.4 Prevence problémů s přesností a výběrem typu

Pro desetinné nebo velké numerické hodnoty pomáhá převod na typ DECIMAL zachovat přesnost.

Výběr mezi SIGNED a UNSIGNED je také důležitý v závislosti na tom, zda mohou v datech existovat záporné hodnoty.

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

5.5 Detekce chyb nebo neplatných dat

Pokud převod vede k neočekávaným hodnotám NULL nebo 0, může to naznačovat neplatná data.

Například pokud řetězec nelze převést na číslo, CAST() může vrátit 0 nebo NULL v závislosti na SQL módu.

V takových případech může kontrola výsledků převodu pomoci identifikovat potřeby čištění nebo validace dat.

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. Rozdíly mezi CAST a CONVERT

Při provádění převodu typů v MySQL obvykle používáte buď funkci CAST, nebo funkci CONVERT. Ačkoli se zdají podobné, existují rozdíly v použití a vlastnostech. Tato sekce vysvětluje tyto rozdíly a jak mezi nimi vybírat.

6.1 Základní rozdíly

• Funkce CAST Používá syntaxi CAST(value AS type) k explicitnímu převodu hodnoty na zadaný typ. Je definována ve standardním SQL a funguje podobně napříč mnoha databázovými systémy. SELECT CAST('123' AS SIGNED);
• Funkce CONVERT Používá syntaxi CONVERT(value, type) k převodu hodnoty na zadaný typ. V MySQL lze také použít pro konverzi znakové sady. SELECT CONVERT('123', SIGNED); -- Převod typu SELECT CONVERT('hello' USING utf8mb4); -- Konverze znakové sady

6.2 Kompatibilita se standardním SQL

Protože CAST je součástí mezinárodního SQL standardu, nabízí lepší přenositelnost do jiných databází (jako PostgreSQL, SQL Server a Oracle). Na druhé straně CONVERT obsahuje MySQL-specifické rozšíření, zejména pro konverzi znakové sady, pomocí syntaxe jako CONVERT(expr USING charset_name).

6.3 Jak mezi nimi vybírat

• Pro konverzi typů (čísla, řetězce, data atd.) Ve většině případů je nejbezpečnější volbou použití CAST. Jedná se o standardní SQL, je vysoce přenosné a vhodné pro budoucí migrace databází.
• Pro konverzi znakové sady (např. sjis na utf8mb4) Musíte použít CONVERT(expr USING charset_name) .
• Pro specifické případy MySQL nebo speciální situace CONVERT může nabídnout další flexibilitu, ale pro obecné účely začněte s CAST a CONVERT používejte jen když je to nutné.

6.4 Ukázkové srovnání

-- 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 Důležité poznámky

• Pokud konverze selže, mohou obě funkce vrátit NULL nebo 0 .
• Konverzi znakové sady nelze provést pomocí CAST .
• Chování se může lišit v závislosti na režimu SQL nebo verzi MySQL, proto vždy testujte jak ve vývojovém, tak v produkčním prostředí.

7. Důležité poznámky a osvědčené postupy

Při používání konverze řetězcových a číselných typů v MySQL existuje několik potenciálních úskalí, na která je třeba si dát pozor. Tato sekce představuje osvědčené postupy, které pomáhají předcházet problémům a zajišťují bezpečné a přesné používání.

7.1 Chyby nebo neočekávané NULL/0 z neplatných konverzí

Při provádění konverze typu, pokud vstupní hodnota není ve správném formátu, MySQL může vrátit neočekávané hodnoty NULL nebo 0 .

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

Jak je uvedeno výše, převod nečíselného řetězce na číselný typ vede k 0. Proto vždy ověřte, že původní data neobsahují neplatné hodnoty před konverzí.

V závislosti na nastavení režimu SQL může konverze místo toho vyvolat chybu nebo NULL. Vždy potvrďte konfiguraci vašeho produkčního prostředí.

7.2 Volba přesnosti a typů signed/unsigned

• Pro hodnoty obsahující desetinná místa převádějte na DECIMAL nebo FLOAT
• Pokud jsou možné záporné hodnoty, použijte SIGNED; pokud jsou hodnoty striktně kladná celá čísla, použijte UNSIGNED

Vždy vyberte vhodný typ podle charakteristik vašich dat.

7.3 Dopad na indexy

Použití funkcí pro konverzi typů, jako jsou CAST nebo CONVERT, v klauzulích WHERE nebo ORDER BY může zabránit využití indexů, což vede ke snížení výkonu.

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

Pro velké datové sady nebo dotazy kritické na výkon je nejlepší sjednotit typy sloupců v návrhu schématu, aby se předešlo zbytečným konverzím.

7.4 Nespoléhejte se příliš na implicitní konverzi

Implicitní konverze typů v MySQL je pohodlná, ale může vést k nechtěnému chování. Pro důležitou logiku vždy používejte explicitní CAST nebo CONVERT.

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

Aby se předešlo jemným chybám a nekonzistencím v datech, udělejte z explicitní konverze zvyk.

7.5 Ověřujte typy ve fázi zadávání dat

Při návrhu databáze ukládejte číselná data jako číselné typy a řetězcová data jako řetězcové typy. Minimalizace potřeby konverze pomocí správného návrhu schématu je jedním z nejúčinnějších způsobů, jak předejít problémům.

8. Shrnutí

Konverze typů mezi řetězci a čísly v MySQL je nevyhnutelným tématem v každodenním zpracování dat, agregaci a systémových operacích. V tomto článku jsme pokryli vše od základního použití po pokročilé techniky a důležité opatření.

Pro konverzi řetězec‑na‑číslo jsou k dispozici explicitní metody jako CAST a CONVERT. I když se v praxi často používá implicitní konverze, upřednostnění explicitní konverze zvyšuje spolehlivost a kvalitu dat. Naopak konverze číslo‑na‑řetězec je také široce využívána pro formátování ID, kódů a generování výstupních zpráv.

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 (Často kladené otázky)

Q1. Co se stane, když použiji CAST na řetězec jako ‚abc‘ na číslo?

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. Mám použít CAST nebo 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. Je implicitní konverze typů dostatečná?

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. Způsobí použití konverze typů ve WHERE nebo ORDER BY deaktivaci indexů?

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. Je konverze typů bezpečná pro desetinná čísla nebo velká čísla?

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. Jaký je rozdíl mezi řazením jako řetězec a řazením jako číslo?

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.