mysql与lightdb中的insert on duplicate/replace

最近看pg中insert的实现源码，看到on conflict的excluded优点疑惑，顺带总结下mysql和pg中已存在更新、不存在插入的差异（注：oracle是merge into实现）。

在mysql中的insert on duplicate和lightdb的on conflict是等价的。

逻辑都是基于唯一约束进行已存在则更新，否则插入。

insert record

IF

　　exist duplicate record

THEN

　　do something on duplicated rows

ELSE

　　do nothing

END IF

mysql示例

create table t1
(
    id bigint primary key auto_increment,
    a  integer unique,
    b  integer default 999
);
INSERT INTO test_insert_on_dup_update(id, a)
VALUES (1, 1);
INSERT INTO test_insert_on_dup_update(id, a)
VALUES (5, 5);
INSERT INTO test_insert_on_dup_update(id, a)
VALUES (10, 10);

insert into t1(a,b) values(1,199) on duplicate update b = 1;

　　如果插入的记录与a跟b上的索引值都发生了冲突，且发生冲突的记录有多条会怎么样呢？

　　insert into t1(id, a) values(1,5) on duplicate update b = 1;

　　因为a=1跟b=5都存在，这个时候有两行记录与即将插入的记录有冲突。按照前面介绍的规则来看，貌似id=1跟a=5这两条记录的b都会被更新成1。但事实是只有一条有冲突的记录会应用on duplicate后面的子句。而这条被命中记录就是在所有满足条件的记录中，其id值在聚集索引叶节点的链表中最靠前的那条记录。在本例中也就是id=1的那条记录。该sql的实际效果等价于：

　　update t1 set b=1 where id=1 or a=5 limit 1;

lightdb实例

create table meta_data (
        id serial,
        user_id varchar(128) DEFAULT NULL,
        file_name varchar(1024) DEFAULT NULL,
        file_path varchar(1024) DEFAULT NULL,
        update_time TIMESTAMP DEFAULT NULL,
        UNIQUE (user_id,file_name)
    );


postgres=# d meta_data
                                      Table "public.meta_data"
   Column    |            Type             |                       Modifiers                        
-------------+-----------------------------+--------------------------------------------------------
 id          | integer                     | not null default nextval('meta_data_id_seq'::regclass)
 user_id     | character varying(128)      | default NULL::character varying
 file_name   | character varying(1024)     | default NULL::character varying
 file_path   | character varying(1024)     | default NULL::character varying
 update_time | timestamp without time zone | 
Indexes:
    "meta_data_user_id_file_name_key" UNIQUE CONSTRAINT, btree (user_id, file_name)

INSERT INTO meta_data (
               user_id,
               file_name,
               file_path,
               UPDATE_TIME )
     VALUES ( 'user_id01',
              'file_name01',
              '/usr/local/file_name01',
              now())
ON CONFLICT (user_id, file_name) DO UPDATE SET file_path = EXCLUDED.file_path, UPDATE_TIME = EXCLUDED.UPDATE_TIME;    

INSERT INTO meta_data (
               user_id,
               file_name,
               file_path,
               UPDATE_TIME )
     VALUES ( 'user_id02',
              'file_name02',
              '/usr/local/file_name02',
              now())
ON CONFLICT (user_id, file_name) DO UPDATE SET file_path = EXCLUDED.file_path, UPDATE_TIME = EXCLUDED.UPDATE_TIME;
=================
INSERT INTO meta_data (
               user_id,
               file_name,
               file_path,
               UPDATE_TIME )
     VALUES ( 'user_id02',
              'file_name02',
              '/usr/local/file_name03',
              now())
ON CONFLICT (user_id, file_name) DO UPDATE SET file_path = EXCLUDED.file_path, UPDATE_TIME = EXCLUDED.UPDATE_TIME;

Insert on meta_data  (cost=0.00..0.02 rows=0 width=0)
  Conflict Resolution: UPDATE
  Conflict Arbiter Indexes: meta_data_user_id_file_name_key
  ->  Result  (cost=0.00..0.02 rows=1 width=1318)

http://events.jianshu.io/p/57f262e3a07d replace会导致自增键增加，insert on duplicate不会。

https://www.sjkjc.com/postgresql/insert-on-conflict/

　　在pg中，如果表上定义了主键或多个唯一索引，是通过resolve_unique_index_expr函数先得到where字段列表，然后在plan时确定用哪个索引进行唯一性判断。

　　如果有多个符合条件的索引，通常是被选中用来过滤记录的索引的key的那条记录被修改，而不是多条都被修改。

 http://www.light-pg.com/docs/lightdb/current/sql-insert.html