Paminsan-minsan, kailangan ng developer ipasa ang isang set ng mga parameter o kahit isang buong seleksyon sa kahilingan "sa pasukan". Minsan may mga kakaibang solusyon sa problemang ito.
Pumunta tayo "mula sa kabaligtaran" at tingnan kung paano hindi ito gagawin, bakit, at kung paano mo ito magagawa nang mas mahusay.
Direktang "pagpasok" ng mga halaga sa katawan ng kahilingan
Karaniwan itong mukhang ganito:
query = "SELECT * FROM tbl WHERE id = " + value... o ganito:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)Tungkol sa pamamaraang ito ay sinabi, nakasulat at tama na:
Halos lagi na lang direktang landas sa SQL injection at dagdag na pagkarga sa lohika ng negosyo, na pinipilit na "idikit" ang string ng iyong query.
Ang pamamaraang ito ay maaaring bahagyang makatwiran lamang kung kinakailangan. gumamit ng partitioning sa PostgreSQL bersyon 10 at mas mababa para sa isang mas mahusay na plano. Sa mga bersyong ito, ang listahan ng mga na-scan na seksyon ay tinutukoy nang hindi isinasaalang-alang ang ipinadala na mga parameter, batay lamang sa katawan ng kahilingan.
$n argumento
Gamitin ang mga parameter ay mabuti, pinapayagan ka nitong gamitin , binabawasan ang pagkarga sa parehong lohika ng negosyo (ang query string ay nabuo at ipinadala nang isang beses lamang) at sa database server (re-parsing at pagpaplano ay hindi kinakailangan para sa bawat pagkakataon ng kahilingan).
Variable na bilang ng mga argumento
Maghihintay sa atin ang mga problema kapag gusto nating magpasa ng hindi kilalang bilang ng mga argumento nang maaga:
... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...Kung iiwan mo ang kahilingan sa form na ito, bagama't ililigtas kami nito mula sa mga potensyal na iniksyon, hahantong pa rin ito sa pangangailangang idikit / i-parse ang kahilingan para sa bawat opsyon mula sa bilang ng mga argumento. Mas mahusay na kaysa gawin ito sa bawat oras, ngunit magagawa mo nang wala ito.
Ito ay sapat na upang pumasa lamang ng isang parameter na naglalaman serialized na representasyon ng isang array:
... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'Ang pagkakaiba lang ay ang pangangailangang tahasang i-convert ang argumento sa nais na uri ng array. Ngunit hindi ito nagdudulot ng mga problema, dahil alam na natin nang maaga kung saan tayo tutugunan.
Sample na paglipat (matrix)
Karaniwan ang mga ito ay lahat ng uri ng mga pagpipilian para sa paglilipat ng mga set ng data para sa pagpasok sa database "sa isang kahilingan":
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...Bilang karagdagan sa mga problemang inilarawan sa itaas sa "re-gluing" ng kahilingan, maaari rin itong humantong sa amin wala sa memorya at pag-crash ng server. Ang dahilan ay simple - ang PG ay naglalaan ng karagdagang memorya para sa mga argumento, at ang bilang ng mga tala sa set ay limitado lamang ng application na logic ng negosyo na Wishlist. Sa partikular na mga klinikal na kaso ito ay kinakailangan upang makita "numbered" na mga argumento na higit sa $9000 - huwag gawin ito sa ganitong paraan.
Isulat muli natin ang query, nag-aaplay na "dalawang antas" na serialization:
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;
Oo, sa kaso ng "kumplikadong" mga halaga sa loob ng isang array, kailangan nilang ma-frame na may mga quote.
Malinaw na sa ganitong paraan maaari mong "palawakin" ang pagpili sa isang di-makatwirang bilang ng mga patlang.
unnest, unnest,...
Paminsan-minsan ay may mga opsyon para sa pagpasa sa halip na isang "array of arrays" ilang "arrays of columns" na aking nabanggit :
SELECT
unnest($1::text[]) k
, unnest($2::integer[]) v;Sa pamamaraang ito, kung nagkamali ka kapag bumubuo ng mga listahan ng mga halaga para sa iba't ibang mga haligi, napakadaling makuha nang buo hindi inaasahang resulta, na nakadepende rin sa bersyon ng server:
-- $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
Simula sa bersyon 9.3, ang PostgreSQL ay may ganap na mga function para sa pagtatrabaho sa uri ng json. Samakatuwid, kung ang iyong mga parameter ng input ay tinukoy sa browser, maaari kang doon mismo at bumuo json object para sa SQL query:
SELECT
key k
, value v
FROM
json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'Para sa mga nakaraang bersyon, ang parehong paraan ay maaaring gamitin para sa bawat(hstore), ngunit ang tamang "folding" sa pagtakas ng mga kumplikadong bagay sa hstore ay maaaring magdulot ng mga problema.
json_populate_recordset
Kung alam mo nang maaga na ang data mula sa "input" json array ay pupunta upang punan ang ilang talahanayan, maaari kang makatipid ng marami sa mga field na "dereferencing" at pag-cast sa mga gustong uri gamit ang json_populate_recordset function:
SELECT
*
FROM
json_populate_recordset(
NULL::pg_class
, $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
);json_to_recordset
At ang function na ito ay "palawakin" lamang ang naipasa na hanay ng mga bagay sa isang seleksyon, nang hindi umaasa sa format ng talahanayan:
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 | 2PANSAMANTALA TABLE
Ngunit kung ang dami ng data sa nailipat na sample ay napakalaki, kung gayon ang pagtapon nito sa isang serialized na parameter ay mahirap, at kung minsan ay imposible, dahil nangangailangan ito ng isang beses malaking paglalaan ng memorya. Halimbawa, kailangan mong mangolekta ng isang malaking batch ng data ng kaganapan mula sa isang panlabas na system sa loob ng mahabang panahon, at pagkatapos ay gusto mong iproseso ito nang isang beses sa gilid ng database.
Sa kasong ito, ang pinakamahusay na solusyon ay ang paggamit :
CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- ΠΏΠΎΠ²ΡΠΎΡΠΈΡΡ ΠΌΠ½ΠΎΠ³ΠΎ-ΠΌΠ½ΠΎΠ³ΠΎ ΡΠ°Π·
...
-- ΡΡΡ Π΄Π΅Π»Π°Π΅ΠΌ ΡΡΠΎ-ΡΠΎ ΠΏΠΎΠ»Π΅Π·Π½ΠΎΠ΅ ΡΠΎ Π²ΡΠ΅ΠΉ ΡΡΠΎΠΉ ΡΠ°Π±Π»ΠΈΡΠ΅ΠΉ ΡΠ΅Π»ΠΈΠΊΠΎΠΌ
Ang pamamaraan ay mabuti para sa madalang na paghahatid ng malalaking volume datos.
Mula sa punto ng view ng paglalarawan ng istraktura ng data nito, ang isang pansamantalang talahanayan ay naiiba sa isang "regular" na talahanayan sa isang tampok lamang. sa pg_class system tableat sa pg_type, pg_depend, pg_attribute, pg_attrdef, ... β at wala sa lahat.
Samakatuwid, sa mga web system na may malaking bilang ng mga panandaliang koneksyon para sa bawat isa sa kanila, ang naturang talahanayan ay bubuo ng mga bagong talaan ng system sa bawat oras, na tatanggalin kapag ang koneksyon sa database ay sarado. Sa bandang huli, ang walang kontrol na paggamit ng TEMP TABLE ay humahantong sa "pamamaga" ng mga talahanayan sa pg_catalog at pagpapabagal sa maraming operasyon na gumagamit ng mga ito.
Siyempre, ito ay maaaring labanan periodic pass VACUUM FULL ayon sa mga talahanayan ng katalogo ng system.
Mga Variable ng Session
Ipagpalagay na ang pagproseso ng data mula sa nakaraang kaso ay medyo kumplikado para sa isang query sa SQL, ngunit gusto mong gawin ito nang madalas. Ibig sabihin, gusto naming gumamit ng procedural processing in , ngunit ang paggamit ng paglipat ng data sa pamamagitan ng mga pansamantalang talahanayan ay magiging masyadong mahal.
Hindi rin namin magagamit ang mga $n-parameter upang ipasa sa isang hindi kilalang bloke. Ang mga variable ng session at ang function ay makakatulong sa amin na makaalis sa sitwasyon. kasalukuyang setting.
Bago ang bersyon 9.2, kailangan mong i-pre-configure custom_variable_classes para sa "kanilang" mga variable ng session. Sa mga kasalukuyang bersyon, maaari kang sumulat ng ganito:
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 : 3Mayroong iba pang mga solusyon na magagamit sa iba pang mga sinusuportahang procedural na wika.
Alamin ang higit pang mga paraan? Ibahagi sa mga komento!
Pinagmulan: www.habr.com