Conversione di tipi stringa e numerico in MySQL: CAST vs CONVERT spiegati con esempi pratici

1. Introduzione

Quando si gestisce un database con MySQL, ci si imbatte spesso in situazioni come “i dati che dovrebbero essere trattati come numeri sono memorizzati come tipo stringa” o “calcoli e aggregazioni non possono essere eseguiti finché i dati rimangono una stringa”. Per esempio, i dati importati da file Excel o CSV sono frequentemente memorizzati come VARCHAR o CHAR, anche se rappresentano valori numerici. In questi casi, operazioni come somma, media e confronto numerico potrebbero non funzionare come previsto, rendendo difficile scrivere query SQL corrette.

D’altra parte, esistono scenari in cui i dati numerici devono essere gestiti come stringhe. Per esempio, quando si aggiunge lo zero‑padding a un ID o a un codice, o quando si concatenano dati numerici con altre colonne a scopo di visualizzazione. In tali situazioni, è necessario convertire i tipi numerici in tipi stringa.

In questo modo, “conversione di tipo tra stringhe e numeri” è una delle competenze fondamentali richieste per manipolare i dati in modo flessibile in MySQL. Utilizzando la conversione di tipo in modo appropriato, è possibile mantenere l’integrità dei dati consentendo al contempo aggregazioni e elaborazioni flessibili.

In questo articolo spiegheremo chiaramente come convertire tra stringhe e numeri in MySQL, come utilizzare queste tecniche in modo efficace e i comuni errori da evitare e le migliori pratiche basate sull’esperienza reale. Che siate principianti o sviluppatori SQL esperti, questa guida fornirà valore pratico.

2. Tipi di conversione di tipo in MySQL

MySQL offre diverse funzionalità pratiche per convertire i tipi di dato. In questa sezione spiegheremo due modelli principali: “conversione di tipo esplicita” e “conversione di tipo implicita”, insieme ai loro metodi rappresentativi.

2.1 Conversione di tipo esplicita

La conversione di tipo esplicita significa specificare chiaramente in SQL che “questo valore deve essere convertito in un tipo specifico”. Le due funzioni seguenti sono comunemente usate.

Funzione CAST
CAST() è una funzione SQL standard che converte un valore in un tipo specificato. È ampiamente utilizzata in MySQL.

SELECT CAST('123' AS SIGNED);

In questo esempio, la stringa '123' viene convertita in un intero con segno (SIGNED). Altri tipi convertibili includono UNSIGNED (intero senza segno), DECIMAL (numero a virgola fissa), CHAR (stringa) e DATE (data).

Funzione CONVERT
CONVERT() è un’altra funzione usata per convertire valori in diversi tipi di dato. La sintassi è la seguente.

SELECT CONVERT('456', UNSIGNED);

In questo esempio, la stringa '456' viene convertita in un intero senza segno (UNSIGNED). La differenza principale rispetto a CAST() è che CONVERT() può essere usata anche per la conversione di set di caratteri.

2.2 Conversione di tipo implicita

La conversione di tipo implicita è un meccanismo per cui MySQL converte automaticamente i tipi di dato quando esegue operazioni o confronti.

Ad esempio, quando si somma un numero e una stringa, MySQL converte automaticamente la stringa in un valore numerico.

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

Allo stesso modo, quando si concatenano valori numerici come stringhe:

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

La conversione di tipo implicita è comoda, ma può produrre risultati inattesi. Pertanto, per logiche complesse o processi critici, si consiglia vivamente di utilizzare la conversione di tipo esplicita.

3. Esempi pratici: conversione di stringhe in numeri

In MySQL, quando i valori numerici sono memorizzati come stringhe (ad esempio CHAR o VARCHAR), non è possibile eseguire in modo affidabile calcoli o confronti numerici così come sono. Per aggregare e analizzare correttamente tali dati, è necessario convertire le stringhe in tipi numerici. Questa sezione presenta i metodi di conversione più usati e le precauzioni chiave.

3.1 Conversione con la funzione CAST

L’approccio più basilare è utilizzare la funzione CAST(). Per esempio, per convertire la stringa '100' in un intero, scrivete quanto segue:

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

Use SIGNED per gli interi con segno e UNSIGNED per gli interi senza segno. Per i dati decimali, puoi usare anche DECIMAL o FLOAT.

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

3.2 Conversione con la funzione CONVERT

La funzione CONVERT() può essere usata quasi allo stesso modo:

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

