合服

compound.sql:

DROP PROCEDURE IF EXISTS compound_server_dbfrom_to_dbto;
DELIMITER $$
CREATE PROCEDURE compound_server_dbfrom_to_dbto()
BEGIN
　　DECLARE done INT DEFAULT 0;
　　DECLARE from_role_id BIGINT;
　　DECLARE from_account_id BIGINT;
　　DECLARE from_role_name VARCHAR(32);
　　DECLARE from_account_name VARCHAR(64);
　　DECLARE to_account_id BIGINT DEFAULT 0;

　　DECLARE from_guild_id BIGINT;
　　DECLARE from_guild_name VARCHAR(64);

　　DECLARE guild_same_name INT DEFAULT 0;
　　DECLARE guild_same_name_id BIGINT DEFAULT 0;
　　DECLARE hero_same_name INT DEFAULT 0;
　　DECLARE hero_same_name_id BIGINT DEFAULT 0;

　　-- 找到族长的工会ID
　　DECLARE hsn_guild_id BIGINT DEFAULT 0;
　　DECLARE hsn_guild_duty INT DEFAULT 3;

　　DECLARE hero_cursor CURSOR FOR SELECT id, account, name FROM herodbfrom.heros;
　　DECLARE guild_cursor CURSOR FOR SELECT guild_id, guild_name FROM herodbfrom.guild;
　　DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

　　-- hero_ext_data
　　INSERT INTO herodbto.hero_ext_data (SELECT * FROM herodbfrom.hero_ext_data);
　　-- hero_ext_json
　　INSERT INTO herodbto.hero_ext_json (SELECT * FROM herodbfrom.hero_ext_json);
　　-- hero_item
　　INSERT INTO herodbto.hero_item (SELECT * FROM herodbfrom.hero_item);
　　-- hero_json
　　INSERT INTO herodbto.hero_json (SELECT * FROM herodbfrom.hero_json);
　　-- hero_mail
　　INSERT INTO herodbto.hero_mail (SELECT * FROM herodbfrom.hero_mail);
　　-- hero_skill
　　INSERT INTO herodbto.hero_skill (SELECT * FROM herodbfrom.hero_skill);
　　-- hero_task
　　INSERT INTO herodbto.hero_task (SELECT * FROM herodbfrom.hero_task);
　　-- hero_task_json
　　INSERT INTO herodbto.hero_task_json (SELECT * FROM herodbfrom.hero_task_json);
　　-- pet
　　INSERT INTO herodbto.pet (SELECT * FROM herodbfrom.pet);
　　-- mine (need change name)
　　INSERT INTO herodbto.mine (SELECT * FROM herodbfrom.mine);
　　-- guild_member (need change name)
　　INSERT INTO herodbto.guild_member (SELECT * FROM herodbfrom.guild_member);

　　-- guild
　　SET done = 0;
　　OPEN guild_cursor;
　　FETCH guild_cursor INTO from_guild_id, from_guild_name;
　　WHILE done != 1 DO
　　　　SET guild_same_name = 0;
　　　　SET guild_same_name_id = 0;

　　　　-- 重名检测
　　　　SELECT guild_id INTO guild_same_name_id FROM herodbto.guild WHERE guild_name = from_guild_name limit 1;
　　　　IF guild_same_name_id != 0 THEN
　　　　　　SET guild_same_name = 1;
　　　　END IF;

　　　　INSERT INTO herodbto.guild (SELECT * FROM herodbfrom.guild WHERE guild_id = from_guild_id);

　　　　IF guild_same_name = 1 THEN
　　　　　　UPDATE herodbto.guild SET guild_name = CONCAT(from_guild_name, "dbfrom") WHERE guild_id = from_guild_id;
　　　　END IF;

　　　　SET done = 0;
　　　　FETCH guild_cursor INTO from_guild_id, from_guild_name;
　　END WHILE;
　　CLOSE guild_cursor;

