Cara membuat array dalam SQL: teknik dan truk yang populer

Beranda » Ular sanca » Cara membuat array dalam SQL: teknik dan truk yang populer

Jika Anda bekerja berdasarkan data relasional dan pertanyaan tentang cara mengelola struktur seperti daftar, maka tidak hanya itu: dukungan "array" dalam SQL tidak seragam dan merupakan standar seluruh mesin. Ini berarti bahwa solusinya bervariasi tergantung pada penggunaan SQL Server, PostgreSQL, MySQL, atau SQLite, dan juga menyesuaikan dengan versi dan kompatibilitas sistem.

Dalam artikel ini, dengan rincian dan contoh praktik, seperti yang mirip atau menyetujui array yang ada di SGBD, ketika lebih baik menggunakan tab sementara, TVP, fungsi seperti STRING_SPLIT atau OPENJSON, dan bagaimana membatalkannya jika menyangkut etiket filter array. Juga benar bahwa Anda sering kali dapat meninggalkan array dan memodelkan informasi dalam tabel relasional normal, dan jangan gunakan botol minuman seperti biasanya.

Yang dimaksud dengan "array" dalam SQL dan karena itu tidak standar

Meskipun kami ingin menelusuri daftar yang ada dalam bahasa program, SQL nació en los 70 orientasi ke tabel data, dan konsep susunan seperti yang pertama tidak menjadi bagian dari standar. Beberapa motor dilengkapi dengan tip dan fungsi untuk daftar, ekstensi terbaru lainnya, dan lainnya yang tidak perlu Anda pikirkan. Kehati-hatian dalam praktik adalah sesuatu yang memerlukan solusi khusus untuk motor, dan mengadakan pertemuan untuk menghindari kesalahan dan kerusakan pada kinerja.

Selain itu, sering kali kecenderungan untuk memasukkan IN ke dalam daftar yang dipisahkan dengan koma di dalam variabel (misalnya '1,2,3,4') diharapkan dapat diperluas secara ajaib. Jadi tidak berhasil: DALAM operator yang parser tulis ulang sebagai satu rangkaian OR (col = val1 OR col = val2…), dan satu nomor '1,2,3,4' hanya itu, satu nomor. Harapan palsu dari “IN (@lista)” adalah sebuah pertandingan klasik yang ingin Anda hancurkan dari awal.

SQL Server: tidak ada array nativos, tapi ada alternatif lain yang solid

Di SQL Server tidak ada array yang umum, cara yang digunakan adalah melalui praktik tablas temporales, variabel de tabla o TVPs (parameter bernilai tabel) untuk mewakili daftar. Perkiraan ini wajar dalam suatu sistem relasional dan peningkatan yang baik baik dari segi rencana maupun statistik.

Untuk beberapa hal kecil, variabel tab dapat sangat berguna. Sebagai contoh, ketika ada yang menangkap rangos atau gabungan yang direduksi, @tabla dapat memenuhi kebutuhan 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;

