首页 > 数据库 >mysql与lightdb中的insert on duplicate/replace

mysql与lightdb中的insert on duplicate/replace

时间:2023-01-25 13:22:20浏览次数:67  
标签:insert name UPDATE replace duplicate user file id

最近看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;

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的那条记录被修改,而不是多条都被修改。

 

标签:insert,name,UPDATE,replace,duplicate,user,file,id
From: https://www.cnblogs.com/lightdb/p/17017586.html

相关文章

  • VK Cup 2022 F. Bracket Insertion
    有一个初始为空的括号序列,依次执行\(n(n\le500)\)次操作,每次有\(p\)的概率选取(),\(1-p\)的概率选取)(,随机插入首部、尾部、中间的空隙(也就是从\(2i-1\)个位置中......
  • Execution failed for task ':app:checkDebugDuplicateClasses'解决办法
    Afailureoccurredwhileexecutingcom.android.build.gradle.internal.tasks.CheckDuplicatesRunnable >Duplicateclassandroid.support.v4.app.INotificationSi......
  • Stata:replace
    replacexm=substr(xm,1,3)+strofreal(_n)//以每个记录xm观测值的第一个字符和行号的组合替换当前的xm的观测值,substr(xm,1,3)截取第一个汉字,strofreal(_n)将行号转为字符......
  • SQL---insert方法中使用foreach
    如何在Mysql语句的insert语句中使用foreach方法,做循环插入?这里有一个user的集合,users,将其遍历插入到数据库表user中,方法如下:<insertid="addList"parameterType="com.java4......
  • Mysql,replace into,存在则更新,不存在则插入
    REPLACEINTO首先判断数据是否存在;如果不存在,则插入;如果已存在则更新(先删除再插入) 注意:根据主键或唯一索引判断记录是否已存在,所以插入数据的表必须要有主键或者唯......
  • 使用Stream进行List转Map踩坑Duplicate key错误
    报错代码myList.stream().collect(Collectors.toMap(MyDto::getCd,MyDto::getNm));报错信息java.lang.IllegalStateException:Duplicatekey000001-01-000000000000......
  • c++ std string replaceAll函数
    std提供的string的replace方法,不太方便stringreplaceAll(string&str,stringoldStr,stringnewStr){string::size_typepos=str.find(oldStr);while(pos......
  • *424. Longest Repeating Character Replacement [Medium]
    424.LongestRepeatingCharacterReplacementYouaregivenastringsandanintegerk.Youcanchooseanycharacterofthestringandchangeittoanyotheru......
  • java8 对象转 Map 时重复 key Duplicate key xxxx
    java8对象转Map时重复keyDuplicatekeyxxxxhttps://dongguabai.blog.csdn.net/article/details/98397784?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_rel......
  • Oracle使用append对表insert会阻塞表的其他会话DML操作
     Oracle使用append对表insert会阻塞其他会话DML操作 快春节了,抽点时间把NNNNNNN久之前的东西整理记录。insert/*+append*/into会对表持有LOCKED_MODE=6的TM锁,导......