　　-- heros
　　SET done = 0;
　　OPEN hero_cursor;
　　FETCH hero_cursor INTO from_role_id, from_account_id, from_role_name;
　　WHILE done != 1 DO
　　　　SET to_account_id = 0;
　　　　SET hero_same_name = 0;
　　　　SET hero_same_name_id = 0;

　　　　-- 重名检测
　　　　SELECT id INTO hero_same_name_id FROM herodbto.heros WHERE name = from_role_name limit 1;
　　　　IF hero_same_name_id != 0 THEN
　　　　　　SET hero_same_name = 1;
　　　　END IF;

　　　　SELECT account INTO from_account_name FROM accountgcdbfrom.accounts WHERE id = from_account_id limit 1;
　　　　SELECT id INTO to_account_id FROM accountgcdbto.accounts WHERE account = from_account_name limit 1;

　　　　IF to_account_id != 0 THEN
　　　　　　INSERT INTO herodbto.heros (SELECT * FROM herodbfrom.heros WHERE id = from_role_id);
　　　　　　UPDATE herodbto.heros SET account = to_account_id WHERE id = from_role_id;
　　　　ELSE
　　　　　　INSERT INTO accountgcdbto.accounts (SELECT * FROM accountgcdbfrom.accounts WHERE id = from_account_id);
　　　　　　INSERT INTO herodbto.heros (SELECT * FROM herodbfrom.heros WHERE id = from_role_id);
　　　　END IF;

　　　　IF hero_same_name = 1 THEN
　　　　　　UPDATE herodbto.heros SET name = CONCAT(from_role_name, "dbfrom") WHERE id = from_role_id;
　　　　　　UPDATE herodbto.mine SET name = CONCAT(from_role_name, "dbfrom") WHERE heroID = from_role_id;
　　　　　　UPDATE herodbto.guild_member SET mem_name = CONCAT(from_role_name, "dbfrom") WHERE mem_id = from_role_id;

　　　　　　-- 找到族长的工会ID
　　　　　　SET hsn_guild_id = 0;
　　　　　　SET hsn_guild_duty = 3;
　　　　　　SELECT guild_id, mem_duty INTO hsn_guild_id, hsn_guild_duty FROM herodbfrom.guild_member WHERE mem_id = from_role_id limit 1;
　　　　　　IF hsn_guild_id != 0 THEN
　　　　　　　　IF hsn_guild_duty = 0 THEN
　　　　　　　　　　UPDATE herodbto.guild SET guild_master = CONCAT(from_role_name, "dbfrom") WHERE guild_id = hsn_guild_id;
　　　　　　　　END IF;
　　　　　　END IF;
　　　　END IF;

　　　　SET done = 0;
　　　　FETCH hero_cursor INTO from_role_id, from_account_id, from_role_name;
　　END WHILE;
　　CLOSE hero_cursor;
END
$$

DELIMITER ;
CALL compound_server_dbfrom_to_dbto();

涉及到了不同的数据库，但是在函数中执行拼接的sql语句不行，所以用批处理把上面dbfrom和dbto替换成相应的数据库名字，批处理为compound.bat。

compound.bat：

 

@echo off
:: setlocal enabledelayedexpansion

set fn=compound.sql
set tem_sql_file="compound_%1_to_%2.sql"

(for /f "tokens=* delims=" %%i in ('type "%fn%"') do (
    set "var=%%i"
    call set "var=%%var:dbfrom=%1%%"　　:: 使用delayexpansin不行，文本中的叹号会被略过，所以使用2个%%
    call set "var=%%var:dbto=%2%%"
    call echo %%var%%
))>%tem_sql_file%

C:
cd C:\Program Files\MySQL\MySQL Server 5.7\bin\
@echo on
mysql -uroot -p123456 -Dhero%2 --default-character-set=utf8<D:\shell\%tem_sql_file%

del D:\shell\%tem_sql_file%

 

调用批处理的bat为compound_2_to_1.bat

compound_2_to_1.bat:

 

@echo off

D:
cd D:\game\db\data_compound
compound.bat 2 1

pause