MySQL FIND_IN_SET spiegato: come cercare correttamente i valori separati da virgola

1. Introduzione

La sfida di cercare dati separati da virgole in MySQL

Quando si lavora con i database, si possono incontrare casi in cui più valori sono memorizzati in una singola colonna separati da virgole. Ad esempio, una colonna può contenere una stringa come "1,3,5" e si potrebbe voler estrarre solo i record che includono il valore “3”.

In tali casi, l’uso dell’operatore standard = o della clausola IN spesso non produce i risultati attesi. Questo perché una stringa separata da virgole è trattata come un unico valore stringa, il che significa che i confronti vengono valutati sull’intera stringa anziché sui singoli elementi al suo interno.

Che cos’è la funzione FIND_IN_SET?

In situazioni come questa, la funzione MySQL FIND_IN_SET diventa molto utile.
Questa funzione consente di determinare facilmente se un valore specificato esiste all’interno di una stringa separata da virgole.

Ad esempio, considera la seguente istruzione SQL:

SELECT * FROM users WHERE FIND_IN_SET('3', favorite_ids);

In questa query, è possibile estrarre i record in cui la stringa separata da virgole nella colonna favorite_ids (ad es. "1,2,3,4") contiene il valore “3”.

Scopo di questo articolo e pubblico di riferimento

Questo articolo spiega come utilizzare la funzione FIND_IN_SET dalle basi in modo chiaro e strutturato. Dalla sintassi di base agli esempi pratici, ai confronti con altri metodi di ricerca, alle considerazioni importanti e alle FAQ, questa guida fornisce conoscenze pratiche per lo sviluppo reale.

Questo articolo è destinato a:

• Ingegneri web e sviluppatori backend che usano regolarmente MySQL
• Sviluppatori che devono lavorare con sistemi esistenti che memorizzano dati separati da virgole
• Principianti di SQL che hanno difficoltà con il matching parziale e le ricerche basate su valori

2. Sintassi di base e comportamento della funzione FIND_IN_SET

Sintassi di FIND_IN_SET

FIND_IN_SET è una funzione MySQL usata per determinare se un valore specifico esiste all’interno di una stringa separata da virgole. La sintassi di base è la seguente:

FIND_IN_SET(search_value, comma_separated_string)

Ad esempio:

SELECT FIND_IN_SET('3', '1,2,3,4'); -- Result: 3

In questo esempio, poiché “3” appare nella terza posizione, la funzione restituisce il valore numerico 3.

Regole sul valore di ritorno

La funzione FIND_IN_SET si comporta secondo le seguenti regole:

Esempio (posizione restituita)

SELECT FIND_IN_SET('b', 'a,b,c'); -- Result: 2

Esempio (valore non trovato)

SELECT FIND_IN_SET('d', 'a,b,c'); -- Result: 0

Esempio (incluso NULL)

SELECT FIND_IN_SET(NULL, 'a,b,c'); -- Result: NULL

Esempio di utilizzo in una clausola WHERE

Questa funzione è più comunemente usata per filtrare all’interno di una clausola WHERE.

SELECT * FROM users WHERE FIND_IN_SET('admin', roles);

In questo esempio, verranno restituite solo le righe in cui la colonna roles contiene la stringa “admin”. Se la colonna contiene un valore come "user,editor,admin", verrà trovata la corrispondenza.

Note importanti su numeri e stringhe

FIND_IN_SET esegue i confronti come stringhe, il che significa che si comporta come segue:

SELECT FIND_IN_SET(3, '1,2,3,4');     -- Result: 3
SELECT FIND_IN_SET('3', '1,2,3,4');   -- Result: 3

Sebbene funzioni sia con valori numerici sia con stringhe, tipi di dato poco chiari possono portare a comportamenti inattesi. Pertanto, è buona pratica gestire i valori esplicitamente come stringhe ogni volta possibile.

3. Esempi pratici

Ricerca in una colonna che memorizza stringhe separate da virgole

Nei sistemi reali, si possono trovare casi in cui più valori (come ID o permessi) sono memorizzati in una singola colonna come stringa separata da virgole. Ad esempio, considera la seguente tabella users.

Quando vuoi “recuperare gli utenti che includono 3”, la funzione FIND_IN_SET è estremamente comoda.

SELECT * FROM users WHERE FIND_IN_SET('3', favorite_ids);

Eseguendo questo SQL verranno restituiti i record per “Taro” e “Jiro”.

Funziona bene anche quando i valori sembrano numerici

Anche quando favorite_ids sembra contenere numeri, FIND_IN_SET esegue confronti basati su stringhe, quindi è più sicuro passare l’argomento come stringa tra virgolette.

-- OK
SELECT * FROM users WHERE FIND_IN_SET('5', favorite_ids);

-- Works, but strictly speaking not recommended
SELECT * FROM users WHERE FIND_IN_SET(5, favorite_ids);

Per mantenere le query leggibili e il comportamento prevedibile, è consigliato specificare esplicitamente il valore come stringa.

Ricerche dinamiche (segnaposto e variabili)

