遊戲隨著運營時間變成導致DAU變少。尤其是手遊採用袞服的開服策略。導致伺服器合服變得更加頻繁且要求高效。合服的目的有二。其一是降低伺服器成本，其二利於玩家之間的相互競爭。曾經合服採用的方法都是透過研發程式去實現。按功能把所有表資料載入到記憶體。透過邏輯關聯實現。最後把新資料寫入資料庫。印象中最大的痛點是合服程式碼維護和合服過程低效。

如何解決痛點？因為自己本身對資料庫儲存過程感興趣且比較擅長。結合遊戲研發中的特點思考如何使用儲存過程實現合服的目的。資料庫也是一種強大的語言。具體可參閱。[如何檢視錶中的二進位制流](http://blog.itpub.net/69975060/viewspace-2692910/)。完全可以實現強大的邏輯功能。

具體結合遊戲特徵說明下原理和思路。

1. 為了能合併中出現意外等問題。確保不影響原始資料。一定要克隆出資料庫。加上_bak的標識。克隆資料庫時一定要把表結構完全複製好。包括主鍵，索引等。最終使用的庫就是第一個XXX_bak對應的庫。具體參考CloneDbFun函式。
2. 在第一個XXX_bak對應的庫中刪除不需要的表資料。也就是合服時可以清除的資料。比如:活動，特殊功能等。具體參考DealDbDataFun1自定義函式。
3. 合服中最關鍵的是表的主鍵衝突問題。為了避免合服出現這樣的問題。遊戲邏輯中就提前考慮和避免了。生成全服唯一的主鍵。具體可參閱。[預分配法生成唯一ID](http://blog.itpub.net/69975060/viewspace-2692901/)。即使有些主鍵可能存在衝突的問題。也可以透過邏輯處理了。具體參考函式DealOnlyCopyDbDataFun和DealDbDataFun3就直接搞定。一些特殊的可以擴充套件豐富。
4. 採用儲存過程就是合服操作簡潔。維護合服指令碼簡單。最重要的是執行效率更高。大大縮短合服時間。在實際使用中非常有效。直接非常推崇的最佳方案。

文章提供儲存過程指令碼。可直接複製下來。根據自己遊戲表情況。直接修改可用於專案中。關於遊戲開發中的問題歡迎大家留言溝通。共同分享自己的知識。

Shell指令碼：LinkServer.sh

```shell
#!/bin/bash

mysql -uroot -pzjuNT -h 10.6.10.50 -P 3306 -Dtest<linkserversql.sql mysql="" set="" character_set_client="utf8;" character_set_connection="utf8;" delimiter="" drop="" procedure="" if="" exists="" create="" mainserverid0="" varchar="" srcetablename0="" tempcolumnstr0="" begin="" declare="" i0="" int="" default="" srcetablecount0="" tempdbnamesql0="" tempcolumnname0="" checksrcesql0="" runsrcesql0="" count="" into="" from="" information_schema.columns="" where="" table_name="",srceTableName0,"" and="" table_schema="",@tempDbNameSql0,"" prepare="" stmt="" execute="" column_name="" limit="" while="" do="">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);
```</linkserversql.sql>

遊戲合服新方案

相關文章