百度360必应搜狗淘宝本站头条
yum下载包及依赖到本地vuexstoregitcommit-ambashecho控制台打印
当前位置：网站首页 > 博客教程 > 正文

游戏合服新方案

connygpt 2024-09-08 13:31 20 浏览

游戏随着运营时间变成导致DAU变少。尤其是手游采用衮服的开服策略。导致服务器合服变得更加频繁且要求高效。合服的目的有二。其一是降低服务器成本，其二利于玩家之间的相互竞争。曾经合服采用的方法都是通过研发程序去实现。按功能把所有表数据加载到内存。通过逻辑关联实现。最后把新数据写入数据库。印象中最大的痛点是合服代码维护和合服过程低效。

如何解决痛点？因为自己本身对数据库存储过程感兴趣且比较擅长。结合游戏研发中的特点思考如何使用存储过程实现合服的目的。数据库也是一种强大的语言。具体可参阅。如何查看表中的二进制流。完全可以实现强大的逻辑功能。

具体结合游戏特征说明下原理和思路。

  1. 为了能合并中出现意外等问题。确保不影响原始数据。一定要克隆出数据库。加上_bak的标识。克隆数据库时一定要把表结构完全复制好。包括主键，索引等。最终使用的库就是第一个XXX_bak对应的库。具体参考CloneDbFun函数。
  2. 在第一个XXX_bak对应的库中删除不需要的表数据。也就是合服时可以清除的数据。比如:活动，特殊功能等。具体参考DealDbDataFun1自定义函数。
  3. 合服中最关键的是表的主键冲突问题。为了避免合服出现这样的问题。游戏逻辑中就提前考虑和避免了。生成全服唯一的主键。具体可参阅。预分配法生成唯一ID。即使有些主键可能存在冲突的问题。也可以通过逻辑处理了。具体参考函数DealOnlyCopyDbDataFun和DealDbDataFun3就直接搞定。一些特殊的可以扩展丰富。
  4. 采用存储过程就是合服操作简洁。维护合服脚本简单。最重要的是执行效率更高。大大缩短合服时间。在实际使用中非常有效。直接非常推崇的最佳方案。

文章提供存储过程脚本。可直接复制下来。根据自己游戏表情况。直接修改可用于项目中。关于游戏开发中的问题欢迎大家留言沟通。共同分享自己的知识。

Shell脚本：LinkServer.sh

 #!/bin/bash
 ?
 mysql -uroot -pzjuNT -h 10.6.10.50 -P 3306 -Dtest<LinkServerSql.sql

