游戏合区、拆区，数据库操作CASE - PostgreSQL Data merge case

背景

打个比方，有两个游戏区要合并。

单个游戏区的用户ID可以有多个角色，角色名不能重复。

当需要合并游戏区时，可能会涉及到同一个ID的不同区使用的相同的角色。

PostgreSQL没有merge的SQL语法(PostgreSQL 9.5有了)

PostgreSQL 9.5 new feature - Add support for INSERT … ON CONFLICT DO NOTHING/UPDATE

http://blog.163.com/digoal@126/blog/static/163877040201541094137923/

http://blog.163.com/digoal@126/blog/static/163877040201552343231537/

, 当然Oracle用MERGE也无法在一条SQL中实现这个需求.

举个例子 :

user_info1 表代表1区的用户表。

digoal=> create table user_info1 (userid int,nick text,info text,unique (userid,nick));  
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "user_info1_userid_nick_key" for table "user_info1"  
CREATE TABLE  

user_info2 表代表2区的用户表

digoal=> create table user_info2 (userid int,nick text,info text,unique (userid,nick));  
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "user_info2_userid_nick_key" for table "user_info2"  
CREATE TABLE  

创建测试数据, 1区和2区在userid=1到100范围内nick相同, 因此合并2个区的用户数据时, 这部分nick需要修改一下. 假如改成nick

‘_2’

digoal=> insert into user_info1 select generate_series(1,100),'digoal'||generate_series(1,100),'user_info1';  
INSERT 0 100  
digoal=> insert into user_info2 select generate_series(1,200),'digoal'||generate_series(1,200),'user_info2';  
INSERT 0 200  
digoal=> insert into user_info1 select generate_series(201,300),'digoal'||generate_series(201,300),'user_info1';  
INSERT 0 100  

创建nick获取函数 :

CREATE OR REPLACE FUNCTION digoal.get_nick(i_userid int,i_nick text)  
 RETURNS text  
 LANGUAGE plpgsql  
AS $function$  
declare  
begin  
perform 1 from user_info1 where userid=i_userid and nick=i_nick;  
if found then   
return i_nick||'_2';  
end if;  
return i_nick;  
end;  
$function$  

合并2区的表到1区

digoal=> insert into user_info1 select userid,get_nick(userid,nick),info from user_info2;  
INSERT 0 200  

合并后, 查看重叠部分的数据, 2区的nick已经加上了_2.

digoal=> select * from user_info1 where userid=1;  
 userid |   nick    |    info      
--------+-----------+------------  
      1 | digoal1   | user_info1  
      1 | digoal1_2 | user_info2  
(2 rows)  

非重叠数据正常插入 :

digoal=> select * from user_info1 where userid=200;  
 userid |   nick    |    info      
--------+-----------+------------  
    200 | digoal200 | user_info2  
(1 row)  

另一个例子 :

digoal=> create table t1 (id int,name text unique);  
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "t1_name_key" for table "t1"  
CREATE TABLE  
digoal=> create table t2 (id int,name text unique);  
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "t2_name_key" for table "t2"  
CREATE TABLE  
digoal=> insert into t1 values(1,'digoal');  
INSERT 0 1  
digoal=> insert into t1 values(2,'digoal1');  
INSERT 0 1  
digoal=> insert into t1 values(3,'digoal11');  
INSERT 0 1  
digoal=> insert into t1 values(4,'digoal111');  
INSERT 0 1  
digoal=> insert into t1 values(5,'digoal1111');  
INSERT 0 1  
digoal=> insert into t1 values(6,'digoal11111');  
INSERT 0 1  
digoal=> insert into t2 values(1,'digoal');  

把t2的数据合并到t1 :

create or replace function get_name(i_name text) returns text as $$  
declare  
v_name text;  
v_suffix text;  
begin  
v_suffix = '1';  
perform 1 from t1 where name = i_name limit 1;  
if not found then  
 return i_name;  
else  
 loop  
  perform 1 from t1 where name = i_name||v_suffix limit 1;  
  if not found then  
   exit;  
  else  
   v_suffix=v_suffix||'1';  
  end if;  
 end loop;  
end if;  
return i_name||v_suffix;  
end;  
$$ language plpgsql;  
digoal=> insert into t1 select id,get_name(name) from t2;  
INSERT 0 1  
  
digoal=> select * from t1;  
 id |     name       
----+--------------  
  1 | digoal  
  2 | digoal1  
  3 | digoal11  
  4 | digoal111  
  5 | digoal1111  
  6 | digoal11111  
  1 | digoal111111  

digoal’s 大量PostgreSQL文章入口