数据库技术:实操MySQL+PostgreSQL批量插入更新insertOrUpdate

目录

• 1、mysql
• 2、postgresql
• 3、postgresql相对于mysql的优势
• 二、postgres中insertorupdate代码实例
• 四、usermapper.xml写法
• 五、mysql中insertorupdate代码实例
• 3、on duplicate key update
• 4、replace into
• 5、insert ignore into

  create table public.t_user (      username varchar(100) not null,      age int4 not null default 0,      "password" varchar(100) null,      deleted int4 null,      created_time timestamp null  );  create unique index t_user_union_name_age_password on public.t_user using btree (username, password, age);

  insert      into      public.t_user (username , password,age,created_time)  values ('zs', '123', 18,now()), ('ls', '123456', 19,now()),('ww', '123', 20,now())   on conflict (username, age,password) do update set username = excluded.username,age = excluded.age,password = excluded.password,created_time = excluded.created_time

  insert      into      public.t_user (username , password,age,created_time)  values (unnest(array['zs', 'ls', 'ww']), unnest(array['123', '123', '123456']),unnest(array[18, 19, 20]), unnest(array[now(), now(), now()]))   on conflict (username, age,password) do update set username = excluded.username,age = excluded.age,password = excluded.password,created_time = excluded.created_time

                                                         insert into ${map.tableinfo.schemaname}.${map.tableinfo.tablename}          (          "table_id",          "file_name",          "create_time",                        "${key}"                    )          values                        (              ${map.tableinfo.tableid},              #{map.tableinfo.filename},              now(),                                                                                #{value}                                                                      to_timestamp(#{value},'yyyy-mm-dd hh24:mi:ss')                                                                      ${value}                                                                    )                                                   on conflict (          file_name, table_id                        , "id_number"                    ) do update          set          "table_id" = excluded."table_id",          "file_name" = excluded."file_name",          "create_time" = excluded."create_time",                        "${key}" = excluded."${key}"                  

  create table `t_user`  (    `username` varchar(255) character set utf8 collate utf8_general_ci not null,    `password` varchar(255) character set utf8 collate utf8_general_ci not null,    `age` int(0) null default null,    `address` varchar(255) character set utf8 collate utf8_general_ci null default null,    `create_time` datetime(0) null default null,    `update_time` datetime(0) null default null,    `version` int(0) not null,    unique index `user_union_index`(`username`, `password`, `age`) using btree  ) engine = innodb character set = utf8 collate = utf8_general_ci row_format = dynamic;

  insert into t_user  (username,password,age,create_time)   values('张三' ,'123456',18,now())  on duplicate key update   username='张三',  password='123456',  create_time=now()

  insert into t_user   (username,password,age,create_time,update_time,version)  values( 'zs' ,'123',10,now(),now(),1)   ,( 'ls' ,'123456',20,now(),now(),1)   ,( 'ww' ,'123',30,now(),now(),1)   on duplicate key update   username= values(username)  ,password=values(password)  ,age=values(age)  ,update_time=values(update_time)  ,version = version + 1

  replace into t_user   (username,password,age,create_time,update_time,version)   values   ( 'zs' ,'123',10,now(),now(),1) 

  insert ignore into t_user   (username,password,age,create_time,update_time,version)   values   ( 'zs' ,'123',10,now(),now(),1) ,  ( '哪吒' ,'123',30,now(),now(),2)