Quando si genera SQL in modo dinamico da un’applicazione web, è comune utilizzare variabili o parametri di binding.

Se utilizzi una variabile MySQL, appare così:

SET @target_id = '3';
SELECT * FROM users WHERE FIND_IN_SET(@target_id, favorite_ids);

Quando si effettua il binding dal livello dell’applicazione (come PHP, Python o Node.js), è possibile gestirlo in modo simile usando i segnaposto.

Come gestire la ricerca di più valori

Sfortunatamente, FIND_IN_SET può cercare solo un valore alla volta.
Se vuoi recuperare i record che contengono “3 o 4”, devi scriverlo più volte usando OR.

SELECT * FROM users
WHERE FIND_IN_SET('3', favorite_ids) OR FIND_IN_SET('4', favorite_ids);

Se le condizioni diventano più complesse, dovresti oppure costruire dinamicamente l’SQL nella tua applicazione oppure considerare la migrazione a una struttura di tabella normalizzata.

4. Confrontare FIND_IN_SET con altri metodi di ricerca

Alternative comuni: IN e LIKE

In MySQL, oltre a FIND_IN_SET, potresti vedere anche la clausola IN o la clausola LIKE usate per verificare se un valore è incluso. Tuttavia, ogni metodo si comporta in modo diverso, e usarlo in modo errato può portare a risultati di query errati.

Qui, chiariremo come differiscono da FIND_IN_SET e quando utilizzare ciascun approccio.

Confronto con la clausola IN

La clausola IN è tipicamente usata per verificare se un valore corrisponde a uno di più valori costanti.

-- Example of IN (this does NOT search inside "favorite_ids" for the value 3)
SELECT * FROM users WHERE favorite_ids IN ('3');

In questo caso, verranno restituiti solo i record in cui favorite_ids è una corrispondenza esatta per “3”. Ciò significa che valori come "1,3,5" non corrisponderanno—solo una riga in cui il valore della colonna è esattamente "3" corrisponderà.

Al contrario, FIND_IN_SET verifica la posizione di un elemento all’interno di una lista separata da virgole, consentendo di recuperare con precisione i record che includono “3” così:

SELECT * FROM users WHERE FIND_IN_SET('3', favorite_ids);

✅ Linee guida chiave per l’uso:

• IN : Da utilizzare con tabelle normalizzate (ad es., SELECT * FROM posts WHERE category_id IN (1, 3, 5) )
• FIND_IN_SET : Da utilizzare con stringhe denormalizzate separate da virgole

Confronto con la clausola LIKE

Tecnicamente, è possibile usare LIKE per corrispondenze parziali, ma comporta importanti insidie.

-- A common mistake with LIKE
SELECT * FROM users WHERE favorite_ids LIKE '%3%';

Questa query non significa realmente “contiene il valore 3”—corrisponde a qualsiasi stringa che contiene il carattere “3”, il che può far corrispondere erroneamente "13", "23" o "30".

Ciò rende impossibile rilevare in modo affidabile se 3 esiste come valore autonomo.

✅ Linee guida chiave per l’uso:

• LIKE : Utile per ricerche testuali approssimative, ma non può riconoscere i confini separati da virgole
• FIND_IN_SET : Controlla accuratamente le corrispondenze di valori autonomi all’interno di liste separate da virgole

Differenze di prestazioni

In particolare, FIND_IN_SET non può utilizzare gli indici e spesso provoca scansioni complete della tabella. Se lavori con set di dati di grandi dimensioni, potresti dover ripensare lo schema.

5. Note importanti e migliori pratiche

Non compatibile con valori contenenti virgole

La funzione FIND_IN_SET presume una lista semplice di valori separati da virgole. Pertanto, se un elemento individuale nella lista contiene una virgola, la funzione non si comporterà come previsto.

Esempio errato:

SELECT FIND_IN_SET('1,2', '1,2,3,4'); -- Result: 1

Usarlo in questo modo può produrre corrispondenze errate perché l’intera stringa viene valutata in modo improprio.
Dovresti usare questa funzione solo quando puoi garantire che i valori individuali non contengano virgole.

Problemi di prestazioni

Poiché FIND_IN_SET non può utilizzare gli indici, esegue una scansione completa della tabella. Di conseguenza, quando viene usato su tabelle di grandi dimensioni, le prestazioni delle query possono degradarsi significativamente.

Soluzioni alternative:

• Invece di memorizzare valori separati da virgole, normalizza la relazione e gestiscila in una tabella separata.
• In ambienti critici per le prestazioni, considera l’espansione di tabelle temporanee o strategie basate su JOIN .

Ad esempio, se crei una tabella intermedia come user_favorites, puoi sfruttare gli indici per ricerche più rapide:

SELECT users.*
FROM users
JOIN user_favorites ON users.id = user_favorites.user_id
WHERE user_favorites.favorite_id = 3;

Leggibilità e Manutenibilità

Sebbene FIND_IN_SET possa sembrare comodo, presenta diversi svantaggi:

• Le query non sono intuitive (restituiscono valori di posizione)
• Aggiungere o rimuovere valori è ingombrante
• L’integrità dei dati è difficile da garantire (significati multipli in una singola colonna)