Entrambe producono lo stesso risultato, ma poiché CAST() è SQL standard e offre una migliore portabilità, è generalmente consigliata in caso di dubbi.

3.3 Conversione implicita tramite operazioni aritmetiche

Quando esegui operazioni aritmetiche con tipi numerici e stringa nelle espressioni SQL, MySQL converte automaticamente la stringa in un numero. Per esempio:

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

Puoi applicare lo stesso comportamento anche alle funzioni di aggregazione. Ad esempio, se passi una colonna di tipo stringa a SUM(), MySQL tenta automaticamente la conversione numerica e somma i valori:

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

3.4 Attenzione a stringhe con zeri iniziali e valori non numerici

Le stringhe con zeri iniziali (es., '000100') possono essere convertite in numeri:

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

Tuttavia, fai attenzione se la stringa contiene caratteri non numerici. Se converti qualcosa come CAST('abc123' AS SIGNED), e non ci sono caratteri numerici all’inizio, MySQL restituisce 0. A seconda della qualità dei dati, è importante eseguire la validazione dell’input prima della conversione.

3.5 Casi d’uso comuni nel mondo reale

• Aggregare vendite o importi che sono diventati stringhe a causa di importazioni Excel/CSV
• Ordinare ID memorizzati come stringhe (anche se rappresentano numeri) in ordine numerico
• Ordinare stringhe di data memorizzate nel formato YYYYMMDD per ordine cronologico (spiegato più avanti)

4. Esempi pratici: conversione di numeri in stringhe

In MySQL esistono molti casi in cui vuoi trattare i dati numerici come stringhe. Esempi tipici includono la visualizzazione di ID o codici con zeri iniziali, o la costruzione di messaggi concatenando valori numerici con altro testo. Questa sezione presenta metodi rappresentativi e casi d’uso pratici.

4.1 Conversione con la funzione CAST

Per convertire esplicitamente un tipo numerico (come INT o DECIMAL) in un tipo stringa, usa CAST():

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

Con questo metodo, puoi concatenare facilmente colonne numeriche con altre stringhe.

4.2 Conversione con la funzione CONVERT

Puoi ottenere lo stesso risultato anche con CONVERT():

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

C’è poca differenza rispetto a CAST(), ma dal punto di vista dello standard SQL, CAST() è leggermente preferita.

4.3 Conversione implicita tramite concatenazione

Quando concatenati numeri e stringhe usando funzioni come CONCAT(), MySQL converte automaticamente i numeri in stringhe.

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

Questa conversione implicita è comunemente usata per output di reportistica di routine e formattazione dei dati.

4.4 Casi d’uso comuni nel mondo reale

• Generazione di ID con zeri iniziali Per visualizzare un ID numerico in 5 cifre, combinalo con LPAD() così: SELECT LPAD(CAST(id AS CHAR), 5, '0') AS zero_padded_id FROM users; -- Se id=7, il risultato è '00007'
• Concatenazione di date o importi in stringhe SELECT CONCAT('The total amount is ', CAST(total AS CHAR), ' yen.') AS message FROM orders; -- Se total=1500, il risultato è 'The total amount is 1500 yen.'

4.5 Note

Anche se la stringa convertita appare visivamente identica, il comportamento di “ordinamento” e “operazioni di confronto” cambia.
Ad esempio, ordinando come stringhe, '20' può comparire prima di '100' (ordine lessicografico). È importante scegliere l’approccio in base al tuo scopo.

5. Casi d’uso avanzati con la conversione di tipo

La conversione di tipo non è limitata a semplici trasformazioni numeriche o di stringa. Può anche essere applicata in vari scenari pratici nei sistemi reali. Questa sezione introduce i casi avanzati più comunemente usati e le loro considerazioni chiave.

5.1 Confrontare e Convertire Stringhe Simili a Date

Se il tuo database memorizza le date in formati come YYYYMMDD come tipi numerici o di stringa, il semplice confronto di stringhe potrebbe non comportarsi sempre come previsto.

In tali casi, convertire il valore in un tipo numerico usando CAST() consente un ordinamento e un confronto cronologico corretti.

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

Puoi anche combinare questo con la funzione REPLACE() per convertire date separate da trattini come '2024-05-01' in interi.

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

5.2 Ordinare i tipi ENUM o i valori di codice numericamente

Per i tipi ENUM o i valori di codice che rappresentano un significato numerico, potresti volerli ordinare in ordine numerico anziché lessicografico. Convertendoli in tipi numerici con CAST() prima dell’ordinamento, ottieni un ordine intuitivo.

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

