buff3.0/docs/steam_account_migrations_execute.sql

/*
 ============================================================
 Steam 账号相关：表结构修改 + 新表
 在目标库执行前请确认当前库中 steam_accounts 结构，按需执行对应部分。
 ============================================================
 一、表结构修改（已有旧版 steam_accounts / steam_accounts_his 时执行）
 二、新表：每用户 Steam 账号数量上限
 ============================================================
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- 一、steam_accounts 表结构修改（精简：删除敏感字段）
-- 仅当表中存在 shared_secret、identity_secret 时执行，否则会报错
-- ============================================================
ALTER TABLE `steam_accounts`
  DROP COLUMN `shared_secret`,
  DROP COLUMN `identity_secret`;

-- 若表中还有 last_used_time 列，可取消下面注释执行：
-- ALTER TABLE `steam_accounts` DROP COLUMN `last_used_time`;

-- ============================================================
-- 一、steam_accounts_his 表结构修改（与主表一致精简）
-- 仅当该表存在且含有 shared_secret、identity_secret 时执行
-- ============================================================
-- ALTER TABLE `steam_accounts_his`
--   DROP COLUMN `shared_secret`,
--   DROP COLUMN `identity_secret`;
-- 若还有 last_used_time：
-- ALTER TABLE `steam_accounts_his` DROP COLUMN `last_used_time`;

-- ============================================================
-- 二、新表：每用户 Steam 账号绑定数量上限
-- 每个用户一条记录，默认 3；管理员可为不同用户设不同上限
-- ============================================================
DROP TABLE IF EXISTS `user_steam_account_limit`;
CREATE TABLE `user_steam_account_limit` (
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `max_steam_accounts` int(11) NOT NULL DEFAULT 3 COMMENT '该用户最多可绑定的 Steam 账号数',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`user_id`),
  KEY `idx_max_steam_accounts` (`max_steam_accounts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='每用户Steam账号绑定数量上限';

SET FOREIGN_KEY_CHECKS = 1;

/*
 ============================================================
 【可选】若需从零建表或按新结构重建 steam_accounts / steam_accounts_his
 请单独执行下面整段（会 DROP 再 CREATE，数据会清空）
 ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `steam_accounts_his`;
DROP TABLE IF EXISTS `steam_accounts`;

CREATE TABLE `steam_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `steam_username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Steam用户名',
  `steam_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Steam ID（来自登录验证）',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用：1-启用，0-禁用',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '绑定时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_steam_username` (`steam_username`),
  KEY `idx_is_deleted` (`is_deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Steam账号绑定表（仅账号信息，敏感数据存本地）';

CREATE TABLE `steam_accounts_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `steam_username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Steam用户名',
  `steam_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Steam ID',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  `created_at` datetime DEFAULT NULL COMMENT '绑定时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型：INSERT/UPDATE/DELETE',
  PRIMARY KEY (`id`,`his_created_at`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Steam账号绑定表历史';

DROP TABLE IF EXISTS `user_steam_account_limit`;
CREATE TABLE `user_steam_account_limit` (
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `max_steam_accounts` int(11) NOT NULL DEFAULT 3 COMMENT '该用户最多可绑定的 Steam 账号数',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`user_id`),
  KEY `idx_max_steam_accounts` (`max_steam_accounts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='每用户Steam账号绑定数量上限';

SET FOREIGN_KEY_CHECKS = 1;

*/