Array-Manier in SQL: Techniken und Tricks bei beliebten Motoren

Startseite » Python » Array-Manier in SQL: Techniken und Tricks bei beliebten Motoren

Wenn Sie auf der Grundlage von Beziehungsdaten arbeiten und mit den in der Liste aufgeführten Strukturen arbeiten, ist dies nicht der Fall: Die Unterstützung für „Arrays“ in SQL ist nicht einheitlich, sondern auf allen Motoren standardisiert . Dies bedeutet, dass die Lösungen von der Verwendung von SQL Server, PostgreSQL, MySQL oder SQLite abhängen und außerdem mit der Version und der Kompatibilität des Systems kompatibel sind.

Dieser Artikel enthält ausführliche Informationen und praktische Beispiele, die ähnlich sind oder die Arrays mit dem SGBD freigeben. Es ist besser, zeitliche Tabellen, TVPs und Funktionen wie STRING_SPLIT oder OPENJSON zu verwenden, und ich verzichte auf reale Fälle, die mit den Filteretiketten eines Arrays verbunden sind. Auch das bedeutet, dass Sie die Arrays aufgeben und die Informationen in normalen Beziehungstabellen nachbilden müssen , und dass Sie nicht mehr auf die gewohnten Flaschen warten müssen.

Ich habe mich für „array“ in SQL entschieden und es ist nicht normal

Wir haben die Listen als Standard in einer Programmiersprache gelesen, SQL basiert auf 70 tabellarischen Daten , und das Array-Konzept, wie es ursprünglich angenommen wurde, ist nicht Teil der Norm. Algunos Motoren enthalten Tipps und Funktionen für Listen, andere aktuelle Erweiterungen und andere, die nicht in Betracht gezogen werden. Die praktische Konsequenz besteht darin, dass ein Fehler erforderlich ist, um verschiedene Lösungen für den Motor zu finden , und Sie müssen sich keine Sorgen machen, um Fehler und Leistungsverluste zu vermeiden.

Außerdem ist es häufig der Versuch, ein IN mit einer Liste zu verbinden, die durch eine Variable (z. B. „1,2,3,4“) getrennt ist, die magisch erweitert werden soll. Dies funktioniert nicht: IN ist ein Operator, der den Parser als eine Folge von OR (col = val1 OR col = val2…) umschreibt, und eine Folge '1,2,3,4' ist nur eine Folge. Die falsche Erwartung von „IN (@lista)“ ist ein klassischer Trampa, der vom Start weg kommt.

SQL Server: keine nativen Arrays, aber solide Alternativen

In SQL Server gibt es normalerweise keine Arrays, daher werden in der Praxis Zeittabellen, Tabellenvariablen oder TVPs (Tabellenwertparameter) verwendet , um Listen darzustellen. Diese Annäherung ist in einem Beziehungssystem selbstverständlich und wird je nach Plan-, Index- und Entwicklungsplan skaliert.

Für viele Kleinigkeiten kann eine Tischvariable sehr nützlich sein. Zum Beispiel, wenn wir Ranglisten oder reduzierte Verbindungen erfassen möchten, kann eine @tabla die Notwendigkeit eines Arrays ergänzen :

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;

