tag: 数据库应用开发  merge ASA HANA PostgreSQL IQ Sybase

前言

merge的语法的出现，其实有一些年头了，至少10多年以前，ASA (Sybase SQLAnywhere)就开始支持了，而ASE数据库反而一直不支持，这个是蛮奇怪，主要缘于ASA是Sybase收购而来的产品，属于完全不同的产品线。后来的Sybase IQ (即现在的SAP IQ)也是在ASA的基础上二次创新，是世界上第一批列存数据库，再到后来的SAP并购Sybase，再在IQ的基础上，再拔一个台阶，结合HANA in Memory，打造出了耀眼的列存数据库。ASA以有它的嵌入式数据库Ultralite数据库，在2008年以前，是传奇般的存在，那个时候移动网络带宽不是完全普及，无线网络在很多商业场景也不是完全覆盖，离线和在线相结合的特性让ASA的架构非常有优势，它可以在离线的时候执行本地操作，等在线的时候，再同步到后端的ASE数据库，完全业务集中核算，特别适用于一些公司的分支机构、联锁商店的业务。

扯远了，总之一句话，世界上很少有直接从0到1的跳跃式创新，大多是站在前人前列的肩膀上，进一步继承和发展式创新融合。

MERGE出现之前

Merge顾名思义，就是进行合并列表，合并成一个。大多用于可能出现的数据重复，但想用一条简单的SQL语句来搞定。PostgreSQL数据库在Merge支持出现之前(即15.0以前)，数据插入操作，如果出现重复类的冲突，怎么解决？

我们看看PostgreSQL中的insert的语法：(参考：pg-doc-14.5/sql-insert.html)

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

利用其中的[ ON CONFLICT [ conflict_target ] conflict_action ] 子句，可以进行重复数据的处理：

示例，准备一张表test及简单的几条数据：

postgres=# create table test(id int primary key, col2 text);
CREATE TABLE
postgres=# insert into test values(1, 'wang'), (2, 'zhou'), (3, 'wu'), (4, 'zheng');
INSERT 0 4

再尝试插入一些可能重复的数据:

postgres=# insert into test values(1, 'ttt') ON CONFLICT(id)  do update set col2= EXCLUDED.col2 returning *;
 id | col2
----+------
  1 | ttt
(1 row)

postgres=# insert into test values(1, 'ttt2') ON CONFLICT(id) do nothing returning *;
 id | col2
----+------
(0 rows)

INSERT 0 0

我们看到，如果采取do nothing, 那就是忽略主键重复的数据，如果用的是do update子句，就会使用新的值 EXCLUDED中的值去取代表中的原有的值。

postgres=# select * from test;
 id | col2
----+-------
  2 | zhou
  3 | wu
  4 | zheng
  1 | ttt
(4 rows)

MERGE到来时

PostgreSQL 15

仍然以PostgreSQL15.X为例，它的语法是：(see:  pg-doc-15.2/sql-merge.html)

MERGE — conditionally insert, update, or delete rows of a table

Compatibility 兼容性

This command conforms to the SQL standard.

The WITH clause and DO NOTHING action are extensions to the SQL standard.  with子句和do nothing子句相应扩展了SQL标准，也就是说它加了点新料。

Synopsis
[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]

where data_source is:

{ [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]

and when_clause is:

{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } }

and merge_insert is:

INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

and merge_update is:

UPDATE SET { column_name = { expression | DEFAULT } |
             ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

and merge_delete is:

DELETE

一样的，我们准备一下表和几行数据:

mydb=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
mydb=# create table test(id int primary key, col2 text);
CREATE TABLE
mydb=# insert into test values(1, 'wang'), (2, 'zhou'), (3, 'wu'), (4, 'zheng');
INSERT 0 4

接着，我们插入几条可能重复的数据试一下：

mydb=# with t(id, col2) as (select 1, 'ttt') merge into test using t on t.id=test.id  when matched then do nothing;
MERGE 1
mydb=# select * from test;
 id | col2
----+-------
  1 | wang
  2 | zhou
  3 | wu
  4 | zheng
(4 rows)

这里用到了WITH子句，上边用的是DO NOTHING. 结果保持不变. 但是不支持returning子句（可以思考一下，为什么？)