Pertanto, quando la manutenibilità e l’integrità dei dati sono importanti, la revisione dello schema stesso è spesso la migliore pratica.

Quando è necessario usare FIND_IN_SET

Ci sono situazioni in cui non hai altra scelta se non lavorare con colonne separate da virgole—come sistemi legacy o prodotti di terze parti. In tali casi, considera le seguenti precauzioni:

• Applica prima altre condizioni di filtro per ridurre l’ambito di ricerca
• Previeni errori di formattazione come virgole doppie o spazi iniziali/finali
• Esegui elaborazioni supplementari a livello dell’applicazione quando possibile

6. Domande frequenti (FAQ)

Può FIND_IN_SET utilizzare gli indici?

No, FIND_IN_SET non può utilizzare gli indici. Internamente, divide e valuta la stringa, quindi non beneficia dell’ottimizzazione degli indici di MySQL.

Di conseguenza, usarlo su tabelle di grandi dimensioni può rallentare le prestazioni delle query. Per sistemi critici per le prestazioni, considera di ridisegnare lo schema o normalizzare i dati.

Funziona correttamente con numeri e stringhe miste?

In generale, sì—ma tieni presente che i confronti vengono eseguiti come stringhe. Se i valori numerici e di stringa sono mescolati, può verificarsi un comportamento inatteso.

Ad esempio, entrambi i seguenti restituiscono una corrispondenza per 3:

SELECT FIND_IN_SET(3, '1,2,3,4');     -- Result: 3
SELECT FIND_IN_SET('3', '1,2,3,4');   -- Result: 3

Tuttavia, in casi come FIND_IN_SET('03', '01,02,03'), la formattazione con zero iniziale può influenzare il comportamento di corrispondenza.
È più sicuro standardizzare la formattazione dei valori.

Come posso cercare più valori contemporaneamente?

Poiché FIND_IN_SET accetta solo un singolo valore di ricerca, se vuoi cercare record contenenti “3 o 4”, devi chiamarlo più volte usando OR:

SELECT * FROM users
WHERE FIND_IN_SET('3', favorite_ids)
   OR FIND_IN_SET('4', favorite_ids);

Se le condizioni diventano più complesse, considera di costruire dinamicamente SQL a livello dell’applicazione o migrare a una struttura di tabella normalizzata.

FIND_IN_SET sta causando problemi di prestazioni. Cosa devo fare?

Le seguenti strategie sono efficaci:

• Passa a un design di tabella normalizzato
• Applica prima condizioni di filtro per ridurre l’ambito di ricerca
• Usalo solo quando lavori con piccoli set di dati
• Considera la migrazione a formati strutturati come la ricerca full-text o i tipi di dati JSON

Le versioni moderne di MySQL supportano i tipi di dati JSON. Ad esempio, se gestisci la colonna roles come un array JSON, puoi usare JSON_CONTAINS() per ricerche flessibili ed efficienti.

FIND_IN_SET diventerà deprecato in futuro?

A partire da MySQL 8.0, FIND_IN_SET non è ufficialmente deprecato. Tuttavia, le strutture dati denormalizzate (colonne separate da virgole) non sono consigliate, quindi l’uso pratico di questa funzione dovrebbe diminuire nel tempo.

Quando ridisegni il tuo database, è ideale adottare strutture normalizzate o progetti basati su JSON.

7. Conclusione

Revisione delle funzionalità e dei vantaggi di FIND_IN_SET

La funzione FIND_IN_SET è estremamente utile in MySQL per la ricerca di stringhe separate da virgole. È particolarmente utile quando è necessario estrarre record che contengono un valore specifico all’interno di una singola colonna che memorizza più valori.

Con la sua sintassi semplice, consente verifiche per corrispondenze di valori autonomi che sono difficili da ottenere con precisione usando le clausole LIKE o IN. Questa capacità di rilevare elementi distinti all’interno di un elenco separato da virgole è il suo punto di forza più grande.

Considerazioni importanti quando la si utilizza

Allo stesso tempo, ci sono diverse limitazioni e considerazioni importanti, quindi non dovrebbe essere usata in eccesso senza una riflessione attenta:

• Gli indici non possono essere utilizzati (il che può rallentare le ricerche)
• Non è compatibile con valori che contengono virgole
• Presuppone una struttura denormalizzata
• Supporta solo ricerche a valore singolo (ricerche multiple richiedono condizioni OR )

Comprendere queste caratteristiche è essenziale per utilizzare la funzione in modo appropriato.

Quando dovresti — e quando non dovresti — usarla

Come applicare questo nella pratica

• Comprenderla come uno strumento flessibile per lavorare all’interno delle strutture di database esistenti
• Usarla come punto di riferimento quando si decide se adottare un design di dati normalizzato in futuro
• Piuttosto che usarla come una soluzione rapida, comprendere chiaramente cosa fa realmente la funzione

Per gli sviluppatori che danno priorità a manutenibilità e leggibilità, è meglio considerare questa funzione come qualcosa che si può “usare temporaneamente—ma alla fine si dovrebbe abbandonare.”