账号数据库更新

前言

此次更新为账号数据库的更新。数据包括角色名、等级、体力、经验、金币等

数据库格式

这些储存在MySQL中。挂载到 MySQL 容器内部的/docker-entrypoint-initdb.d启用docker的时候自动建立。

-- 玩家角色表：基础信息，uid 关联 account.id
CREATE TABLE IF NOT EXISTS player (
    uid        BIGINT UNSIGNED PRIMARY KEY COMMENT '玩家ID = account.id',
    name       VARCHAR(64) NOT NULL DEFAULT '' COMMENT '角色名',
    level      INT         NOT NULL DEFAULT 1 COMMENT '等级',
    stamina    INT         NOT NULL DEFAULT 120 COMMENT '体力',
    exp        INT         NOT NULL DEFAULT 0 COMMENT '经验（战斗结算累积）',
    gold       INT         NOT NULL DEFAULT 0 COMMENT '金币（战斗结算累积）',
    created_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (uid) REFERENCES account (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- 战斗记录表：每场结算落一条，uid 关联 player.uid
CREATE TABLE IF NOT EXISTS battle_record (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '记录ID',
    uid          BIGINT UNSIGNED NOT NULL COMMENT '玩家ID',
    result       INT         NOT NULL COMMENT '0 胜 / 1 败',
    score        INT         NOT NULL DEFAULT 0 COMMENT '结算分数',
    duration_sec INT         NOT NULL DEFAULT 0 COMMENT '战斗时长（秒）',
    rewards      VARCHAR(255) NOT NULL DEFAULT '' COMMENT '奖励描述，如 exp:100,gold:500',
    created_at   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (uid) REFERENCES player (uid)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

数据库请求

在我们此前讲过的”bettle“的“room”文件中创建一条reportSettle 函数作为客户端连接 DataSave，发送 3000 写战斗记录请求并读取回包。

    body, err := json.Marshal(&datasave.SaveBattleRecordReq{
		UID:         uid,
		Result:      result,
		Score:       score,
		DurationSec: duration,
		Rewards:     rewards,
	})
	if err != nil {
		logger.Println("序列化战斗记录失败:", err)
		return
	}
	pkt, err := common.Encode(datasave.MsgSaveBattleRecord, 0, body)
	if err != nil {
		logger.Println("编码战斗记录失败:", err)
		return
	}

	conn, err := net.DialTimeout("tcp", dataSaveAddr, 2*time.Second)
	if err != nil {
		logger.Printf("连接datasave服务失败，战斗记录未落库: %v", err)
		return
	}
	defer conn.Close()
	_ = conn.SetDeadline(time.Now().Add(3 * time.Second))

	if _, err := conn.Write(pkt); err != nil {
		logger.Println("发送战斗记录失败:", err)
		return
	}

向DataASave发送请求后读取回包，检测是否落库成功。

    cache := &bytes.Buffer{}
	readBuf := make([]byte, 4096)
	for {
		n, err := conn.Read(readBuf)
		if err != nil {
			logger.Println("等待datasave回包失败:", err)
			return
		}
		cache.Write(readBuf[:n])

		p, err := common.Decode(cache)
		if err != nil {
			logger.Println("datasave回包解码失败:", err)
			return
		}
		if p == nil {
			continue
		}

		var resp datasave.SaveBattleRecordResp
		if err := json.Unmarshal(p.Payload, &resp); err != nil {
			logger.Println("解析datasave回包失败:", err)
			return
		}
		if resp.Code != 0 {
			logger.Printf("战斗记录落库失败：code=%d msg=%s", resp.Code, resp.Msg)
			return
		}
		logger.Printf("战斗记录落库成功：uid=%d exp+%d gold+%d", uid, resp.ExpGain, resp.GoldGain)
		return
	}

战斗的服务文件

DataSave 服务：内部服务，接收其他服务（如 battle）的写库请求。

内部协议沿用 common.Encode/Decode 的包格式，负载用 JSON（不对客户端暴露，省去 proto 代码生成）

先定义几个MsgId

const (
	MsgSaveBattleRecord uint16 = 3000 // 写战斗记录并累加 exp/gold
	MsgDeductStamina    uint16 = 3001 // 扣体力
	MsgGetRole          uint16 = 3002 // 查角色

	msgSaveBattleRecordResp uint16 = 3100
	msgDeductStaminaResp    uint16 = 3101
	msgGetRoleResp          uint16 = 3102
)

在链接上数据库后开始处理内部请求，然后就是handleConn解码、按MsgId分发、回包（和之前几篇的handleConn差不多就不花费篇幅了）。

// Serve 接受连接并处理内部请求；阻塞运行
func Serve(db *sql.DB, listener net.Listener) {
	s := &server{db: db}
	for {
		conn, err := listener.Accept()
		if err != nil {
			continue
		}
		go s.handleConn(conn)
	}
}

MsgId=3000就是写一条战斗记录，并在同一事务里累加玩家 exp/gold。

先从请求数据中解析。利用另外一个函数获得经验、金币的获得数量

    var req SaveBattleRecordReq
	if err := json.Unmarshal(payload, &req); err != nil {
		logger.Println("解析 SaveBattleRecordReq 失败:", err)
		return &SaveBattleRecordResp{Code: codeBadParam, Msg: "请求格式错误"}
	}
	if req.UID == 0 {
		return &SaveBattleRecordResp{Code: codeBadParam, Msg: "uid 不能为 0"}
	}
	expGain, goldGain := parseRewards(req.Rewards)

parseRewards这个函数是 从 "exp:100"、"gold:500" 形式的奖励描述里解析累加值

func parseRewards(rewards []string) (exp, gold int32) {
	for _, r := range rewards {
		k, v, ok := strings.Cut(r, ":")
		if !ok {
			continue
		}
		n, err := strconv.Atoi(strings.TrimSpace(v))
		if err != nil {
			continue
		}
		switch strings.TrimSpace(k) {
		case "exp":
			exp += int32(n)
		case "gold":
			gold += int32(n)
		}
	}
	return exp, gold
}

继续我们要开启事务对象

tx, err := s.db.Begin()
	if err != nil {
		logger.Println("开启事务失败:", err)
		return &SaveBattleRecordResp{Code: codeServerError, Msg: "服务器繁忙"}
	}
	defer tx.Rollback()

再确认玩家存在，避免外键失败时错误信息含糊

    var exist int
	if err := tx.QueryRow("SELECT 1 FROM player WHERE uid = ?", req.UID).Scan(&exist); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			logger.Printf("写战斗记录失败：玩家 uid=%d 不存在", req.UID)
			return &SaveBattleRecordResp{Code: codeNotFound, Msg: "玩家不存在"}
		}
		logger.Println("查询玩家失败:", err)
		return &SaveBattleRecordResp{Code: codeServerError, Msg: "服务器繁忙"}
	}

之后写入战斗记录和累加玩家奖励。并提交事务。

	rewards := strings.Join(req.Rewards, ",")
	if _, err := tx.Exec(
		"INSERT INTO battle_record (uid, result, score, duration_sec, rewards) VALUES (?, ?, ?, ?, ?)",
		req.UID, req.Result, req.Score, req.DurationSec, rewards,
	); err != nil {
		logger.Println("写入战斗记录失败:", err)
		return &SaveBattleRecordResp{Code: codeServerError, Msg: "服务器繁忙"}
	}

	if _, err := tx.Exec(
		"UPDATE player SET exp = exp + ?, gold = gold + ? WHERE uid = ?",
		expGain, goldGain, req.UID,
	); err != nil {
		logger.Println("累加玩家奖励失败:", err)
		return &SaveBattleRecordResp{Code: codeServerError, Msg: "服务器繁忙"}
	}

	if err := tx.Commit(); err != nil {
		logger.Println("提交事务失败:", err)
		return &SaveBattleRecordResp{Code: codeServerError, Msg: "服务器繁忙"}
	}

handleDeductStamina 函数是原子扣体力：stamina >= amount 才更新，不足则回包剩余体力。由于这里只用一条SQL语句所以就不用启动事务了。

	res, err := s.db.Exec(
		"UPDATE player SET stamina = stamina - ? WHERE uid = ? AND stamina >= ?",
		req.Amount, req.UID, req.Amount,
	)
	if err != nil {
		logger.Println("扣除体力失败:", err)
		return &DeductStaminaResp{Code: codeServerError, Msg: "服务器繁忙"}
	}
	n, err := res.RowsAffected()
	if err != nil {
		logger.Println("读取受影响行数失败:", err)
		return &DeductStaminaResp{Code: codeServerError, Msg: "服务器繁忙"}
	}

n, err := res.RowsAffected()是用于检测是否玩家存在且体力 ≥ 扣减量，如果是，则n=1，否则，n=0.

如果n=0，就查询剩余体力并回包。

	if n == 0 {
		var stamina int32
		err := s.db.QueryRow("SELECT stamina FROM player WHERE uid = ?", req.UID).Scan(&stamina)
		if errors.Is(err, sql.ErrNoRows) {
			return &DeductStaminaResp{Code: codeNotFound, Msg: "玩家不存在"}
		}
		if err != nil {
			logger.Println("查询体力失败:", err)
			return &DeductStaminaResp{Code: codeServerError, Msg: "服务器繁忙"}
		}
		return &DeductStaminaResp{Code: codeStaminaNotEnough, Msg: "体力不足", Remaining: stamina}
	}

如果n=1，就发送体力成功扣除的回包。

	var stamina int32
	if err := s.db.QueryRow("SELECT stamina FROM player WHERE uid = ?", req.UID).Scan(&stamina); err != nil {
		logger.Println("查询剩余体力失败:", err)
		return &DeductStaminaResp{Code: codeServerError, Msg: "服务器繁忙"}
	}
	logger.Printf("扣除体力：uid=%d -%d，剩余%d", req.UID, req.Amount, stamina)
	return &DeductStaminaResp{Code: codeOK, Msg: "ok", Remaining: stamina}

最后是handleGetRole 函数，查角色基础信息。很简单，也是和上面的SQL语句的查询差不多。

	var resp GetRoleResp
	err := s.db.QueryRow(
		"SELECT name, level, stamina, exp, gold FROM player WHERE uid = ?", req.UID,
	).Scan(&resp.Name, &resp.Level, &resp.Stamina, &resp.Exp, &resp.Gold)
	...
	resp.Code = codeOK
	resp.Msg = "ok"
	resp.UID = req.UID
	return &resp

好了，今天到这里。我还要去做6级试卷。