Үе үе хөгжүүлэгч хэрэгтэй хүсэлтэд параметрийн багц эсвэл бүр бүхэл бүтэн сонголтыг дамжуулах "Үүдэнд". Заримдаа энэ асуудлыг шийдэх маш хачирхалтай шийдэл байдаг.
"Эсрэг талаасаа" явж, яаж үүнийг хийхгүй байх, яагаад, яаж үүнийг илүү сайн хийж болохыг харцгаая.
Хүсэлтийн биед утгуудыг шууд "оруулах"
Энэ нь ихэвчлэн иймэрхүү харагддаг:
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 төрөлтэй ажиллах бүрэн хэмжээний функцуудтай. Тиймээс, хэрэв таны оролтын параметрүүд хөтөч дээр тодорхойлогдвол та яг тэндээс хэлбэржүүлж болно SQL асуулгад зориулсан json объект:
SELECT
key k
, value v
FROM
json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'Өмнөх хувилбаруудын хувьд ижил аргыг ашиглаж болно тус бүр (hstore), гэхдээ hstore дахь нарийн төвөгтэй объектуудаас зугтаж зөв "нугалах" нь асуудал үүсгэж болзошгүй.
json_populate_recordset
Хэрэв та "оролтын" json массивын өгөгдөл нь зарим хүснэгтийг бөглөх болно гэдгийг урьдчилан мэдэж байгаа бол json_populate_recordset функцийг ашиглан "dereferencing" талбарт их хэмжээний хэмнэлт хийж, хүссэн төрлүүдэд шилжүүлэх боломжтой.
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-параметрүүдийг нэргүй блок руу дамжуулах боломжгүй. Сеансын хувьсагч ба функц нь нөхцөл байдлаас гарахад тусална. одоогийн_тохиргоо.
9.2 хувилбараас өмнө та урьдчилан тохируулах шаардлагатай байсан өөрчлөн_хувьсагчийн_анги "тэдний" сесс хувьсагчийн хувьд. Одоогийн хувилбарууд дээр та дараах зүйлийг бичиж болно.
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