5.3 Utilizzo in aggregazione e formattazione dei dati

Ad esempio, se gli importi delle vendite sono memorizzati come VARCHAR, puoi ottenere un totale accurato convertendoli esplicitamente all’interno di SUM().

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

5.4 Prevenire problemi con precisione e selezione del tipo

Per valori decimali o numerici di grandi dimensioni, la conversione al tipo DECIMAL aiuta a preservare la precisione.

Scegliere tra SIGNED e UNSIGNED è anche importante, a seconda che possano esistere valori negativi nei tuoi dati.

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

5.5 Rilevare errori o dati non validi

Se la conversione produce valori NULL o 0 inaspettati, ciò può indicare dati non validi.

Ad esempio, se una stringa non può essere convertita in un numero, CAST() può restituire 0 o NULL a seconda della modalità SQL.

In tali casi, verificare i risultati della conversione può aiutare a identificare esigenze di pulizia o validazione dei dati.

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. Differenze tra CAST e CONVERT

Quando si esegue la conversione di tipo in MySQL, si utilizza tipicamente la funzione CAST o la funzione CONVERT. Sebbene sembrino simili, ci sono differenze nell’uso e nelle caratteristiche. Questa sezione spiega tali differenze e come scegliere tra di esse.

6.1 Differenze di base

• Funzione CAST Usa la sintassi CAST(value AS type) per convertire esplicitamente un valore nel tipo specificato. È definita nello standard SQL e funziona in modo simile su molti sistemi di database. SELECT CAST('123' AS SIGNED);
• Funzione CONVERT Usa la sintassi CONVERT(value, type) per convertire un valore nel tipo specificato. In MySQL, può anche essere usata per la conversione di set di caratteri. SELECT CONVERT('123', SIGNED); -- Conversione di tipo SELECT CONVERT('hello' USING utf8mb4); -- Conversione del set di caratteri

6.2 Compatibilità con lo Standard SQL

Poiché CAST fa parte dello standard SQL internazionale, offre una migliore portabilità verso altri database (come PostgreSQL, SQL Server e Oracle). D’altra parte, CONVERT include estensioni specifiche di MySQL, in particolare per la conversione del set di caratteri, usando una sintassi come CONVERT(expr USING charset_name).

6.3 Come scegliere tra di essi

• Per la conversione di tipo (numeri, stringhe, date, ecc.) Nella maggior parte dei casi, usare CAST è la scelta più sicura. È SQL standard, altamente portabile e adatto a future migrazioni di database.
• Per la conversione di set di caratteri (ad es., sjis a utf8mb4) È necessario usare CONVERT(expr USING charset_name).
• Per casi specifici di MySQL o particolari CONVERT può offrire flessibilità aggiuntiva, ma per scopi generali, iniziare con CAST e usare CONVERT solo quando necessario.

6.4 Confronto di esempio

-- 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 Note importanti

• Se la conversione fallisce, entrambe le funzioni possono restituire NULL o 0.
• La conversione del set di caratteri non può essere eseguita con CAST.
• Il comportamento può variare a seconda della modalità SQL o della versione di MySQL, quindi è sempre consigliabile testare sia in ambienti di sviluppo che di produzione.

7. Note importanti e migliori pratiche

Quando si utilizza la conversione di tipo stringa e numerica in MySQL, esistono diversi potenziali problemi da tenere in considerazione. Questa sezione presenta le migliori pratiche per aiutare a prevenire problemi e garantire un utilizzo sicuro e accurato.

7.1 Errori o NULL/0 inaspettati da conversioni non valide

Durante l’esecuzione della conversione di tipo, se il valore di origine non è nel formato corretto, MySQL può restituire valori NULL o 0 inaspettati.

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

Come mostrato sopra, convertire una stringa non numerica in un tipo numerico restituisce 0. Pertanto, verificare sempre che i dati originali non contengano valori non validi prima della conversione.

A seconda delle impostazioni della modalità SQL, la conversione può invece generare un errore o NULL. Verificare sempre la configurazione del proprio ambiente di produzione.

7.2 Scelta della precisione e dei tipi signed/unsigned

• Per valori contenenti decimali, convertire in DECIMAL o FLOAT
• Se sono possibili valori negativi, usare SIGNED; se i valori sono interi strettamente positivi, usare UNSIGNED

Selezionare sempre il tipo appropriato in base alle caratteristiche dei propri dati.

7.3 Impatto sugli indici

