93 lines
5.5 KiB
SQL
93 lines
5.5 KiB
SQL
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE hyapp_user;
|
||
|
||
-- 靓号管理把展示号从纯数字短号扩展为可发放的展示字符串，默认短号生成规则仍由业务代码保持纯数字。
|
||
ALTER TABLE users
|
||
MODIFY COLUMN default_display_user_id VARCHAR(64) NOT NULL COMMENT '默认展示用户 ID',
|
||
MODIFY COLUMN current_display_user_id VARCHAR(64) NOT NULL COMMENT '当前展示用户 ID';
|
||
|
||
ALTER TABLE user_display_user_ids
|
||
MODIFY COLUMN display_user_id VARCHAR(64) NOT NULL COMMENT '展示用户 ID',
|
||
MODIFY COLUMN live_display_user_id VARCHAR(64)
|
||
GENERATED ALWAYS AS (
|
||
CASE
|
||
WHEN status IN ('active', 'held', 'reserved', 'blocked') THEN display_user_id
|
||
ELSE NULL
|
||
END
|
||
) STORED COMMENT '实时展示用户 ID';
|
||
|
||
ALTER TABLE pretty_display_user_id_leases
|
||
MODIFY COLUMN display_user_id VARCHAR(64) NOT NULL COMMENT '展示用户 ID',
|
||
MODIFY COLUMN active_display_user_id VARCHAR(64)
|
||
GENERATED ALWAYS AS (
|
||
CASE WHEN status = 'active' THEN display_user_id ELSE NULL END
|
||
) STORED COMMENT '启用展示用户 ID',
|
||
ADD COLUMN released_at_ms BIGINT NOT NULL DEFAULT 0 COMMENT '释放时间，UTC epoch ms' AFTER expires_at_ms,
|
||
ADD COLUMN release_reason VARCHAR(64) NOT NULL DEFAULT '' COMMENT '释放原因' AFTER released_at_ms;
|
||
|
||
ALTER TABLE display_user_id_change_logs
|
||
MODIFY COLUMN old_display_user_id VARCHAR(64) NOT NULL COMMENT '原展示用户 ID',
|
||
MODIFY COLUMN new_display_user_id VARCHAR(64) NOT NULL COMMENT '新展示用户 ID';
|
||
|
||
CREATE TABLE IF NOT EXISTS pretty_display_id_pools (
|
||
app_code VARCHAR(32) NOT NULL DEFAULT 'lalu' COMMENT '应用编码，用于多租户隔离',
|
||
pool_id VARCHAR(64) NOT NULL PRIMARY KEY COMMENT '靓号池 ID',
|
||
name VARCHAR(128) NOT NULL COMMENT '靓号池名称',
|
||
level_track VARCHAR(32) NOT NULL COMMENT '等级轨道：wealth/game/charm',
|
||
min_level INT NOT NULL COMMENT '最小等级，包含',
|
||
max_level INT NOT NULL COMMENT '最大等级，包含',
|
||
rule_type VARCHAR(32) NOT NULL COMMENT '生成规则',
|
||
rule_config_json JSON NULL COMMENT '生成规则配置',
|
||
status VARCHAR(32) NOT NULL COMMENT '业务状态：active/disabled',
|
||
sort_order INT NOT NULL DEFAULT 0 COMMENT '排序权重',
|
||
created_by_admin_id BIGINT NOT NULL DEFAULT 0 COMMENT '创建管理员 ID',
|
||
updated_by_admin_id BIGINT NOT NULL DEFAULT 0 COMMENT '更新管理员 ID',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
|
||
UNIQUE KEY uk_pretty_pool_range_rule (app_code, level_track, min_level, max_level, name),
|
||
KEY idx_pretty_pool_status (app_code, status, sort_order)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='靓号池配置表';
|
||
|
||
CREATE TABLE IF NOT EXISTS pretty_display_ids (
|
||
app_code VARCHAR(32) NOT NULL DEFAULT 'lalu' COMMENT '应用编码，用于多租户隔离',
|
||
pretty_id VARCHAR(64) NOT NULL PRIMARY KEY COMMENT '靓号记录 ID',
|
||
pool_id VARCHAR(64) NOT NULL DEFAULT '' COMMENT '所属靓号池 ID，后台发放为空',
|
||
source VARCHAR(32) NOT NULL DEFAULT 'pool' COMMENT '来源：pool/admin_grant',
|
||
display_user_id VARCHAR(64) NOT NULL COMMENT '靓号展示内容',
|
||
status VARCHAR(32) NOT NULL COMMENT '业务状态',
|
||
assigned_user_id BIGINT NOT NULL DEFAULT 0 COMMENT '当前占用用户 ID',
|
||
assigned_lease_id VARCHAR(64) NOT NULL DEFAULT '' COMMENT '当前占用租约 ID',
|
||
assigned_at_ms BIGINT NOT NULL DEFAULT 0 COMMENT '占用时间，UTC epoch ms',
|
||
released_at_ms BIGINT NOT NULL DEFAULT 0 COMMENT '释放时间，UTC epoch ms',
|
||
release_reason VARCHAR(64) NOT NULL DEFAULT '' COMMENT '释放原因',
|
||
generated_batch_id VARCHAR(64) NOT NULL DEFAULT '' COMMENT '生成批次 ID',
|
||
created_by_admin_id BIGINT NOT NULL DEFAULT 0 COMMENT '创建管理员 ID',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
|
||
live_display_user_id VARCHAR(64)
|
||
GENERATED ALWAYS AS (
|
||
CASE WHEN status IN ('available', 'assigned', 'disabled', 'reserved') THEN display_user_id ELSE NULL END
|
||
) STORED COMMENT '未删除且未释放的靓号内容',
|
||
UNIQUE KEY uk_pretty_display_ids_live (app_code, live_display_user_id),
|
||
KEY idx_pretty_display_ids_pool_status (app_code, pool_id, status),
|
||
KEY idx_pretty_display_ids_assigned_user (app_code, assigned_user_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='靓号池号码表';
|
||
|
||
CREATE TABLE IF NOT EXISTS pretty_display_id_generation_batches (
|
||
app_code VARCHAR(32) NOT NULL DEFAULT 'lalu' COMMENT '应用编码，用于多租户隔离',
|
||
batch_id VARCHAR(64) NOT NULL PRIMARY KEY COMMENT '生成批次 ID',
|
||
pool_id VARCHAR(64) NOT NULL COMMENT '靓号池 ID',
|
||
rule_type VARCHAR(32) NOT NULL COMMENT '生成规则',
|
||
rule_config_json JSON NULL COMMENT '生成规则配置',
|
||
requested_count INT NOT NULL COMMENT '请求生成数量',
|
||
generated_count INT NOT NULL COMMENT '实际生成数量',
|
||
skipped_conflict_count INT NOT NULL COMMENT '冲突跳过数量',
|
||
status VARCHAR(32) NOT NULL COMMENT '批次状态',
|
||
operator_admin_id BIGINT NOT NULL COMMENT '操作管理员 ID',
|
||
request_id VARCHAR(64) NOT NULL DEFAULT '' COMMENT '链路请求 ID',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
|
||
KEY idx_pretty_batch_pool (app_code, pool_id, created_at_ms)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='靓号批量生成记录表';
|