SQL_game_db

创建数据库：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);