Questo documento fa parte di una serie che fornisce informazioni chiave e indicazioni relative alla pianificazione e all'esecuzione delle migrazioni dei database Oracle® 11g/12c in Cloud SQL per MySQL versione 5.7, istanze di seconda generazione. La serie include le seguenti parti:
- Migrazione degli utenti Oracle a Cloud SQL per MySQL: terminologia e funzionalità
- Migrazione degli utenti Oracle a Cloud SQL per MySQL: tipi di dati, utenti e tabelle
- Migrazione degli utenti Oracle a Cloud SQL per MySQL: query, procedure archiviate, funzioni e trigger (questo documento)
- Migrazione degli utenti Oracle a Cloud SQL per MySQL: sicurezza, operazioni, monitoraggio e logging
Query
Oracle e Cloud SQL per MySQL supportano lo standard ANSI SQL. In genere la migrazione delle istruzioni SQL è semplice utilizzando solo elementi di sintassi di base (ad esempio, non specificando funzioni scalari o qualsiasi altra funzionalità estesa di Oracle). La seguente sezione illustra gli elementi di query Oracle comuni e i corrispondenti equivalenti di Cloud SQL per MySQL.
Sintassi di base SELECT e FROM
Nome della funzionalità Oracle o del nome della sintassi | Panoramica o implementazione di Oracle | Supporto per MySQL | Soluzione equivalente o alternativa MySQL |
---|---|---|---|
Sintassi di base SQL per il recupero dei dati | SELECT FROM WHERE GROUP BY HAVING ORDER BY |
Sì | SELECT FROM WHERE GROUP BY HAVING ORDER BY |
SELECT per la stampa dell'output |
SELECT 1 FROM DUAL |
Sì | SELECT 1 OR SELECT 1 FROM DUAL |
Alias colonna | SELECT COL1 AS C1 |
Sì | SELECT COL1 AS C1 OR SELECT COL1 C1 |
Nome tabella sensibilità alle maiuscole |
Nessuna sensibilità alle maiuscole (ad esempio, il nome della tabella può essere orders e/o ORDERS ). |
No | Distingue tra maiuscole e minuscole in base al nome della tabella definito (ad esempio, il nome della tabella può essere solo orders o ORDERS ). |
Ulteriori dettagli sulla sintassi SELECT
di MySQL.
- Visualizzazioni in linea
- Le viste in linea (note anche come tabelle derivate) sono istruzioni
SELECT
, situate nella clausolaFROM
e utilizzate come sottoquery. - Le viste in linea possono semplificare le query complesse rimuovendo i calcoli composti o le operazioni di join, e accorpando al contempo diverse query separate in un'unica query semplificata.
- Nota di conversione: le viste in linea Oracle non richiedono l'uso di alias, mentre MySQL richiede alias specifici per ogni visualizzazione in linea.
- Le viste in linea (note anche come tabelle derivate) sono istruzioni
La tabella seguente presenta un esempio di conversione da Oracle a MySQL, sotto forma di visualizzazione in linea.
Oracle 11g/12c |
---|
SQL> SELECT FIRST_NAME, DEPARTMENT_ID, SALARY, DATE_COL FROM EMPLOYEES, (SELECT SYSDATE AS DATE_COL FROM DUAL); L'output è simile al seguente: FIRST_NAME DEPARTMENT_ID SALARY DATE_COL -------------------- ------------- ---------- --------- Steven 90 24000 30-JUL-19 Neena 90 17000 30-JUL-19 Lex 90 17000 30-JUL-19 |
Cloud SQL per MySQL 5.7 |
Senza alias per la visualizzazione in linea:mysql> SELECT FIRST_NAME, DEPARTMENT_ID, SALARY, DATE_COL FROM EMPLOYEES, (SELECT SYSDATE() AS DATE_COL FROM DUAL); Aggiunta di un alias alla visualizzazione in linea: mysql> SELECT FIRST_NAME, DEPARTMENT_ID, SALARY, DATE_COL FROM EMPLOYEES, (SELECT SYSDATE() AS DATE_COL FROM DUAL) AS A1; L'output è simile al seguente: +-------------+---------------+----------+---------------------+ | FIRST_NAME | DEPARTMENT_ID | SALARY | DATE_COL | +-------------+---------------+----------+---------------------+ | Steven | 90 | 23996.00 | 2019-07-30 09:28:00 | | Neena | 90 | 22627.00 | 2019-07-30 09:28:00 | | Lex | 90 | 22627.00 | 2019-07-30 09:28:00 | |
Istruzioni JOIN
Le istruzioni JOIN
Oracle sono supportate dalle istruzioni MySQL JOIN
, ad eccezione della clausola FULL JOIN
. Inoltre, le istruzioni JOIN
di MySQL supportano l'utilizzo di una sintassi alternativa, come la clausola USING
, la clausola WHERE
anziché la clausola ON
e l'uso di SUBQUERY
nell'istruzione JOIN
.
La tabella seguente presenta un esempio di conversione JOIN.
Tipo di JOIN Oracle | Supportato da MySQL | Sintassi JOIN MySQL |
---|---|---|
INNER JOIN |
Sì | SELECT E.FIRST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID; |
CROSS JOIN |
Sì | SELECT E.FIRST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E CROSS JOIN DEPARTMENTS D |
FULL JOIN |
No | Per trovare una soluzione alternativa, valuta l'utilizzo di UNION con le istruzioni LEFT
e RIGHT JOIN . |
LEFT JOIN [ OUTER ] |
Sì | SELECT E.FIRST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID; |
RIGHT JOIN [ OUTER ] |
Sì | SELECT E.FIRST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID; |
SUBQUERY |
Sì | SELECT E.FIRST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E JOIN (SELECT * FROM DEPARTMENTS)D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID; |
UNION, UNION ALL, INTERSECT e MENO
MySQL non supporta le funzioni Oracle INTERSECT
e MINUS
, ad eccezione delle funzioni UNION
e UNION ALL
:
UNION
: associa l'insieme di risultati di due o più istruzioniSELECT
ed elimina i record duplicati.UNION ALL
: associa l'insieme di risultati di due o più istruzioniSELECT
senza eliminare i record duplicati.INTERSECT
: restituisce l'intersezione di due o più istruzioniSELECT
solo se un record esiste in entrambi i set di dati.MINUS
: confronta due o più istruzioniSELECT
, restituendo solo righe distinte dalla prima query che non sono restituite dalle altre istruzioni.
Note sulle conversioni
Quando esegui la conversione dalle funzioni Oracle INTERSECT
e MINUS
a MySQL, utilizza le istruzioni JOIN
e IN
e EXISTS
come soluzione alternativa.
Esempi
Funzione Oracle | Implementazione di Oracle | Supporto per MySQL | Soluzione equivalente o alternativa MySQL |
---|---|---|---|
UNION |
SELECT COL1 FROM TBL1 UNION SELECT COL1 FROM TBL2 |
Sì | SELECT COL1 FROM TBL1 UNION SELECT COL1 FROM TBL2 |
UNION ALL |
SELECT COL1 FROM TBL1 UNION ALL SELECT COL1 FROM TBL2 |
Sì | SELECT COL1 FROM TBL1 UNION ALL SELECT COL1 FROM TBL2 |
INTERSECT |
SELECT COL1 FROM TBL1 INTERSECT SELECT COL1 FROM TBL2 |
No | SELECT COL1 FROM TBL1 WHERE COL1 IN (SELECT COL1 FROM TBL2) |
MINUS |
SELECT COL1 FROM TBL1 MINUS SELECT COL1 FROM TBL2 |
No | SELECT A.COL1 FROM TBL1 A LEFT JOIN TBL2 B ON USING(COL1) WHERE B.COL1 IS NULL |
Funzioni scalari (a riga singola) e di gruppo
MySQL offre un elenco completo di funzioni scalari (a riga singola) e di aggregazione. Alcune funzioni MySQL sono simili alle rispettive controparti Oracle (per nome e funzionalità o con un nome diverso ma con funzionalità simili). Anche se le funzioni MySQL possono avere nomi identici alle loro controparti Oracle, possono presentare funzionalità diverse.
Le seguenti tabelle descrivono dove Oracle e MySQL sono equivalenti per nome e funzionalità (specificati con "Yes") e dove è consigliata una conversione (tutti i casi diversi da "Yes").
Funzioni caratteri
Funzione Oracle | Specifica o implementazione della funzione Oracle | Equivalente MySQL | Funzione corrispondente MySQL | Implementazione o specifica della funzione MySQL |
---|---|---|---|---|
CONCAT(str1,str2) |
Restituisce str1 concatenato con str2:CONCAT('A', 1) = A1 |
Sì | CONCAT |
Equivalente a Oracle:CONCAT('A', 1) = A1 |
LOWER/UPPER |
Restituisce char, con tutte le lettere minuscole o maiuscole:LOWER('SQL') = sql |
Sì | LOWER/UPPER |
Equivalente a Oracle:LOWER('SQL') = sql |
LPAD/RPAD(expr1,n,expr2) |
Restituisce expr1, a sinistra o a destra con spaziatura interna a n caratteri
con la sequenza di caratteri in expr2:LPAD('A',3,'*') = **A |
Sì | LPAD/RPAD |
Equivalente a Oracle:LPAD('A',3,'*') = **A |
SUBSTR(char,p,n) |
Restituisce una parte di char, che inizia nella posizione del carattere p,
lunghezza della lunghezza della sottostringa n caratteri:SUBSTR('MySQL', 3, 3) = SQL |
Sì | SUBSTR(char,p,n) |
Equivalente a Oracle:SUBSTR('MySQL', 3, 3) = SQL |
INSTR(index,str) |
Restituisce la posizione (indice) della stringa str:INSTR('MySQL', 'y') = 2 |
Sì | INSTR |
Equivalente a Oracle:INSTR('MySQL', 'y') = 2 |
REPLACE(char,str1,str2) |
Restituisce un carattere con ogni occorrenza di una stringa di ricerca sostituita da una stringa di sostituzione: REPLACE('ORADB', 'ORA', 'MySQL') |
Sì | REPLACE(char,str1,str2) |
Equivalente a Oracle:REPLACE('ORADB', 'ORA', 'MySQL') |
TRIM(str) |
Elimina i caratteri iniziali e finali (o entrambi) da una stringa:TRIM(both '-' FROM '-MySQL-') = MySQL |
Sì | TRIM(str) |
Equivalente a Oracle:TRIM(both '-' FROM '-MySQL-') = MySQL |
LTRIM/RTRIM(str) |
Rimuove dall'estremità sinistra o destra della stringa tutti i caratteri visualizzati nella ricerca:LTRIM(' MySQL', ' ') = MySQL |
Parzialmente | LTRIM/RTRIM(str) |
Funzione Oracle R/LTRIM tranne una sostituzione del parametro (spazio vuoto o stringa). MySQL R/LTRIM elimina solo
gli spazi vuoti, accettando solo la stringa di input:LTRIM(' MySQL') = MySQL |
ASCII(char) |
Restituisce la rappresentazione decimale nel set di caratteri del database del primo carattere di char: ASCII('A') = 65 |
Sì | ASCII(char) |
Equivalente a Oracle:ASCII('A') = 65 |
CHR(char) |
Restituisce il valore del codice ASCII, che è un valore numerico compreso tra 0 e 225, in un carattere:CHR(65) = A |
Parzialmente con nome funzione diverso | CHAR(char) |
MySQL utilizza la funzione CHAR per la stessa funzionalità; pertanto,
devi modificare il nome di una funzione:CHAR(65) = A |
LENGTH(str) |
Restituisce la lunghezza di una determinata stringa:LENGTH ('MySQL') = 5 |
Sì | LENGTH(str) |
Equivalente a Oracle:LENGTH('MySQL') = 5 |
REGEXP_REPLACE(str1,expr,str2) |
Cerca un pattern di espressione regolare in una stringa:REGEXP_REPLACE('John', '[hn].', '1') = Jo1 |
No | N/A | Supportata solo da MySQL versione 8. Come soluzione alternativa, utilizza la funzione REPLACE , se possibile, o convertila a livello di applicazione |
REGEXP_SUBSTR(str,expr) |
Estende la funzionalità della funzione SUBSTR cercando un pattern di espressione regolare in una stringa:REGEXP_SUBSTR('https://console.cloud.google.com/sql/instances','https://([[:alnum:]]+\.?){3,4}/?') = https://console.cloud.google.com/ |
No | N/A | Funzionalità supportata solo da MySQL versione 8. Per trovare una soluzione alternativa, utilizza la funzione SUBSTR , se possibile, oppure converti la funzionalità al livello di applicazione. |
REGEXP_COUNT(str,expr) |
Restituisce il numero di volte in cui un pattern si verifica in una stringa di origine. | No | N/A | Per una soluzione alternativa, converti la funzionalità al livello di applicazione. |
REGEXP_INSTR(index,expr) |
Cerca una posizione stringa (indice) per un modello di espressione regolare. | No | N/A | Funzionalità supportata solo da MySQL versione 8. |
REVERSE(str) |
Restituisce una stringa invertitaREVERSE('MySQL') = LQSyM |
Sì | REVERSE |
Equivalente a Oracle:REVERSE('MySQL') = LQSyM |
Funzioni numeriche
Funzione Oracle | Specifica o implementazione della funzione Oracle | Equivalente MySQL | Funzione corrispondente MySQL | Implementazione o specifica della funzione MySQL |
---|---|---|---|---|
ABS(n) |
Valore assoluto di n: ABS(-4.6) = 4.6 |
Sì | ABS |
Equivalente a Oracle:ABS(-4.6) = 4.6 |
CEIL(n) |
Restituisce il numero intero più piccolo maggiore di o uguale a n:CEIL(21.4) = 22 |
Sì | CEIL |
Equivalente a Oracle:CEIL(21.4) = 22 |
FLOOR(n) |
Restituisce il numero intero più grande uguale o inferiore a n: FLOOR(-23.7) = -24 |
Sì | FLOOR |
Equivalente a Oracle:FLOOR(-23.7) = -24 |
MOD(m,n) |
Restituisce il resto di m diviso per n:MOD(10, 3) = 1 |
Sì | MOD(m,n) |
Equivalente a Oracle:MOD(10,3) = 1 |
ROUND(m,n) |
Restituisce m arrotondato a n posizioni interi a destra del punto decimale:ROUND(1.39,1) = 1.4 |
Sì | ROUND |
Equivalente a Oracle:ROUND(1.39,1) = 1.4 |
TRUNC(n1, n2) |
Restituisce n1 troncato a n2 posizioni decimali:TRUNC(99.999) = 99 TRUNC(99.999,0) = 99 |
Parzialmente con nome funzione diverso | TRUNCATE(n1, n2) |
La funzione TRUNCATE di MySQL deve accettare un numero di input e un
numero intero per specificare il livello di accuratezza alla destra del punto
decimale:TRUNCATE(99.999,0) = 99 |
Funzioni di data e ora
Funzione Oracle | Specifica o implementazione della funzione Oracle | Equivalente MySQL | Funzione corrispondente MySQL | Implementazione o specifica della funzione MySQL |
---|---|---|---|---|
SYSDATE |
Restituisce la data e l'ora correnti impostate per il sistema operativo su cui si trova il server di database:SELECT SYSDATE FROM DUAL = 31-JUL-2019 |
Parzialmente | SYSDATE() |
SYSDATE() MySQL deve includere parentesi e restituire un formato di data/ora diverso rispetto alla funzione SYSDATE di Oracle:SELECT SYSDATE() FROM DUAL; = 2019-01-31 10:01:01.0 Tieni presente che la formattazione di data e ora può essere modificata a livello di sessione. |
SYSTIMESTAMP |
Restituisce la data di sistema, inclusi i secondi frazionari e il fuso orario:SELECT SYSTIMESTAMP FROM DUAL = 01-JAN-19 07.37.11.622187000 AM +00:00 |
Parzialmente con nome funzione diverso | CURRENT_TIMESTAMP |
MySQL restituisce una formattazione di data/ora diversa da quella di Oracle. È necessaria una formattazione
della data (o una funzione di data diversa) per corrispondere alla formattazione
originale di data/ora:SELECT CURRENT_TIMESTAMP FROM DUAL = 2019-01-31 06:55:07 |
LOCAL_TIMESTAMP |
Restituisce la data e l'ora correnti nel fuso orario della sessione in un valore del tipo di dati TIMESTAMP :SELECT LOCAL_TIMESTAMP FROM DUAL = 01-JAN-19 10.01.10.123456 PM |
Parzialmente con una formattazione di data/ora diversa. | LOCAL_TIMESTAMP |
MySQL restituisce una formattazione di data/ora diversa da quella di Oracle. È necessaria la formattazione di data/ora (o una funzione di data diversa) per rispettare la formattazione originale di data/ora:SELECT LOCAL_TIMESTAMP FROM DUAL = 2019-01-01 10:01:01.0 |
CURRENT_DATE |
Restituisce la data corrente nel fuso orario della sessione:SELECT CURRENT_DATE FROM DUAL = 31-JAN-19 |
Parzialmente con formattazione di data/ora diversa | CURRENT_DATE |
MySQL restituisce una formattazione di data/ora diversa da quella di Oracle. È necessaria la formattazione di data/ora (o una funzione di data diversa) per rispettare la formattazione originale di data/ora:SELECT CURRENT_DATE FROM DUAL = 2019-01-31 |
CURRENT_TIMESTAMP |
Restituisce la data e l'ora correnti nel fuso orario della sessione:SELECT CURRENT_TIMESTAMP FROM DUAL = 31-JAN-19 06.54.35.543146 AM +00:00 |
Parzialmente con formattazione di data/ora diversa | CURRENT_TIMESTAMP |
MySQL restituisce una formattazione di data/ora diversa da quella di Oracle. La formattazione di data/ora è obbligatoria (o l'utilizzo di una funzione di data diversa) affinché corrisponda alla formattazione originale di data/ora:SELECT CURRENT_TIMESTAMP FROM DUAL = 2019-01-31 06:55:07 |
ADD_MONTHS |
Restituisce la data più mesi interi:ADD_MONTHS(SYSDATE, 1) = 31-JAN-19 |
Parzialmente con nome funzione diverso | ADDDATE |
Per ottenere la stessa funzionalità, MySQL utilizza la funzione ADDDATE :ADDDATE(SYSDATE(), 1) = 2019-08-01 06:42:49.0 Per impostazione predefinita, MySQL restituisce data/ora e intervallo/formato diversi rispetto a Oracle. La formattazione di data/ora è obbligatoria (o una funzione di data diversa) per corrispondere alla formattazione di data/ora originale. |
EXTRACT (parte della data) |
Restituisce il valore di un campo data/ora specificato da un'espressione di data/ora o intervallo:EXTRACT(YEAR FROM DATE '2019-01-31') = 2019 |
Sì | EXTRACT (parte della data) |
Equivalente a Oracle:EXTRACT(YEAR FROM DATE '2019-01-31') = 2019 |
LAST_DAY |
Restituisce la data dell'ultimo giorno del mese:LAST_DAY('01-JAN-2019') = 31-JAN-19 |
Parzialmente con formattazione di data/ora diversa | LAST_DAY |
MySQL restituisce una formattazione di data/ora diversa da quella di Oracle. La formattazione di data/ora è obbligatoria (o una funzione di data diversa) per corrispondere alla formattazione originale di data/ora:LAST_DAY('2019-01-01') = 2019-01-31 |
MONTH_BETWEEN |
Restituisce il numero di mesi compresi tra le date date1 e data 2:MONTHS_BETWEEN( SYSDATE, SYSDATE-60) = 1.96 |
Parzialmente con nome funzione diverso | PERIOD_DIFF(date1,date2) |
La funzione MySQL PERIOD_DIFF restituisce la differenza in mesi come numero intero tra due periodi (formattati come YYMM o YYYYMM ):PERIOD_DIFF( '201903', '201901') = 2 Per ottenere gli stessi valori della funzione MONTH_BETWEEN Oracle, sarà necessaria una conversione più specifica |
TO_CHAR (data/ora) |
Converte un tipo di dati data/ora o timestamp in un valore del tipo di dati VARCHAR2 nel formato specificato dal formato data:TO_CHAR( SYSDATE,'DD-MM-YYYY HH24:MI:SS') = 01-01-2019 10:01:01 |
Parzialmente con nome funzione diverso | DATE_FORMAT |
La funzione MySQL DATE_FORMAT formatta una data come specificato da una definizione del formato data:DATE_FORMAT( SYSDATE(),'%d-%m-%Y %H:%i:%s') = 01-01-2019 10:01:01 |
Funzioni di codifica e decodifica
Funzione Oracle | Specifica o implementazione della funzione Oracle | Equivalente MySQL | Funzione corrispondente MySQL | Implementazione o specifica della funzione MySQL |
---|---|---|---|---|
DECODE |
Confronta l'espressione con ogni valore di ricerca uno alla volta utilizzando la funzionalità di un'istruzione IF-THEN-ELSE |
No | CASE |
Utilizza l'istruzione CASE di MySQL per ottenere una funzionalità simile. |
DUMP |
Restituisce un valore VARCHAR2 contenente il codice del tipo di dati, la lunghezza in byte e la rappresentazione interna di una data espressione. |
No | N/A | Non supportati. |
ORA_HASH |
Calcola un valore hash per una data espressione. | No | MD5/SHA |
Utilizza MySQL MD5 per il checksum a 128 bit o la funzione SHA per il checksum a 160 bit per generare valori hash |
Funzioni di conversione
Funzione Oracle | Specifica o implementazione della funzione Oracle | Equivalente MySQL | Funzione corrispondente MySQL | Implementazione o specifica della funzione MySQL |
---|---|---|---|---|
CAST |
Converte un tipo di dati incorporato o un valore di raccolta in un altro
tipo di dati integrato o valore di raccolta: CAST('1' as int) + 1 = 2 |
Parzialmente | CAST |
La funzione CAST di MySQL è simile alla funzionalità di Oracle, ma in
alcuni casi deve essere modificata a seconda che sia richiesta una conversione esplicita o
implicita:CAST('1' AS SIGNED) + 1 = 2 |
CONVERT |
Converte una stringa di caratteri da un set di caratteri a un altro: CONVERT('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') = ?? ?? ?? A B C |
Parzialmente | CONVERT |
La funzione CONVERT di MySQL richiede alcune modifiche alla sintassi e ai parametri per restituire i risultati esatti come Oracle:CONVERT('Ä Ê Í A B C ' USING utf8) = Ä Ê Í A B C |
TO_CHAR (stringa/numerico) |
La funzione converte un numero o una data in una stringa: TO_CHAR(22.73,'$99.9') = $22.7 |
No | FORMAT |
La funzione FORMAT di MySQL esegue il formato "#,###.##"
da un numero, arrotondandolo a un certo numero di cifre decimali e poi
restituisce il risultato come stringa. Ha una funzionalità diversa da
Oracle:CONCAT('$', FORMAT(22.73, 1)) = $22.7 |
TO_DATE |
La funzione Oracle TO_DATE converte una stringa in una data in base al formato data/ora specifico dell'origine:TO_DATE( '2019/01/01', 'yyyy-mm-dd') = 01-JAN-2019 |
Parzialmente con nome della funzione e formattazione di data/ora diversa | STR_TO_DATE |
La funzione MySQL STR_TO_DATE accetta una stringa e restituisce una data specificata tramite il formato di data/ora:STR_TO_DATE( '2019/01/01', '%Y/%m/%d') = 2019-01-01 |
TO_NUMBER |
Converte l'espressione in un valore di un tipo di dati NUMBER :TO_NUMBER('01234') = 1234 |
No | CAST |
In alternativa, utilizza la funzione CAST di MySQL per restituire lo stesso risultato di Oracle TO_NUMBER :CAST('01234' as SIGNED) = 1234 |
Funzioni SELECT condizionali
Funzione Oracle | Specifica o implementazione della funzione Oracle | Equivalente MySQL | Funzione corrispondente MySQL | Implementazione o specifica della funzione MySQL |
---|---|---|---|---|
CASE |
L'istruzione CASE sceglie da una sequenza di condizioni ed esegue un'istruzione corrispondente con la sintassi seguente:CASE WHEN condition THEN result [WHEN ...] [ELSE result] END |
Sì | CASE |
Oltre alla funzione CASE , MySQL supporta anche l'utilizzo della gestione condizionale IF/ELSE all'interno dell'istruzione SELECT :CASE WHEN condition THEN result [WHEN ...] [ELSE result] END |
Funzioni nulle
Funzione Oracle | Specifica o implementazione della funzione Oracle | Equivalente MySQL | Funzione corrispondente MySQL | Implementazione o specifica della funzione MySQL |
---|---|---|---|---|
COALESCE |
Restituisce la prima espressione con valore non null nell'elenco di espressioni: COALESCE( null, '1', 'a') = a |
Sì | COALESCE |
Equivalente a Oracle:COALESCE( null, '1', 'a') = 1 |
NULLIF |
Confronta espr1 ed espr2m. Se sono uguali, la funzione restituisce null. Se
non sono uguali, la funzione restituisce expr1: NULLIF('1', '2') = a |
Sì | NULLIF |
Equivalente a Oracle:NULLIF('1', '2') = a |
NVL |
Sostituisci null (restituito come vuoto) con una stringa nei risultati di una query:
NVL(null, 'a') = a |
No | IFNULL |
La funzione equivalente MySQL è la funzione IFNULL , che
sostituisce i valori nulli con una determinata stringa:IFNULL(null, 'a') = a |
NVL2 |
Determinare il valore restituito da una query a seconda che un'espressione specificata sia nulla o meno. |
No | CASE |
L'istruzione CASE sceglie da una sequenza di condizioni ed esegue un'istruzione corrispondente: CASE WHEN condition THEN result [WHEN ...] [ELSE result] END |
Funzioni di ambiente e identificatore
Funzione Oracle | Specifica o implementazione della funzione Oracle | Equivalente MySQL | Funzione corrispondente MySQL | Implementazione o specifica della funzione MySQL |
---|---|---|---|---|
SYS_GUID |
Genera e restituisce un identificatore univoco globale (valore RAW) composto da 16 byte:SELECT SYS_GUID() FROM DUAL = 8EFA4A31468B4C6DE05011AC0200009E |
No | REPLACE e UUID |
Per trovare una soluzione alternativa, utilizza le funzioni MySQL REPLACE e UUID
per simulare la funzione Oracle SYS_GUID :REPLACE( UUID(), '-', '') |
UID |
Restituisce un numero intero che identifica in modo univoco l'utente della sessione (l'utente che ha eseguito l'accesso): SELECT UID FROM DUAL = 43 |
No | N/A | N/A |
USER |
Restituisce il nome del nome utente della sessione corrente:SELECT USER FROM DUAL = UserName |
Parzialmente | USER + INSTR + SUBSTR |
La funzione UTENTE MySQL restituisce il nome utente insieme al server di connessione (root@IP ). Per recuperare solo il nome utente, utilizza altre funzioni di supporto:SELECT SUBSTR(USER(), 1, INSTR(USER(), '@') -1) FROM DUAL = root |
USERENV |
Restituisce informazioni sulla sessione utente corrente con la configurazione del parametro attuale:SELECT USERENV('LANGUAGE') FROM DUAL = ENGLISH_AMERICA.AL32UTF8 |
No | SHOW SESSION VARIABLES |
Utilizza l'istruzione MySQL SHOW SESSION VARIABLES per visualizzare le impostazioni per la sessione
corrente:SHOW SESSION VARIABLES LIKE '%collation%'; = utf8_general_ci |
ROWID |
Il server Oracle assegna a ogni riga in ogni tabella un valore ROWID univoco per identificare la riga nella tabella. ROWID è l'indirizzo della riga che contiene il numero dell'oggetto dati, il blocco dati della riga, la posizione della riga e il file di dati. |
No | N/A | Se possibile, prova a emulare la stessa funzionalità con altre funzioni MySQL. |
ROWNUM |
Restituisce un numero che rappresenta l'ordine con cui una riga viene selezionata da Oracle da una tabella o da tabelle unite. | No | N/A | Se possibile, prova a emulare la stessa funzionalità con altre funzioni o variabili di sessione MySQL. |
Funzioni di aggregazione (gruppo)
Funzione Oracle | Specifica o implementazione della funzione Oracle |
Equivalente MySQL |
Funzione corrispondente MySQL | Implementazione o specifica della funzione MySQL |
---|---|---|---|---|
AVG |
Restituisce il valore medio della colonna o dell'espressione. | Sì | AVG |
Equivalente a Oracle |
COUNT |
Restituisce il numero di righe restituite da una query. | Sì | COUNT |
Equivalente a Oracle |
COUNT (DISTINCT) |
Restituisce il numero di valori univoci nella colonna o nell'espressione. | Sì | COUNT (DISTINCT) |
Equivalente a Oracle |
MAX |
Restituisce il valore massimo di una colonna o di un'espressione. | Sì | MAX |
Equivalente a Oracle |
MIN |
Restituisce il valore minimo di una colonna o di un'espressione. | Sì | MIN |
Equivalente a Oracle |
SUM |
Restituisce la somma del valore di una colonna o di un'espressione. | Sì | SUM |
Equivalente a Oracle |
LISTAGG |
Visualizza i dati all'interno di ciascun gruppo in base a una singola riga specificata nella clausola ORDER BY concatenando i valori della colonna di misurazione:SELECT LISTAGG( DEPARTMENT_NAME, ', ') WITHIN GROUP (ORDER BY DEPARTMENT_NAME) DEPT FROM DEPARTMENTS; |
No | GROUP_CONCAT |
Utilizza la funzione MySQL GROUP_CONCAT per restituire risultati simili a quelli di Oracle,
aspettando differenze di sintassi in alcuni casi:SELECT GROUP_CONCAT( DEPARTMENT_NAME ORDER BY DEPARTMENT_NAME SEPARATOR ', ') DEPT FROM DEPARTMENTS; |
Recupero Oracle 12c
Funzione Oracle | Specifica o implementazione della funzione Oracle |
Equivalente MySQL |
Funzione corrispondente MySQL | Implementazione o specifica della funzione MySQL |
---|---|---|---|---|
FETCH |
Recupera le righe di dati dal set di risultati di una query su più righe: SELECT * FROM EMPLOYEES FETCH FIRST 10 ROWS ONLY; |
No | LIMIT | Utilizza la clausola MySQL LIMIT per recuperare solo un insieme specifico di record:SELECT * FROM EMPLOYEES LIMIT 10; |
Filtri, operatori e sottoquery di base
Durante la conversione, i filtri di base, le funzioni degli operatori e le sottoquery sono relativamente semplici e richiedono uno sforzo minimo o nullo.
Note sulle conversioni
Esamina e gestisci i formati data perché i formati Oracle e MySQL restituiscono risultati predefiniti diversi:
- La funzione Oracle
SYSDATE
restituisce per impostazione predefinita01-AUG-19
. - La funzione MySQL
SYSDATE()
restituisce per impostazione predefinita2019-08-01 12:04:05
. - I formati di data e ora possono essere impostati utilizzando le funzioni MySQL
[DATE_FORMAT](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format)
o[STR_TO_DATE](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date)
.
Funzione o sottoquery Oracle | Equivalente MySQL | Funzione o sottoquery corrispondente a MySQL | Implementazione o specifica della funzione MySQL |
---|---|---|---|
EXISTS/NOT EXISTS |
Sì | EXISTS/NOT EXISTS |
SELECT * FROM DEPARTMENTS D WHERE EXISTS (SELECT 1 FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID); |
IN/NOT IN |
Sì | IN/NOT IN |
SELECT * FROM DEPARTMENTS D WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES E); |
LIKE/NOT LIKE |
Sì | LIKE/NOT LIKE |
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '_e_n%'; |
BETWEEN/NOT BETWEEN |
Sì | BETWEEN/NOT BETWEEN |
SELECT * FROM EMPLOYEES WHERE EXTRACT(YEAR FROM HIRE_DATE) NOT BETWEEN 2001 and 2004; |
AND/OR |
Sì | AND/OR |
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN(100, 101) AND (SALARY >= 1000 OR HIRE_DATE <= '2006-02-05'); |
SubQuery |
Sì | SubQuery |
MySQL supporta le sottoquery a livello di SELECT , per le istruzioni JOIN e per l'applicazione di filtri nelle clausole WHERE/AND :-- SELECT SubQuery SELECT D.DEPARTMENT_NAME, (SELECT AVG(SALARY) AS AVG_SAL FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID) AVG_SAL FROM DEPARTMENTS D; |
Operatori | Sì | Operatori | MySQL supporta tutti gli operatori di base:> | >= | < | <= | = | <> | != |
Funzioni analitiche (o funzioni di finestra e ranking)
Le funzioni analitiche Oracle estendono la funzionalità delle funzioni analitiche SQL standard fornendo capacità per calcolare i valori aggregati in base a un gruppo di righe. Queste funzioni possono essere applicate a set di risultati partizionati logicamente all'interno dell'ambito di una singola espressione di query. Di solito vengono utilizzati in combinazione con analisi e report di business intelligence, con il potenziale di aumentare le prestazioni delle query come alternativa all'ottenimento dello stesso risultato utilizzando codice SQL più complesso e non analitico.
Note sulle conversioni
- MySQL versione 5.7 non fornisce funzioni analitiche che supportano una conversione di istruzioni SQL semplici. Tuttavia, questa funzionalità è stata aggiunta parzialmente in MySQL versione 8, rendendo le funzioni analitiche di conversione un punto da considerare, che probabilmente richiedono sforzi manuali nel processo di migrazione.
- Una soluzione facoltativa è riscrivere il codice per eliminare l'uso di funzioni analitiche, ripristinare le soluzioni di codice SQL più tradizionali o spostare questa logica a un livello di applicazione.
Nella tabella seguente sono elencate le funzioni analitiche comuni di Oracle.
Famiglia di funzioni | Funzioni correlate | Supportato da MySQL 5.7 |
---|---|---|
Analisi e ranking | RANK AVERAGE_RANK DENSE_RANK RANK ROW_NUMBER PERCENT_RANK CUME_DIST NTILE FIRST_VALUE LAST_VALUE OVER (PARTITION BY...) |
No |
Gerarchica | CONNECT BY HIER_ANCESTOR HIER_CHILD_COUNT HIER_DEPTH HIER_LEVEL HIER_ORDER HIER_PARENT HIER_TOP |
No |
Sfasamento | LAG LAG_VARIANCE LAG_VARIANCE_PERCENT LEAD LEAD_VARIANCE LEAD_VARIANCE_PERCENT |
No |
Espressione della tabella comune (CTE)
Le CTE forniscono un modo per implementare la logica del codice sequenziale per riutilizzare codice SQL che potrebbe essere troppo complesso o non efficiente per un uso multiplo. Le CTE possono essere denominate e utilizzate più volte in diverse parti di un'istruzione SQL con la clausola WITH
.
Note sulle conversioni
- MySQL versione 5.7 non supporta le CTE, al contrario di MySQL versione 8.
- Per una soluzione alternativa, utilizza le tabelle derivate o le query secondarie oppure riscrivi l'istruzione SQL per eliminare la funzionalità CTE.
Esempi
Oracle |
---|
WITH DEPT_COUNT (DEPARTMENT_ID, DEPT_COUNT) AS (SELECT DEPARTMENT_ID, COUNT(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID) |
MySQL |
SELECT * FROM ( SELECT CONCAT(E.FIRST_NAME, ' ', E.LAST_NAME) AS EMP_NAME, (SELECT COUNT(*) FROM EMPLOYEES D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID GROUP BY DEPARTMENT_ID) AS EMP_DEPT_COUNT FROM EMPLOYEES E ORDER BY 2 DESC) TBL WHERE EMP_DEPT_COUNT IS NOT NULL; |
Istruzione MERGE
L'istruzione MERGE
(o UPSERT
) consente di specificare singole istruzioni SQL che eseguono operazioni DML in modo condizionale in un'operazione MERGE
, anziché in una singola operazione DML eseguita separatamente. Seleziona i record dalla tabella di origine e poi, specificando una struttura logica, esegue automaticamente più operazioni DML sulla tabella di destinazione. Questa funzione consente di evitare di utilizzare più inserti, aggiornamenti o eliminazioni. Tieni presente che MERGE
è un'istruzione deterministica, il che significa che una volta che una riga è stata elaborata dall'istruzione MERGE
, non può essere elaborata di nuovo utilizzando la stessa istruzione MERGE
.
Note sulle conversioni
A differenza di Oracle, MySQL versione 5.7 non supporta la funzionalità MERGE
. Per simulare parzialmente la funzionalità MERGE
, MySQL fornisce le istruzioni REPLACE
e INSERT… ON DUPLICATE KEY UPDATE
:
REPLACE
: funziona come un'istruzioneINSERT
, ma se una riga precedente della tabella ha lo stesso valore di una nuova riga per un indicePRIMARY KEY
oUNIQUE
, la riga precedente viene eliminata prima dell'inserimento della nuova riga.INSERT… ON DUPLICATE KEY UPDATE
: se una riga inserita causa un valore duplicato in un indicePRIMARY KEY
oUNIQUE
, viene generatoUPDATE
della riga precedente per eliminare l'eccezione di chiave duplicata, ad esempio:INSERT INTO tbl (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; UPDATE tbl SET c=c+1 WHERE a=1;
Un'altra soluzione consiste nel convertire la funzionalità MERGE
in una
procedura archiviata per gestire le operazioni DML, utilizzando i comandi INSERT
, UPDATE
e
DELETE
con gestione delle eccezioni e dei duplicati.
Suggerimenti istruzione SQL
Oracle offre un'ampia raccolta di hint delle query SQL che consentono agli utenti di influenzare il comportamento di Optimize e il suo processo decisionale, con l'obiettivo di produrre piani di esecuzione delle query più efficienti. Oracle supporta oltre 60 diversi hint di database. MySQL fornisce un insieme limitato di suggerimenti sulle query.
In generale, MySQL versione 5.7 supporta due tipi di suggerimenti di query: OPTIMIZER
HINTS
e INDEX HINTS
.
I suggerimenti di ottimizzazione MySQL consentono di controllare il comportamento dell'ottimizzatore all'interno di singole istruzioni SQL, ad esempio:
SELECT /*+ NO_RANGE_OPTIMIZATION(tbl PRIMARY, col1_idx) */ col1 FROM tbl;
Suggerimenti disponibili dello strumento di ottimizzazione per MySQL versione 5.7
Nome suggerimento | Panoramica dei suggerimenti | Ambiti applicabili |
---|---|---|
BKA, NO_BKA |
Interessa l'elaborazione del join di accesso alle chiavi in batch | Blocco query, tabella |
BNL, NO_BNL |
Interessa l'elaborazione del blocco dei join a loop nidificato | Blocco query, tabella |
MAX_EXECUTION_TIME |
Limita il tempo di esecuzione dell'istruzione | Globale |
MRR, NO_MRR |
Interessa l'ottimizzazione della lettura su più intervalli | Tabella, indice |
NO_ICP |
Interessa l'ottimizzazione push-down delle condizioni dell'indice | Tabella, indice |
NO_RANGE_OPTIMIZATION |
Interessa l'ottimizzazione dell'intervallo | Tabella, indice |
QB_NAME |
Assegna un nome al blocco di query | Blocco query |
SEMIJOIN, NO_SEMIJOIN |
Interessa le strategie semi-unione | Blocco query |
SUBQUERY |
Interessa le strategie di materializzazione da IN a EXISTS . |
Blocco query |
I suggerimenti indice MySQL forniscono allo strumento di ottimizzazione informazioni su come scegliere gli indici durante l'elaborazione delle query. Le parole chiave USE
, FORCE
o IGNORE
vengono utilizzate per controllare il processo di utilizzo dell'indice di Optimize, ad esempio:
SELECT * FROM tbl USE INDEX (col1_index, col2_index);
-- OR
SELECT * FROM tbl IGNORE INDEX (col1_index, col2_index);
Note sulle conversioni
Poiché esistono differenze fondamentali tra l'ottimizzatore Oracle e MySQL e esiste una sovrapposizione limitata di eventuali sovrapposizioni tra i suggerimenti di query Oracle e MySQL, ti consigliamo di convertire qualsiasi istruzione Oracle SQL contenente suggerimenti di query non specificati sul database MySQL di destinazione.
Esegui l'ottimizzazione delle prestazioni di MySQL tramite gli strumenti MySQL (ad esempio MySQL Workbench per dashboard delle prestazioni in tempo reale) e funzionalità come l'esame delle query utilizzando i piani di esecuzione e la regolazione dei parametri dell'istanza o della sessione in base al caso d'uso.
Piani di esecuzione
Lo scopo principale dei piani di esecuzione è fornire uno sguardo dall'interno alle scelte effettuate dall'ottimizzatore di query per accedere ai dati del database. Lo strumento di ottimizzazione delle query genera piani di esecuzione per le istruzioni SELECT
, INSERT
, UPDATE
e DELETE
per gli utenti del database, consentendo inoltre agli amministratori di avere una visione migliore su query specifiche e operazioni DML. Sono particolarmente utili quando devi ottimizzare le prestazioni delle query, ad esempio per determinare le prestazioni degli indici o per determinare se mancano indici da creare.
I piani di esecuzione possono essere influenzati da volumi di dati, statistiche dei dati e parametri di istanza (parametri globali o di sessione).
Considerazioni sulle conversioni
I piani di esecuzione non sono oggetti di database di cui è necessario eseguire la migrazione, ma uno strumento per analizzare le differenze di prestazioni tra Oracle e MySQL che eseguono la stessa istruzione su set di dati identici.
MySQL non supporta la sintassi, le funzionalità o lo stesso output del piano di esecuzione di Oracle.
Esempi
Piano di esecuzione Oracle |
---|
SQL> EXPLAIN PLAN FOR SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105; |
Piano di esecuzione MySQL |
mysql> EXPLAIN SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105; |
Stored procedure, funzioni e trigger
PL/SQL è il linguaggio procedurale esteso Oracle utilizzato per creare, archiviare e applicare soluzioni basate su codice all'interno del database. In generale, le procedure e le funzioni archiviate nei database sono elementi di codice costituiti da linguaggio procedurale esteso ANSI SQL e SQL, ad esempio PL/SQL per Oracle, PL/pgSQL per PostgreSQL e linguaggio procedurale MySQL per MySQL. MySQL usa lo stesso nome del database per il proprio linguaggio procedurale esteso.
Lo scopo di queste funzioni e stored procedure è fornire soluzioni per i requisiti più adatti per l'esecuzione dall'interno del database e non dall'applicazione (ad esempio prestazioni, compatibilità e sicurezza). Sebbene sia le stored procedure che le funzioni utilizzino PL/SQL, le stored procedure vengono utilizzate principalmente per eseguire operazioni DDL/DML e le funzioni vengono utilizzate principalmente per eseguire calcoli per restituire risultati specifici.
Linguaggio procedurale da PL/SQL a MySQL
Dal punto di vista della migrazione del codice da Oracle PL/SQL a MySQL, l'implementazione procedurale di MySQL è diversa da quella di Oracle. Di conseguenza, la migrazione del codice è necessaria per convertire la funzionalità PL/SQL da Oracle in stored procedure e funzioni MySQL. Inoltre, il corpo del pacchetto e del pacchetto Oracle non sono supportati da MySQL, quindi quando esegui la conversione del codice, converti questi elementi (o analizzali) in singole unità di codice MySQL. Tieni presente che le stored procedure e le funzioni MySQL sono chiamate anche routine.
Proprietario oggetto di codice
In Oracle, il proprietario di una stored procedure o di una funzione è un utente specifico. In MySQL, il proprietario è uno schema specifico (creato in un database da un utente del database).
Privilegi e sicurezza degli oggetti di codice
In Oracle, per creare una stored procedure o una funzione, l'utente deve disporre del privilegio di sistema CREATE PROCEDURE
(per creare procedure o funzioni con altri utenti diversi, gli utenti del database devono disporre del privilegio CREATE
ANY PROCEDURE
). Per eseguire una stored procedure o una funzione, gli utenti del database devono disporre del privilegio EXECUTE
.
In MySQL, per creare un elemento di codice, l'utente deve disporre dei privilegi CREATE
ROUTINE
e EXECUTE
per l'esecuzione. La clausola DEFINER
di MySQL definisce l'autore dell'oggetto di codice e l'utente deve disporre dei privilegi appropriati, ad esempio CREATE ROUTINE
.
Sintassi delle stored procedure e delle funzioni MySQL
L'esempio seguente mostra la stored procedure e la syntax della funzione MySQL:
CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = user]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement