数据库结构

本文档详细介绍 Yuan-ICP 系统的数据库结构设计，包括所有数据表、字段定义、关系约束和索引设计。

📋 数据库概览

Yuan-ICP 系统采用关系型数据库设计，支持多种数据库类型：

SQLite：默认数据库，零配置部署
MySQL：生产环境推荐数据库
PostgreSQL：企业级应用支持

数据库特点

跨平台兼容：支持多种数据库系统
自动迁移：自动处理不同数据库的语法差异
索引优化：针对查询性能优化的索引设计
外键约束：保证数据完整性和一致性

🗄️ 数据表结构

1. 管理员用户表 (admin_users)

存储系统管理员账户信息：

sql

CREATE TABLE admin_users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    is_admin BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP
);

字段说明

字段名	类型	约束	说明
`id`	INTEGER	PRIMARY KEY, AUTOINCREMENT	用户唯一标识
`username`	VARCHAR(50)	NOT NULL, UNIQUE	登录用户名
`password`	VARCHAR(255)	NOT NULL	加密后的密码哈希
`email`	VARCHAR(100)	NULL	管理员邮箱地址
`is_admin`	BOOLEAN	DEFAULT 0	是否为超级管理员
`created_at`	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	账户创建时间
`last_login`	TIMESTAMP	NULL	最后登录时间

索引设计

sql

-- 用户名索引（唯一）
CREATE UNIQUE INDEX idx_admin_users_username ON admin_users(username);

-- 邮箱索引
CREATE INDEX idx_admin_users_email ON admin_users(email);

-- 创建时间索引
CREATE INDEX idx_admin_users_created ON admin_users(created_at);

2. 系统配置表 (system_config)

存储系统配置参数：

sql

CREATE TABLE system_config (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    config_key VARCHAR(100) NOT NULL UNIQUE,
    config_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

字段说明

字段名	类型	约束	说明
`id`	INTEGER	PRIMARY KEY, AUTOINCREMENT	配置项唯一标识
`config_key`	VARCHAR(100)	NOT NULL, UNIQUE	配置键名
`config_value`	TEXT	NULL	配置值
`updated_at`	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	最后更新时间

索引设计

sql

-- 配置键索引（唯一）
CREATE UNIQUE INDEX idx_system_config_key ON system_config(config_key);

-- 更新时间索引
CREATE INDEX idx_system_config_updated ON system_config(updated_at);

配置项示例

sql

-- 基本配置
INSERT INTO system_config (config_key, config_value) VALUES
('site_name', 'Yuan-ICP'),
('site_url', 'http://localhost'),
('timezone', 'Asia/Shanghai'),
('icp_prefix', 'ICP'),
('icp_digits', '8');

-- 邮件配置
INSERT INTO system_config (config_key, config_value) VALUES
('smtp_host', 'smtp.gmail.com'),
('smtp_port', '587'),
('smtp_username', 'your-email@gmail.com'),
('smtp_password', 'your-password'),
('smtp_secure', 'tls');

-- 号码池配置
INSERT INTO system_config (config_key, config_value) VALUES
('number_auto_generate', '1'),
('number_generate_format', 'Yuan{U}{U}{N}{N}{N}{N}{N}{N}'),
('reserved_numbers', 'YuanAA00000001\nYuanAA11111111');

3. 公告表 (announcements)

存储系统公告信息：

sql

CREATE TABLE announcements (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    is_pinned BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

字段说明

字段名	类型	约束	说明
`id`	INTEGER	PRIMARY KEY, AUTOINCREMENT	公告唯一标识
`title`	VARCHAR(255)	NOT NULL	公告标题
`content`	TEXT	NOT NULL	公告内容
`is_pinned`	BOOLEAN	DEFAULT 0	是否置顶
`created_at`	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	创建时间
`updated_at`	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	更新时间

索引设计

sql

-- 置顶状态索引
CREATE INDEX idx_announcements_pinned ON announcements(is_pinned);

-- 创建时间索引
CREATE INDEX idx_announcements_created ON announcements(created_at);

-- 复合索引：置顶+创建时间
CREATE INDEX idx_announcements_pinned_created ON announcements(is_pinned, created_at DESC);

4. 可选号码池表 (selectable_numbers)

存储可供用户选择的备案号码：

sql

CREATE TABLE selectable_numbers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    number VARCHAR(20) NOT NULL UNIQUE,
    is_premium BOOLEAN DEFAULT 0,
    sponsor_info TEXT,
    status VARCHAR(20) DEFAULT 'available',
    used_by INTEGER,
    used_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

字段说明

字段名	类型	约束	说明
`id`	INTEGER	PRIMARY KEY, AUTOINCREMENT	号码记录唯一标识
`number`	VARCHAR(20)	NOT NULL, UNIQUE	备案号码
`is_premium`	BOOLEAN	DEFAULT 0	是否为靓号
`sponsor_info`	TEXT	NULL	赞助商信息
`status`	VARCHAR(20)	DEFAULT 'available'	号码状态
`used_by`	INTEGER	NULL	使用者ID
`used_at`	TIMESTAMP	NULL	使用时间
`created_at`	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	创建时间

状态值说明

available：可用
used：已使用
reserved：保留

索引设计

sql

-- 号码索引（唯一）
CREATE UNIQUE INDEX idx_selectable_numbers_number ON selectable_numbers(number);

-- 状态索引
CREATE INDEX idx_selectable_numbers_status ON selectable_numbers(status);

-- 靓号索引
CREATE INDEX idx_selectable_numbers_premium ON selectable_numbers(is_premium);

-- 复合索引：状态+靓号
CREATE INDEX idx_selectable_numbers_status_premium ON selectable_numbers(status, is_premium);

5. 备案申请表 (icp_applications)

存储用户提交的备案申请：

sql

CREATE TABLE icp_applications (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    number VARCHAR(20) NOT NULL UNIQUE,
    website_name VARCHAR(100) NOT NULL,
    domain VARCHAR(100) NOT NULL,
    description TEXT,
    owner_name VARCHAR(50),
    owner_email VARCHAR(100),
    owner_phone VARCHAR(20),
    status VARCHAR(20) DEFAULT 'pending',
    reject_reason TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reviewed_at TIMESTAMP,
    reviewed_by INTEGER,
    FOREIGN KEY (reviewed_by) REFERENCES admin_users(id)
);

字段说明

字段名	类型	约束	说明
`id`	INTEGER	PRIMARY KEY, AUTOINCREMENT	申请唯一标识
`number`	VARCHAR(20)	NOT NULL, UNIQUE	备案号码
`website_name`	VARCHAR(100)	NOT NULL	网站名称
`domain`	VARCHAR(100)	NOT NULL	网站域名
`description`	TEXT	NULL	网站描述
`owner_name`	VARCHAR(50)	NULL	申请人姓名
`owner_email`	VARCHAR(100)	NULL	申请人邮箱
`owner_phone`	VARCHAR(20)	NULL	申请人电话
`status`	VARCHAR(20)	DEFAULT 'pending'	申请状态
`reject_reason`	TEXT	NULL	驳回原因
`created_at`	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	申请时间
`reviewed_at`	TIMESTAMP	NULL	审核时间
`reviewed_by`	INTEGER	NULL	审核人ID

状态值说明

pending：待审核
approved：已通过
rejected：已驳回

索引设计

sql

-- 备案号索引（唯一）
CREATE UNIQUE INDEX idx_icp_applications_number ON icp_applications(number);

-- 状态索引
CREATE INDEX idx_icp_applications_status ON icp_applications(status);

-- 域名索引
CREATE INDEX idx_icp_applications_domain ON icp_applications(domain);

-- 创建时间索引
CREATE INDEX idx_icp_applications_created ON icp_applications(created_at);

-- 审核人索引
CREATE INDEX idx_icp_applications_reviewed_by ON icp_applications(reviewed_by);

-- 复合索引：状态+创建时间
CREATE INDEX idx_icp_applications_status_created ON icp_applications(status, created_at DESC);

6. 插件表 (plugins)

存储系统插件信息：

sql

CREATE TABLE plugins (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    plugin_id VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    version VARCHAR(20) NOT NULL,
    author VARCHAR(100),
    description TEXT,
    is_active BOOLEAN DEFAULT 0,
    installed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

字段说明

字段名	类型	约束	说明
`id`	INTEGER	PRIMARY KEY, AUTOINCREMENT	插件记录唯一标识
`plugin_id`	VARCHAR(100)	NOT NULL, UNIQUE	插件唯一标识符
`name`	VARCHAR(100)	NOT NULL	插件名称
`version`	VARCHAR(20)	NOT NULL	插件版本
`author`	VARCHAR(100)	NULL	插件作者
`description`	TEXT	NULL	插件描述
`is_active`	BOOLEAN	DEFAULT 0	是否启用
`installed_at`	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	安装时间

索引设计

sql

-- 插件ID索引（唯一）
CREATE UNIQUE INDEX idx_plugins_plugin_id ON plugins(plugin_id);

-- 激活状态索引
CREATE INDEX idx_plugins_active ON plugins(is_active);

-- 安装时间索引
CREATE INDEX idx_plugins_installed ON plugins(installed_at);

7. 操作日志表 (operation_logs)

记录系统操作日志：

sql

CREATE TABLE operation_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    action VARCHAR(50) NOT NULL,
    details TEXT,
    ip_address VARCHAR(50),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES admin_users(id)
);

字段说明

字段名	类型	约束	说明
`id`	INTEGER	PRIMARY KEY, AUTOINCREMENT	日志记录唯一标识
`user_id`	INTEGER	NULL	操作用户ID
`action`	VARCHAR(50)	NOT NULL	操作类型
`details`	TEXT	NULL	操作详情
`ip_address`	VARCHAR(50)	NULL	操作者IP地址
`user_agent`	TEXT	NULL	用户代理信息
`created_at`	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	操作时间

索引设计

sql

-- 用户ID索引
CREATE INDEX idx_operation_logs_user_id ON operation_logs(user_id);

-- 操作类型索引
CREATE INDEX idx_operation_logs_action ON operation_logs(action);

-- 创建时间索引
CREATE INDEX idx_operation_logs_created ON operation_logs(created_at);

-- 复合索引：用户+时间
CREATE INDEX idx_operation_logs_user_created ON operation_logs(user_id, created_at DESC);

-- 复合索引：操作类型+时间
CREATE INDEX idx_operation_logs_action_created ON operation_logs(action, created_at DESC);

🔗 表关系设计

外键关系图

mermaid

erDiagram
    admin_users ||--o{ icp_applications : "审核"
    admin_users ||--o{ operation_logs : "操作记录"
    
    selectable_numbers ||--o{ icp_applications : "号码分配"
    
    icp_applications {
        INTEGER id PK
        VARCHAR number UK
        VARCHAR website_name
        VARCHAR domain
        TEXT description
        VARCHAR owner_name
        VARCHAR owner_email
        VARCHAR owner_phone
        VARCHAR status
        TEXT reject_reason
        TIMESTAMP created_at
        TIMESTAMP reviewed_at
        INTEGER reviewed_by FK
    }
    
    admin_users {
        INTEGER id PK
        VARCHAR username UK
        VARCHAR password
        VARCHAR email
        BOOLEAN is_admin
        TIMESTAMP created_at
        TIMESTAMP last_login
    }
    
    selectable_numbers {
        INTEGER id PK
        VARCHAR number UK
        BOOLEAN is_premium
        TEXT sponsor_info
        VARCHAR status
        INTEGER used_by
        TIMESTAMP used_at
        TIMESTAMP created_at
    }
    
    system_config {
        INTEGER id PK
        VARCHAR config_key UK
        TEXT config_value
        TIMESTAMP updated_at
    }
    
    announcements {
        INTEGER id PK
        VARCHAR title
        TEXT content
        BOOLEAN is_pinned
        TIMESTAMP created_at
        TIMESTAMP updated_at
    }
    
    plugins {
        INTEGER id PK
        VARCHAR plugin_id UK
        VARCHAR name
        VARCHAR version
        VARCHAR author
        TEXT description
        BOOLEAN is_active
        TIMESTAMP installed_at
    }
    
    operation_logs {
        INTEGER id PK
        INTEGER user_id FK
        VARCHAR action
        TEXT details
        VARCHAR ip_address
        TEXT user_agent
        TIMESTAMP created_at
    }

关系说明

管理员与备案申请：一对多关系，一个管理员可以审核多个备案申请
管理员与操作日志：一对多关系，一个管理员可以产生多条操作记录
号码池与备案申请：一对多关系，一个号码可以被多个申请使用（但同一时间只能被一个使用）

📊 数据库性能优化

索引策略

查询优化索引

sql

-- 备案申请状态查询优化
CREATE INDEX idx_applications_status_created ON icp_applications(status, created_at DESC);

-- 号码池状态查询优化
CREATE INDEX idx_numbers_status_premium ON selectable_numbers(status, is_premium);

-- 公告显示优化
CREATE INDEX idx_announcements_pinned_created ON announcements(is_pinned, created_at DESC);

复合索引设计原则

最左前缀原则：复合索引的列顺序要符合查询条件
选择性原则：选择性高的列放在前面
查询频率原则：经常查询的组合优先考虑

查询优化建议

备案申请查询优化

sql

-- 优化前：全表扫描
SELECT * FROM icp_applications WHERE status = 'pending';

-- 优化后：使用索引
SELECT * FROM icp_applications 
WHERE status = 'pending' 
ORDER BY created_at DESC;

-- 分页查询优化
SELECT * FROM icp_applications 
WHERE status = 'pending' 
ORDER BY created_at DESC 
LIMIT 15 OFFSET 0;

号码池查询优化

sql

-- 可用号码查询优化
SELECT * FROM selectable_numbers 
WHERE status = 'available' 
ORDER BY is_premium DESC, created_at ASC;

-- 靓号查询优化
SELECT * FROM selectable_numbers 
WHERE status = 'available' AND is_premium = 1;

🔧 数据库维护

定期维护任务

数据清理

sql

-- 清理过期日志（保留90天）
DELETE FROM operation_logs 
WHERE created_at < datetime('now', '-90 days');

-- 清理已删除的插件记录
DELETE FROM plugins 
WHERE is_active = 0 AND installed_at < datetime('now', '-30 days');

索引维护

sql

-- SQLite索引重建
REINDEX;

-- MySQL索引优化
OPTIMIZE TABLE icp_applications;
OPTIMIZE TABLE selectable_numbers;
OPTIMIZE TABLE operation_logs;

统计信息更新

sql

-- SQLite统计信息更新
ANALYZE;

-- MySQL统计信息更新
ANALYZE TABLE icp_applications;
ANALYZE TABLE selectable_numbers;

备份策略

完整备份

bash

# SQLite备份
cp data/sqlite.db data/backup/sqlite_$(date +%Y%m%d_%H%M%S).db

# MySQL备份
mysqldump -u username -p database_name > backup_$(date +%Y%m%d_%H%M%S).sql

# PostgreSQL备份
pg_dump database_name > backup_$(date +%Y%m%d_%H%M%S).sql

增量备份

sql

-- 备份新增的备案申请
SELECT * FROM icp_applications 
WHERE created_at > (SELECT MAX(created_at) FROM backup_applications);

-- 备份修改的配置
SELECT * FROM system_config 
WHERE updated_at > (SELECT MAX(updated_at) FROM backup_config);

🚨 注意事项

数据库兼容性

SQLite特性

支持 AUTOINCREMENT 主键
使用 datetime('now') 获取当前时间
支持 BOOLEAN 类型（实际存储为INTEGER）

MySQL特性

使用 AUTO_INCREMENT 主键
使用 NOW() 获取当前时间
支持 BOOLEAN 类型

PostgreSQL特性

使用 SERIAL 主键
使用 NOW() 获取当前时间
原生支持 BOOLEAN 类型

数据迁移

版本升级迁移

sql

-- 添加新字段示例
ALTER TABLE icp_applications ADD COLUMN new_field VARCHAR(100);

-- 修改字段类型示例
ALTER TABLE system_config MODIFY COLUMN config_value LONGTEXT;

-- 添加索引示例
CREATE INDEX idx_new_index ON table_name(column_name);

数据导入导出

sql

-- 导出数据
SELECT * FROM icp_applications INTO OUTFILE '/tmp/applications.csv';

-- 导入数据
LOAD DATA INFILE '/tmp/applications.csv' INTO TABLE icp_applications;

📚 相关文档

项目结构 - 了解项目组织方式
核心函数 - 数据库操作函数说明
安装指南 - 数据库初始化步骤
备份与恢复 - 数据库备份策略

🎯 总结

Yuan-ICP 系统的数据库设计具有以下特点：

跨平台兼容：支持多种数据库系统，满足不同部署需求
性能优化：合理的索引设计和查询优化，确保系统性能
数据完整：外键约束和字段验证，保证数据一致性
易于维护：清晰的表结构和关系设计，便于系统维护和扩展

数据库结构 ​

📋 数据库概览 ​

数据库特点 ​

🗄️ 数据表结构 ​

1. 管理员用户表 (admin_users) ​

字段说明 ​

索引设计 ​

2. 系统配置表 (system_config) ​

字段说明 ​

索引设计 ​

配置项示例 ​

3. 公告表 (announcements) ​

字段说明 ​

索引设计 ​

4. 可选号码池表 (selectable_numbers) ​

字段说明 ​

状态值说明 ​

索引设计 ​

5. 备案申请表 (icp_applications) ​

字段说明 ​

状态值说明 ​

索引设计 ​

6. 插件表 (plugins) ​

字段说明 ​

索引设计 ​

7. 操作日志表 (operation_logs) ​

字段说明 ​

索引设计 ​

🔗 表关系设计 ​

外键关系图 ​

关系说明 ​

📊 数据库性能优化 ​

索引策略 ​

查询优化索引 ​

复合索引设计原则 ​

查询优化建议 ​

备案申请查询优化 ​

号码池查询优化 ​

🔧 数据库维护 ​

定期维护任务 ​

数据清理 ​

索引维护 ​

统计信息更新 ​

备份策略 ​

完整备份 ​

增量备份 ​

🚨 注意事项 ​

数据库兼容性 ​

SQLite特性 ​

MySQL特性 ​

PostgreSQL特性 ​

数据迁移 ​

版本升级迁移 ​

数据导入导出 ​

📚 相关文档 ​

🎯 总结 ​

数据库结构

📋 数据库概览

数据库特点

🗄️ 数据表结构

1. 管理员用户表 (admin_users)

字段说明

索引设计

2. 系统配置表 (system_config)

字段说明

索引设计

配置项示例

3. 公告表 (announcements)

字段说明

索引设计

4. 可选号码池表 (selectable_numbers)

字段说明

状态值说明

索引设计

5. 备案申请表 (icp_applications)

字段说明

状态值说明

索引设计

6. 插件表 (plugins)

字段说明

索引设计

7. 操作日志表 (operation_logs)

字段说明

索引设计

🔗 表关系设计

外键关系图

关系说明

📊 数据库性能优化

索引策略

查询优化索引

复合索引设计原则

查询优化建议

备案申请查询优化

号码池查询优化

🔧 数据库维护

定期维护任务

数据清理

索引维护

统计信息更新

备份策略

完整备份

增量备份

🚨 注意事项

数据库兼容性

SQLite特性

MySQL特性

PostgreSQL特性

数据迁移

版本升级迁移

数据导入导出

📚 相关文档

🎯 总结