MySQL LIMITE E OFFSET con esempi

Qual è la parola chiave LIMIT in MySQL?

Migliori LIMITE La parola chiave limita il numero di righe restituite nel risultato di una query. Può essere utilizzata con le istruzioni SELECT, UPDATE e DELETE, quindi limita sia le righe lette da una query sia le righe interessate da un'operazione di scrittura.

La sintassi per la parola chiave LIMIT è la seguente.

SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT N;

QUI

• “SELEZIONA {nome/i campo/i | *} FROM tableName(s)" Europe è Istruzione SELECT contenente i campi che vorremmo restituire nella nostra query.
• “[Condizione DOVE]” è facoltativo, ma se fornito specifica un filtro sul set di risultati. Dove la clausola viene applicato prima di LIMIT, quindi il filtraggio avviene per primo e il limite viene applicato a ciò che sopravvive.
• “LIMIT N” è la parola chiave, e N N è un numero qualsiasi a partire da 0. Impostando 0 come limite, non viene restituito alcun record. Impostando un numero come 5, vengono restituiti cinque record. Se la tabella contiene meno di N record, vengono restituiti tutti e non viene generato alcun errore.

La sintassi è breve, ma vale la pena spiegarne il motivo prima di passare agli esempi.

Perché dovremmo usare la parola chiave LIMIT?

Supponiamo di essere sviluppatoriping L'applicazione che gira su MyFlixDB. I progettisti del sistema ci hanno chiesto di limitare il numero di record visualizzati per pagina a 20, al fine di contrastare i tempi di caricamento lenti. Come possiamo implementare un sistema che soddisfi questo requisito?

La parola chiave LIMIT gestisce esattamente questa situazione. Invece di importare ogni riga relativa ai membri nell'applicazione e scartarne la maggior parte, la query restituisce 20 record per pagina e il database si occupa del resto. Da ciò derivano tre vantaggi.

• Risposta più veloce: Vengono letti meno dati dal disco e meno dati transitano attraverso la rete.
• Minore utilizzo della memoria: L'applicazione contiene una sola pagina di righe, non l'intera tabella.
• Safer scrive: un LIMITE su un AGGIORNAMENTO o un DELETE L'istruzione limita il numero di righe che un errore può interessare.

MySQL Esempi di query LIMIT

Gli esempi seguenti vengono eseguiti sulla tabella members del database myflixdb. Il primo restituisce due righe e nient'altro.

SELECT * FROM members LIMIT 2;

Come dimostra il risultato sopra riportato, sono stati restituiti solo due membri.

Ottenere un elenco di dieci (10) membri dal database

Supponiamo di voler ottenere un elenco dei primi 10 membri registrati nel database di Myflix. Lo script seguente li richiede.

SELECT * FROM members LIMIT 10;

L'esecuzione dello script produce il risultato mostrato di seguito.

Sono stati restituiti solo 9 elementi, perché N nella clausola LIMIT è maggiore del numero di record nella tabella. Richiedere esplicitamente 9 righe produce lo stesso set di risultati.

SELECT * FROM members LIMIT 9;

Suggerimento: LIMIT seleziona le righe dall'ordine in cui il server le produce. Aggiungi un ORDINATO DA clausola ogniqualvolta l'identità delle righe è rilevante, altrimenti "i primi 10 membri" non garantisce che si riferiscano alle stesse nove persone due volte.

Limitare il numero di righe rappresenta la prima parte della funzionalità. Scegliere il punto di inizio della finestra costituisce la seconda.

Utilizzo del valore OFFSET nella query LIMIT

Migliori COMPENSARE Il parametro VALUE viene spesso utilizzato insieme alla parola chiave LIMIT. Specifica da quale riga il server inizia a recuperare i dati, pertanto le righe precedenti a quel punto vengono saltate.

Supponiamo di voler limitare il numero di membri a partire dal centro della tabella. Lo script seguente inizia dalla seconda riga e limita il risultato a due record.

