Статьи

Тайная магия с оператором SQL: 2003 MERGE

Ноябрь 10, 2018
Время от времени мы чувствуем себя неловко из-за необходимости отличать INSERT от UPDATE по любой из следующих причин:

  • Мы должны сделать как минимум два заявления
  • Мы должны думать о производительности
  • Мы должны думать об условиях гонки
  • Мы должны выбирать между [ОБНОВЛЕНИЕ; ЕСЛИ UPDATE_COUNT = 0, ТО ВСТАВИТЬ] и [ВСТАВИТЬ; ЕСЛИ ИСКЛЮЧЕНИЕ, ЧЕМ ОБНОВЛЕНИЕ
  • Мы должны делать эти заявления один раз для обновленной / вставленной записи

В общем, это большой источник ошибок и разочарований. В то же время с оператором SQL MERGE могло бы быть так просто!

Типичная ситуация для MERGE

Среди многих других вариантов использования оператор MERGE может пригодиться при обработке отношений «многие ко многим». Допустим, у нас есть эта схема:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE TABLE documents (
 
  id NUMBER(7) NOT NULL,
 
  CONSTRAINT docu_id PRIMARY KEY (id)
 
);
 
 
 
CREATE TABLE persons (
 
  id NUMBER(7) NOT NULL,
 
  CONSTRAINT pers_id PRIMARY KEY (id)
 
);
 
 
 
CREATE TABLE document_person (
 
  docu_id NUMBER(7) NOT NULL,
 
  pers_id NUMBER(7) NOT NULL,
 
  flag NUMBER(1) NULL,
 
 
 
  CONSTRAINT docu_pers_pk PRIMARY KEY (docu_id, pers_id),
 
  CONSTRAINT docu_pers_fk_docu
 
    FOREIGN KEY (docu_id) REFERENCES documents(id),
 
  CONSTRAINT docu_pers_fk_pers
 
    FOREIGN KEY (pers_id) REFERENCES persons(id)
 
);

Приведенные выше таблицы используются для моделирования того, какой человек прочитал (flag = 1) / удалил (flag = 2) какой документ. Для простоты сущность «document_person» обычно НАРУЖНО присоединяется к «документам», так что наличие или отсутствие записи «document-person» может иметь одинаковую семантику: «flag IS NULL» означает, что документ не прочитан.
Теперь, когда вы хотите пометить документ как прочитанный, вы должны решить, вставить ли вам новый «document_person» или ОБНОВИТЬ существующий. То же самое с удалением. То же самое с маркировкой всех документов как прочитанных или удалением всех документов.

Вместо этого используйте MERGE

Вы можете сделать все это в одном утверждении! Допустим, вы хотите ВСТАВИТЬ / ОБНОВИТЬ одну запись, чтобы пометить один документ как прочитанный для человека:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- The target table
 
MERGE INTO document_person dst
 
 
 
-- The data source. In this case, just a dummy record
 
USING (
 
  SELECT :docu_id as docu_id,
 
         :pers_id as pers_id,
 
         :flag    as flag
 
  FROM DUAL
 
) src
 
 
 
-- The merge condition (if true, then update, else insert)
 
ON (dst.docu_id = src.docu_id AND dst.pers_id = src.pers_id)
 
 
 
-- The update action
 
WHEN MATCHED THEN UPDATE SET
 
  dst.flag = src.flag
 
 
 
-- The insert action
 
WHEN NOT MATCHED THEN INSERT (
 
  dst.docu_id,
 
  dst.pers_id,
 
  dst.flag
 
)
 
VALUES (
 
  src.docu_id,
 
  src.pers_id,
 
  src.flag
 
)

Это выглядит довольно похоже, но невероятно более многословно, чем оператор INSERT .. ON DUPLICATE KEY UPDATE в MySQL, который немного более лаконичен.

Принимая это до крайности

Но вы можете пойти дальше! Как я уже говорил ранее, вы также можете пометить ВСЕ документы как прочитанные для данного человека. Нет проблем с MERGE. Следующий оператор делает то же самое, что и предыдущий, если вы укажете: document_id. Если вы оставите его пустым, все документы будут помечены как: flag:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
MERGE INTO document_person dst
 
 
 
-- The data source is now all "documents" (or just :docu_id) left outer
 
-- joined with the "document_person" mapping
 
USING (
 
  SELECT d.id     as docu_id,
 
         :pers_id as pers_id,
 
         :flag    as flag
 
  FROM documents d
 
  LEFT OUTER JOIN document_person d_p
 
  ON d.id = d_p.docu_id AND d_p.pers_id = :pers_id
 
  -- If :docu_id is set, select only that document
 
  WHERE (:docu_id IS NOT NULL AND d.id = :docu_id)
 
  -- Otherwise, select all documents
 
     OR (:docu_id IS NULL)
 
) src
 
 
 
-- If the mapping already exists, update. Else, insert
 
ON (dst.docu_id = src.docu_id AND dst.pers_id = src.pers_id)
 
 
 
-- The rest stays the same
 
WHEN MATCHED THEN UPDATE SET
 
  dst.flag = src.flag
 
WHEN NOT MATCHED THEN INSERT (
 
  dst.docu_id,
 
  dst.pers_id,
 
  dst.flag
 
)
 
VALUES (
 
  src.docu_id,
 
  src.pers_id,
 
  src.flag
 
)

Поддержка MERGE в JOOQ

MERGE также полностью поддерживается в jOOQ. Смотрите руководство для более подробной информации (прокрутите вниз):
http://www.jooq.org/manual/JOOQ/Query/
Счастливого слияния! :-)

Ссылка: тайная магия с оператором SQL: 2003 MERGE от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и AND JOOQ .

Статьи по Теме :

36
Share: