MySQL Funzioni: stringa, numerica, definita dall'utente, memorizzata

Che cosa sono MySQL Funzioni?

MySQL può fare molto di più che semplicemente archiviare e recuperare dati. Può anche eseguire manipolazioni sui dati prima di recuperarlo o salvarlo. È lì che MySQL Entrano in gioco le funzioni. Le funzioni sono semplicemente porzioni di codice che eseguono un'operazione e restituiscono un risultato. Alcune funzioni accettano parametri, mentre altre non ne accettano.

Vediamo brevemente un esempio. Per impostazione predefinita, MySQL salva i tipi di dati data nel formato "AAAA-MM-GG". Supponiamo di aver creato un'applicazione e che i nostri utenti desiderino che la data venga restituita nel formato "GG-MM-AAAA". Possiamo usare il MySQL funzione integrata DATE_FORMAT per raggiungere questo obiettivo. DATE_FORMAT è una delle funzioni più utilizzate in MySQLe lo analizzeremo in dettaglio più avanti in questa lezione.

Qualunque sia il suo tipo, una funzione restituisce sempre un singolo valore, può accettare zero o più parametri tra parentesi, e può essere utilizzato ovunque sia consentita un'espressione — in un elenco SELECT, in una clausola WHERE o in una clausola ORDER BY.

Perché usare MySQL Funzioni?

Ora che sappiamo cos'è una funzione, la domanda successiva è perché dovremmo inserire questo lavoro nel database.

Come mostra il diagramma sopra, una funzione riceve un valore in input, applica la logica una sola volta all'interno del motore del database e restituisce un singolo risultato a ogni applicazione che lo richiede.

I programmatori potrebbero pensare: "Perché preoccuparsi di MySQL Funzioni? Lo stesso effetto si può ottenere con un linguaggio di scripting o di programmazione. È vero che possiamo ottenerlo scrivendo una procedura nel programma applicativo.

Tornando al nostro esempio relativo alla DATA, affinché i nostri utenti possano ottenere i dati nel formato desiderato, il livello business dovrebbe occuparsi autonomamente dell'elaborazione necessaria.

Questo diventa un problema quando l'applicazione deve integrarsi con altri sistemi. Quando usiamo MySQL funzioni come DATE_FORMAT, tale funzionalità è incorporata nel database e qualsiasi applicazione che necessiti dei dati li ottiene nel formato richiesto. Questo Riduce le rilavorazioni nella logica aziendale e riduce le incongruenze dei dati..

Un altro motivo da considerare MySQL La loro funzione è quella di contribuire a ridurre il traffico di rete nelle applicazioni client/server.Il livello business deve solo richiamare la funzione memorizzata, senza dover scaricare i dati grezzi dalla rete per elaborarli. In media, l'utilizzo delle funzioni può migliorare notevolmente le prestazioni complessive del sistema.

Tipi di MySQL funzioni

Una volta definiti il ​​"cosa" e il "perché", possiamo ora esaminare le tre famiglie di funzioni MySQL Offre: funzioni integrate, funzioni memorizzate e funzioni definite dall'utente.

Funzioni integrate

MySQL viene fornito con una serie di funzioni integrate — funzioni già implementate nel MySQL server. Ci consentono di eseguire molti tipi di manipolazione sui dati e rientrano nei seguenti gruppi comunemente utilizzati.

• Funzioni di stringa – operare su tipi di dati stringa
• Funzioni numeriche – operare su tipi di dati numerici
• Funzioni data – operare su tipi di dati di data
• Funzioni aggregate – operare su tutti i tipi di dati di cui sopra e produrre set di risultati riepilogativi.
• altre funzioni - MySQL Supporta anche altri tipi di funzioni integrate, ma in questa lezione ci limiteremo ai gruppi sopra menzionati.

Analizziamo ora nel dettaglio ciascuno dei gruppi menzionati in precedenza. Spiegheremo le funzioni più utilizzate prendendo come esempio il nostro database "Myflixdb".

Funzioni di stringa

Le funzioni stringa operano su valori di testo. Nella nostra tabella dei film, i titoli sono memorizzati utilizzando un mix di lettere minuscole e maiuscole. Supponiamo di voler eseguire una query che restituisca i titoli in maiuscolo. La funzione "UCASE" accetta una stringa come parametro e converte ogni lettera in maiuscolo, come dimostra lo script seguente.

SELECT `movie_id`, `title`, UCASE(`title`) AS `upper_case_title` FROM `movies`;

