MySQL存储过程实现游戏合服

最新推荐文章于 2021-02-02 11:11:47 发布

铠甲蘑菇

最新推荐文章于 2021-02-02 11:11:47 发布

阅读量1k

点赞数 1

CC 4.0 BY-SA版权

分类专栏：游戏 mysql 文章标签：存储过程 PROCEDURE 合服 SQL脚本游戏

本文链接：https://blog.youkuaiyun.com/chenyun19890626/article/details/50804274

本文介绍如何使用MySQL的存储过程来实现游戏服务器的合服操作。通过提供的DROP和CREATE PROCEDURE语句，展示了创建名为`combine`的存储过程，该过程接受两个参数：fromdbs和todb，用于指定源数据库和目标数据库名称。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DELIMITER $$

DROP PROCEDURE IF EXISTS `combine`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `combine`(IN fromdbs VARCHAR(200),IN todb VARCHAR(30))

BEGIN
DECLARE dsize INT DEFAULT 0;
DECLARE lastLoginId BIGINT DEFAULT -1;
DECLARE currentUUID BIGINT DEFAULT -1;
DECLARE currentLoginId BIGINT DEFAULT -1;
DECLARE done INT DEFAULT FALSE;
-- 遍历playercache 通过 loginId,lv,gsPoint,uuid降序
DECLARE cur1 CURSOR FOR SELECT `uuid`,loginid FROM playercache ORDER BY loginId DESC, lv DESC,gsPoint DESC,`uuid` DESC;
-- 遍历废弃玩家，条件是等级小于20 ，30天类没有升级
DECLARE cur2 CURSOR FOR SELECT `uuid` FROM playercache WHERE lv<20 AND (UNIX_TIMESTAMP(NOW())-(lvUpTime/1000))>30*24*60*60;
-- 遍历有效玩家
DECLARE cur3 CURSOR FOR SELECT `uuid` FROM playercache;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET @needDeletes ='';
SET @count=CHAR_LENGTH(fromdbs)-CHAR_LENGTH(REPLACE(fromdbs,',','')) + 1;
SET @i=1;
ALTER TABLE pay MODIFY id BIGINT;
WHILE @i <= @count
DO
SET @fromdb=SUBSTRING_INDEX(SUBSTRING_INDEX(fromdbs,',',@i),',',-1);
SET @tail=SUBSTRING(@fromdb,5);
#合并bags表
SET @sql_bags=CONCAT('INSERT INTO ',todb,'.bags SELECT * FROM ',@fromdb,'.bags');
PREPARE pre_bags FROM @sql_bags;
EXECUTE pre_bags;
DEALLOCATE PREPARE pre_bags;
-- 合并friends表
SET @sql_friends=CONCAT('INSERT INTO ',todb,'.friends SELECT * FROM ',@fromdb,'.friends');
PREPARE pre_friends FROM @sql_friends;
EXECUTE pre_friends;
DEALLOCATE PREPARE pre_friends;
-- 合并heros表
SET @sql_heros=CONCAT('INSERT INTO ',todb,'.heros SELECT * FROM ',@fromdb,'.heros');
PREPARE pre_heros FROM @sql_heros;
EXECUTE pre_heros;
DEALLOCATE PREPARE pre_heros;
#合并mails表
SET @sql_mails=CONCAT('INSERT INTO ',todb,'.