ProHoster > blog > Gweinyddiaeth > Gwrthbatrymau PostgreSQL: Pasio Setiau a Dewisiadau i SQL

Gwrthbatrymau PostgreSQL: Pasio Setiau a Dewisiadau i SQL

O bryd i'w gilydd, mae angen y datblygwr pasio set o baramedrau neu hyd yn oed ddetholiad cyfan i'r cais "wrth y fynedfa". Weithiau mae atebion rhyfedd iawn i'r broblem hon.
Gwrthbatrymau PostgreSQL: Pasio Setiau a Dewisiadau i SQL
Gadewch i ni fynd "o'r gwrthwyneb" a gweld sut i beidio â'i wneud, pam, a sut y gallwch chi ei wneud yn well.

Uniongyrchol "gosod" gwerthoedd yn y corff cais

Fel arfer mae'n edrych yn rhywbeth fel hyn:

query = "SELECT * FROM tbl WHERE id = " + value

... neu fel hyn:

query = "SELECT * FROM tbl WHERE id = :param".format(param=value)

Am y dull hwn y dywedir, yn ysgrifenedig a hyd yn oed yn tynnu digon:

Gwrthbatrymau PostgreSQL: Pasio Setiau a Dewisiadau i SQL

Bron bob amser y mae llwybr uniongyrchol i chwistrelliad SQL a llwyth ychwanegol ar resymeg y busnes, sy'n cael ei orfodi i “gludo” llinyn eich ymholiad.

Dim ond os oes angen y gellir cyfiawnhau'r dull hwn yn rhannol. defnyddio rhaniad yn fersiynau PostgreSQL 10 ac is i gael cynllun mwy effeithlon. Yn y fersiynau hyn, pennir y rhestr o adrannau wedi'u sganio heb ystyried y paramedrau a drosglwyddir, dim ond ar sail y corff cais.

$n dadl