Sobald die Liste erstellt wurde, ist sie die beste für eine zeitliche Tabelle (#temp) oder ein TVP für statistische und Optimierungsfragen. Alles in allem ist es eine Liste von Teilnehmern in Gewerkschaften und komplexen Filtern. Wenn Ihr Front-End jedoch .NET ist, sind die TVPs nicht nur nützlich, sondern auch sicher und effizient, um viele wertvolle Dinge nicht wiederkehren zu lassen und zerbrechliche Verbindungen herzustellen.

Getrennte Listen nach Trennzeichen in SQL Server: STRING_SPLIT, Funktionen, XML und JSON

Wenn Sie eine durch Komma getrennte Liste (oder einen anderen Trenner) haben und den Ursprung nicht ändern können, bietet SQL Server verschiedene Quellen an. Die direkteste Version in der modernen Version ist STRING_SPLIT , die eine Zeile in den Ordner einfügt:

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

In SQL Server 2022 und Azure SQL können Sie auch die Ordinalzahl (Position in der Liste) eingeben und einen dritten Parameter (1) eingeben, um mehrere parallele Listen zu synchronisieren . Vor 2022 gab es keine Ordinalspalte und das Trennzeichen allein könnte ein Charakter sein, da wir mit komplexeren Szenarios konfrontiert waren. Wenn der BD mit einer Kompatibilität < 130 kompatibel ist, ist dieser Typ STRING_SPLIT verfügbar , dieser ist ab Version 2016 oder höher.

Für Gäste ist es notwendig, das Zimmer, den Urlaub und die Räume zu kontrollieren, es gibt einfache Funktionen, die man erstellen kann, zum Beispiel eins, das man in ein anderes Haus umwandeln muss. Die Versionen mehrerer Sätze lassen sich leicht an Ihr Reglas anpassen (z. B. NULL in leere Elemente übertragen, Räume öffnen, Trennzeichen für verschiedene Zeichen hinzufügen):

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;

Die Verwendung einer binären Sortierung beschleunigt die Begrenzung des Trennzeichens und die Längengradberechnung mit DATALENGTH/2 vermeidet Probleme mit Räumen . Für die Listen der Kader können Sie die Spalten varchar und nvarchar erweitern, um die Tipps zu respektieren und die Indizes für implizite Konvertierungen nicht zu deaktivieren.

Wenn Sie keine Funktionen erstellen möchten, die STRING_SPLIT verwenden, sind dies vielseitige Alternativen wie XML und OPENJSON . Um Ihre Verwendung mit JSON zu vertiefen, konsultieren Sie das JSON- und SQL-Verfahren . Mit XML wird das Trennzeichen für Knoten wiederhergestellt und .nodes('/x/text()'); mit JSON, umhüllt die Liste mit Schlüsseln und Daten mit OPENJSON, holt den Schlüssel (Position 0-basiert) und den Wert zurück:

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

Mit XML/JSON ist es am besten, eine Liste zu erstellen, und wenn Strings verwendet werden, die besondere Zeichen enthalten , werden Schutzmaßnahmen (z. B. CDATA und XML) benötigt, um die Analyse nicht zu beeinträchtigen. Umgehen Sie alle gängigen SQL-Anweisungen des Typs „… WHERE col IN (“ + @lista + „)“: Es ist zerbrechlich, inaktiviert (injiziert) und wird mit erweiterten Listen zurückgegeben.

Ein wichtiger Schlüssel zur Vergabe: Wenn Sie die Liste in vollständigen Beratungsgesprächen verwenden, laden Sie zunächst den Wert in einer #Tabla mit PK ein, damit der Optimierer in der Zukunft ist. Es ist üblich, dass ein IN-Filter gegen eine TVF-Funktion dazu führt, dass der Optimierer die Kardinalität korrekt einschätzt und untergeordnete Ebenen auswählt.

PostgreSQL: Native Arrays, UNNEST und LATERAL für mögliche Abfragen

PostgreSQL verfügt über native Arrays und ein umfassendes Funktionsökosystem. Der Befehl zum Konvertieren eines Arrays in Dateien lautet UNNEST und wird mit CROSS JOIN LATERAL aufgerufen, um die folgenden Spalten zu vergleichen:

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

Dieser Benutzer löst das typische Problem, dass „Sie ein Spalten-Array verwenden und nur die Elemente filtern und kontaktieren müssen, die in einen Zustand geraten sind“. Benutzen Sie LIKE oder den Filter, bevor Sie ihn über die Spalte „uneingeschränkt“ übertragen , nicht über das Original-Array, da das Problem nicht darin besteht, jedes Element aufzurufen, wenn Sie das Array wie ein Ganzes filtern.

Wenn Sie in einem Block mit mehreren Spalten arbeiten müssen, ermöglicht UNNEST das gleichzeitige Einfügen verschiedener Arrays und eine Reihe von Positionen (als „Struktur von Arrays“ und „Array von Strukturen“):

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

Dies eliminiert die Generierung digitaler N-Platzhalter und ermöglicht eine statische Anweisung mit nur zwei Parametern, die wie folgt eingefügt werden können. Bei Vergleichstests wird die Einfügung mit UNNEST über die gestapelten Einfügungen mit VALUES (…),(…) zur Reduzierung der Analyse und Planung hinausgeführt.

Für IN können Sie auch UNNEST mit dem Operator verwenden: WHERE id IN (SELECT * FROM UNNEST($1::INT[])) . Im Kontext der Erstellung von Platzhaltern in Wettbewerbssituationen ist es jedoch nicht möglich, den Plan zu verkleinern und wiederverwendbar zu machen . Mide y elige según you carga.

MySQL: JSON als Inhaltsverzeichnis und Datenbankfunktionen

MySQL verfügt nicht über ein typisches Array, aber der JSON-Typ ermöglicht es, Listen abzurufen und ihre Elemente zu konsultieren. Wenn eine Spalte ein JSON-Array schützt, können Sie diese mit JSON_SEARCH suchen oder den Schnitt mit JSON_OVERLAPS vergleichen:

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;

Wenn die Textspalte ein gültiges JSON-Array enthält, muss JSON_OVERLAPS(vehicles, JSON_ARRAY('Luton')) alle Elemente enthalten. Stellen Sie die Daten in JSON bereit, sobald Sie diese Funktionen verwenden. Vermeiden Sie Konvertierungen und Fehler mithilfe von Befehlen und Escapezeichen.

Da diese Funktionen derzeit in MySQL 8.xy verfügbar sind, müssen Sie die Version und den SQL-Modus überprüfen, um Fehler zu vermeiden. Insbesondere ist die Syntax und Kompatibilität von JSON_SEARCH/JSON_OVERLAPS in 8.0.34 stabil, wie im Beispiel.

SQLite: Sin-Arrays nativ; Erweiterungen carray/rarray und ihre Grenzen

SQLite enthält keine nativen Arrays in der SQL-Sprache, verfügt jedoch über Erweiterungen wie Carray (und Rarray in Rusqlite), die als virtuelle Tabellen in einer Spalte angezeigt werden. Dies ermöglicht die Konstruktion von Filtereinsätzen oder Filtern ohne generische N-Platzhalter:

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

Das Enfoque funktioniert, es ist jedoch kompliziert, die Spalten zu erweitern , und Sie können es beim Rendern nicht hochstufen. Zu Testzwecken wurde die Verwendung dieser virtuellen Tabellen dadurch beeinträchtigt, dass sie Batch-Klassiker in SQLite einfügt, insbesondere weil sie den Rowid-Zugriff in VTables implementieren. Fazit: Mittlerweile ist es nicht so, dass „array“ in SQLite schneller geht ; Vieles, was Sie für eine Transaktion tun müssen, ist die optimale Lösung.

Für IN ist die Idee von „IN (SELECT * FROM rarray(?))“ möglich, aber auch die Möglichkeit, Platzhalter zu generieren, wenn die Liste nicht riesig ist. Die Konsequenz hier ist pragmatisch: Es ist sehr wichtig, dass Sie Ihr Unternehmen und Ihre Version gut erhalten .

Die Anti-Patron-Liste ist in Spalten abgegrenzt und das Design orientiert sich an Tabellen

Almacenar hat in einer Kolumne eine separate Liste von Komas (oder Ähnlichem) erstellt, die dem Prinzip der Atommörderung widerspricht und die Konsultationen, Indizes und Wartungen kompliziert macht. Im Extremfall gibt es mehrere Kolumnen mit Listen „übereinstimmender“ Positionen (Produkte, Preise, Preise), Schwierigkeiten bei der Aktualisierung und Inkohärenz.

Die professionelle Alternative ist auf Tischen modelliert . Konvertieren Sie beispielsweise die Dateien mit „productos/qty/precio“ in eine Tabelle mit Bestelldetails und geben Sie sie mit „STRING_SPLIT“ (ordinal) oder mit den für die Position synchronisierten Funktionen „/OPENJSON/XML“ ein. Nachdem wir die Daten normalisiert haben, ist das Index- und Aggregationssystem auf Ihre Kosten gekommen , und die Wiederherstellung einer vorzeigbaren Liste (string_agg seit 2017, oder FOR XML PATH seit 2016 und davor) ist trivial.

In diesem Zusammenhang sind zusätzliche flexible Attribute zu einem Unternehmen (z. B. ein Formular) erforderlich, und ein Wert (EAV) ist am anpassungsfähigsten für Arrays in den Spalten . Eine Tabelle mit Attributen (ID, ID-Formular, Attribut, Wert) ermöglicht das Hinzufügen und Filtern von Attributen, ohne Spalten in manipulierbaren Matrizen neu zu gestalten. Es ist am einfachsten, einen Text[][] zu konsultieren, zu indizieren und zu verwalten, um eine JSON-Kontrolle hinzuzufügen.

Es liegen gemeinschaftliche Fehler und Probleme mit IN und Kompatibilität vor

Wie gesagt, IN keine Variablen mit abgegrenzten Listen erweitern . Für diese Funktion muss die Liste in Dateien (STRING_SPLIT, Funktion, XML/JSON) konvertiert oder in eine tabellarische Verbindung (TVP, #tabla) umgewandelt werden. Evita SQL-Dinámico : Erzielt hohe Einspeisekosten, erfordert keine Wiederverwendbarkeit und kostet das Parsen, sobald die Liste ansteigt.

Überprüfen Sie die Version und die Kompatibilitätsstufe. Zum Beispiel erfordert STRING_SPLIT eine Kompatibilität von 130+ und das dritte Argument muss nur in SQL Server 2022/Azure SQL vorhanden sein. Auf der Website „Ungültiger Objektname ‚STRING_SPLIT‘“ im Jahr 2016 wurde die Kompatibilitätsstufe der Basis vor den Schlussfolgerungen überprüft.

Wenn Sie die externe Dokumentation verwenden, wird davon ausgegangen, dass diese veraltet oder verschickt werden kann. Wenn es sich um eine Referenz handelt, verwenden Sie die Website-Suche oder die Support-Funktion . Mehr als eine Seite „perfekt“ für Ihre Version, die Sie noch nicht erstellt haben und die Sie später oder gleichwertig lokalisieren müssen.

Rendimiento-Kunden: Weil die Arrays nicht auf dem schnellsten Weg sind

„Array“ wird schneller, aber die SQL-Anweisung hängt mehr davon ab, wie man die in der Struktur enthaltene Anweisung plant und wiederverwendet . In PostgreSQL wird UNNEST verwendet, um viele Einfügungen vorzunehmen und die Statistiken mit den Parametern zu konsultieren. In SQLite können Arrays über VTables jedoch länger dauern als herkömmliche Batch-Einfügungen.

Entfernen Sie in SQL Server die Liste der Online-Funktionen von OPENJSON/XML und verschieben Sie das Ergebnis zu einer #Tabelle mit dem Index aller entscheidenden Optimierungsinformationen (Statistikdaten), um Indizes zu erstellen und Scans zu vermeiden. Es ist nicht möglich, Dividenden zu zahlen, sobald die Beratung abgeschlossen ist.

Für Filter, die in Echtzeit verwendet werden, wenn die Verbindungsgröße unterschiedlich ist, berücksichtigen Sie gemischte Strategien: Platzhalter für kleine Listen und tabellarische Struktur (TVP/#temp/UNNEST) in der Umgebung. Mide con tu carga de trabajo real; Dies ist die einzige Form der Bestätigung.

Schnelles Recetas vom Motor: lo essenziell

SQL Server : TVPs/Temporales/Variables-Tabelle für „Arrays“ verwenden; STRING_SPLIT für abgegrenzte Listen (ordinal ab 2022); OPENJSON für erforderliche Position ohne Funktion; XML und JSON sind nicht verschlüsselt; Vermeiden Sie es, mit dem SQL-Dinámico fortzufahren.

PostgreSQL : Native Arrays verwenden und mit LATERAL UNNESTIEREN; Filtra tras descomprimir (LIKE 'label_%', reguläre Ausdrucke usw.); Für größere Einfügungen, UNNEST parallelisiert Arrays zur Vereinfachung und Beschleunigung; Bitte beachten Sie, dass IN mit UNNEST die Verwendung von Platzhaltern ersetzt werden muss.

MySQL : Listen wie JSON überwachen und mit JSON_SEARCH/JSON_OVERLAPS konsultieren; Standardisierung des Eingabeformats (gültige JSON-Arrays), um Eingaben zu vermeiden; Wenn Sie Textteile haben, konvertieren Sie JSON_ARRAY in die Abfrage mit der Messung.

SQLite : Zugriff auf Transaktionen, vorbereitete Anweisungen und Batches; rarray/carray kann in konkreten Fällen eingesetzt werden, aber es gibt keine Aussicht auf Rückzahlung ; Für N Spalten ist die Kombination von Zeilen mit CROSS JOIN kompliziert.

Letztendlich, sobald Sie weitere Spalten in der Liste haben, normalisieren Sie sie . Es ist schrecklich, mehr Geld zu verdienen und die Flugzeuge des Optimierers mit weniger Magie und mehr Indizes auf deine Gunst zu bringen.

Es existiert noch kein einziger Empfang, jetzt werden sie mit einer klaren Karte angezeigt: Wenn Ihr SGBD keine Arrays mehr hat, simulieren sie mit Tabellen ; si los trae, exprímelos con las funciones nativas adecuadas; Wenn Sie abgegrenzte Listen erhalten, löschen Sie sie korrekt. Und auf der Suche nach einem Flojea-Rendimiento sind diese Werte für eine Struktur mit städtischen Verhältnissen wertvoll. Mit dieser Mühe muss die „Manier von Arrays in SQL“ aus einem Labor bestehen und wurde zu einem gut sortierten Werkzeugkasten.