Manejo de arrays en SQL: técnicas y trucos en motores populares

Etusivu » Python » Manejo de arrays en SQL: técnicas y trucos en motores populares

Si trabajas con bases de datos relacionales y te preguntas cómo manejar estructuras tipo lista, no estás solo: el soporte de “arrays” fi SQL no es uniforme ni está estandarizado entre motores. Tämä koskee erilaisia ​​ratkaisuja, jotka ovat riippuvaisia ​​usas:sta SQL Serveristä, PostgreSQL:stä, MySQL:stä tai SQLitestä, sekä cambianin versiosta ja järjestelmän yhteensopivuudesta.

En este artículo repasamos, con detalle y ejemplos prácticos, cómo simular o aprovechar los arrays según el SGBD, cuándo es mejor usar tablas temporales, TVPs, funciones como STRING_SPLIT o OPENJSON, y cómo filtrostar de et abor joukko. También verás por qué a veces conviene abandonar los arrays y modelar la información en tablas relacionales normales, y dónde están los cuellos de botella de rendimiento más habituales.

Qué entendemos por “array” en SQL y por qué no es estándar

Aunque nos gustaría tratar las listas como haríamos en un lenguaje de programación, SQL nació en los 70 orientado ja datas tables, y el concepto de array como tipo primerizo no forma parte del estándar. Algunos motores incorporan tipos y funciones para lists, otros ofrecen extensiones, y otros no lo contemplan. La consecuencia práctica es que una misma necesidad tiene soluciones distintas según el motor, y conviene conocer sus matices para evitar errores y pérdidas de rendimiento.

Además, es frecuente la tentación de lanzar un IN con una lista separada por comas dentro de una variable (esim. '1,2,3,4') esperando que se expanda mágicamente. Eso no sucede: IN es un operador que el parser reescribe como una cadena de OR (col = val1 TAI col = val2…), y una cadena '1,2,3,4' on soolo eso, una cadena. La falsa expectativa de "IN (@lista)" on una trampa clásica que conviene desterrar desde el inicio.

SQL Server: no hay arrays nativos, mutta on vaihtoehtoinen solidas

En SQL Server ei ole olemassa erilaisia ​​taulukoita, de modo que la vía práctica es usar tablas temporales, variables de tabla o TVPs (taulukkoarvoiset parametrit) para representar listas. Esta aproximación es natural en un sistema relacional y además escala bien si planificas índices y estadísticas.

Para lotes pequeños, una variable de tabla puede ser muy útil. Por ejemplo, cuando queremos capturar rangos o conjuntos reducidos, una @tabla puede suplir la necesidad de un array:

DECLARE @MyDateArray TABLE (StartDate DATETIME, EndDate DATETIME);
INSERT INTO @MyDateArray (StartDate, EndDate)
SELECT StartDate, EndDate
FROM Reservations1
WHERE DATEADD(day, 0, StartDate) >= @StartDate
  AND DATEADD(day, 0, EndDate)   <= @EndDate
  AND RequestId IN (
      SELECT RequestId
      FROM RequestModelMap1
      WHERE ModelSerialNumber = @ModelSerialNumber
  );

SELECT *
FROM @MyDateArray;

