當(dāng)前位置:首頁(yè) > IT技術(shù) > 數(shù)據(jù)庫(kù) > 正文

SQL_game_db
2022-01-01 23:20:22

創(chuàng)建數(shù)據(jù)庫(kù):CREATE DATABASE `game_s1`;

在game數(shù)據(jù)庫(kù)source game_db.sql:

CREATE TABLE IF NOT EXISTS `account` (
    `accountid` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '賬號(hào)ID,唯一,自增',
    `accountname` VARCHAR(64) NOT NULL COMMENT '賬號(hào)名',
    `passwd` VARCHAR(32) NOT NULL COMMENT '密碼',
    `createtime` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '賬號(hào)創(chuàng)建時(shí)間',
    `lastlogintime` DATETIME COMMENT '上次登錄時(shí)間',
    `lastlogouttime` DATETIME COMMENT '上次登出時(shí)間',
    `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 '角色所屬賬號(hào)ID',
    `roleid` BIGINT NOT NULL COMMENT '角色I(xiàn)D',
    `rolename` VARCHAR(64) NOT NULL COMMENT '角色名字',
    `createtime` DATETIME DEFAULT CURRENT_TIMESTAMP    COMMENT '角色創(chuàng)建時(shí)間',
    `serverid` INT DEFAULT 0 COMMENT '當(dāng)前所在的服務(wù)器ID,合服會(huì)改變?cè)撝?/span>',
    `bornserverid` INT DEFAULT 0 COMMENT '創(chuàng)建時(shí)的服務(wù)器ID',
    `status` INT DEFAULT 1 COMMENT '等于0:已刪除,第0位:被封禁,第1位:有效,第2位:首選,第3位:是否在線',
    `level` INT DEFAULT 1 COMMENT '等級(jí)',
    `exp` BIGINT DEFAULT 0 COMMENT '經(jīng)驗(yàn)',
    `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 '貨幣所屬角色I(xiàn)D',
    `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 '背包物品所屬角色I(xiàn)D',
    `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 '過(guò)期時(shí)間',
    `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);

?

本文摘自 :https://www.cnblogs.com/

開(kāi)通會(huì)員,享受整站包年服務(wù)立即開(kāi)通 >