Qee lub sij hawm tus tsim tawm xav tau hla ib txheej ntawm cov kev txwv lossis txawm tias tag nrho cov kev xaiv rau qhov kev thov "ntawm qhov nkag". Qee lub sij hawm koj tuaj hla kev daws teeb meem txawv heev rau qhov teeb meem no.
Cia peb rov qab mus saib dab tsi tsis ua, yog vim li cas, thiab peb yuav ua li cas zoo dua.
Kev nkag ncaj qha ntawm qhov tseem ceeb rau hauv lub cev thov
Nws feem ntau zoo li no:
query = "SELECT * FROM tbl WHERE id = " + value...los yog zoo li no:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)Txoj kev no tau hais, sau thiab ntau:
Yuav luag ib txwm yog qhov no ncaj qha mus rau SQL txhaj thiab tsis tsim nyog thauj khoom ntawm kev lag luam logic, uas yog yuam kom "kos" koj cov lus nug kab.
Txoj hauv kev no tuaj yeem ua tiav qee qhov tsuas yog tias tsim nyog siv partitioning hauv PostgreSQL versions 10 thiab hauv qab no kom tau txais cov phiaj xwm ua tau zoo dua. Hauv cov qauv no, cov npe ntawm cov ntawv txheeb xyuas tau txiav txim siab yam tsis xav txog qhov kis tsis tau, tsuas yog nyob ntawm lub hauv paus ntawm lub cev thov.
$n-kev sib cav
Siv parameters yog qhov zoo, nws tso cai rau koj siv , txo cov load ob qho tib si ntawm kev lag luam logic (cov lus nug txoj hlua yog tsim thiab xa ib zaug xwb) thiab ntawm cov neeg rau zaub mov database (re-parsing thiab teem sijhawm rau txhua qhov lus nug tsis tas yuav tsum tau).
Tus naj npawb ntawm cov lus sib txawv
Cov teeb meem yuav tos peb thaum peb xav kom dhau qhov tsis paub txog cov lus sib cav:
... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...Yog tias peb tawm hauv daim ntawv thov no, txawm hais tias nws yuav tiv thaiv peb ntawm kev txhaj tshuaj, nws tseem yuav ua rau xav tau kev sib koom ua ke / txheeb xyuas qhov kev thov. rau txhua qhov kev xaiv nyob ntawm seb muaj pes tsawg qhov kev sib cav. Nws zoo dua ua nws txhua zaus, tab sis koj tuaj yeem ua yam tsis muaj nws.
Nws yog txaus kom dhau ib qho parameter uas muaj serialized array sawv cev:
... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'Qhov txawv tsuas yog qhov yuav tsum tau ua kom meej meej hloov qhov kev sib cav mus rau qhov xav tau hom array. Tab sis qhov no tsis ua teeb meem, txij li peb twb paub ua ntej peb yuav mus qhov twg.
Hloov cov qauv (matrix)
Feem ntau cov no yog txhua yam kev xaiv rau kev hloov cov ntaub ntawv teev rau kev ntxig rau hauv cov ntaub ntawv "hauv ib qho kev thov":
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...Ntxiv rau cov teeb meem tau piav qhia saum toj no nrog "rov-gluing" qhov kev thov, qhov no tuaj yeem coj peb mus tawm ntawm lub cim xeeb thiab server crash. Qhov laj thawj yog qhov yooj yim - PG khaws cia lub cim xeeb ntxiv rau kev sib cav, thiab cov ntaub ntawv teev tseg tsuas yog txwv los ntawm daim ntawv thov kev xav tau ntawm kev lag luam logic. Hauv qhov tshwj xeeb hauv kev kho mob kuv yuav tsum tau pom "tus lej" sib cav ntau dua $ 9000 - tsis txhob ua li no.
Wb rov sau dua qhov kev thov uas twb siv lawm "ob theem" ββ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;
Yog, nyob rau hauv cov ntaub ntawv ntawm "complex" qhov tseem ceeb nyob rau hauv ib tug array, lawv yuav tsum tau surrounded los ntawm quotes.
Nws yog qhov tseeb tias nyob rau hauv txoj kev no koj tuaj yeem "nthuav" kev xaiv nrog tus lej ntawm cov teb.
tsis paub, tsis paub,β¦
Txij lub sij hawm dhau los muaj cov kev xaiv rau dhau los ntawm "array ntawm arrays" ob peb "arrays ntawm kab" uas kuv tau hais. :
SELECT
unnest($1::text[]) k
, unnest($2::integer[]) v;Nrog rau txoj kev no, yog tias koj ua yuam kev thaum tsim cov npe ntawm qhov tseem ceeb rau cov kab sib txawv, nws yooj yim heev kom tau txais tsis xav tau, uas kuj nyob ntawm tus neeg rau zaub mov version:
-- $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
Txij li thaum version 9.3, PostgreSQL tau muaj kev ua haujlwm puv ntoob rau kev ua haujlwm nrog hom json. Yog li ntawd, yog hais tias lub ntsiab lus ntawm input parameters tshwm sim nyob rau hauv koj tus browser, koj muaj peev xwm tsim nws nyob rau ntawd json object rau SQL query:
SELECT
key k
, value v
FROM
json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'Rau yav dhau los versions, tib txoj kev yuav siv tau rau txhua (hstore), tab sis yog "convolution" nrog escaping complex khoom nyob rau hauv hstore yuav ua rau muaj teeb meem.
json_populate_recordset
Yog tias koj paub ua ntej tias cov ntaub ntawv los ntawm "input" json array yuav raug siv los sau qee lub rooj, koj tuaj yeem txuag tau ntau hauv "dereferencing" teb thiab muab lawv rau cov hom xav tau los ntawm kev siv json_populate_recordset muaj nuj nqi:
SELECT
*
FROM
json_populate_recordset(
NULL::pg_class
, $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
);json_to_recordset
Thiab qhov kev ua haujlwm no tsuas yog "nthuav" cov khoom dhau los rau hauv kev xaiv, yam tsis muaj kev vam khom rau lub rooj hom:
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 | 2TABSIS TABSIS
Tab sis yog hais tias tus nqi ntawm cov ntaub ntawv nyob rau hauv tus qauv pauv yog loj heev, ces muab pov rau hauv ib tug serialized parameter yog ib qho nyuaj thiab tej zaum yuav tsis yooj yim sua, vim hais tias nws yuav tsum tau ib zaug ib zaug. faib cov cim xeeb loj. Piv txwv li, koj yuav tsum tau sau ib pob loj ntawm cov ntaub ntawv ntawm cov xwm txheej los ntawm ib qho chaw sab nraud rau lub sijhawm ntev, ntev, thiab tom qab ntawd koj xav ua nws ib zaug ntawm sab database.
Hauv qhov no, qhov kev daws teeb meem zoo tshaj plaws yuav yog siv :
CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- ΠΏΠΎΠ²ΡΠΎΡΠΈΡΡ ΠΌΠ½ΠΎΠ³ΠΎ-ΠΌΠ½ΠΎΠ³ΠΎ ΡΠ°Π·
...
-- ΡΡΡ Π΄Π΅Π»Π°Π΅ΠΌ ΡΡΠΎ-ΡΠΎ ΠΏΠΎΠ»Π΅Π·Π½ΠΎΠ΅ ΡΠΎ Π²ΡΠ΅ΠΉ ΡΡΠΎΠΉ ΡΠ°Π±Π»ΠΈΡΠ΅ΠΉ ΡΠ΅Π»ΠΈΠΊΠΎΠΌ
Txoj kev yog qhov zoo rau qee zaus hloov pauv ntawm cov ntim loj cov ntaub ntawv.
Los ntawm qhov kev xav ntawm kev piav qhia cov qauv ntawm nws cov ntaub ntawv, lub rooj ib ntus txawv ntawm ib qho "tsis tu ncua" hauv ib txoj kev. hauv pg_class system rooj, thiab hauv pg_type, pg_depend, pg_attribute, pg_attrdef, ... - tsis muaj dab tsi hlo li.
Yog li ntawd, nyob rau hauv lub web systems nrog ib tug loj tus naj npawb ntawm luv luv-nyob sib txuas rau txhua tus ntawm lawv, xws li ib lub rooj yuav tsim tshiab system cov ntaub ntawv txhua lub sij hawm, uas yog deleted thaum qhov kev twb kev txuas mus rau lub database kaw. Thaum kawg, Kev tswj tsis tau siv TEMP TABLE ua rau "o" ntawm cov ntxhuav hauv pg_catalog thiab qeeb ntau txoj haujlwm uas siv lawv.
Tau kawg, qhov no tuaj yeem cuam tshuam nrog kev siv periodic passage VACUUM FULL raws li qhov system catalog tables.
Session Variables
Cia peb xav tias kev ua cov ntaub ntawv los ntawm rooj plaub dhau los yog qhov nyuaj heev rau ib qho lus nug SQL, tab sis koj xav ua nws ntau zaus. Ntawd yog, peb xav siv cov txheej txheem ua hauv , tab sis kev siv cov ntaub ntawv hloov mus los ntawm cov ntxhuav ib ntus yuav kim heev.
Peb kuj tseem yuav tsis tuaj yeem siv $n-tsis tau dhau mus rau qhov thaiv tsis qhia npe. Kev sib hloov pauv thiab cov haujlwm yuav pab peb tawm ntawm qhov xwm txheej no tam sim no_setting.
Ua ntej version 9.2 nws yuav tsum tau ua ntej teeb tsa custom_variable_classes rau "koj" kev sib hloov pauv hloov pauv. Ntawm cov versions tam sim no koj tuaj yeem sau qee yam zoo li no:
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 : 3Lwm cov kev daws teeb meem tuaj yeem pom nyob rau hauv lwm yam lus txhawb nqa txheej txheem.
Koj puas paub lwm txoj kev? Qhia tawm hauv cov lus!
Tau qhov twg los: www.hab.com