Kun lista muodostui, suele ser mejor una tabla temporal (#temp) o un TVP por cuestiones de estadísticas y optimización, sobre todo si esa lista participa en uniones y filtros complejos. Además, joka on käyttöliittymä ja .NET, los TVPs son cómodos, seguros y eficaces para pasar lotes de valores sin recurrir a concatenaciones fragiles.

SQL-palvelimen erotusten luettelo: STRING_SPLIT, toiminnot, XML ja JSON

Si ya tienes una lista separada por comas (o por otro delimitador) y no puedes cambiar el origen, SQL Server ofrece varias salidas. La more directa en versiones modernas es STRING_SPLIT, que descompone una cadena en filas:

SELECT ...
FROM dbo.TuTabla t
WHERE t.Col IN (
  SELECT CONVERT(int, value)
  FROM STRING_SPLIT('1,2,3,4', ',')
);

En SQL Server 2022 ja Azure SQL puedes pedir también el ordinal (posición en la list) pasando un tercer parámetro (1), útil para sincronizar múltiples listas paralelas. Antes de 2022 no existe esa columna ordinal y el delimitador solo puede ser de un carácter, así que ojo con escenarios más complejos. Si la BD está con compatibilidad < 130, ni siquiera tendrás STRING_SPLIT disponible, aunque estés en version 2016 o superior.

Para quienes necesitan controlar el tipo, vacíos y espacios, hay funciones simples que puedes crear, por eemplo una que convierte a int y otra para cadenas. Las versiones multi-sentencia son fáciles de adaptar a tus reglas (s. ej. devolver NULL en elementos vacíos, recortar espacios, soportar delimitadores de varios caracteres):

CREATE FUNCTION dbo.intlist_to_tbl(@list nvarchar(MAX), @delim nvarchar(10))
RETURNS @tbl TABLE (listpos int NOT NULL IDENTITY(1,1), n int NULL) AS
BEGIN
  DECLARE @pos int = 1, @nextpos int = 1, @valuelen int,
          @delimlen int = DATALENGTH(@delim) / 2;
  WHILE @nextpos > 0
  BEGIN
    SELECT @nextpos = CHARINDEX(@delim COLLATE Czech_BIN2, @list, @pos);
    SELECT @valuelen = (CASE WHEN @nextpos > 0 THEN @nextpos ELSE LEN(@list) + 1 END) - @pos;
    INSERT @tbl(n)
    VALUES (CONVERT(int, NULLIF(SUBSTRING(@list, @pos, @valuelen), '')));
    SELECT @pos = @nextpos + @delimlen;
  END;
  RETURN;
END;

El uso de una colación binaria acelera el hallazgo del delimitador, y el cálculo de longitudes con DATALENGTH/2 evita problems con espacios. Para listas de cadenas, puedes devolver columnas varchar y nvarchar para respetar los tipos y no deshabilitar índices por conversiones implícitas.

Si no puedes crear funciones ni usar STRING_SPLIT, dos alternativas muy versátiles son XML ja OPENJSON; para profundizar en su uso con JSON, konsultti JSON-prosessi SQL:nä. Con XML, reemplazas el delimitador por nodos y haces .nodes('/x/text()'); con JSON, envuelves la lista con corchetes y la abres con OPENJSON, recuperando key (sijainti 0-pohjainen) y arvo:

DECLARE @list nvarchar(MAX) = '1,99,22,33,45';
SELECT CONVERT(int, ) + 1 AS listpos, CONVERT(int, value) AS n
FROM OPENJSON('');

Con XML/JSON tendrás mejor rendimiento en listas largas y, si trabajas con strings que incluyen caracteres especiales, recuerda protegerlos (esim. CDATA ja XML) para no romper el parseo. Evita a toda costa generar SQL dinámico del tipo “… WHERE col IN (” + @lista + “)”: es frágil, inseguro (inyección) y suele rendir peor con listas extensas.

Un apunte de rendimiento clave: si vas a usar la lista en consultas complejas, carga primero los valores en una #tabla con PK para que el optimizador tenga estadísticas. Es habitual que un filtro IN contra una función TVF impida al optimizador estimar cardinalidades correctamente y seleccione planes subóptimos.

PostgreSQL: taulukot nativos, UNNEST ja LATERAL para konsultas potentes

PostgreSQL on saatavilla natiivit taulukot y un ecosistema de funciones muy completo. La herramienta estrella para convertir un array en filas es UNNEST, a menudo con CROSS JOIN LATERAL para correlacionar columnas:

-- Contar solo elementos que empiecen por 'label_'
SELECT tag, COUNT(*) AS total
FROM public.description_labels t
CROSS JOIN LATERAL UNNEST(t.business_tags) AS u(tag)
WHERE tag LIKE 'label\_%'
GROUP BY tag
ORDER BY total DESC;

Este patrón resuelve el típico problem de "tengo una columna array y quiero filtrar y contar solo los elementos que cumplen una condición". Aplicar LIKE o cualquier filtro debe hacerse sobre la columna "unnesteada", no sobre el array original, porque el predicado no se empuja dentro de cada elemento si filtras el array como un todo.

Cuando necesitas operar en bloque con múltiples columnas, UNNEST sallie pasar varios arrays en paralelo y los alinea por posición (kuten ”taulukkorakenne” ja ”rakenteiden taulukko”):

INSERT INTO test (id, name)
SELECT *
FROM UNNEST($1::INT[], $2::TEXT[]);

Esto elimina la generación dinámica de N placeholders y habilita un lausunto estático con solo dos parámetros que puede insertar tantos registros como quieras. En pruebas comparativas, la inserción con UNNEST supera a los inserts erässä con VALUES (…), (…) al reducir parseo y planificación.

Para IN, también puedes usar UNNEST detro del operador: WHERE id IN (SELECT * FROM UNNEST($1::INT[])). En ciertos contextos, construir placeholders dinámicos sigue siendo competitivo, mutta UNNEST te da limpieza y reusabilidad del plan. Mide y elige según tu carga.

MySQL: JSON, joka sisältää luettelot ja toiminnot

MySQL no tiene un tipo array como tal, pero el tipo JSON sallii kaikkien luetteloiden keräämisen y konsultti sus elementos. Jos sarake on suojattu ja array JSON, puedes buscar ja JSON_SEARCH tai comprobar intersección ja JSON_OVERLAPS:

CREATE TABLE supplier (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company VARCHAR(10),
  vehicles JSON
);
INSERT INTO supplier VALUES
(DEFAULT, 'A', '');

-- Buscar un valor concreto en el array JSON
SELECT *
FROM supplier
WHERE JSON_SEARCH(vehicles, 'one', 'Luton') IS NOT NULL;

Si la columna es de texto y contiene un JSON array válido, JSON_OVERLAPS(ajoneuvot, JSON_ARRAY('Luton')) te dirá si hay elementos comunes. Procura almacenar data ja JSON todellinen cuando vayas a usar estas funciones; evitarás conversiones y errores sutiles con comillas y escapes.

Recuerda que estas funciones están disponibles en MySQL 8.xy voidaan varmistaa SQL-version ja modo para evitar sorpresas. Erityisesti JSON_SEARCH/JSON_OVERLAPS:n la sintaxis y Comportamiento on estable en 8.0.34, como en el eemplo.

SQLite: sin arrays nativos; laajennukset carray/rarray y sus límites

SQLite no incluye arrays nativos ja el lenguaje SQL, pero dispone de extensiones como carray (y rarray en rusqlite) que exponen arreglos como tablas virtuales de una columna. Esto allowe construir inserciones masivas o filtros tipo IN sin generar N paikkamerkkiä:

-- Ejemplo de inserción combinando dos arrays con CROSS JOIN por rowid
INSERT INTO test (id, name)
SELECT *
FROM rarray(?) AS a
CROSS JOIN rarray(?) AS b
ON a.rowid = b.rowid;

Enintään yksi toimii se complica al aumentar columnas y puede no escalar en rendimiento. En pruebas, el uso de estas tablas virtuales ha mostrado peor desempeño que los inserts batched clásicos fi SQLite, especialmente por cómo se implementa el acceso por rowid en las VTables. Päätelmä: mide y no des por hecho que "array" en SQLite será más rápido; muchas veces insertar por lotes con statements preparados dentro de una transacción es lo óptimo.

Para IN, idea "IN (SELECT * FROM rarray(?))" on mahdollista, mutta también puede rendir peor que generar dinámicamente paikkamerkit si la lista no es enorme. El consejo aquí es pragmático: elige la menor complejidad que te dé buen rendimiento sisälläsi ja versiossasi.

El anti-patrón de listas delimitadas en columnas y el diseño orientado a tablas

Almacenar en una columna una lista separada por comas (o samankaltainen) contradice el principio relacional de atomicidad y suele complicar consultas, índices y mantenimientos. Un caso extremo poika moninkertainen columnas con listas "sincronizadas" por posición (productos, cantidades, precios), algo difícil de aktualizar y propenso a incoherencias.

Vaihtoehtoinen ammattilainen on mallintaa taulukoissa. Esimerkiksi convertir pedidos con products/qty/precio en una orderdetails y poblarla “desempaquetando” con STRING_SPLIT (con ordinaal) tai con funciones/OPENJSON/XML sincronizadas por posición. Una vez normalizados los datos, todo el ecosistema de índices y agregados juega a tu favor, y reconstruir una lista presentable puntualmente (string_agg a partir de 2017, o FOR XML PATH en 2016 y previas) on triviaali.

En línea con esto, cuando necesites adjuntar atributosflexs a una entidad (como una proforma), un esquema nombre-valor (EAV) on enemmän mukautettavissa olevia taulukoita, jotka ovat detro de columnas. Una tabla atributos(id, idproforma, atributo, valor) sallia añadir y filtrar atributos sin rediseñar columnas ni manipular matriiseja. Es más simple de consultar, de indexar y de mantener que un text[][] o una cadena JSON sin ohjaus.

Virheet comunes y trampas con IN y compatibilidades

Kuten ylipäätään IN no expande variables con listas delimidas. Para que funcione, necesitas convertir la list en filas (STRING_SPLIT, función, XML/JSON) tai pass un conjunto tablear (TVP, #tabla). Dynaaminen Evita SQL: acarrea riesgos de inyección, planes no reutilisables y jäsennys costoso cuando la lista crece.

Comprueba siempre la versio y el nivel de compatibilidad. Esimerkiksi, STRING_SPLIT vaatii yhteensopivuuden 130+ y el tercer argumento con ordinal solo olemassa SQL Server 2022/Azure SQL:ssä. Myydään virheellinen objektinimi 'STRING_SPLIT'. fi 2016, revisa el nivel de compatibilidad de la base antes de sacar päätelmät.

Cuando consumas documentación externa, recuerda que puede quedar obsoleta o movida. Si una referencia falla, usa el buscador del sitio o pide soporte; más de una vez la página "perfecta" para tu versio ya no está donde estaba y hay que localizar su sucesora tai vastaavae.

Patrones de rendimiento: por qué los arrays no siempre son la vía rápida

"Array" suena ja velocidad, mutta fi SQL el rendimiento depende más de cómo se planifica y reutiliza el -lause que de la estructura en sí. En PostgreSQL, UNNEST suele ganar en inserts masivos al mantener consultas estáticas con pocos parámetros. En SQLite, sin embargo, los arrays vía VTables pueden ser más lentos que los inserts erässä tradicionales.

En SQL Server, descomponer listas a través de funciones en línea o OPENJSON/XML y mover el resultado a una #tabla con índice da al Optimador information crucial (estadísticas) para elegir índices y evitar scans. Ese pequeño paso suele pagar dividendos cuando las consultas se vuelven complejas.

Para filtros tipo IN en cualquier engine, si el tamaño del conjunto varía mucho, huomioi estrategias mixtas: paikkamerkki dinámico para listas muy pequeñasJa estructura tablear (TVP/#temp/UNNEST) partir de cierto umbral. Mide con tu rahti todellista tavaraa; es la única forma de acertar.

Recetas nopeat moottorit: lo essencial

SQL Server: usa TVPs/temporales/variables de tabla para "arrays"; STRING_SPLIT para listas delimitas (con ordinaal en 2022+); OPENJSON para necesidad de posición sin funciones; XML ja JSON no encaja; evita IN con cadenas y el SQL dinámico.

PostgreSQL: apóyate en arrays nativos ja UNNEST con LATERAL; filtra tras descomprimir (LIKE 'label_%', expresiones regulares jne.); para insertit masivos, UNNEST paralelizando arrays por columna yksinkertaistettu kiihdytin; valora si IN con UNNEST te compensa frente a paikkamerkit.

MySQL: valvoa listaa como JSON ja konsultoi JSON_SEARCH/JSON_OVERLAPS; estandariza el formato de entrada (taulukot JSON válidos) para evitar sorpresas; si partes de texto, convierte a JSON_ARRAY en la consulta con mesura.

SQLite: apuesta por transacciones, lausunnot preparados y batches; rarray/carray puede servir en casos concretos, pero no esperes milagros de rendimiento; para N columnas, la combinación por rowid con CROSS JOIN se komplikaatio.

Lopuksi, cuando veas columnas con listas, plantéate normalizar. Te ahorrará sufrimiento más adelante y hará que los planes del optimizador jueguen a tu favor, con menos magia y más índices.

Aunque no existe una única receta, ahora cuentas con un mapa claro: si tu SGBD no trae arrays, simúlalos con tablas; si los trae, exprímelos con las funciones nativas adecuadas; si recibes listas delimitadas, desmenúzalas correctamente; y si el rendimiento flojea, lleva esos valores a una estructura con estadísticas. Con estas pautas, el "manejo de arrays en SQL" deja de ser un laberinto y pasa a ser una caja de herramientas bien ordenada.