SELECT * FROM `members` LIMIT 1, 2;

Eseguendolo in MySQL banco di lavoro Il confronto con myflixdb fornisce il seguente risultato.

Nota che qui COMPENSAZIONE = 1, quindi la riga n. 2 è la prima riga restituita e LIMITE = 2, quindi vengono restituiti solo 2 record.

Nella forma a due argomenti, l'offset viene scritto per primo e il numero di righe per secondo, il che rende facile invertire l'ordine per errore. MySQL Accetta inoltre una forma esplicita che elimina l'ambiguità ed è quella da preferire nel nuovo codice.

SELECT * FROM `members` LIMIT 2 OFFSET 1;

Entrambe le istruzioni restituiscono le stesse due righe. Una volta compreso l'offset, il modello di paginazione su cui si basa ogni schermata di elenco ne deriva direttamente.

Come impaginare i risultati di una query con LIMIT e OFFSET

La paginazione suddivide un set di risultati di grandi dimensioni in pagine numerate, e LIMIT, insieme a OFFSET, è il meccanismo che la realizza. Due valori determinano ogni richiesta di pagina: la dimensione della pagina, ovvero quanti record vengono visualizzati su una schermata, e il numero di pagina richiesto dall'utente.

L'offset viene ricavato da essi con un'unica formula.

-- OFFSET = page_size * (page_number - 1)
SELECT membership_number, full_names
FROM members
ORDER BY membership_number ASC
LIMIT 20 OFFSET 0;   -- page 1

La pagina 2 mantiene lo stesso limite e sposta l'offset in avanti di una dimensione di pagina.

SELECT membership_number, full_names
FROM members
ORDER BY membership_number ASC
LIMIT 20 OFFSET 20;  -- page 2

Tre regole garantiscono la correttezza e la rapidità di creazione di un elenco impaginato.

1. Ordina sempre: Una query paginata senza ORDER BY può mostrare lo stesso record su due pagine diverse e nasconderne completamente un'altra, perché il server è libero di modificare l'ordine delle righe tra una chiamata e l'altra.
2. Ordina in base a una colonna univoca: I valori uguali nella colonna di ordinamento lasciano indefinito l'ordine delle righe a pari merito. Ordinare in base alla chiave primaria, o aggiungerla come criterio di spareggio, risolve il problema.
3. Guarda le pagine profonde: OFFSET 100000 forze MySQL Leggere centomila righe e scartarle prima di restituirne le successive venti. Il tempo di risposta aumenta con il numero di pagina.

Per una paginazione molto profonda, la paginazione basata su keyset evita completamente l'offset. Invece di contare le righe da saltare, la query memorizza l'ultima chiave della pagina precedente e richiede le righe successive.

SELECT membership_number, full_names
FROM members
WHERE membership_number > 20      -- last id from the previous page
ORDER BY membership_number ASC
LIMIT 20;

Questa forma rimane veloce a qualsiasi profondità, perché l'indice salta direttamente alla chiave iniziale invece di percorrere le righe che la precedono. Il compromesso è che le pagine devono essere percorse in sequenza, quindi saltareping Non è più possibile andare direttamente a pagina 500.

Limite in MySQL vs TOP e FETCH FIRST

LIMIT non è presente in tutti i dialetti SQL, il che diventa rilevante non appena una query deve essere elaborata da un motore di database all'altro. MySQL, PostgreSQLe SQLite condividono la parola chiave LIMIT. SQL Server utilizza TOP e Oracle utilizza la clausola FETCH FIRST standard. La tabella seguente confronta le tre.

Il comportamento è lo stesso in ogni caso: limitare il numero di righe e, facoltativamente, saltare prima un certo numero di righe. Cambia solo l'ortografia. Una query che deve essere eseguita su più di un motore dovrebbe quindi isolare la clausola di limitazione delle righe anziché disperderla nel codice.

DOMANDE FREQUENTI