Cuando la lista crece, biasanya lebih baik pada tab temporal (#temp) atau TVP untuk pertanyaan tentang statistik dan pengoptimalan, semua yang perlu dilakukan adalah daftar partisipasi dalam serikat pekerja dan filter lengkap. Selain itu, jika front-end Anda adalah .NET, TVP adalah comodos, seguros dan eficaces untuk pasar lotes de valores tanpa berulang kali rangkaian rapuh.

Daftar yang dipisahkan oleh pembatas di SQL Server: STRING_SPLIT, fungsi, XML dan JSON

Jika Anda memiliki daftar yang dipisahkan oleh koma (atau oleh pembatas lainnya) dan tidak dapat mengubah aslinya, SQL Server menawarkan berbagai solusi. Lebih langsung dalam versi modern adalah STRING_SPLIT, yang menguraikan serangkaian file:

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

Di SQL Server 2022 dan Azure SQL, Anda juga dapat melakukan yang biasa (posisi dalam daftar) setelah parameter tercer (1), berguna untuk menyinkronkan beberapa daftar yang paralel. Sebelum tahun 2022 tidak ada kolom ordinal dan pembatas yang hanya bisa menjadi karakter, karena ini adalah skenario yang lebih lengkap. Jika BD memiliki kompatibilitas <130, maka Anda akan mendapatkan STRING_SPLIT yang tersedia, meskipun ini dalam versi 2016 atau lebih unggul.

Agar Anda tetap perlu mengontrol jenis, ruang kosong, dan ruang, ada fungsi sederhana yang dapat dibuat, misalnya untuk mentransfer ke dalam dan ke ruang lainnya. Versi multi-kalimatnya mudah disesuaikan a tus reglas (p. ej. devolver NULL en elementos vacíos, recortar espacios, soportar delimitadores de varios characteres):

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;

Penggunaan kombinasi binaria acelera pada pembatas, dan perhitungan garis bujur dengan PANJANG DATAL/2 tidak ada masalah dengan bidangnya. Untuk daftar kadena, Anda dapat memindahkan kolom varchar dan nvarchar ke perbaiki kesalahan dan tidak ada indeks yang dapat dinonaktifkan karena implikasi konversi.

Jika Anda tidak dapat membuat fungsi atau menggunakan STRING_SPLIT, alternatifnya sangat serbaguna dalam XML dan OPENJSON; untuk mendalami penggunaannya dengan JSON, konsultasikan procesamiento de JSON en SQL. Dengan XML, terapkan kembali pembatas untuk node dan lakukan .nodes('/x/text()'); dengan JSON, masukkan daftar dengan corchetes dan ambil dengan OPENJSON, pulihkan kunci (posisi berbasis 0) dan nilai:

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

Dengan XML/JSON yang cenderung mengalami penurunan dalam daftar besar, jika Anda bekerja dengan string yang mencakup karakter utama, menyimpan data yang aman (misalnya, CDATA dan XML) agar tidak mengganggu parseo. Ini semua adalah cara untuk menghasilkan SQL secara dinamis jenisnya “… WHERE col IN (“” + @lista + “)”: sangat rapuh, tidak aman (inyección) dan kemudian berakhir dengan daftar yang luas.

Pada titik tertentu untuk mendapatkan kelas: jika Anda ingin menggunakan daftar dan konsultasi lengkap, Anda harus terlebih dahulu mendapatkan nilai dan #tabla con PK untuk mengoptimalkan kinerja tersebut. Hal ini biasa terjadi pada filter IN terhadap fungsi TVF yang bertujuan untuk mengoptimalkan estimasi jumlah yang benar dan memilih bidang yang suboptimal.

PostgreSQL: array nativos, UNNEST dan LATERAL untuk berkonsultasi dengan pihak yang berwenang

PostgreSQL sí dispone de array asli dan ekosistem yang fungsinya sudah lengkap. Pengaturan ini untuk mengonversi array ke file UNNEST, lalu menu dengan CROSS JOIN LATERAL untuk kolom yang terkait:

-- 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;

Ini melindungi Anda dari masalah umum “memiliki array kolom dan ingin menyaring dan hanya memutar elemen yang memenuhi kondisi”. Gunakan LIKE atau filter mana pun yang perlu Anda kerjakan di kolom “unnessteada”, tidak ada array aslinya, karena predikatnya tidak diisikan ke dalam setiap elemen jika menyaring array seperti yang lainnya.

Ketika Anda perlu beroperasi dalam blok dengan beberapa kolom, izin pasar UNNEST berbagai array secara paralel dan garis-garis pada posisi (seperti “struct of arrays” atau “array of structs”):

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

Ini menghilangkan generasi dinamis dari N placeholder dan kemampuan pernyataan estático con solo dos parámetros Anda dapat memasukkan banyak registrasi seperti yang Anda inginkan. Sebagai perbandingan, penyisipan dengan UNNEST super dan los menyisipkan batch dengan VALUES (…),(…) untuk mengurangi parseo dan planificación.

Untuk IN, Anda juga dapat menggunakan UNNEST oleh operator: DI MANA id DI (PILIH * DARI UNNEST($1::INT[])). Dalam konteks ini, bangunlah placeholder secara dinamis ketika Anda sedang berkompetisi, tapi UNNEST akan membatalkan rencana dan dapat digunakan kembali. Mide y Elige según tu carga.

MySQL: JSON sebagai wadah daftar dan fungsi bisnis

MySQL tidak memiliki jenis array yang umum, tetapi jenisnya JSON permite almacenar listas dan konsultasikan elemennya. Jika kolom menjaga array JSON, Anda dapat menelusuri JSON_SEARCH atau memeriksa persimpangan dengan 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;

Jika kolom teksnya dan berisi array JSON yang valid, JSON_OVERLAPS(kendaraan, JSON_ARRAY('Luton')) te dirá si hay elementos comunes. Dapatkan data data dalam JSON nyata ketika Anda ingin menggunakan fungsi ini; menghindari konversi dan kesalahan sutiles dengan comillas dan lolos.

Memperhatikan fungsi-fungsi ini yang tersedia di MySQL 8.xy memeriksa versi dan cara SQL untuk menghindari sorpresa. Khususnya, sintaksis dan perilaku JSON_SEARCH/JSON_OVERLAPS dapat diterapkan pada 8.0.34, seperti pada contoh.

SQLite: array sin nativos; ekstensi carray/rarray dan batasnya

SQLite tidak menyertakan array asli dalam bahasa SQL, tetapi tersedia ekstensi seperti carray (y rarray en rusqlite) yang menunjukkan area seperti tab virtual pada kolom. Ini memungkinkan Anda membuat penyisipan besar atau jenis filter DALAM tanpa menghasilkan N placeholder:

-- 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;

El enfoque funciona, aunque itu rumit untuk menambah kolom dan Anda tidak dapat eskalasi dalam waktu singkat. Dalam praktiknya, penggunaan tab virtual ini telah dimulai peor desempeño que los inserts batched clasicos di SQLite, terutama karena mengimplementasikan akses ke baris di VTables. Kesimpulan: Saya tidak tahu bahwa “array” di SQLite akan lebih cepat; berkali-kali memasukkan banyak pernyataan yang disiapkan melalui transaksi yang optimal.

Untuk IN, ide “IN (SELECT * FROM rarray(?))” mungkin saja terjadi, tapi Anda juga dapat menghasilkan placeholder secara dinamis jika daftarnya tidak terlalu besar. Inilah alasan pragmatisnya: memenuhi syarat untuk menyelesaikan tugas yang tidak dapat diterima en tu entorno y versión.

Anti‑patrón de listas delimitadas en Columnas dan el diseño orientado a tablas

Simpan di kolom daftar yang dipisahkan oleh koma (atau serupa) bertentangan dengan prinsip relasional atom dan tentu saja memerlukan konsultasi, indeks, dan pemeliharaan. Un caso ekstrim nak berganda kolom dengan daftar “sinkronisasi” posisi (produk, jumlah, harga), juga sulit diaktualisasikan dan cenderung tidak koheren.

La alternativa profesional es modelar en tablas hijas. Sebagai contoh, ubah data menjadi produk/kuantitas/harga menjadi tab detail pesanan dan gunakan “penawaran” dengan STRING_SPLIT (urutan pertama) atau fungsi/OPENJSON/XML yang disinkronkan berdasarkan posisi. Setelah menormalisasi data, semua indeks ekosistem dan agregat membantu Anda, dan merekonstruksi daftar yang dapat disajikan tepat waktu (string_agg mulai tahun 2017, o UNTUK XML PATH di tahun 2016 dan sebelumnya) adalah hal yang sepele.

Secara online dengan ini, ketika memerlukan tambahan atribut yang fleksibel dalam suatu entitas (seperti sebuah proforma), esquema nombre‑valor (EAV) adalah array yang paling mudah beradaptasi di dalam kolom. Tab atribut(id, idproforma, atributo, valor) mengizinkan penambahan dan penyaringan atribut tanpa mengubah kolom dan matriks manipular. Ini lebih mudah untuk berkonsultasi, mengindeks, dan memelihara ada teks[][] atau rangkaian kontrol dosa JSON.

Kesalahan komunitas dan kesalahan dengan IN dan kompatibilitas

Como apuntábamos, DALAM tidak ada variabel yang diperluas dengan daftar yang dibatasi. Untuk menjalankannya, Anda perlu mengonversi daftar ke file (STRING_SPLIT, fungsi, XML/JSON) ke tabel gabungan (TVP, #tabla). Evita SQL dinámico: akan memakan waktu lama untuk melakukan injeksi, pesawat tidak dapat digunakan kembali, dan biaya penguraian akan tergantung pada daftar yang dibuat.

Pelajari selalu versi dan tingkat kompatibilitasnya. Misalnya, STRING_SPLIT memerlukan kompatibilitas 130+ dan argumen tersebut hanya ada di SQL Server 2022/Azure SQL. Si te sale un “Invalid object name 'STRING_SPLIT'” Pada tahun 2016, revisi tingkat kompatibilitas dasar sebelum kesimpulan dibuat.

Ketika Anda menggunakan dokumentasi eksternal, Anda akan menemukan bahwa film tersebut mungkin sudah usang. Jika ada referensi yang gagal, usa el buscador del sitio atau pide soporte; lebih dari sekali halaman “sempurna” untuk versi Anda, tetapi Anda tidak perlu melokalkan penerusnya atau yang setara.

Pelanggan Rendimiento: mengapa array tidak selalu melalui jalur yang cepat

"Array" akan datang dengan cepat, tapi dalam SQL, kinerjanya bergantung pada cara merencanakan dan menggunakan kembali pernyataan tersebut itulah strukturnya. Di PostgreSQL, UNNEST akan menghasilkan dan menyisipkan besar-besaran untuk menjaga estetika dengan parameter tertentu. Dalam SQLite, namun, array melalui VTables bisa lebih ringan que los memasukkan kumpulan tradisi.

Di SQL Server, uraikan daftar fungsi online atau OPENJSON/XML dan pindahkan hasilnya ke #tabla con índice dan pengoptimal informasi penting (statistik) untuk memilih indeks dan menghindari pemindaian. Ini adalah hal yang sulit untuk membayar dividen ketika konsultasi dilakukan jika Anda sedang menyelesaikannya.

Untuk menyaring jenis-jenis IN pada mesin apa pun, jika tugas bersama sangat bervariasi, pertimbangkan campuran strategi: placeholder dinamis untuk daftar banyak hal, Dan tabel estructura (TVP/#temp/UNNEST) sebuah partir de cierto umbra. Mide con tu carga de trabajo nyata; itu adalah bentuk yang pasti.

Kecepatan motor: sangat penting

SQL Server: menggunakan TVPs/temporales/variables de tabla untuk “arrays”; STRING_SPLIT untuk daftar pembatas (biasanya pada 2022+); OPENJSON untuk kebutuhan posisi tanpa fungsi; XML dan JSON tidak ada; evita IN con cadenas dan el SQL dinámico.

PostgreSQL: apóyate en arrays nativos y UNNEST con LATERAL; filtra tras descomprimir (SEPERTI 'label_%', expresiones regulares, dll.); untuk menyisipkan masivos, Array paralel UNNEST pada kolom penyederhanaan dan percepatan; valora si IN con UNNEST te kompensasi dari placeholder.

MySQL: menjaga daftar seperti JSON dan berkonsultasi dengan JSON_SEARCH/JSON_OVERLAPS; standarisasi format entri (array JSON valid) untuk menghindari kesalahan; jika Anda bagian dari teks, kirim JSON_ARRAY ke pesan tersebut.

SQLite: membuka transaksi, laporan disiapkan dan batch; rarray/carray dapat melayani dalam kasus yang konkret, tapi tidak ada rasa milagros de rendimiento; untuk N kolom, kombinasi baris dengan CROSS JOIN itu rumit.

Akhirnya, ketika Anda melewati kolom dengan daftar, tanaman akan normal. Anda merasa sangat jauh lebih cepat dan merasa bahwa bidang pengoptimalan akan membantu Anda, dengan lebih dari satu magia dan lebih banyak indeks.

Meskipun tidak ada satu penerima pun, sekarang ada peta yang jelas: jika SGBD Anda tidak memiliki array trae, simúlalos con tablas; jika pekerjaan itu dilakukan, cobalah dengan fungsi-fungsi lokal yang sesuai; jika menerima daftar pembatas, tuliskan dengan benar; dan jika terjadi kegagalan, itu akan bernilai pada struktur yang stabil. Dengan kata lain, “Manejo de arrays en SQL” akan menjadi sebuah laberinto dan pasa yang merupakan sebuah rangkaian herramientas yang baik.