Από καιρό σε καιρό, ο προγραμματιστής χρειάζεται περάστε ένα σύνολο παραμέτρων ή ακόμα και μια ολόκληρη επιλογή στο αίτημα "στην είσοδο". Μερικές φορές υπάρχουν πολύ περίεργες λύσεις σε αυτό το πρόβλημα.
Ας πάμε «από το αντίθετο» και ας δούμε πώς να μην το κάνεις, γιατί και πώς μπορείς να το κάνεις καλύτερα.
Άμεση "εισαγωγή" τιμών στο σώμα αιτήματος
Συνήθως μοιάζει κάπως έτσι:
query = "SELECT * FROM tbl WHERE id = " + value... ή όπως αυτό:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)Περί αυτής της μεθόδου λέγεται, γράφεται και αρκετά:
Σχεδόν πάντα είναι άμεση διαδρομή για την ένεση SQL και ένα επιπλέον φορτίο στην επιχειρηματική λογική, η οποία αναγκάζεται να «κολλήσει» τη συμβολοσειρά ερωτήματός σας.
Αυτή η προσέγγιση μπορεί να δικαιολογηθεί εν μέρει μόνο εάν είναι απαραίτητο. χρήση κατάτμησης στις εκδόσεις PostgreSQL 10 και παρακάτω για πιο αποτελεσματικό σχέδιο. Σε αυτές τις εκδόσεις, η λίστα των σαρωμένων τμημάτων καθορίζεται χωρίς να λαμβάνονται υπόψη οι μεταδιδόμενες παραμέτρους, μόνο με βάση το σώμα του αιτήματος.
$n ορίσματα
Χρήση οι παράμετροι είναι καλές, σας επιτρέπει να χρησιμοποιήσετε , μειώνοντας το φόρτο τόσο στην επιχειρησιακή λογική (η συμβολοσειρά ερωτήματος σχηματίζεται και μεταδίδεται μόνο μία φορά) όσο και στον διακομιστή της βάσης δεδομένων (δεν απαιτείται εκ νέου ανάλυση και προγραμματισμός για κάθε περίπτωση του αιτήματος).
Μεταβλητός αριθμός ορισμάτων
Θα μας περιμένουν προβλήματα όταν θέλουμε να περάσουμε έναν άγνωστο αριθμό ορισμάτων εκ των προτέρων:
... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...Εάν αφήσετε το αίτημα σε αυτήν τη φόρμα, τότε αν και θα μας σώσει από πιθανές ενέσεις, θα εξακολουθεί να οδηγεί στην ανάγκη να κολλήσουμε / αναλύσουμε το αίτημα για κάθε επιλογή από τον αριθμό των ορισμάτων. Είναι ήδη καλύτερο από το να το κάνεις κάθε φορά, αλλά μπορείς και χωρίς αυτό.
Αρκεί να περάσετε μόνο μία παράμετρο που περιέχει σειριακή αναπαράσταση ενός πίνακα:
... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'Η μόνη διαφορά είναι η ανάγκη να μετατραπεί ρητά το όρισμα στον επιθυμητό τύπο πίνακα. Αυτό όμως δεν δημιουργεί προβλήματα, αφού ήδη γνωρίζουμε εκ των προτέρων πού απευθυνόμαστε.
Μεταφορά δείγματος (μήτρα)
Συνήθως αυτές είναι κάθε είδους επιλογές για τη μεταφορά συνόλων δεδομένων για εισαγωγή στη βάση δεδομένων "σε ένα αίτημα":
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...Εκτός από τα προβλήματα που περιγράφηκαν παραπάνω με το «επανακόλληση» του αιτήματος, αυτό μπορεί να μας οδηγήσει και σε μη διαθέσιμη μνήμη και συντριβή διακομιστή. Ο λόγος είναι απλός - το PG διατηρεί πρόσθετη μνήμη για τα ορίσματα και ο αριθμός των εγγραφών στο σύνολο περιορίζεται μόνο από την εφαρμογή επιχειρηματικής λογικής Wishlist. Σε ιδιαίτερα κλινικές περιπτώσεις ήταν απαραίτητο να δούμε "αριθμημένα" επιχειρήματα μεγαλύτερα από 9000 $ - μην το κάνετε με αυτόν τον τρόπο.
Ας ξαναγράψουμε το ερώτημα, εφαρμόζοντας ήδη σειριοποίηση "δύο επιπέδων".:
INSERT INTO tbl
SELECT
unnest[1]::text k
, unnest[2]::integer v
FROM (
SELECT
unnest($1::text[])::text[] -- $1 : '{"{a,1}","{b,2}","{c,3}","{d,4}"}'
) T;
Ναι, στην περίπτωση των "σύνθετων" τιμών μέσα σε έναν πίνακα, πρέπει να πλαισιωθούν με εισαγωγικά.
Είναι σαφές ότι με αυτόν τον τρόπο μπορείτε να «επεκτείνετε» την επιλογή με αυθαίρετο αριθμό πεδίων.
ξεφωλιάζω, ξεφωλιάζω,…
Κατά καιρούς υπάρχουν επιλογές για να περάσετε αντί για "πίνακα πινάκων" αρκετούς "πίνακες στηλών" που ανέφερα :
SELECT
unnest($1::text[]) k
, unnest($2::integer[]) v;Με αυτήν τη μέθοδο, εάν κάνετε λάθος κατά τη δημιουργία λιστών τιμών για διαφορετικές στήλες, είναι πολύ εύκολο να λάβετε πλήρως απροσδόκητα αποτελέσματα, τα οποία εξαρτώνται επίσης από την έκδοση διακομιστή:
-- $1 : '{a,b,c}', $2 : '{1,2}'
-- PostgreSQL 9.4
k | v
-----
a | 1
b | 2
c | 1
a | 2
b | 1
c | 2
-- PostgreSQL 11
k | v
-----
a | 1
b | 2
c |JSON
Ξεκινώντας από την έκδοση 9.3, η PostgreSQL διαθέτει πλήρεις λειτουργίες για εργασία με τον τύπο json. Επομένως, εάν οι παράμετροι εισαγωγής σας έχουν καθοριστεί στο πρόγραμμα περιήγησης, μπορείτε να σχηματίσετε ακριβώς εκεί αντικείμενο json για ερώτημα SQL:
SELECT
key k
, value v
FROM
json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'Για προηγούμενες εκδόσεις, μπορεί να χρησιμοποιηθεί η ίδια μέθοδος καθένα (hstore), αλλά το σωστό "δίπλωμα" με διαφυγή σύνθετων αντικειμένων στο hstore μπορεί να προκαλέσει προβλήματα.
json_populate_recordset
Εάν γνωρίζετε εκ των προτέρων ότι τα δεδομένα από τον πίνακα "input" json θα συμπληρωθούν σε κάποιον πίνακα, μπορείτε να αποθηκεύσετε πολλά σε πεδία "αποαναφοράς" και μετάδοση στους επιθυμητούς τύπους χρησιμοποιώντας τη συνάρτηση json_populate_recordset:
SELECT
*
FROM
json_populate_recordset(
NULL::pg_class
, $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
);json_to_recordset
Και αυτή η συνάρτηση απλώς θα «επεκτείνει» τον διαβιβασμένο πίνακα αντικειμένων σε μια επιλογή, χωρίς να βασίζεται στη μορφή του πίνακα:
SELECT
*
FROM
json_to_recordset($1::json) T(k text, v integer);
-- $1 : '[{"k":"a","v":1},{"k":"b","v":2}]'
k | v
-----
a | 1
b | 2ΠΡΟΣΩΡΙΝΟΣ ΤΡΑΠΕΖΙ
Αλλά εάν ο όγκος των δεδομένων στο μεταδιδόμενο δείγμα είναι πολύ μεγάλος, τότε η απόρριψή του σε μία σειριακή παράμετρο είναι δύσκολη και μερικές φορές αδύνατη, καθώς απαιτεί εφάπαξ μεγάλη κατανομή μνήμης. Για παράδειγμα, πρέπει να συλλέξετε μια μεγάλη παρτίδα δεδομένων συμβάντων από ένα εξωτερικό σύστημα για μεγάλο, μεγάλο χρονικό διάστημα και, στη συνέχεια, θέλετε να τα επεξεργαστείτε μία φορά από την πλευρά της βάσης δεδομένων.
Σε αυτή την περίπτωση, η καλύτερη λύση θα ήταν η χρήση :
CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком
Η μέθοδος είναι καλή για σπάνια μετάδοση μεγάλων όγκων δεδομένα.
Από την άποψη της περιγραφής της δομής των δεδομένων του, ένας προσωρινός πίνακας διαφέρει από έναν "κανονικό" πίνακα μόνο σε ένα χαρακτηριστικό. στον πίνακα συστήματος pg_class, ενώ στο pg_type, pg_depend, pg_attribute, pg_attrdef, ... — και τίποτα απολύτως.
Επομένως, σε συστήματα Ιστού με μεγάλο αριθμό βραχύβιων συνδέσεων για καθένα από αυτά, ένας τέτοιος πίνακας θα δημιουργεί νέες εγγραφές συστήματος κάθε φορά, οι οποίες διαγράφονται όταν κλείσει η σύνδεση με τη βάση δεδομένων. Τελικά, Η ανεξέλεγκτη χρήση του TEMP TABLE οδηγεί σε "φούσκωμα" των πινάκων στο pg_catalog και επιβραδύνοντας πολλές λειτουργίες που τα χρησιμοποιούν.
Φυσικά, αυτό μπορεί να καταπολεμηθεί περιοδικό πέρασμα VACUUM FULL σύμφωνα με τους πίνακες καταλόγου συστήματος.
Μεταβλητές συνεδρίας
Ας υποθέσουμε ότι η επεξεργασία των δεδομένων από την προηγούμενη περίπτωση είναι αρκετά περίπλοκη για ένα ερώτημα SQL, αλλά θέλετε να το κάνετε αρκετά συχνά. Δηλαδή, θέλουμε να χρησιμοποιήσουμε διαδικαστική επεξεργασία στο , αλλά η χρήση μεταφοράς δεδομένων μέσω προσωρινών πινάκων θα είναι πολύ ακριβή.
Επίσης, δεν μπορούμε να χρησιμοποιήσουμε $n-parameters για να περάσουμε σε ένα ανώνυμο μπλοκ. Οι μεταβλητές συνεδρίας και η συνάρτηση θα μας βοηθήσουν να βγούμε από την κατάσταση. τρέχουσα ρύθμιση.
Πριν από την έκδοση 9.2, έπρεπε να ρυθμίσετε εκ των προτέρων custom_variable_classes για τις μεταβλητές συνόδου "τους". Στις τρέχουσες εκδόσεις, μπορείτε να γράψετε κάτι σαν αυτό:
SET my.val = '{1,2,3}';
DO $$
DECLARE
id integer;
BEGIN
FOR id IN (SELECT unnest(current_setting('my.val')::integer[])) LOOP
RAISE NOTICE 'id : %', id;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- NOTICE: id : 1
-- NOTICE: id : 2
-- NOTICE: id : 3Υπάρχουν άλλες διαθέσιμες λύσεις σε άλλες υποστηριζόμενες διαδικαστικές γλώσσες.
Γνωρίζετε περισσότερους τρόπους; Κοινοποιήστε στα σχόλια!
Πηγή: www.habr.com