
ソシャゲのサーバーを作る。ユーザーのデータを設計する(10)
前回でクライアントがサーバに繋がりました。
ここまでで、ユーザーのデータ以外はひととおり揃っています。
今回はその残りです。
所持ヒーロー、デッキ、アイテム、進捗。
テーブルを一式作ります。
マスタとは性質が違う
7本目でマスタを17テーブル作りました。
マスターデータの前提はこうでした。
変わらない
全員共通
量が知れている
ユーザーのデータは全部逆です。
常に変わる
一人ひとり違う
人数ぶん増える
なのでメモリには載せません。
必要なときにDBを引いて、更新したら書き戻す。
8本目でやったことと正反対ですね。
余談ですが、
MMORPGとかになるとまたその逆になります(笑)
というのも常時接続なので、各プレイヤーのアクションのたびに都度DBから引いてたら、とんでもないことになります(マジで)
なので、ユーザーデータはログイン時などにサーバー上に読み込み、基本的にプレイヤーの操作でサーバー内のメモリを書き換え、非同期的DBに保存するという仕組みが多いかと思います。
そういった仕組みなので、サーバーが例外などで落ちると、保存していないものが消えます。
いわゆるデータの巻き戻りというやつが起きるわけです(泣)
さて、
同じ「テーブルを作る」でも、
考えることがまるで違ってきます。
分かりやすいのがSQLの書き方です。
マスタは総入れ替えなので、DROPしてから作り直します。
ユーザーのテーブルで同じことをしたら......
まぁヤバいですね、終了時刻未定のメンテナンスというやつが始まる予感しかしません(恐怖)
CREATE TABLE IF NOT EXISTS `user_status` (`IF NOT EXISTS` だけ。DROPは書きません。
1行も消せないテーブルなので。
先に全部作る
機能ごとに足していく作り方もあります。
ガチャを作るときに所持ヒーローのテーブルを足して、
編成を作るときにデッキのテーブルを足して。
今回は先に一式作ります。
理由はマスタのときと同じです。
後から足すと、そのたびに既存のテーブルを触ることになる。
「ここでこの列を追加」が積み重なると、
最終形がどうなっているのか誰も分からなくなります。
作るのは7つです。
テーブル 内容
user_status プレイヤーの現在の状態
user_currency 通貨の所持量
user_hero 所持ヒーロー
user_deck デッキ編成
user_item アイテム所持数
user_stage ステージ進捗
user_gacha_log ガチャ履歴3本目で作った `user` と `user_auth` はそのままです。
あちらは認証とアカウント管理、
今回のはゲーム上の状態、という分担ですね。

定義はSQLを直接書きました。
マスタのほうは自作のエディタで管理していますが、
ユーザー系のテーブルは値を入れるものではないので、定義だけSQLでつくります。
マスターデータエディタは、過去記事で仕様を載せてますので、気になった方はAIに作らせてもいいかもしれません。
巻末にCREATE TABLEを全部載せておきます。
所持ヒーローは個体で持つ
最初の判断はここです。
同じキャラを2体持ったとき、
1行で「2体持っている」とするか、
2行にして別々の個体とするか。
2行にします。
レベルも強化度合いも個体ごとに違うからですね。
1体はLv30まで育てて、もう1体は素材用にLv1のまま、ということができるように。
user_hero_id | user_id | hero_id | level
-------------|---------|---------|------
1 | 4 | 11 | 30
2 | 4 | 11 | 1`user_hero_id` はAUTO_INCREMENTです。
所持している1体ごとにIDが振られる。
重複を素材やポイントに変換するゲームも多いですが、今回のゲームでは増やします。
同じキャラを複数編成できるようにもしたいので。
デッキはスロットごとに1行
次はデッキです。
素直に作るなら、1デッキ1行ですね。
user_id | deck_id | hero1 | hero2 | hero3 | hero4これでも動きます。
ただ人数を変えたくなったときに詰みます。
運用中のALTERは基本的には御法度です。
レコードによって処理時間がかかる可能性があるのでメンテ時間との兼ね合いですが、なるべくやりません。
バックアップ取ってローカルに復元してそれに対して流しこんで時間計測したりもしますが、確実ではないので。
5人編成のイベントを作ろう、となったら列を足すことになる。
既存の行も全部触ります。
なのでスロットごとに1行にします。
user_id | deck_id | slot_no | user_hero_id
--------|---------|---------|-------------
4 | 1 | 0 | 1
4 | 1 | 1 | 5
4 | 1 | 2 | 8
4 | 1 | 3 | 124人なら4行、5人なら5行。
テーブルは変わりません。
参照しているのが `hero_id` ではなく `user_hero_id` なのが大事なところですね。
「11番のキャラ」ではなく「自分が持っている1番の個体」を編成している。
Lv30のほうを入れたのか、Lv1のほうを入れたのかが区別できます。
デッキは複数持てる作りにしました。
いま使っているのがどれかは `user_status` に持たせています。
スタミナは時間で回復する
スタミナは時間が経つと回復します。
ではその回復を、誰がいつ計算するのか。
定期的に全ユーザーを更新するという手はあります。
バッチを回して、30秒ごとに全員のスタミナを1増やす。
分かりやすいですが、当然プレイヤー数が多くなると、時間未定の上DB負荷が上がり、定期実行間隔内に終わらなければ、実行予定分がどんどん溜まり、後はもうおわかりですね(笑)
ログインしていない人のぶんまで更新することになります。
なのでアクセスがあった時に計算します。
持っているのは現在値と、最後に更新した時刻の2つだけ。
経過ミリ秒 = now - stamina_updated_at
回復量 = floor(経過 / 回復間隔)
新しい値 = min(現在値 + 回復量, 上限)アクセスがあったときにこれを計算して、書き戻す。
遊んでいない人のデータは触らない。
回復間隔は `game_config` に入れました。
前回まででマスタに定数を置く場所があるので、そこに足すだけですね。
上限は `player_level_curve` から引きます。
プレイヤーLvが上がると上限も増える、という作りです。
上限を超えて持てるようにしました
回復アイテムを使ったときですね。
上限が20のときに満タンから回復剤を使うと、25になったりする。
超えている間は時間回復しません。
減って上限を下回ったら、また回復が始まります。
起点の進め方が3通りあります
ここが今回いちばん細かいところでした。
状況 スタミナ 起点
既に上限以上 変えない now に進める
回復して上限に届いた 上限 now(端数は切り捨て)
上限に届かなかった 回復後の値 回復に使った分だけ進める3つ目が要注意です。
起点を now にしてしまうと、
次の1回分に足りなかった端数が毎回消えます。
70秒経ったなら60秒ぶんだけ進めて、10秒は残しておく。
1つ目も理由があります。
上限に張り付いている間、起点を進めておかないと、
消費して上限を割った瞬間に、
溜まっていた経過時間がまとめて回復に化けます。
実装は次回以降ですが、計算だけ先に書いておきました。
通貨は種別で分ける
`user_status` にゴールドを持たせてもよかったのですが、分けました。
user_id | currency_id | amountいまはゲーム内通貨1種だけです。
ただ何とかポイント、何とかメダル、そういうものは必ず増えます。
種別IDで持っておけば、増えても行が増えるだけですね。
有償通貨は入れません。
やるとしたら別の作りにします。
無償と有償を同じテーブルで扱うと、消費の順番や返金で厄介なことになるので。
マスタ側に `currency` を1つ足しました。
17テーブルが18テーブルになります。
残り3つ
アイテムは個数だけです。
ヒーローと違って個体差が無いので、1行で「回復剤5個」。
ステージ進捗はクリアしたものだけ行を作ります。
未クリアは行がない。
なのでクリア済みフラグの列は持ちません。
行があるかどうかがクリア済みかどうかなので、
フラグを持つと同じことを2箇所で表すことになります。
スコアの列は用意しましたが、中身はまだ決めていません。
バトルを作るときに、何をスコアとするか決めます。
ガチャ履歴は引いた分だけ残します。
10連なら10行ですね。
まとめて1行にすると、後から集計するときに面倒なので。
1つだけ、書き方で迷ったところがあります。
出たヒーローのレアリティを持つかどうかです。
hero_id があればマスタから引けるので、要らないように見えます。
ただマスタは入れ替わるんですよね。
レアリティを上げたり下げたりすると、
過去の履歴の見え方まで変わってしまう。
なので当時の値をそのまま残します。
天井は入れていないので、
いまのところ履歴を機能として使う予定はありませんが、それでも残します。
運営を始めると必ず見たくなるものなので。
初期データ
新規アカウントを作ったときに何を持たせるか。
ヒーローは配りません。
配ってもいいのですが、
最初にガチャを引かせたいので。
持っていない状態から1体目を引く、というのが最初の体験になります。
なのでガチャ1回分の通貨だけ入れます。
スタミナは満タン。
アイテムもデッキも行を作りません。
user_status Lv1 / 名前は「プレイヤー」/ スタミナ満タン
user_currency ガチャ1回分
それ以外 行なし名前の初期値を「プレイヤー」にしたのは、
名前入力を飛ばしても成立させたいからですね。
入力させるのは次回ですが、無くても動く状態にしておきます。
数値は直接書きません
スタミナの初期値もガチャ1回分の金額も、マスタから引いています。
/** 開始時の所持通貨。ガチャ1回分ちょうど */
function initialCurrencyAmount(): number {
const pool = master.get_gacha_pool(INITIAL_GACHA_POOL_ID);
...
return pool.cost_single;
}ガチャの値段を10から20に変えたとき、
初期付与だけ10のまま取り残される、というのを避けたいので。
企画がマスタを変えたら初期値も追従する形にしておきます。
あと、この初期データはユーザー作成と同じトランザクションで入れています。
途中で失敗したらユーザーごと無かったことにする。
初期データの無いユーザーを残さないためですね。
そういうユーザーが1人いるだけで、以降の実装で延々と例外処理が必要になります。
動かす
新規でサインアップして、中身を見てみます。
mysql> SELECT user_id, name, player_lv, stamina, active_deck_id FROM user_status;
+---------+------------+-----------+---------+----------------+
| user_id | name | player_lv | stamina | active_deck_id |
+---------+------------+-----------+---------+----------------+
| 10 | プレイヤー | 1 | 10 | 1 |
+---------+------------+-----------+---------+----------------+
mysql> SELECT * FROM user_currency;
+---------+-------------+--------+
| user_id | currency_id | amount |
+---------+-------------+--------+
| 10 | 1 | 10 |
+---------+-------------+--------+スタミナ10は `player_level_curve` のLv1の上限、
通貨10は `gacha_pool.cost_single` と同額です。
どちらもマスタから引いた値ですね。
`user_hero` は0行。ヒーローは持っていません。
同じ端末でもう一度サインアップしても、
既存ユーザーとして扱われるので初期データは増えません。
今回はここまで
ユーザー系のテーブルを7つ作った
所持ヒーローは個体ごとに1行。同じキャラを複数持てる
デッキはスロットごとに1行。人数を変えても壊れない
スタミナは引かれたときに計算する
通貨は種別で分けた。マスタは18テーブルに
初期データはガチャ1回分だけ。値はマスタから引く
次回は、ここまでで機能の作りこみができる土台が完成したので、これまでの振り返りをしたいと思います。
そのあと各機能の実装を入れていきます。
【最後まで読んでいただきありがとうございます!】
面白かったと感じた方は、ぜひ「スキ」と「フォロー」をお願いします!
テーブル定義
今回作ったテーブルの定義です、興味のある方はどうぞ。
user_tables_all.sql
-- api_game_user : ユーザーデータのテーブル一式
--
-- 1テーブル1ファイルで置いてあるものを、参照順に連結したもの。中身は個別ファイルと同じ。
-- 個別ファイルを直したら、このファイルにも同じ内容を反映すること。
--
-- 流し方:
-- docker compose exec -T db mysql -uroot -p"$MYSQL_ROOT_PASSWORD" api_game_user < db/api_game_user/_all.sql
--
-- マスタ側の _all.sql は DROP → CREATE → INSERT の全消し全入れだが、こちらは CREATE TABLE IF NOT EXISTS だけ。
-- ユーザーデータは1行も消せないので DROP を書かない。何度流してもテーブルもデータもそのまま残る。
--
-- `user` / `user_auth` はここに含めない。DBの作成と GRANT を伴うので auth.sql のまま分けてある。
-- このファイルを流す前に auth.sql を流しておくこと。
--
-- 並び順は参照関係から決めている。参照される側が先、参照する側が後。
-- 外部キー制約は張っていないので順番が違ってもエラーにはならないが、読む順として揃えておく。
SET NAMES utf8mb4;
USE `api_game_user`;
-- ---------- user_hero ----------
-- user_hero : 所持ヒーロー。個体ごとに1行
--
-- 重複入手はそのまま行が増える。素材やポイントへの変換はしない。
-- したがって同じ user_id で同じ hero_id の行が何行あってもよい。
--
-- 主キーは個体を指す user_hero_id。デッキやホーム設定が参照するのはこちらで、
-- マスタのヒーローID(hero_id)ではない。
-- ユーザーデータのIDなので AUTO_INCREMENT で採番する(マスタと逆)。
CREATE TABLE IF NOT EXISTS `user_hero` (
`user_hero_id` BIGINT NOT NULL AUTO_INCREMENT,
`user_id` BIGINT NOT NULL,
-- api_game_master の `hero`.`id`
`hero_id` BIGINT NOT NULL,
`level` INT NOT NULL DEFAULT 1,
-- 強化度合い。次のレベルまでの経験値
`exp` INT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user_hero_id`),
-- 所持一覧は必ず user_id で引く
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ---------- user_deck ----------
-- user_deck : デッキの編成。スロットごとに1行
--
-- 1デッキ1行(slot1〜slot4 の列を並べる形)にはしない。
-- デッキ人数は game_config.deck_size で変わるので、人数を列の数で表現すると
-- 人数を変えるたびに ALTER TABLE することになる。
--
-- デッキは複数持てる。deck_id は 1 始まり、slot_no は 0 始まり。
-- 空きスロットは行を作らない。
CREATE TABLE IF NOT EXISTS `user_deck` (
`user_id` BIGINT NOT NULL,
`deck_id` INT NOT NULL,
`slot_no` INT NOT NULL,
-- 編成されている個体。user_hero.user_hero_id を指す。
-- 同一デッキ内での重複はアプリ側で弾く(DBの制約では担保しない)。
-- 別デッキに同じ個体が入るのは許す
`user_hero_id` BIGINT NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`, `deck_id`, `slot_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ---------- user_status ----------
-- user_status : プレイヤーの現在の状態。user 1件につき1行
--
-- ここに置くのは「常に1つしかない値」だけ。数が増えるもの(通貨・ヒーロー・アイテム)は別テーブル。
-- user_id は `user`.`id` を指す。外部キーは張らない(整合性はアプリ側の責任)。
CREATE TABLE IF NOT EXISTS `user_status` (
`user_id` BIGINT NOT NULL,
`name` VARCHAR(32) NOT NULL,
`player_lv` INT NOT NULL DEFAULT 1,
`player_exp` INT NOT NULL DEFAULT 0,
-- 現在のスタミナ。上限(player_level_curve.stamina_max)を超えることがあるので、
-- ここでは上限を持たず、超過も素直に入れる
`stamina` INT NOT NULL,
-- 時間回復の起点。「最後に回復を計算した時刻」であって、最後に消費した時刻ではない
`stamina_updated_at` DATETIME NOT NULL,
-- 使用中のデッキ番号。user_deck.deck_id を指す。deck_id は 1 始まり
`active_deck_id` INT NOT NULL DEFAULT 1,
-- ホームに表示するキャラ。user_hero.user_hero_id を指す。未設定は 0
`home_user_hero_id` BIGINT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ---------- user_currency ----------
-- user_currency : 所持している通貨。種別ごとに1行
--
-- 通貨を user_status の列にしない。種類が増えるたびに ALTER TABLE するのを避けるため。
-- currency_id は api_game_master の `currency`.`id` を指す。
CREATE TABLE IF NOT EXISTS `user_currency` (
`user_id` BIGINT NOT NULL,
`currency_id` INT NOT NULL,
`amount` INT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`, `currency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ---------- user_item ----------
-- user_item : 所持アイテム。種別ごとに1行
--
-- アイテムには個体差が無いので、個数だけ持つ。user_hero のように1個1行にはしない。
-- item_id は api_game_master の `item`.`id` を指す。
CREATE TABLE IF NOT EXISTS `user_item` (
`user_id` BIGINT NOT NULL,
`item_id` BIGINT NOT NULL,
`amount` INT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`, `item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ---------- user_stage ----------
-- user_stage : ステージの進捗
--
-- クリアしたステージの行だけ作る。未クリアは行が無い。
-- 「行が無い=未クリア」なので、クリア済みフラグの列は持たない。
-- stage_id は api_game_master の `stage`.`id` を指す。
CREATE TABLE IF NOT EXISTS `user_stage` (
`user_id` BIGINT NOT NULL,
`stage_id` BIGINT NOT NULL,
-- 中身は未定。何をスコアにするか決まっていないので、箱だけ用意しておく
`score` INT NOT NULL DEFAULT 0,
-- 初回クリア日時。2回目以降のクリアでは更新しない
`cleared_at` DATETIME NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`, `stage_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ---------- user_gacha_log ----------
-- user_gacha_log : ガチャの排出履歴
--
-- 出たもの1つにつき1行。10連なら10行入る。
-- 「10連としてのまとまり」は持たせない。まとめて見せる必要が出たら created_at で寄せる。
-- 天井は入れないので、カウンタ用の列も持たない。
CREATE TABLE IF NOT EXISTS `user_gacha_log` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`user_id` BIGINT NOT NULL,
-- 引いたプール。api_game_master の `gacha_pool`.`id`
`pool_id` BIGINT NOT NULL,
-- 出たヒーロー。api_game_master の `hero`.`id`
`hero_id` BIGINT NOT NULL,
-- 出たレアリティ。api_game_master の `rarity`.`id`。
-- hero から辿れるが、マスタを入れ替えても当時の記録が変わらないようここに残す
`rarity_id` INT NOT NULL,
-- 付与した個体。user_hero.user_hero_id
`user_hero_id` BIGINT NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
-- 履歴は必ず user_id で引く。新しい順に出すので id も入れておく
KEY `idx_user_id` (`user_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;