再看看do update/insert:

mydb=# with t(id, col2) as (select n, 'test'||n from generate_series(1,5) as n) merge into test using t on t.id=test.id  when matched then update set col2 = t.col2 when not matched then insert (id, col2) values (t.id, t.col2);
MERGE 5
mydb=# select * from test;
 id | col2
----+-------
  1 | test1
  2 | test2
  3 | test3
  4 | test4
  5 | test5
(5 rows)

注意，MERGE还有一个很强大的功能，还可以去掉符合条件的数据行：

mydb=# with t(id, col2) as (select n, 'test'||n from generate_series(1,8) as n) merge into test using t on t.id=test.id  when matched then DELETE when not matched then insert (id, col2) values (t.id, t.col2);
MERGE 8
mydb=# select * from test;
 id | col2
----+-------
  8 | test8
  6 | test6
  7 | test7
(3 rows)

所以，相对以前的功能，MERGE是大大的前进了一步了。上边的演示只是展示用了临时数据生成。如果你有源表，那么就可以直接用源表和新表之间进行关联操作了。

ASA (SQLAnywhere 17)

它的Merge语法：

MERGE statement
Merges tables, views, and procedure results into a table or view.

Syntax 
MERGE
INTO target-object [ into-column-list ]
USING [ WITH AUTO NAME ] source-object
   ON merge-search-condition
merge-operation [...]
 [ OPTION ( query-hint, ... ) ]target-object : 
[ userid.]target-table-name [ [ AS ] target-correlation-name ]  
| [ userid.]target-view-name [ [ AS ] target-correlation-name ] 
| ( select-statement ) [ AS ] target-correlation-name source-object : 
[ userid.]source-table-name [ [ AS ] source-correlation-name ] [ WITH ( table-hints ) ]
| [ userid.]source-view-name [ [ AS ] source-correlation-name ] 
| [ userid.]source-mat-view-name [ [ AS ] source-correlation-name ] 
| ( select-statement ) [ AS ] source-correlation-name [  using-column-list ]
| procedureprocedure :
[ owner.]procedure-name ( procedure-syntax )
   [ WITH ( column-name data-type, ... ) ]
   [ [ AS ] source-correlation-name ]merge-search-condition :
search-condition
| PRIMARY KEYmerge-operation :
WHEN MATCHED [ AND search-condition ] THEN match-action
| WHEN NOT MATCHED [ AND search-condition ] THEN not-match-actionmatch-action :
DELETE
| RAISERROR [ error-number ]
| SKIP 
| UPDATE SET set-item, ...  
| UPDATE [ DEFAULTS { ON | OFF } ]not-match-action :
INSERT 
| INSERT [ insert-column-list ] VALUES ( value, ... ) 
| RAISERROR [ error-number ]
| SKIPset-item :
[target-correlation-name.]column-name = { expression | DEFAULT }
| [ owner-name.]target-table-name.column-name = { expression | DEFAULT }insert-column-list : 
( column-name, ... )query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| option-name = option-valueinto-column-list :
( column-name, ... )using-column-list :
( column-name, ... )error-number : positive integer or variable greater than 17000option-name : identifieroption-value :
hostvar (indicator allowed)
| string
| identifier
| numbertable-hints : see the documentation for the FROM clausesearch-condition : see the documentation for search conditionsset-clause-list : see the documentation for the SET statement

与PostgreSQL大同小异。

实例中下：

先建一个简单的ASA数据库demo.db，启动它，并建示例表：

