创建数据库:CREATE DATABASE `game_s1`;
在game数据库source game_db.sql:
CREATE TABLE IF NOT EXISTS `account` ( `accountid` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '账号ID,唯一,自增', `accountname` VARCHAR(64) NOT NULL COMMENT '账号名', `passwd` VARCHAR(32) NOT NULL COMMENT '密码', `createtime` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '账号创建时间', `lastlogintime` DATETIME COMMENT '上次登录时间', `lastlogouttime` DATETIME COMMENT '上次登出时间', `status` INT DEFAULT 0 COMMENT '状态 0正常 1被封禁', PRIMARY KEY (`accountid`), UNIQUE KEY `uk_accountname` (`accountname`) ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS `role` ( `accountid` INT UNSIGNED NOT NULL COMMENT '角色所属账号ID', `roleid` BIGINT NOT NULL COMMENT '角色ID', `rolename` VARCHAR(64) NOT NULL COMMENT '角色名字', `createtime` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '角色创建时间', `serverid` INT DEFAULT 0 COMMENT '当前所在的服务器ID,合服会改变该值', `bornserverid` INT DEFAULT 0 COMMENT '创建时的服务器ID', `status` INT DEFAULT 1 COMMENT '等于0:已删除,第0位:被封禁,第1位:有效,第2位:首选,第3位:是否在线', `level` INT DEFAULT 1 COMMENT '等级', `exp` BIGINT DEFAULT 0 COMMENT '经验', `data` MEDIUMBLOB DEFAULT NULL COMMENT '角色其他数据,json格式', PRIMARY KEY (`roleid`), INDEX `idx_accountid` (`accountid`), INDEX `idx_rolename` (`rolename`) ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS `currency` ( `roleid` BIGINT NOT NULL COMMENT '货币所属角色ID', `currencytype` INT UNSIGNED NOT NULL COMMENT '货币类型', `currencyvalue` BIGINT DEFAULT 0 COMMENT '货币值', PRIMARY KEY (`roleid`, `currencytype`) ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS `bagitem` ( `roleid` BIGINT NOT NULL COMMENT '背包物品所属角色ID', `bagid` INT NOT NULL COMMENT '背包ID', `guid` BIGINT NOT NULL COMMENT '物品的唯一ID', `itemid` INT NOT NULL COMMENT '物品类型ID', `count` INT DEFAULT 0 COMMENT '数量', `bind` INT DEFAULT 0 COMMENT '是否绑定', `expirationtime` DATETIME DEFAULT 0 COMMENT '过期时间', `attrs` BLOB DEFAULT NULL COMMENT '特殊属性', INDEX `idx_roleid` (`roleid`) ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4; /****************************************/ /* create procedure */ /****************************************/ DROP PROCEDURE IF EXISTS `createnewrole`; DELIMITER $$ CREATE PROCEDURE `createnewrole` ( IN _accountid INT UNSIGNED, IN _roleid BIGINT, IN _rolename VARCHAR(64), IN _serverid INT, IN _bornserverid INT) BEGIN DECLARE nowcount INT DEFAULT 0; SET nowcount = (SELECT COUNT(*) FROM `role` WHERE `status`<>0 AND `accountid`=_accountid AND `serverid`=_serverid AND `bornserverid`=_bornserverid); IF nowcount < 3 THEN INSERT INTO `role`(`accountid`,`roleid`,`rolename`,`serverid`,`bornserverid`,`status`) VALUES(_accountid,_roleid,_rolename,_serverid,_bornserverid,2); END IF; END $$ DELIMITER ; -- CALL `createnewrole`(1,1,'rose',1,1);