QUI

• UCASE(`title`) è la funzione integrata che prende il titolo come parametro e lo restituisce in lettere maiuscole.
• COME `titolo_maiuscolo` assegna un alias alla colonna calcolata, in modo che il set di risultati contenga un'intestazione leggibile anziché l'espressione grezza.

Eseguendo lo script precedente in MySQL L'analisi con Workbench su Myflixdb ci fornisce i risultati mostrati di seguito.

Oltre a UCASE, vale la pena ricordare due compagni: LCA converte una stringa in minuscolo e CONCAT unisce due o più stringhe in una sola. Per l'elenco completo, fare riferimento a MySQL riferimento alla funzione stringa.

Funzioni numeriche

Come accennato in precedenza, le funzioni numeriche operano su tipi di dati numerici. Possiamo anche eseguire calcoli matematici su dati numerici direttamente nelle nostre istruzioni SQL.

Operatori aritmetici

MySQL Supporta i seguenti operatori aritmetici, che possono essere utilizzati per eseguire calcoli nelle istruzioni SQL.

Seguono esempi per ciascun operatore.

Divisione intera (DIV) — DIV scarta la parte frazionaria e restituisce solo il numero intero.

SELECT 23 DIV 6;

L'esecuzione dello script sopra riportato ci fornisce 3.

Operatore di divisione (/) — a differenza di DIV, l'operatore di divisione mantiene la parte decimale del risultato.

SELECT 23 / 6;

L'esecuzione dello script sopra riportato ci fornisce 3.8333.

Subtracoperatore di zione (-)

SELECT 23 - 6;

L'esecuzione dello script sopra riportato ci fornisce 17.

Operatore di addizione (+)

SELECT 23 + 6;

L'esecuzione dello script sopra riportato ci fornisce 29.

Operatore di moltiplicazione (*)

SELECT 23 * 6 AS `multiplication_result`;

Risultato:

Operatore modulo (% o MOD)

L'operatore modulo divide N per M e ci dà il resto. Vediamo un esempio con l'operatore modulo, usando gli stessi valori degli esempi precedenti.

SELECT 23 % 6;
-- OR, equivalently:
SELECT 23 MOD 6;

L'esecuzione di uno dei due script ci fornisce 5.

Diamo ora un'occhiata ad alcune delle funzioni numeriche comuni in MySQL.

PAVIMENTO – questa funzione rimuove le cifre decimali da un numero e lo arrotonda per difetto al numero intero più vicino. Lo script mostrato di seguito ne illustra l'utilizzo.

SELECT FLOOR(23 / 6) AS `floor_result`;

Risultato:

ROTONDO – questa funzione arrotonda un numero al numero intero più vicino. Poiché 23 / 6 è uguale a 3.8333, ARROTONDA restituisce 4 mentre FONDO restituisce 3: le due funzioni non sono intercambiabili.

SELECT ROUND(23 / 6) AS `round_result`;

Risultato:

RAND – Questa funzione genera un numero casuale. Il suo valore cambia ogni volta che la funzione viene chiamata. Lo script mostrato di seguito ne illustra l'utilizzo.

SELECT RAND() AS `random_result`;

Funzioni data

Le funzioni relative alle date operano su tipi di dati data e data-ora. DATE_FORMAT è la funzione che risolve il problema "AAAA-MM-GG contro GG-MM-AAAA" descritto nell'introduzione.

FORMATO DATA Lo script seguente accetta due parametri: il valore della data da formattare e una stringa di formato creata a partire da segnaposto. Lo script restituisce ogni data di rilascio nel formato giorno-mese-anno richiesto dai nostri utenti.

SELECT `title`, DATE_FORMAT(`date_released`, '%d-%m-%Y') AS `formatted_date`
FROM `movies`;

Di seguito sono elencati i segnaposto di formato più utilizzati.

Altre tre funzioni relative alle date ricorrono costantemente nel lavoro quotidiano:

• CURDATE() Restituisce la data corrente nel formato AAAA-MM-GG.
• ADESSO() restituisce la data corrente and tempo.
• DATA DIFFERENZA (d1, d2) Restituisce il numero di giorni tra due date, ovvero l'elemento base di qualsiasi report sugli affitti non pagati.

Per l'elenco completo, vedere il MySQL Riferimento alla funzione data e ora.

Funzioni memorizzate