D:\SQLAnywhere\Bin64>dbinit.exe -dba dba,test123 D:\SQLAnywhere\data\demo.db
SQL Anywhere Initialization Utility Version 17.0.10.6315
CHAR collation sequence:  1252LATIN1(CaseSensitivity=Ignore)
CHAR character set encoding:  windows-1252
NCHAR collation sequence:  UCA(CaseSensitivity=Ignore;AccentSensitivity=Ignore;PunctuationSensitivity=Primary)
NCHAR character set encoding:  UTF-8
Database is not encrypted
Creating system tables
Creating system views
Setting option values
Database "D:\SQLAnywhere\data\demo.db" created successfully

D:\SQLAnywhere\Bin64>dbsrv17.exe D:\SQLAnywhere\data\demo.db

准备表和数据：

create table test(id int primary key, col2 varchar(32));

insert into test values(1, 'wang'), (2, 'zhou'), (3, 'wu'), (4, 'zheng');

merge操作：

INSERT:

MERGE INTO test ( ID, col2 ) 
   USING WITH AUTO NAME ( 
      SELECT 5 AS ID, 
             'ttt' AS col2 ) AS DT
   ON PRIMARY KEY
   WHEN NOT MATCHED THEN INSERT;

select * from test;
|id |col2 |
|---|-----|
|1  |wang |
|2  |zhou |
|3  |wu   |
|4  |zheng|
|5  |ttt  |

注意到上边，用到了ON PRIMARY KEY的语法 。

UPDATE/DELETE:

create table t(id int, col2 varchar(32));
insert into t values(1, 'test1'), (3, 'abcde');

MERGE INTO test
   USING t
   ON t.id = test.id
   WHEN MATCHED THEN UPDATE SET test.col2 = t.col2;

MERGE INTO test
   USING t
   ON t.id = test.id
   WHEN MATCHED THEN DELETE;   

HANA

Merges data into an existing column store table.

Syntax
MERGE INTO <target_table> [ <partition_restriction> ] [ [ AS ] <alias> ]
 USING <table_reference>
 ON <search_condition> <merge_operation_specification>
Specifies the update to perform when values match.
<when_matched_clause> ::=
 WHEN MATCHED [ AND <search_condition> ] THEN <when_matched_specification>
<when_matched_specification> ::=
<update_specification>
 | <delete_specification>
<update_specification> ::= UPDATE SET <set_clause_list>
<delete_specification> ::= DELETE
when_not_matched_clause
Specifies the insert to perform when values do not match.
<when_not_matched_clause> ::=
 WHEN NOT MATCHED [ AND <search_condition> ] THEN 
<when_not_matched_specification>
<when_not_matched_specification> ::=
 INSERT [ ( <insert_column_list> ) ] VALUES <insert_value_list>)
<insert_value_list> ::=
 ( <insert_value_element> [ {, <insert_value_element> } … ] )
<insert_value_element> ::=
<value_expression>
 | <contextually_typed_value_specification>

示例：

CREATE TABLE test(id int PRIMARY KEY, col2 varchar(32));

INSERT INTO test values(1, 'wang');
INSERT INTO test values(2, 'zhou');
INSERT INTO test values(3, 'wu');
INSERT INTO test values(4, 'zheng');

CREATE TABLE t(id int, col2 varchar(32));
insert INTO t values(1, 't1');
insert INTO t values(7, 't1');

MERGE:

MERGE INTO test
   USING t
   ON t.id = test.id
   WHEN MATCHED THEN UPDATE SET test.col2 = t.col2
   WHEN NOT MATCHED THEN INSERT VALUES(t.id, t.col2);

select * from test;
|ID |COL2 |
|---|-----|
|2  |zhou |
|3  |wu   |
|4  |zheng|
|1  |t1   |
|7  |t1   |

小结

MERGE这种操作可以简化重复数据的处理。遵从越简单越好的原则。没有它的支持，少不了要多写一些代码实现相同的功能。