SQL_game_db
2022-01-01 23:20:22

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 '賬號創(chuàng)建時間',
    `lastlogintime` DATETIME COMMENT '上次登錄時間',
    `lastlogouttime` DATETIME COMMENT '上次登出時間',
    `status` INT DEFAULT 0 COMMENT '狀態(tài) 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 '角色創(chuàng)建時間',
    `serverid` INT DEFAULT 0 COMMENT '當(dāng)前所在的服務(wù)器ID，合服會改變該值',
    `bornserverid` INT DEFAULT 0 COMMENT '創(chuàng)建時的服務(wù)器ID',
    `status` INT DEFAULT 1 COMMENT '等于0:已刪除，第0位:被封禁，第1位:有效，第2位:首選，第3位:是否在線',
    `level` INT DEFAULT 1 COMMENT '等級',
    `exp` BIGINT DEFAULT 0 COMMENT '經(jīng)驗',
    `data` MEDIUMBLOB DEFAULT NULL COMMENT '角色其他數(shù)據(jù)，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 '數(shù)量',
    `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);