Mysql合服存储过程脚本：LinkServerSql.sql

 
 SET CHARACTER_SET_CLIENT=utf8;
 SET CHARACTER_SET_CONNECTION=utf8;
 ?
 -- 克隆数据库
 DELIMITER $
 ?
 -- 获取主表字段顺序(函数0)
 DROP PROCEDURE IF EXISTS `GetColumnsDbDataFun`$
 CREATE PROCEDURE `GetColumnsDbDataFun`(IN mainServerId0 VARCHAR(32),IN srceTableName0 VARCHAR(64),OUT tempColumnStr0 VARCHAR(4096))
 BEGIN
     DECLARE i0 INT DEFAULT 0;
     DECLARE srceTableCount0 INT DEFAULT 0;
 ?
     DECLARE tempDbNameSql0 VARCHAR(512) DEFAULT '';
     DECLARE tempColumnName0 VARCHAR(64) DEFAULT '';
 ?
     DECLARE checkSrceSql0 VARCHAR(1024) DEFAULT '';
     DECLARE runSrceSql0 VARCHAR(1024) DEFAULT '';
 ?
     SET @tempDbNameSql0=CONCAT("tafang2_s",mainServerId0,"_bak");
 ?
     SET @checkSrceSql0=CONCAT("SELECT COUNT(1) INTO @srceTableCount0 FROM information_schema.COLUMNS where table_name='",srceTableName0,"' AND TABLE_SCHEMA='",@tempDbNameSql0,"'");
     prepare stmt from @checkSrceSql0;
     EXECUTE stmt;
 ?
     -- 循环替换角色ID
     SET tempColumnStr0='';
 ?
     SET @checkSrceSql0=CONCAT("SELECT COLUMN_NAME INTO @tempColumnName0 FROM information_schema.COLUMNS where table_name='",srceTableName0,"' AND TABLE_SCHEMA='",@tempDbNameSql0,"' LIMIT [##],1");
     WHILE i0< @srceTableCount0 DO
         SET @runSrceSql0=REPLACE(@checkSrceSql0,'[##]',i0);
         prepare stmt from @runSrceSql0;
         EXECUTE stmt;
 ?
         IF i0>0 THEN
             SET tempColumnStr0=CONCAT(tempColumnStr0,",");
         END IF;
         SET tempColumnStr0=CONCAT(tempColumnStr0,@tempColumnName0);
         SET i0=i0+1;
     END WHILE;
 END$
 ?
 ?
 -- 克隆DB数据(函数1)
 DROP PROCEDURE IF EXISTS `CloneDbFun`$
 CREATE PROCEDURE `CloneDbFun`(IN mainServerId1 VARCHAR(1024))
 BEGIN
     DECLARE i1 INT DEFAULT 0;
     DECLARE tempCount1 INT DEFAULT 0;
 ?
     DECLARE srceDbNameSql1 VARCHAR(512) DEFAULT '';
     DECLARE destDbNameSql1 VARCHAR(512) DEFAULT '';
     DECLARE runSrceSql1 VARCHAR(1024) DEFAULT '';
 ?
     DECLARE checkDbSql1 VARCHAR(512) DEFAULT '';
     DECLARE tableName1 VARCHAR(128) DEFAULT '';
 ?
     DECLARE tempColumnStr1 VARCHAR(4096) DEFAULT '';
 ?
     -- 创建备份主数据库
     SET @srceDbNameSql1=CONCAT("tafang2_s",mainServerId1);
     SET @destDbNameSql1=CONCAT("tafang2_s",mainServerId1,"_bak");
 ?
     set names 'utf8';
     set character_set_database = 'utf8';
     set character_set_server = 'utf8';
 ?
     SET @runSrceSql1=CONCAT("DROP DATABASE IF EXISTS ",@destDbNameSql1);
     prepare stmt from @runSrceSql1;
     EXECUTE stmt;
 ?
     SET @runSrceSql1=CONCAT("CREATE DATABASE ",@destDbNameSql1," DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
     prepare stmt from @runSrceSql1;
     EXECUTE stmt;
 ?
     -- 循环导入数据
     SET @checkSql1=CONCAT("SELECT COUNT(1) into @tempCount1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='",@srceDbNameSql1,"'");
     prepare stmt from @checkSql1;
     EXECUTE stmt;
 ?
     SELECT @checkSql1,@tempCount1;
 ?
     SET @checkSrceSql1=CONCAT("SELECT table_name into @tableName1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='",@srceDbNameSql1,"' LIMIT [##],1");
     SET i1=0;
     WHILE i1 < @tempCount1 DO
         SET @runSrceSql1=REPLACE(@checkSrceSql1,'[##]',i1);
         prepare stmt from @runSrceSql1;
         EXECUTE stmt;
         
         -- 创建和拷贝内容
         -- SET @copyTableSql1=CONCAT("CREATE TABLE ",@destDbNameSql1,".",@tableName1," SELECT * FROM ",@srceDbNameSql1,".",@tableName1);
         -- SELECT @copyTableSql1;
         -- prepare stmt from @copyTableSql1;
 ?
         -- 复制表结构包括索引
         SET @copyTableSql1=CONCAT("CREATE TABLE ",@destDbNameSql1,".",@tableName1," LIKE ",@srceDbNameSql1,".",@tableName1);
         SELECT @copyTableSql1;
         prepare stmt from @copyTableSql1;
         EXECUTE stmt;
 ?
         -- 执行合并操作
         SET @tempColumnStr1='';
         CALL GetColumnsDbDataFun(mainServerId1,@tableName1,@tempColumnStr1);
         SET @copyTableSql1=CONCAT("INSERT INTO ",@destDbNameSql1,".",@tableName1,"(",@tempColumnStr1,") SELECT ",@tempColumnStr1," FROM ",@srceDbNameSql1,".",@tableName1);
         SELECT @copyTableSql1;
         prepare stmt from @copyTableSql1;
         EXECUTE stmt;
 ?
         SET i1=i1+1;
     END WHILE;
 END$
 ?
 -- 处理数据类型1(函数2)
 DROP PROCEDURE IF EXISTS `DealDbDataFun1`$
 CREATE PROCEDURE `DealDbDataFun1`(IN srceDb2 VARCHAR(32),IN srceTableName2 VARCHAR(64))
 BEGIN
     DECLARE destDbNameSql2 VARCHAR(512) DEFAULT '';
     DECLARE runSrceSql2 VARCHAR(1024) DEFAULT '';
 ?
     SET @destDbNameSql2=CONCAT("tafang2_s",srceDb2,"_bak");
 ?
     SET @runSrceSql2=CONCAT("DELETE FROM ",@destDbNameSql2,".",srceTableName2);
     SELECT @runSrceSql2;
     prepare stmt from @runSrceSql2;
     EXECUTE stmt;
 END$
 ?
 -- 处理数据类型3(函数3)
 DROP PROCEDURE IF EXISTS `DealDbDataFun3`$
 CREATE PROCEDURE `DealDbDataFun3`(IN mainServerId3 VARCHAR(32),IN tempServerId3 VARCHAR(32),
                 IN srceTableName3 VARCHAR(64),IN addUniqueId3 INT)
 BEGIN
     DECLARE mainDbNameSql3 VARCHAR(512) DEFAULT '';
     DECLARE tempDbNameSql3 VARCHAR(512) DEFAULT '';
 ?
     DECLARE runSrceSql3 VARCHAR(1024) DEFAULT '';
     DECLARE tempColumnStr3 VARCHAR(4096) DEFAULT '';
 ?
     SET @mainDbNameSql3=CONCAT("tafang2_s",mainServerId3,"_bak");
     SET @tempDbNameSql3=CONCAT("tafang2_s",tempServerId3,"_bak");
 ?
     -- 先修改UID
     SET @runSrceSql3=CONCAT("UPDATE ",@tempDbNameSql3,".",srceTableName3," SET UID=UID+",addUniqueId3," ORDER BY UID DESC");
     SELECT @runSrceSql3;
     prepare stmt from @runSrceSql3;
     EXECUTE stmt;
 ?
     -- 执行合并操作
     SET @tempColumnStr3='';
     CALL GetColumnsDbDataFun(mainServerId3,srceTableName3,@tempColumnStr3);
     SET @runSrceSql3=CONCAT("INSERT INTO ",@mainDbNameSql3,".",srceTableName3,"(",@tempColumnStr3,") SELECT ",@tempColumnStr3," FROM ",@tempDbNameSql3,".",srceTableName3);
     SELECT @runSrceSql3;
     prepare stmt from @runSrceSql3;
     EXECUTE stmt;
 END$
 ?
 -- 修改字段数据(函数4)
 DROP PROCEDURE IF EXISTS `DealOnlyUpdateDbDataFun`$
 CREATE PROCEDURE `DealOnlyUpdateDbDataFun`(IN tempServerId4 VARCHAR(32),IN srceTableName4 VARCHAR(64),IN srceParamName4 VARCHAR(64),IN addUniqueId4 INT)
 BEGIN
     DECLARE tempDbNameSql4 VARCHAR(512) DEFAULT '';
 ?
     DECLARE runSrceSql4 VARCHAR(1024) DEFAULT '';
 ?
     SET @tempDbNameSql4=CONCAT("tafang2_s",tempServerId4,"_bak");
 ?
     -- 先修改对应字段
     SET @runSrceSql4=CONCAT("UPDATE ",@tempDbNameSql4,".",srceTableName4," SET ",srceParamName4,"=",srceParamName4,"+",addUniqueId4," ORDER BY ",srceParamName4," DESC");
     SELECT @runSrceSql4;
     prepare stmt from @runSrceSql4;
     EXECUTE stmt;
 END$
 ?
 -- 只复制表功能(函数5)
 DROP PROCEDURE IF EXISTS `DealOnlyCopyDbDataFun`$
 CREATE PROCEDURE `DealOnlyCopyDbDataFun`(IN mainServerId5 VARCHAR(32),IN tempServerId5 VARCHAR(32),IN srceTableName5 VARCHAR(64))
 BEGIN
     DECLARE mainDbNameSql5 VARCHAR(512) DEFAULT '';
     DECLARE tempDbNameSql5 VARCHAR(512) DEFAULT '';
 ?
     DECLARE runSrceSql5 VARCHAR(1024) DEFAULT '';
     DECLARE tempColumnStr5 VARCHAR(4096) DEFAULT '';
 ?
 ?
     SET @mainDbNameSql5=CONCAT("tafang2_s",mainServerId5,"_bak");
     SET @tempDbNameSql5=CONCAT("tafang2_s",tempServerId5,"_bak");
 ?
     -- 执行合并操作
     SET @tempColumnStr5='';
     CALL GetColumnsDbDataFun(mainServerId5,srceTableName5,@tempColumnStr5);
     SET @runSrceSql5=CONCAT("INSERT INTO ",@mainDbNameSql5,".",srceTableName5,"(",@tempColumnStr5,") SELECT ",@tempColumnStr5," FROM ",@tempDbNameSql5,".",srceTableName5);
     SELECT @runSrceSql5;
     prepare stmt from @runSrceSql5;
     EXECUTE stmt;
 END$
 ?
 ?
 -- 循环修改角色ID(函数6)
 DROP PROCEDURE IF EXISTS `LoopHumanIdDbDataFun`$
 CREATE PROCEDURE `LoopHumanIdDbDataFun`(IN tempServerId6 VARCHAR(32),IN addUniqueId6 INT)
 BEGIN
     DECLARE i6 INT DEFAULT 0;
     DECLARE srceTableCount6 INT DEFAULT 0;
 ?
     DECLARE oldHumanId6 INT DEFAULT 0;
     DECLARE realHumanId6 INT DEFAULT 0;
 ?
 ?
     DECLARE tempDbNameSql6 VARCHAR(512) DEFAULT '';
 ?
     DECLARE checkSrceSql6 VARCHAR(1024) DEFAULT '';
     DECLARE runSrceSql6 VARCHAR(1024) DEFAULT '';
 ?
     SET @tempDbNameSql6=CONCAT("tafang2_s",tempServerId6,"_bak");
 ?
     SET @checkSrceSql6=CONCAT("SELECT COUNT(1) INTO @srceTableCount6 FROM ",@tempDbNameSql6,".T_ROLE WHERE UID>0");
     prepare stmt from @checkSrceSql6;
     EXECUTE stmt;
 ?
     -- 循环替换角色ID
     SET @checkSrceSql6=CONCAT("SELECT UID INTO @oldHumanId6 FROM ",@tempDbNameSql6,".T_ROLE WHERE UID>0 LIMIT [##],1");
     WHILE i6 < @srceTableCount6 DO
         SET @runSrceSql6=REPLACE(@checkSrceSql6,'[##]',i6);
         prepare stmt from @runSrceSql6;
         EXECUTE stmt;
         
         SET @realHumanId6=@oldHumanId6+@addUniqueId6;
 ?
         -- 开始依次替换
 ?
         SET i6=i6+1;
     END WHILE;
 END$
 ?
 -- 循环修改角色名(函数7)
 DROP PROCEDURE IF EXISTS `LoopHumanNameDbDataFun`$
 CREATE PROCEDURE `LoopHumanNameDbDataFun`(IN tempServerId7 VARCHAR(32),IN beginServerId7 INT)
 BEGIN
     DECLARE i7 INT DEFAULT 0;
     DECLARE srceTableCount7 INT DEFAULT 0;
 ?
     DECLARE oldHumanName7 VARCHAR(64) DEFAULT '';
     DECLARE realHumanName7 VARCHAR(64) DEFAULT '';
 ?
     DECLARE tempDbNameSql7 VARCHAR(512) DEFAULT '';
 ?
     DECLARE checkSrceSql7 VARCHAR(1024) DEFAULT '';
     DECLARE runSrceSql7 VARCHAR(1024) DEFAULT '';
 ?
     SET @tempDbNameSql7=CONCAT("tafang2_s",tempServerId7,"_bak");
 ?
     SET @checkSrceSql7=CONCAT("SELECT COUNT(1) INTO @srceTableCount7 FROM ",@tempDbNameSql7,".T_ROLE WHERE UID>0 AND NAME<>''");
     prepare stmt from @checkSrceSql7;
     EXECUTE stmt;
 ?
     -- 循环替换角色ID
     SET @checkSrceSql7=CONCAT("SELECT NAME INTO  @oldHumanName7 FROM ",@tempDbNameSql7,".T_ROLE WHERE UID>0  AND NAME<>'' LIMIT [##],1");
     WHILE i7 < @srceTableCount7 DO
         SET @runSrceSql7=REPLACE(@checkSrceSql7,'[##]',i7);
         prepare stmt from @runSrceSql7;
         EXECUTE stmt;
         
         SET @realHumanName7=CONCAT("S",CAST(tempServerId7 AS unsigned)-beginServerId7,".",@oldHumanName7);
         -- 开始依次替换
 ?
         SET i7=i7+1;
     END WHILE;
 END$
 ?
 ?
 -- 循环修改工会ID(函数8)
 DROP PROCEDURE IF EXISTS `LoopClubIdDbDataFun`$
 CREATE PROCEDURE `LoopClubIdDbDataFun`(IN tempServerId8 VARCHAR(32),IN addUniqueId8 INT)
 BEGIN
     DECLARE i8 INT DEFAULT 0;
     DECLARE srceTableCount8 INT DEFAULT 0;
 ?
     DECLARE oldClubId8 INT DEFAULT 0;
     DECLARE realClubId8 INT DEFAULT 0;
 ?
 ?
     DECLARE tempDbNameSql8 VARCHAR(512) DEFAULT '';
 ?
     DECLARE checkSrceSql8 VARCHAR(1024) DEFAULT '';
     DECLARE runSrceSql8 VARCHAR(1024) DEFAULT '';
 ?
     SET @tempDbNameSql8=CONCAT("tafang2_s",tempServerId8,"_bak");
 ?
     SET @checkSrceSql8=CONCAT("SELECT COUNT(1) INTO @srceTableCount8 FROM ",@tempDbNameSql8,".T_CLUB");
     prepare stmt from @checkSrceSql8;
     EXECUTE stmt;
 ?
     -- 循环替换角色ID
     SET @checkSrceSql8=CONCAT("SELECT ID INTO @oldClubId8 FROM ",@tempDbNameSql8,".T_CLUB LIMIT [##],1");
     WHILE i8 < @srceTableCount8 DO
         SET @runSrceSql8=REPLACE(@checkSrceSql8,'[##]',i8);
         prepare stmt from @runSrceSql8;
         EXECUTE stmt;
         
         SET @realClubId8=@oldClubId8+@addUniqueId8;
 ?
         -- 开始依次替换
 ?
         SET i8=i8+1;
     END WHILE;
 END$
 ?
 -- 循环修改公会名(函数9)
 DROP PROCEDURE IF EXISTS `LoopClubNameDbDataFun`$
 CREATE PROCEDURE `LoopClubNameDbDataFun`(IN tempServerId9 VARCHAR(32),IN beginServerId9 INT)
 BEGIN
     DECLARE i9 INT DEFAULT 0;
     DECLARE srceTableCount9 INT DEFAULT 0;
 ?
     DECLARE oldClubName9 VARCHAR(64) DEFAULT '';
     DECLARE realClubName9 VARCHAR(64) DEFAULT '';
 ?
     DECLARE tempDbNameSql9 VARCHAR(512) DEFAULT '';
 ?
     DECLARE checkSrceSql9 VARCHAR(1024) DEFAULT '';
     DECLARE runSrceSql9 VARCHAR(1024) DEFAULT '';
 ?
     SET @tempDbNameSql9=CONCAT("tafang2_s",tempServerId9,"_bak");
 ?
     SET @checkSrceSql9=CONCAT("SELECT COUNT(1) INTO @srceTableCount9 FROM ",@tempDbNameSql9,".T_CLUB");
     prepare stmt from @checkSrceSql9;
     EXECUTE stmt;
 ?
     -- 循环替换角色ID
     SET @checkSrceSql9=CONCAT("SELECT CLUBNAME INTO  @oldClubName9 FROM ",@tempDbNameSql9,".T_CLUB LIMIT [##],1");
     WHILE i9 < @srceTableCount9 DO
         SET @runSrceSql9=REPLACE(@checkSrceSql9,'[##]',i9);
         prepare stmt from @runSrceSql9;
         EXECUTE stmt;
         
         SET @realHumanName9=CONCAT("S",CAST(tempServerId9 AS unsigned)-beginServerId9,".",@oldHumanName9);
         -- 开始依次替换
 ?
         SET i9=i9+1;
     END WHILE;
 END$
 ?
 -- 循环修改字符型角色ID(函数10)
 DROP PROCEDURE IF EXISTS `LoopDealHumanStrFun`$
 CREATE PROCEDURE `LoopDealHumanStrFun`(IN tempDealStr10 VARCHAR(10240),IN addUniqueId10 INT,OUT tempOutStr10 VARCHAR(10240))
 BEGIN
     DECLARE tempLen10 INT DEFAULT 0;
     DECLARE tempBeginPos10 INT DEFAULT 0;
 ?
     DECLARE tempHumanId10 INT DEFAULT 0;
     DECLARE tempRunSql100 VARCHAR(1024) DEFAULT '';
 ?
     SET tempOutStr10=tempDealStr10;
 ?
     SET @tempLen10=LENGTH(tempDealStr10);
     SET @tempBeginPos10=2;
     
     WHILE @tempLen10>=@tempBeginPos10+7 DO
         SET @tempHumanId10=MID(tempDealStr10,@tempBeginPos10,7);
         SET tempOutStr10=REPLACE(tempOutStr10,@tempHumanId10,@tempHumanId10+addUniqueId10);
         SET @tempBeginPos10=@tempBeginPos10+8;
     END WHILE;
 END$
 ?
 -- 循环修改字符型角色ID(函数11)
 DROP PROCEDURE IF EXISTS `RealDealHumanStrFun`$
 CREATE PROCEDURE `RealDealHumanStrFun`(IN tempServerId11 VARCHAR(32),IN addUniqueId11 INT)
 BEGIN
     DECLARE i11 INT DEFAULT 0;
     DECLARE srceTableCount11 INT DEFAULT 0;
 ?
     DECLARE realGiftId11 INT DEFAULT 0;
 ?
 ?
     DECLARE tempDbNameSql11 VARCHAR(512) DEFAULT '';
 ?
     DECLARE checkSrceSql11 VARCHAR(1024) DEFAULT '';
     DECLARE runSrceSql11 VARCHAR(1024) DEFAULT '';
 ?
     DECLARE saveRewardSql11 VARCHAR(10240) DEFAULT '';
     DECLARE temprewardSql11 VARCHAR(10240) DEFAULT '';
     DECLARE tempRunSql11 VARCHAR(10240) DEFAULT '';
 ?
     SET @tempDbNameSql11=CONCAT("tafang2_s",tempServerId11,"_bak");
 ?
     SET @checkSrceSql11=CONCAT("SELECT COUNT(1) INTO @srceTableCount11 FROM ",@tempDbNameSql11,".T_CLUB_GIFT");
     prepare stmt from @checkSrceSql11;
     EXECUTE stmt;
 ?
     -- 循环替换角色ID
     SET @checkSrceSql11=CONCAT("SELECT ID,REWARDROLEPACK INTO @realGiftId11,@saveRewardSql11 FROM ",@tempDbNameSql11,".T_CLUB_GIFT LIMIT [##],1");
     WHILE i11 < @srceTableCount11 DO
         SET @runSrceSql11=REPLACE(@checkSrceSql11,'[##]',i11);
         prepare stmt from @runSrceSql11;
         EXECUTE stmt;
 ?
         -- 开始依次替换
         SELECT "<===",@saveRewardSql11;
         CALL LoopDealHumanStrFun(@saveRewardSql11,addUniqueId11,@temprewardSql11);
         SELECT "====>",@temprewardSql11;
 ?
         SET @tempRunSql11=CONCAT("UPDATE tafang2_s",tempServerId11,"_bak.T_CLUB_GIFT SET REWARDROLEPACK='",@temprewardSql11,"' WHERE ID=",@realGiftId11);
         SELECT @tempRunSql11;
         prepare stmt from @tempRunSql11;
         EXECUTE stmt;
 ?
         SET i11=i11+1;
     END WHILE;
 END$
 ?
 DROP PROCEDURE IF EXISTS `LinkServerSql`$
 CREATE PROCEDURE `LinkServerSql`(IN srceDb VARCHAR(1024),IN beginServerId INT)
 BEGIN
 ?
     DECLARE i INT DEFAULT 0;
     DECLARE loopState INT DEFAULT 0;
     DECLARE tempCount INT DEFAULT 0;
 ?
     DECLARE i100 INT DEFAULT 0;
     DECLARE srceTableCount100 INT DEFAULT 0;
 ?
     DECLARE realHumanId100 INT DEFAULT 0;
 ?
     DECLARE tempRunSql100 VARCHAR(1024) DEFAULT '';
 ?
 ?
     DECLARE mainUniqueHumanId INT DEFAULT 0;
     DECLARE tempUniqueHumanId INT DEFAULT 0;
     DECLARE addUniqueHumanId INT DEFAULT 0;
 ?
     DECLARE mainUniqueClubId INT DEFAULT 0;
     DECLARE tempUniqueClubId INT DEFAULT 0;
     DECLARE addUniqueClubId INT DEFAULT 0;
 ?
     -- 主服务器ID
     DECLARE mainServerId VARCHAR(32) DEFAULT '';
     DECLARE tempServerId VARCHAR(32) DEFAULT '';
 ?
     -- 数据库名
     DECLARE srceDbNameSql VARCHAR(512) DEFAULT '';
     DECLARE destDbNameSql VARCHAR(512) DEFAULT '';
     DECLARE runSrceSql VARCHAR(1024) DEFAULT '';
     DECLARE tempRunSql VARCHAR(1024) DEFAULT '';
 ?
     DECLARE checkDbSql VARCHAR(512) DEFAULT '';
     DECLARE tableName VARCHAR(128) DEFAULT '';
 ?
     -- 优先处理主服务器信息
     SET @mainServerId=SUBSTRING_INDEX(srceDb,',',1);
     SET srceDb=REPLACE(srceDb,CONCAT(@mainServerId,','),'');
 ?
     -- 克隆出数据
     CALL CloneDbFun(@mainServerId);
 ?
     -- 清除垃圾数据
     SET @tempRunSql=CONCAT("DELETE FROM  tafang2_s",@mainServerId,"_bak.T_RECRUIT_ALL WHERE UID<0");
     prepare stmt from @tempRunSql;
     EXECUTE stmt;
 ?
     -- 数据0:角色表增加字段.40版本需要
     -- SET @tempRunSql=CONCAT("ALTER TABLE tafang2_s",@mainServerId,"_bak.T_ROLE ADD COLUMN SERVERID int DEFAULT 0 AFTER NAME");
     -- prepare stmt from @tempRunSql;
     -- EXECUTE stmt;
     
     -- 模拟测试需要
     SET @tempRunSql=CONCAT("UPDATE tafang2_s",@mainServerId,"_bak.T_ROLE SET SERVERID=",@mainServerId);
     prepare stmt from @tempRunSql;
     EXECUTE stmt;
 ?
     -- 数据1:不需要的数据.全部删除
     CALL DealDbDataFun1(@mainServerId,"T_ACTIVITY");
     CALL DealDbDataFun1(@mainServerId,"T_ROLE_ACTIVITY");
     CALL DealDbDataFun1(@mainServerId,"T_ROLE_LIMITRECRUIT_ACTIVITY");
 ?
     CALL DealDbDataFun1(@mainServerId,"T_EMAILTIMER");
     
 ?
     -- 数据3:只需修改角色ID或者直接合并
     SET @tempRunSql=CONCAT("SELECT DBUNIQUEID INTO @mainUniqueHumanId FROM tafang2_s",@mainServerId,"_bak.T_DBUNIQUEID WHERE DBUNIQUECACHEID=1");
     prepare stmt from @tempRunSql;
     EXECUTE stmt;
     
     SET @addUniqueHumanId=@mainUniqueHumanId;
 ?
     SET @tempRunSql=CONCAT("SELECT DBUNIQUEID INTO @mainUniqueClubId FROM tafang2_s",@mainServerId,"_bak.T_DBUNIQUEID WHERE DBUNIQUECACHEID=3");
     prepare stmt from @tempRunSql;
     EXECUTE stmt;
     
     SET @addUniqueClubId=@mainUniqueClubId;
 ?
     -- CALL LoopHumanNameDbDataFun(@mainServerId);
     -- CALL LoopClubNameDbDataFun(@mainServerId);
 ?
     -- 全部修改不为空的角色名
     SET @tempRunSql=CONCAT("UPDATE tafang2_s",@mainServerId,"_bak.T_ROLE SET NAME=CONCAT('S',",CAST(@mainServerId AS unsigned)-beginServerId,",'.'",",NAME) WHERE UID>0 AND NAME<>''");
     SELECT @tempRunSql;
     prepare stmt from @tempRunSql;
     EXECUTE stmt;
 ?
     SET @tempRunSql=CONCAT("UPDATE tafang2_s",@mainServerId,"_bak.T_CLUB SET CLUBNAME=CONCAT('S',",CAST(@mainServerId AS unsigned)-beginServerId,",'.'",",CLUBNAME)");
     SELECT @tempRunSql;
     prepare stmt from @tempRunSql;
     EXECUTE stmt;
 ?
     -- 一些数据初始化
     SET @tempRunSql=CONCAT("UPDATE tafang2_s",@mainServerId,"_bak.T_CLUB SET MEMBERIDPACK='[]',LOGPACK='[]',APPLYIDPACK='[]'");
     SELECT @tempRunSql;
     prepare stmt from @tempRunSql;
     EXECUTE stmt;
 ?
     -- 删除战报并且删除有战报的邮件
     SET @tempRunSql=CONCAT("DELETE FROM tafang2_s",@mainServerId,"_bak.T_PVPBATTLEREPORT");
     SELECT @tempRunSql;
     prepare stmt from @tempRunSql;
     EXECUTE stmt;
 ?
     SET @tempRunSql=CONCAT("DELETE FROM tafang2_s",@mainServerId,"_bak.T_EMAIL WHERE BATTLEINFOID>0");
     SELECT @tempRunSql;
     prepare stmt from @tempRunSql;
     EXECUTE stmt;
 ?
     SET @tempRunSql=CONCAT("UPDATE tafang2_s",@mainServerId,"_bak.T_CLUB_BATTLE_LEVEL SET clubHeroHelpPack='[]'");
     prepare stmt from @tempRunSql;
     EXECUTE stmt;
 ?
     -- 处理其它库数据
     SET loopState=1;
     WHILE loopState=1 DO
         SET @tempServerId=SUBSTRING_INDEX(srceDb,',',1);
         SET srceDb=REPLACE(srceDb,CONCAT(@tempServerId,','),'');
         SELECT  srceDb,@tempServerId;
 ?
         -- 克隆出数据
         CALL CloneDbFun(@tempServerId);
 ?
         -- 删除其它服务器不需要的机器人数据
         SET @tempRunSql=CONCAT("DELETE FROM  tafang2_s",@tempServerId,"_bak.T_ROLE WHERE UID<0");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         SET @tempRunSql=CONCAT("DELETE FROM  tafang2_s",@tempServerId,"_bak.T_ROLE_RES WHERE UID<0");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         SET @tempRunSql=CONCAT("DELETE FROM  tafang2_s",@tempServerId,"_bak.T_ROLE_WAR WHERE UID<0");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         SET @tempRunSql=CONCAT("DELETE FROM  tafang2_s",@tempServerId,"_bak.T_RANK WHERE UID<0");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         -- 清除垃圾数据
         SET @tempRunSql=CONCAT("DELETE FROM  tafang2_s",@tempServerId,"_bak.T_RECRUIT_ALL WHERE UID<0");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         -- 数据0:角色表增加字段
         -- SET @tempRunSql=CONCAT("ALTER TABLE tafang2_s",@tempServerId,"_bak.T_ROLE ADD COLUMN SERVERID int DEFAULT 0 AFTER NAME");
         -- prepare stmt from @tempRunSql;
         -- EXECUTE stmt;
         
         -- 模拟测试需要
         SET @tempRunSql=CONCAT("UPDATE tafang2_s",@tempServerId,"_bak.T_ROLE SET SERVERID=",@tempServerId);
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         -- 数据1:不需要处理
         -- 数据2:不需要处理
 ?
         -- 数据3:只需修改角色ID或者直接合并
         SET @tempRunSql=CONCAT("SELECT DBUNIQUEID INTO @tempUniqueHumanId FROM tafang2_s",@tempServerId,"_bak.T_DBUNIQUEID WHERE DBUNIQUECACHEID=1");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         SET @tempRunSql=CONCAT("SELECT DBUNIQUEID INTO @tempUniqueClubId FROM tafang2_s",@tempServerId,"_bak.T_DBUNIQUEID WHERE DBUNIQUECACHEID=3");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
         
         -- 修改主服角色主键
         SET @tempRunSql=CONCAT("UPDATE tafang2_s",@mainServerId,"_bak.T_DBUNIQUEID SET DBUNIQUEID=",(@addUniqueHumanId+@tempUniqueHumanId)," WHERE DBUNIQUECACHEID=1");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         SET @tempRunSql=CONCAT("UPDATE tafang2_s",@mainServerId,"_bak.T_DBUNIQUEID SET DBUNIQUEID=",(@addUniqueClubId+@tempUniqueClubId)," WHERE DBUNIQUECACHEID=3");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         SET @tempRunSql=CONCAT("DELETE FROM tafang2_s",@tempServerId,"_bak.T_EMAIL WHERE BATTLEINFOID>0");
         SELECT @tempRunSql;
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         -- 依次修改UID并且拷贝到主服务器表中
         CALL DealOnlyUpdateDbDataFun(@tempServerId,"T_ARENAEMAIL","UUID",@addUniqueHumanId);
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_ARENAEMAIL",@addUniqueHumanId);
 ?
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_ARTIFACT",@addUniqueHumanId);
 ?
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_CLIMB",@addUniqueHumanId);
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_EQUIP",@addUniqueHumanId);
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_EQUIPFRAGMENT",@addUniqueHumanId);
         
         CALL DealOnlyUpdateDbDataFun(@tempServerId,"T_GUIDEFUNCTION","ID",@addUniqueHumanId);
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_GUIDEFUNCTION",@addUniqueHumanId);
 ?
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_GUIDEROLE",@addUniqueHumanId);
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_HEROFRAGMENT",@addUniqueHumanId);
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_HEROINFO",@addUniqueHumanId);
 ?
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_LOTTERY",@addUniqueHumanId);
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_MALL",@addUniqueHumanId);
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_MAMMON",@addUniqueHumanId);
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_MONTHCARD",@addUniqueHumanId);
 ?
         CALL DealOnlyUpdateDbDataFun(@tempServerId,"T_RANK","ID",@addUniqueHumanId);
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_RANK",@addUniqueHumanId);
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_RECRUIT",@addUniqueHumanId);
         
         -- 数据4:需要依次替换
         CALL DealOnlyUpdateDbDataFun(@tempServerId,"T_CLUB","LEADUID",@addUniqueHumanId);
         CALL DealOnlyUpdateDbDataFun(@tempServerId,"T_CLUB","CLUBID",@addUniqueClubId);
 ?
         CALL DealOnlyUpdateDbDataFun(@tempServerId,"T_CLUB_BATTLE_LEVEL","CLUBID",@addUniqueClubId);
         CALL DealOnlyUpdateDbDataFun(@tempServerId,"T_CLUB_BATTLE_LEVEL","ID",@addUniqueClubId);
         SET @tempRunSql=CONCAT("UPDATE tafang2_s",@tempServerId,"_bak.T_CLUB_BATTLE_LEVEL SET clubHeroHelpPack='[]'");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         -- 工会ID小于0的不处理
         SET @tempRunSql=CONCAT("UPDATE tafang2_s",@tempServerId,"_bak.T_CLUB_BATTLE_ROLE SET CLUBID=CLUBID+",@addUniqueClubId," WHERE CLUBID>0 ORDER BY CLUBID DESC");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         CALL DealOnlyUpdateDbDataFun(@tempServerId,"T_CLUB_BATTLE_ROLE","ID",@addUniqueHumanId);
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_CLUB_BATTLE_ROLE",@addUniqueHumanId);
 ?
         -- 工会ID小于0的不处理
         SET @tempRunSql=CONCAT("UPDATE tafang2_s",@tempServerId,"_bak.T_CLUB_DICE_ROLE SET CLUBID=CLUBID+",@addUniqueClubId," WHERE CLUBID>0 ORDER BY CLUBID DESC");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         CALL DealOnlyUpdateDbDataFun(@tempServerId,"T_CLUB_DICE_ROLE","ID",@addUniqueHumanId);
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_CLUB_DICE_ROLE",@addUniqueHumanId);
 ?
 ?
         -- 工会ID小于0的不处理
         SET @tempRunSql=CONCAT("UPDATE tafang2_s",@tempServerId,"_bak.T_CLUB_GIFT SET CLUBID=CLUBID+",@addUniqueClubId," WHERE CLUBID>0 ORDER BY CLUBID DESC");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         SET @tempRunSql=CONCAT("UPDATE tafang2_s",@tempServerId,"_bak.T_CLUB_GIFT SET SENDUID=SENDUID+",@addUniqueHumanId," WHERE SENDUID>0 ORDER BY SENDUID DESC");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         SET @tempRunSql=CONCAT("UPDATE tafang2_s",@tempServerId,"_bak.T_CLUB_GIFT SET MAXREWARDUID=MAXREWARDUID+",@addUniqueHumanId," WHERE MAXREWARDUID>0 ORDER BY MAXREWARDUID DESC");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         CALL RealDealHumanStrFun(@tempServerId,@addUniqueHumanId);
 ?
         -- 零表示无门派
         SET @tempRunSql=CONCAT("UPDATE tafang2_s",@tempServerId,"_bak.T_CLUB_MEMBER SET CLUBID=CLUBID+",@addUniqueClubId," WHERE CLUBID>0 ORDER BY CLUBID DESC");
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         CALL DealOnlyUpdateDbDataFun(@tempServerId,"T_CLUB_MEMBER","UID",@addUniqueHumanId);
 ?
 ?
         CALL DealOnlyUpdateDbDataFun(@tempServerId,"T_EMAIL","UID",@addUniqueHumanId);
 ?
         -- 数据5:循环替换角色ID和角色名
         -- CALL LoopHumanIdDbDataFun(@tempServerId,@addUniqueHumanId);
         -- CALL LoopHumanNameDbDataFun(@tempServerId);
 ?
         -- 数据6:循环替换工会ID和工会名
         -- CALL LoopClubIdDbDataFun(@tempServerId,@addUniqueClubId);
         -- CALL LoopClubNameDbDataFun(@tempServerId);
 ?
         SET @tempRunSql=CONCAT("UPDATE tafang2_s",@tempServerId,"_bak.T_ROLE SET NAME=CONCAT('S',",CAST(@tempServerId AS unsigned)-beginServerId,",'.'",",NAME) WHERE UID>0 AND NAME<>''");
         SELECT @tempRunSql;
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         SET @tempRunSql=CONCAT("UPDATE tafang2_s",@tempServerId,"_bak.T_CLUB SET CLUBNAME=CONCAT('S',",CAST(@tempServerId AS unsigned)-beginServerId,",'.'",",CLUBNAME)");
         SELECT @tempRunSql;
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         -- 一些数据初始化
         SET @tempRunSql=CONCAT("UPDATE tafang2_s",@tempServerId,"_bak.T_CLUB SET MEMBERIDPACK='[]',LOGPACK='[]',APPLYIDPACK='[]'");
         SELECT @tempRunSql;
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         CALL DealOnlyUpdateDbDataFun(@tempServerId,"T_ROLE","ID",@addUniqueHumanId);
         CALL DealDbDataFun3(@mainServerId,@tempServerId,"T_ROLE",@addUniqueHumanId);
 ?
         CALL DealOnlyUpdateDbDataFun(@tempServerId,"T_CLUB","ID",@addUniqueClubId);
         CALL DealOnlyCopyDbDataFun(@mainServerId,@tempServerId,"T_CLUB");
 ?
         CALL DealOnlyCopyDbDataFun(@mainServerId,@tempServerId,"T_EMAIL");
 ?
         SET @addUniqueHumanId=@addUniqueHumanId+@tempUniqueHumanId;
         SET @addUniqueClubId=@addUniqueClubId+@tempUniqueClubId;
 ?
         IF @tempServerId=srceDb THEN
             SET loopState=0;
         END IF;
     END WHILE;
 ?
     -- 重新进行竞技场排序
     SET @tempRunSql=CONCAT("SELECT COUNT(1) INTO @srceTableCount100 FROM tafang2_s",@mainServerId,"_bak.T_ROLE a,tafang2_s",@mainServerId,"_bak.T_RANK b WHERE a.UID=b.UID AND a.UID>0 ORDER BY a.BATTLEVALUE DESC");
     SELECT @tempRunSql;
     prepare stmt from @tempRunSql;
     EXECUTE stmt;
 ?
     -- 修改机器人的排名
     SET i100=1;
 ?
     WHILE i100 < 5001 DO
         -- 开始依次替换
         SET @tempRunSql=CONCAT("UPDATE tafang2_s",@mainServerId,"_bak.T_RANK SET RANK=",(i100+@srceTableCount100)," WHERE UID=",-i100);
         prepare stmt from @tempRunSql;
         EXECUTE stmt;
 ?
         SET i100=i100+1;
     END WHILE;
 ?
     SELECT  @mainServerId,@tempServerId;
 END$
 ?
 DELIMITER ;
 ?
  ---CALL LinkServerSql("8023,8024,8025,8026,8027,8028",8000);


相关推荐

3分钟让你的项目支持AI问答模块，完全开源!

hello，大家好，我是徐小夕。之前和大家分享了很多可视化，零代码和前端工程化的最佳实践，今天继续分享一下最近开源的Next-Admin的最新更新。最近对这个项目做了一些优化，并集成了大家比较关注...

干货|程序员的副业挂，12个平台分享

1、D2adminD2Admin是一个完全开源免费的企业中后台产品前端集成方案，使用最新的前端技术栈，小于60kb的本地首屏js加载，已经做好大部分项目前期准备工作，并且带有大量示例代码，助...

Github标星超200K，这10个可视化面板你知道几个

在Github上有很多开源免费的后台控制面板可以选择，但是哪些才是最好、最受欢迎的可视化控制面板呢？今天就和大家推荐Github上10个好看又流行的可视化面板：1.AdminLTEAdminLTE是...

开箱即用的炫酷中后台前端开源框架第二篇

#头条创作挑战赛#1、SoybeanAdmin(1)介绍：SoybeanAdmin是一个基于Vue3、Vite3、TypeScript、NaiveUI、Pinia和UnoCSS的清新优...

搭建React+AntDeign的开发环境和框架

搭建React+AntDeign的开发环境和框架随着前端技术的不断发展，React和AntDesign已经成为越来越多Web应用程序的首选开发框架。React是一个用于构建用户界面的JavaScrip...

基于.NET 5实现的开源通用权限管理平台

??大家好，我是为广大程序员兄弟操碎了心的小编，每天推荐一个小工具/源码，装满你的收藏夹，每天分享一个小技巧，让你轻松节省开发效率，实现不加班不熬夜不掉头发，是我的目标！??今天小编推荐一款基于.NE...

StreamPark - 大数据流计算引擎

使用Docker完成StreamPark的部署??1.基于h2和docker-compose进行StreamPark部署wgethttps://raw.githubusercontent.com/a...

教你使用UmiJS框架开发React

1、什么是Umi.js?umi，中文可发音为乌米，是一个可插拔的企业级react应用框架。你可以将它简单地理解为一个专注性能的类next.js前端框架，并通过约定、自动生成和解析代码等方式来辅助...

简单在线流程图工具在用例设计中的运用

敏捷模式下，测试团队的用例逐渐简化以适应快速的发版节奏，大家很早就开始运用思维导图工具比如xmind来编写测试方法、测试点。如今不少已经不少利用开源的思维导图组件（如百度脑图...）来构建测试测试...

【开源分享】神奇的大数据实时平台框架,让Flink&amp;Spark开发更简单

这是一个神奇的框架，让Flink|Spark开发更简单，一站式大数据实时平台！他就是StreamX！什么是StreamX大数据技术如今发展的如火如荼，已经呈现百花齐放欣欣向荣的景象，实时处理流域...

聊聊规则引擎的调研及实现全过程

摘要本期主要以规则引擎业务实现为例,陈述在陌生业务前如何进行业务深入、调研、技术选型、设计及实现全过程分析,如果你对规则引擎不感冒、也可以从中了解一些抽象实现过程。诉求从硬件采集到的数据提供的形式多种...

【开源推荐】Diboot 2.0.5 发布，自动化开发助理

一、前言Diboot2.0.5版本已于近日发布，在此次发布中，我们新增了file-starter组件，完善了iam-starter组件，对core核心进行了相关优化，让devtools也支持对IAM...

微软推出Copilot Actions，使用人工智能自动执行重复性任务

IT之家11月19日消息，微软在今天举办的Ignite大会上宣布了一系列新功能，旨在进一步提升Microsoft365Copilot的智能化水平。其中最引人注目的是Copilot...

Electron 使用Selenium和WebDriver

本节我们来学习如何在Electron下使用Selenium和WebDriver。SeleniumSelenium是ThoughtWorks提供的一个强大的基于浏览器的开源自动化测试工具...

Quick &#39;n Easy Web Builder 11.1.0设计和构建功能齐全的网页的工具

一个实用而有效的应用程序，能够让您轻松构建、创建和设计个人的HTML网站。Quick'nEasyWebBuilder是一款全面且轻巧的软件，为用户提供了一种简单的方式来创建、编辑...

一周热门
最近发表
标签列表