Daga lokaci zuwa lokaci, mai haɓaka yana buƙata wuce saitin sigogi ko ma zaɓi duka zuwa buƙatun "a bakin kofar". Wani lokaci akwai ban mamaki mafita ga wannan matsala.
Bari mu je "daga akasin haka" mu ga yadda ba za a yi ba, me ya sa, da kuma yadda za ku iya yin shi mafi kyau.
Kai tsaye "saka" dabi'u a jikin buƙatun
Yawanci yana kama da wani abu kamar haka:
query = "SELECT * FROM tbl WHERE id = " + value... ko kamar haka:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)Game da wannan hanya an ce, rubuta da isa:
Kusan koyaushe yana faruwa Hanyar kai tsaye zuwa allurar SQL da ƙarin nauyi akan dabaru na kasuwanci, wanda aka tilasta masa “manne” igiyar tambayar ku.
Wannan hanya za a iya ba da hujja kawai idan ya cancanta. amfani da partitioning a cikin nau'ikan PostgreSQL 10 da ƙasa don ingantaccen tsari. A cikin waɗannan juzu'ai, an ƙayyade jerin sassan da aka bincika ba tare da la'akari da sigogin da aka wuce ba, kawai bisa ga ƙungiyar buƙatar.
$n jayayya
Amfani sigogi yana da kyau, yana ba ku damar amfani , Rage nauyi duka a kan dabarun kasuwanci (an ƙirƙiri kirtani na tambaya kuma ana watsa shi sau ɗaya kawai) kuma akan uwar garken bayanan (ba a buƙatar sake yin nazari da tsarawa ga kowane misali na buƙatun).
Adadin mahawara mai canzawa
Matsaloli za su jira mu lokacin da muke son ƙaddamar da adadin mahawara a gaba:
... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...Idan kun bar buƙatun a cikin wannan fom, to kodayake zai cece mu daga yuwuwar allura, har yanzu zai haifar da buƙatar manne / rarraba buƙatun. ga kowane zaɓi daga adadin muhawara. Ya fi kyau fiye da yin shi kowane lokaci, amma kuna iya yin ba tare da shi ba.
Ya isa ya wuce siga ɗaya kawai wanda ya ƙunshi serialized wakilci na tsararru:
... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'Bambancin kawai shine buƙatar canza hujja a sarari zuwa nau'in tsararru da ake so. Amma wannan ba ya haifar da matsala, tun da mun riga mun san inda muke magana.
Canja wurin samfurin (matrix)
Yawancin lokaci waɗannan nau'ikan zaɓuɓɓuka ne don canja wurin saitin bayanai don sakawa cikin bayanan "a cikin buƙatu ɗaya":
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...Baya ga matsalolin da aka bayyana a sama tare da "sake-gluing" na buƙatar, wannan kuma zai iya kai mu ga daga ƙwaƙwalwar ajiya da kuma hadarin uwar garke. Dalilin yana da sauƙi - PG yana tanadi ƙarin ƙwaƙwalwar ajiya don muhawara, kuma adadin rikodin a cikin saitin yana iyakance ne kawai ta hanyar lissafin aikace-aikacen dabaru na kasuwanci. A cikin lokuta na musamman na asibiti ya zama dole a gani Hujjar "lambobi" fiye da $9000 - kar a yi haka.
Bari mu sake rubuta tambayar, muna amfani da riga Serialization na "matakin biyu".:
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;
Haka ne, a cikin yanayin "rikitattun" dabi'u a cikin tsararru, suna buƙatar a tsara su da ƙididdiga.
A bayyane yake cewa ta wannan hanyar za ku iya "fadada" zaɓi tare da adadin filayen sabani.
tashin hankali, tashin hankali,…
Daga lokaci zuwa lokaci akwai zaɓuɓɓuka don wucewa maimakon "array of arrays" da yawa "tsararrun ginshiƙai" waɗanda na ambata. :
SELECT
unnest($1::text[]) k
, unnest($2::integer[]) v;Tare da wannan hanyar, idan kun yi kuskure lokacin ƙirƙirar lissafin ƙima don ginshiƙai daban-daban, yana da sauƙin samun gaba ɗaya. sakamakon bazata, wanda kuma ya dogara da sigar uwar garken:
-- $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
Fara daga sigar 9.3, PostgreSQL yana da cikakkun ayyuka don aiki tare da nau'in json. Don haka, idan an ayyana sigogin shigarwar ku a cikin burauzar, za ku iya nan da nan kuma ku yi tsari json abu don tambayar SQL:
SELECT
key k
, value v
FROM
json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'Don sigogin da suka gabata, ana iya amfani da wannan hanyar kowane (hstore), amma daidai "naɗewa" tare da guje wa hadaddun abubuwa a hstore na iya haifar da matsala.
json_populate_recordset
Idan kun san a gaba cewa bayanan daga "shigarwar" json array za su je don cika wasu tebur, za ku iya adana da yawa a cikin filayen "dereferencing" da jefa zuwa nau'ikan da ake so ta amfani da aikin 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
Kuma wannan aikin zai kawai "fadada" abubuwan da aka wuce zuwa cikin zaɓi, ba tare da dogara ga tsarin tebur ba:
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 | 2TASKAR GINDI
Amma idan adadin bayanan da ke cikin samfurin da aka watsa yana da girma sosai, to jefa shi cikin siga guda ɗaya yana da wahala, kuma wani lokacin ba zai yiwu ba, tunda yana buƙatar lokaci ɗaya. babban adadin ƙwaƙwalwar ajiya. Misali, kuna buƙatar tattara manyan bayanan abubuwan da suka faru daga tsarin waje na dogon lokaci mai tsawo, sannan kuna son aiwatar da shi sau ɗaya a gefen bayanan.
A wannan yanayin, mafi kyawun bayani zai kasance don amfani :
CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком
Hanyar tana da kyau don watsawa da yawa na manyan kundin bayanai.
Daga ra'ayi na kwatanta tsarin bayanansa, tebur na wucin gadi ya bambanta da tebur "na yau da kullun" a cikin sifa ɗaya kawai. a pg_class tsarin tebur, kuma cikin pg_type, pg_depend, pg_attribute, pg_attrdef, ... - kuma babu komai.
Sabili da haka, a cikin tsarin yanar gizo tare da adadi mai yawa na haɗin kai na ɗan gajeren lokaci ga kowannensu, irin wannan tebur zai haifar da sababbin rikodin tsarin kowane lokaci, wanda aka share lokacin da aka rufe haɗin haɗin yanar gizon. Daga karshe, rashin sarrafa amfani da TEMP TABLE yana haifar da "kumburi" na tebur a pg_catalog da kuma rage yawan ayyukan da ke amfani da su.
Hakika, ana iya magance wannan tare da wucewa lokaci-lokaci VACUUM FULL bisa ga tebur kasida na tsarin.
Canje-canjen Zama
A ce sarrafa bayanan daga shari'ar da ta gabata tana da wahala sosai don tambayar SQL guda ɗaya, amma kuna son yin ta sau da yawa. Wato, muna so mu yi amfani da tsarin aiki a ciki , amma yin amfani da canja wurin bayanai ta hanyar tebur na wucin gadi zai yi tsada sosai.
Ba za mu iya amfani da $n-parameters don wucewa zuwa wani shingen da ba a san sunansa ba. Masu canjin zaman da aikin zasu taimake mu mu fita daga halin da ake ciki. halin yanzu_setting.
Kafin sigar 9.2, dole ne ku riga kun saita azuzuwan_mai canzawa ga masu canjin zaman "su". A kan sigar yanzu, zaku iya rubuta wani abu kamar haka:
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 : 3Akwai wasu hanyoyin warwarewa a cikin wasu harsunan tsari masu goyan baya.
Ka san ƙarin hanyoyi? Raba a cikin sharhin!
source: www.habr.com