L’uso di funzioni di conversione di tipo come CAST o CONVERT nelle clausole WHERE o ORDER BY può impedire l’utilizzo degli indici, provocando un degrado delle prestazioni.

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

Per set di dati di grandi dimensioni o query critiche per le prestazioni, è consigliabile uniformare i tipi di colonna nella progettazione dello schema per evitare conversioni inutili.

7.4 Non fare eccessivo affidamento sulla conversione implicita

La conversione di tipo implicita in MySQL è comoda, ma può portare a comportamenti indesiderati. Per la logica importante, usare sempre CAST o CONVERT espliciti.

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

Per evitare bug sottili e incoerenze nei dati, rendere la conversione esplicita un’abitudine.

7.5 Convalidare i tipi nella fase di inserimento dati

Durante la progettazione del database, memorizzare i dati numerici come tipi numerici e i dati stringa come tipi stringa. Ridurre al minimo la necessità di conversione mediante una corretta progettazione dello schema è uno dei modi più efficaci per prevenire problemi.

8. Riepilogo

La conversione di tipo tra stringhe e numeri in MySQL è un argomento inevitabile nella gestione quotidiana dei dati, nell’aggregazione e nelle operazioni di sistema. In questo articolo, abbiamo trattato tutto, dall’uso di base alle tecniche avanzate e alle precauzioni importanti.

Per la conversione da stringa a numero, sono disponibili metodi espliciti come CAST e CONVERT. Sebbene la conversione implicita sia spesso usata in pratica, dare priorità alla conversione esplicita migliora l’affidabilità e la qualità dei dati. Al contrario, la conversione da numero a stringa è anch’essa ampiamente utilizzata per formattare ID, codici e generare messaggi di output.

Inoltre, tecniche come applicazioni legate alle date, aggregazione e ordinamento, e rilevamento degli errori dimostrano quanto la conversione di tipo possa essere potente in scenari reali. Tuttavia, la conversione comporta anche potenziali insidie e impatti sulle prestazioni. Segui sempre le migliori pratiche discusse sopra.

Padroneggiando correttamente la conversione di tipo, puoi ampliare significativamente la tua capacità di manipolare i dati in modo efficace in MySQL.
Speriamo che questo articolo supporti il tuo lavoro quotidiano e il tuo apprendimento.

9. FAQ (Domande Frequenti)

Q1. Cosa succede se eseguo CAST di una stringa come ‘abc’ a un numero?

In MySQL, quando si tenta di convertire una stringa in un tipo numerico (come SIGNED o UNSIGNED), MySQL restituisce la parte numerica iniziale se presente; altrimenti restituisce 0.

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

Tuttavia, a seconda della modalità SQL (ad esempio, STRICT_TRANS_TABLES), il risultato può essere un errore o NULL.

Q2. Devo usare CAST o CONVERT?

Per la conversione di tipo generale (numeri, stringhe, date, ecc.), è consigliato utilizzare CAST perché aderisce allo standard SQL. Per la conversione di set di caratteri, è necessario usare la funzione CONVERT con la clausola USING. Scegli in base al tuo caso d’uso.

Q3. La conversione di tipo implicita è sufficiente?

La conversione implicita può funzionare per query piccole o per test, ma per aggregazioni critiche e sviluppo di sistemi di produzione, è fortemente consigliata la conversione esplicita (CAST o CONVERT). Scrivere conversioni esplicite aiuta a prevenire comportamenti indesiderati e bug.

Q4. L’uso della conversione di tipo in WHERE o ORDER BY disabilita gli indici?

Sì. Applicare funzioni come CAST o CONVERT a una colonna può impedire a MySQL di utilizzare gli indici definiti su quella colonna.
Se è necessario eseguire query ad alte prestazioni su grandi set di dati, uniforma i tipi di colonna in anticipo o considera l’uso di sottoquery o colonne generate.

Q5. La conversione di tipo è sicura per decimali o numeri grandi?

L’uso di DECIMAL o FLOAT può preservare la precisione, ma errori di arrotondamento, perdita di precisione o troncamento possono comunque verificarsi. Specifica sempre una precisione e una scala sufficienti per le tue esigenze.

Q6. Qual è la differenza tra ordinare come stringa e ordinare come numero?

Quando vengono ordinati come stringhe, valori come '10' possono comparire prima di '2' a causa dell’ordinamento lessicografico. Se desideri un ordine numerico, esegui sempre la conversione di tipo prima dell’ordinamento.