Defnyddio dalfannau paramedrau yn dda, mae'n caniatáu ichi ddefnyddio DATGANIADAU PARATOI, lleihau'r llwyth ar resymeg y busnes (mae'r llinyn ymholiad yn cael ei ffurfio a'i drosglwyddo unwaith yn unig) ac ar weinydd y gronfa ddata (nid oes angen ail-ddosrannu a chynllunio ar gyfer pob achos o'r cais).

Nifer amrywiol o ddadleuon

Bydd problemau yn ein disgwyl pan fyddwn am basio nifer anhysbys o ddadleuon ymlaen llaw:

... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...

Os byddwch yn gadael y cais yn y ffurflen hon, yna er y bydd yn ein harbed rhag pigiadau posibl, bydd yn dal i arwain at yr angen i gludo / dosrannu'r cais ar gyfer pob opsiwn o nifer y dadleuon. Eisoes yn well na'i wneud bob tro, ond gallwch chi wneud hebddo.

Mae'n ddigon i basio dim ond un paramedr sy'n cynnwys cynrychiolaeth cyfresol o arae:

... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'

Yr unig wahaniaeth yw'r angen i drosi'r ddadl yn benodol i'r math arae a ddymunir. Ond nid yw hyn yn achosi problemau, gan ein bod eisoes yn gwybod ymlaen llaw ble rydym yn mynd i'r afael.

Trosglwyddiad sampl (matrics)

Fel arfer mae’r rhain yn fathau o opsiynau ar gyfer trosglwyddo setiau data i’w gosod yn y gronfa ddata “mewn un cais”:

INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...

Yn ogystal â'r problemau a ddisgrifir uchod gyda "ail-gludo" y cais, gall hyn hefyd ein harwain at allan o gof a damwain gweinydd. Mae'r rheswm yn syml - mae PG yn cadw cof ychwanegol ar gyfer y dadleuon, ac mae nifer y cofnodion yn y set wedi'i gyfyngu gan y Rhestr Ddymuniadau cymhwysiad rhesymeg busnes yn unig. Mewn achosion arbennig o glinigol roedd angen gweld dadleuon "wedi'u rhifo" sy'n fwy na $9000 - peidiwch â'i wneud fel hyn.

Gadewch i ni ailysgrifennu'r ymholiad, gan wneud cais yn barod cyfresoli "dwy lefel".:

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;

Oes, yn achos gwerthoedd "cymhleth" y tu mewn i arae, mae angen eu fframio â dyfynbrisiau.
Mae'n amlwg y gallwch chi "ehangu" y dewis yn y modd hwn gyda nifer mympwyol o feysydd.

aflonydd, anesmwyth, …

O bryd i'w gilydd mae opsiynau ar gyfer pasio yn lle "arae o araeau" sawl "arae o golofnau" y soniais amdanynt yn yr erthygl ddiweddaf:

SELECT
  unnest($1::text[]) k
, unnest($2::integer[]) v;

Gyda'r dull hwn, os gwnewch gamgymeriad wrth gynhyrchu rhestrau o werthoedd ar gyfer gwahanol golofnau, mae'n hawdd iawn cael yn gyfan gwbl canlyniadau annisgwyl, sydd hefyd yn dibynnu ar fersiwn y gweinydd:

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

Gan ddechrau o fersiwn 9.3, mae gan PostgreSQL swyddogaethau llawn ar gyfer gweithio gyda'r math json. Felly, os yw'ch paramedrau mewnbwn wedi'u diffinio yn y porwr, gallwch chi yno a ffurfio gwrthrych json ar gyfer ymholiad SQL:

SELECT
  key k
, value v
FROM
  json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'

Ar gyfer fersiynau blaenorol, gellir defnyddio'r un dull ar gyfer pob (hstore), ond gall "plygu" cywir gyda dianc gwrthrychau cymhleth yn hstore achosi problemau.

json_populate_recordset

Os ydych chi'n gwybod ymlaen llaw y bydd y data o'r arae json “mewnbwn” yn mynd i lenwi rhywfaint o dabl, gallwch arbed llawer mewn meysydd “di-gyfeirio” a chastio i'r mathau a ddymunir gan ddefnyddio'r swyddogaeth 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_i_recordset

A bydd y swyddogaeth hon yn syml yn “ehangu” yr amrywiaeth o wrthrychau a basiwyd yn ddetholiad, heb ddibynnu ar fformat y tabl:

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

TABL DROS DRO

Ond os yw swm y data yn y sampl a drosglwyddir yn fawr iawn, yna mae'n anodd ei daflu i un paramedr cyfresol, ac weithiau'n amhosibl, gan fod angen un-amser. dyraniad cof mawr. Er enghraifft, mae angen i chi gasglu swp mawr o ddata digwyddiad o system allanol am amser hir, hir, ac yna rydych chi am ei brosesu un-amser ar ochr y gronfa ddata.

Yn yr achos hwn, yr ateb gorau fyddai ei ddefnyddio byrddau dros dro:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком

Mae'r dull yn dda ar gyfer trosglwyddo niferoedd mawr yn anaml data.
O safbwynt disgrifio strwythur ei ddata, mae tabl dros dro yn wahanol i dabl “rheolaidd” mewn un nodwedd yn unig. yn nhabl system pg_class, ac yn pg_type, pg_depend, pg_attribute, pg_attrdef, ... - a dim byd o gwbl.

Felly, mewn systemau gwe sydd â nifer fawr o gysylltiadau byrhoedlog ar gyfer pob un ohonynt, bydd tabl o'r fath yn cynhyrchu cofnodion system newydd bob tro, sy'n cael eu dileu pan fydd y cysylltiad â'r gronfa ddata ar gau. Yn y pen draw, defnydd afreolus o TABL TEMP yn arwain at "chwydd" tablau yn pg_catalog ac arafu llawer o weithrediadau sy'n eu defnyddio.
Wrth gwrs, gellir mynd i'r afael â hyn pas cyfnodol VACUUM LLAWN yn ôl tablau catalog y system.

Newidynnau Sesiwn

Tybiwch fod prosesu'r data o'r achos blaenorol yn eithaf cymhleth ar gyfer un ymholiad SQL, ond rydych chi am ei wneud yn eithaf aml. Hynny yw, rydym am ddefnyddio prosesu gweithdrefnol yn DO bloc, ond bydd defnyddio trosglwyddo data trwy dablau dros dro yn rhy ddrud.

Ni allwn ychwaith ddefnyddio $n-parameters i drosglwyddo i floc dienw. Bydd y newidynnau sesiwn a'r swyddogaeth yn ein helpu i ddod allan o'r sefyllfa. gosodiad_cyfredol.

Cyn fersiwn 9.2, roedd yn rhaid i chi rag-ffurfweddu gofod enwau arbennig dosbarthiadau_newidyn_arferol ar gyfer newidynnau sesiwn "eu". Ar fersiynau cyfredol, gallwch chi ysgrifennu rhywbeth fel hyn:

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

Mae atebion eraill ar gael mewn ieithoedd gweithdrefnol eraill a gefnogir.

Gwybod mwy o ffyrdd? Rhannwch yn y sylwadau!

Ffynhonnell: hab.com

Ychwanegu sylw