Le funzioni integrate coprono i casi più comuni. Quando una regola aziendale è più specifica, ne scriviamo una personalizzata, ed è a questo che serve una funzione memorizzata.

Le funzioni memorizzate si comportano esattamente come le funzioni predefinite, con la differenza che vengono definite dall'utente. Una volta creata, una funzione memorizzata può essere utilizzata nelle istruzioni SQL esattamente come qualsiasi altra funzione. La sintassi di base è mostrata di seguito.

CREATE FUNCTION sf_name ([parameter(s)])
RETURNS data_type
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
    -- procedural statements
END

QUI

• “CREA FUNZIONE sf_name ([parametro/i])” è obbligatorio e comunica MySQL server per creare una funzione denominata `sf_name` con parametri opzionali definiti all'interno delle parentesi.
• “RESTITUISCE il tipo di dati” è obbligatorio e specifica il tipo di dati che la funzione restituisce.
• "DETERMINISTICO" dichiara che la funzione restituisce lo stesso valore ogni volta che vengono forniti gli stessi argomenti. “NON DETERMINISTICO” dichiara il contrario.
• “INIZIO… FINE” racchiude il codice procedurale che la funzione esegue.

Supponiamo di voler sapere quali film noleggiati hanno superato la data di restituzione. Possiamo creare una funzione memorizzata che accetta la data di restituzione come parametro e la confronta con la data corrente sul server. Se la data corrente è successiva alla data di restituzione, il film è scaduto e restituiamo "Sì"; altrimenti restituiamo "No".

DELIMITER |
CREATE FUNCTION sf_past_movie_return_date (return_date DATE)
RETURNS VARCHAR(3)
NOT DETERMINISTIC
BEGIN
    DECLARE sf_value VARCHAR(3);
    IF CURDATE() > return_date THEN
        SET sf_value = 'Yes';
    ELSEIF CURDATE() <= return_date THEN
        SET sf_value = 'No';
    END IF;
    RETURN sf_value;
END|
DELIMITER ;

⚠️ Attenzione: non etichettare questa funzione come DETERMINISTICA. Il corpo chiama CURDATE(), quindi lo stesso argomento può restituire "No" oggi e "Sì" domani. Dichiarare una funzione dipendente dal tempo DETERMINISTIC inganna l'ottimizzatore e non è sicuro per la replica basata su istruzioni. Utilizzare NON DETERMINISTICO ogni volta che il corpo chiama CURDATE(), NOW() o RAND().

L'esecuzione dello script sopra riportato crea la funzione memorizzata `sf_past_movie_return_date`. Ora testiamola.

SELECT `movie_id`, `membership_number`, `return_date`, CURDATE(),
       sf_past_movie_return_date(`return_date`) AS `is_overdue`
FROM `movierentals`;

Eseguendo lo script precedente in MySQL L'analisi con Workbench su myflixdb ci fornisce i seguenti risultati.

Si notino le due righe NULL. Quando `return_date` è NULL, entrambi i confronti restituiscono NULL anziché TRUE o FALSE, quindi nessuno dei due rami IF viene eseguito e la funzione restituisce NULL, il risultato previsto, poiché un film non restituito non ha una data di restituzione con cui confrontarsi.

Funzioni definite dall'utente

Quando SQL da solo non è abbastanza veloce, MySQL consente una terza opzione. Le funzioni definite dall'utente (UDF) sono scritte in un linguaggio compilato come C or C++Le UDF vengono integrate in una libreria condivisa e registrate sul server. Una volta aggiunte, vengono richiamate come qualsiasi altra funzione. Poiché una UDF viene eseguita come codice nativo all'interno del processo del server, è adatta a calcoli complessi, ma un bug al suo interno può mandare in crash il server; per questo motivo, le UDF vengono utilizzate molto meno frequentemente rispetto alle funzioni memorizzate.

Funzioni predefinite, funzioni memorizzate o funzioni definite dall'utente: quale scegliere?

Tutte e tre le famiglie restituiscono un singolo valore e possono essere richiamate da qualsiasi istruzione SQL, ma differiscono per chi le scrive, dove vengono eseguite e quanto rischio comportano. La tabella seguente riassume tali differenze.

Come regola generale, inizia con una funzione integrata. Se nessuna è adatta, scrivi una funzione memorizzata in modo che la regola risieda in un unico punto. Ricorri a una UDF solo quando una funzione memorizzata risulta sensibilmente troppo lenta.

DOMANDE FREQUENTI