sion/sionrui
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
sionrui/sql/mysql/SQL建表语句.sql
sion123 24f66c8e81 feat: 功能优化
2026-01-27 01:39:08 +08:00

297 lines
17 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- Yudao 风格建表语句
-- 包含多租户概念，使用 TenantBaseDO
-- ===============================================
-- 1. 积分管理模块
-- ===============================================
-- 积分兑换配置表
CREATE TABLE `member_point_exchange_config` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
`exchange_rate` int NOT NULL DEFAULT 1 COMMENT '兑换比例(1元兑换多少积分)',
`adjust_reason` varchar(200) NOT NULL DEFAULT '' COMMENT '调整原因',
`operator_id` bigint NOT NULL DEFAULT 0 COMMENT '操作人用户编号',
`operator_name` varchar(64) NOT NULL DEFAULT '' COMMENT '操作人账号',
`status` tinyint NOT NULL DEFAULT 1 COMMENT '状态(0-禁用 1-启用)',
`remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
`updater` varchar(64) NOT NULL DEFAULT '' COMMENT '更新者',
`deleted` bit NOT NULL DEFAULT b'0' COMMENT '是否删除',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_tenant_id` (`tenant_id`) USING BTREE,
KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='积分兑换配置表';
-- 积分签到配置表
CREATE TABLE `member_point_signin_config` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
`daily_points` int NOT NULL DEFAULT 0 COMMENT '每日签到赠送积分',
`continuous_days` int NOT NULL DEFAULT 0 COMMENT '连续签到天数',
`bonus_points` int NOT NULL DEFAULT 0 COMMENT '连续签到奖励积分',
`reset_days` int NOT NULL DEFAULT 0 COMMENT '重置签到天数(0表示不重置)',
`adjust_reason` varchar(200) NOT NULL DEFAULT '' COMMENT '调整原因',
`operator_id` bigint NOT NULL DEFAULT 0 COMMENT '操作人用户编号',
`operator_name` varchar(64) NOT NULL DEFAULT '' COMMENT '操作人账号',
`status` tinyint NOT NULL DEFAULT 1 COMMENT '状态(0-禁用 1-启用)',
`remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
`updater` varchar(64) NOT NULL DEFAULT '' COMMENT '更新者',
`deleted` bit NOT NULL DEFAULT b'0' COMMENT '是否删除',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_tenant_id` (`tenant_id`) USING BTREE,
KEY `idx_continuous_days` (`continuous_days`) USING BTREE,
KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='积分签到配置表';
-- 积分充值配置表
CREATE TABLE `member_point_recharge_config` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
`recharge_amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '充值金额',
`bonus_points` int NOT NULL DEFAULT 0 COMMENT '赠送积分数',
`adjust_reason` varchar(200) NOT NULL DEFAULT '' COMMENT '调整原因',
`operator_id` bigint NOT NULL DEFAULT 0 COMMENT '操作人用户编号',
`operator_name` varchar(64) NOT NULL DEFAULT '' COMMENT '操作人账号',
`status` tinyint NOT NULL DEFAULT 1 COMMENT '状态(0-禁用 1-启用)',
`remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
`updater` varchar(64) NOT NULL DEFAULT '' COMMENT '更新者',
`deleted` bit NOT NULL DEFAULT b'0' COMMENT '是否删除',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_tenant_id` (`tenant_id`) USING BTREE,
KEY `idx_recharge_amount` (`recharge_amount`) USING BTREE,
KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='积分充值配置表';
-- 积分记录表
CREATE TABLE `member_point_record` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
`user_id` bigint NOT NULL DEFAULT 0 COMMENT '用户编号',
`mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '手机号',
`type` varchar(20) NOT NULL DEFAULT '' COMMENT '变动类型(increase-增加 decrease-减少)',
`point_amount` int NOT NULL DEFAULT 0 COMMENT '变动积分数量(正数为增加，负数为减少)',
`balance` int NOT NULL DEFAULT 0 COMMENT '变动后余额',
`reason` varchar(100) NOT NULL DEFAULT '' COMMENT '变动原因',
`biz_type` varchar(50) NOT NULL DEFAULT '' COMMENT '业务类型(signin-签到 recharge-充值 exchange-兑换 admin-后台调整 gift-礼包赠送)',
`biz_id` varchar(64) NOT NULL DEFAULT '' COMMENT '业务关联ID',
`remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_tenant_id_user_id` (`tenant_id`, `user_id`) USING BTREE,
KEY `idx_user_id_create_time` (`user_id`, `create_time`) USING BTREE,
KEY `idx_type` (`type`) USING BTREE,
KEY `idx_biz_type` (`biz_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='积分记录表';
-- ===============================================
-- 2. 客户管理模块
-- ===============================================
-- 会员用户表
CREATE TABLE `member_user` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
`user_id` varchar(32) NOT NULL COMMENT '用户ID',
`mobile` varchar(20) NOT NULL COMMENT '手机号',
`register_time` datetime NOT NULL COMMENT '注册时间',
`last_login_time` datetime NOT NULL COMMENT '最后登录时间',
`total_points` int NOT NULL DEFAULT 0 COMMENT '账户总积分',
`used_points` int NOT NULL DEFAULT 0 COMMENT '账户消耗积分',
`remaining_points` int NOT NULL DEFAULT 0 COMMENT '账户剩余积分',
`total_storage` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '云空间总容量(GB)',
`used_storage` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '云空间已用容量(GB)',
`remaining_storage` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '云空间剩余容量(GB)',
`total_recharge` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '总充值金额',
`status` tinyint NOT NULL DEFAULT 1 COMMENT '状态(0-禁用 1-启用)',
`remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
`updater` varchar(64) NOT NULL DEFAULT '' COMMENT '更新者',
`deleted` bit NOT NULL DEFAULT b'0' COMMENT '是否删除',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uk_user_id` (`user_id`) USING BTREE,
UNIQUE KEY `uk_mobile` (`mobile`) USING BTREE,
KEY `idx_tenant_id` (`tenant_id`) USING BTREE,
KEY `idx_register_time` (`register_time`) USING BTREE,
KEY `idx_last_login_time` (`last_login_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员用户表';
-- 充值记录表
CREATE TABLE `member_recharge_record` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
`user_id` bigint NOT NULL DEFAULT 0 COMMENT '用户编号',
`mobile` varchar(20) NOT NULL COMMENT '手机号',
`recharge_amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '充值金额',
`recharge_type` varchar(20) NOT NULL DEFAULT '' COMMENT '充值方式(alipay-支付宝 wechat-微信 admin-人工)',
`order_type` varchar(50) NOT NULL DEFAULT '' COMMENT '订单类型(purchase-权限购买 exchange-积分兑换)',
`permission_type` varchar(100) NOT NULL DEFAULT '' COMMENT '购买权限类型',
`bonus_points` int NOT NULL DEFAULT 0 COMMENT '获得积分',
`status` tinyint NOT NULL DEFAULT 1 COMMENT '状态(0-失败 1-成功)',
`remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_tenant_id_user_id` (`tenant_id`, `user_id`) USING BTREE,
KEY `idx_user_id_create_time` (`user_id`, `create_time`) USING BTREE,
KEY `idx_recharge_type` (`recharge_type`) USING BTREE,
KEY `idx_order_type` (`order_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='充值记录表';
-- ===============================================
-- 3. 礼包管理模块
-- ===============================================
-- 礼包表
CREATE TABLE `member_gift_package` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
`package_id` varchar(32) NOT NULL COMMENT '礼包ID',
`package_name` varchar(100) NOT NULL COMMENT '礼包名称',
`sort_order` int NOT NULL DEFAULT 0 COMMENT 'C端展示排序',
`status` tinyint NOT NULL DEFAULT 1 COMMENT '状态(0-禁用 1-启用)',
`price` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '购买价格',
`validity_days` int NOT NULL DEFAULT 0 COMMENT '有效期(天)',
`bonus_points` int NOT NULL DEFAULT 0 COMMENT '赠送积分',
`applications` text NOT NULL COMMENT '关联应用(JSON格式)',
`remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
`operator_id` bigint NOT NULL DEFAULT 0 COMMENT '操作人用户编号',
`operator_name` varchar(64) NOT NULL DEFAULT '' COMMENT '操作人账号',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
`updater` varchar(64) NOT NULL DEFAULT '' COMMENT '更新者',
`deleted` bit NOT NULL DEFAULT b'0' COMMENT '是否删除',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uk_package_id` (`package_id`) USING BTREE,
KEY `idx_tenant_id` (`tenant_id`) USING BTREE,
KEY `idx_sort_order` (`sort_order`) USING BTREE,
KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='礼包表';
-- ===============================================
-- 4. 模型管理模块
-- ===============================================
-- AI模型表
CREATE TABLE `ai_model` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
`model_name` varchar(100) NOT NULL COMMENT '模型名称',
`model_code` varchar(100) NOT NULL COMMENT '模型标识/编码',
`platform` varchar(50) NOT NULL COMMENT '所属平台',
`api_key` varchar(200) NOT NULL COMMENT 'API秘钥',
`status` tinyint NOT NULL DEFAULT 1 COMMENT '状态(0-禁用 1-启用)',
`temperature` decimal(3,2) NOT NULL DEFAULT 0.70 COMMENT '温度参数',
`max_tokens` int NOT NULL DEFAULT 0 COMMENT '回复数Token数',
`daily_limit` int NOT NULL DEFAULT 0 COMMENT '每日请求次数',
`model_type` varchar(50) NOT NULL COMMENT '模型类型(image-图像 text-文本 video-视频 audio-音频)',
`consume_points` int NOT NULL DEFAULT 0 COMMENT '消耗积分',
`max_text_length` int NOT NULL DEFAULT 0 COMMENT '最大文本数量',
`max_image_size` varchar(50) NOT NULL DEFAULT '' COMMENT '图片最大像素',
`max_video_duration` int NOT NULL DEFAULT 0 COMMENT '视频最大时长(秒)',
`max_video_quality` varchar(20) NOT NULL DEFAULT '' COMMENT '视频最大质量',
`remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
`updater` varchar(64) NOT NULL DEFAULT '' COMMENT '更新者',
`deleted` bit NOT NULL DEFAULT b'0' COMMENT '是否删除',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_tenant_id` (`tenant_id`) USING BTREE,
KEY `idx_platform` (`platform`) USING BTREE,
KEY `idx_model_type` (`model_type`) USING BTREE,
KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI模型表';
-- ===============================================
-- 5. 应用功能管理模块
-- ===============================================
-- 应用功能表
CREATE TABLE `ai_application` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
`app_id` varchar(32) NOT NULL COMMENT '应用ID',
`app_name` varchar(100) NOT NULL COMMENT '应用名称',
`api_key` varchar(200) NOT NULL COMMENT '第三方API秘钥',
`consume_points` int NOT NULL DEFAULT 0 COMMENT '单位消耗积分',
`unit_type` varchar(20) NOT NULL COMMENT '消耗单位(time-时长 count-次数)',
`unit_value` varchar(50) NOT NULL COMMENT '单位值(如：1min、20次)',
`status` tinyint NOT NULL DEFAULT 1 COMMENT '状态(0-禁用 1-启用)',
`remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
`updater` varchar(64) NOT NULL DEFAULT '' COMMENT '更新者',
`deleted` bit NOT NULL DEFAULT b'0' COMMENT '是否删除',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uk_app_id` (`app_id`) USING BTREE,
KEY `idx_tenant_id` (`tenant_id`) USING BTREE,
KEY `idx_app_name` (`app_name`) USING BTREE,
KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应用功能表';
-- ===============================================
-- 6. 智能体配置模块
-- ===============================================
-- 智能体表
CREATE TABLE `ai_agent` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
`agent_id` varchar(32) NOT NULL COMMENT '智能体ID',
`agent_name` varchar(100) NOT NULL COMMENT '智能体名称',
`icon` varchar(200) NOT NULL DEFAULT '' COMMENT '图标URL',
`status` tinyint NOT NULL DEFAULT 1 COMMENT '状态(0-禁用 1-启用)',
`description` text NOT NULL COMMENT '设定描述',
`system_prompt` text NOT NULL COMMENT '预置提示词',
`remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
`operator_id` bigint NOT NULL DEFAULT 0 COMMENT '操作人用户编号',
`operator_name` varchar(64) NOT NULL DEFAULT '' COMMENT '操作人账号',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
`updater` varchar(64) NOT NULL DEFAULT '' COMMENT '更新者',
`deleted` bit NOT NULL DEFAULT b'0' COMMENT '是否删除',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uk_agent_id` (`agent_id`) USING BTREE,
KEY `idx_tenant_id` (`tenant_id`) USING BTREE,
KEY `idx_agent_name` (`agent_name`) USING BTREE,
KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='智能体表';
-- ===============================================
-- 7. 权限管理表
-- ===============================================
-- 用户权限表
CREATE TABLE `member_user_permission` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
`user_id` bigint NOT NULL DEFAULT 0 COMMENT '用户编号',
`permission_type` varchar(100) NOT NULL COMMENT '权限类型',
`package_id` bigint NOT NULL DEFAULT 0 COMMENT '礼包ID',
`validity_start` datetime NOT NULL COMMENT '有效期开始时间',
`validity_end` datetime NOT NULL COMMENT '有效期结束时间',
`status` tinyint NOT NULL DEFAULT 1 COMMENT '状态(0-过期 1-有效)',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_tenant_id_user_id` (`tenant_id`, `user_id`) USING BTREE,
KEY `idx_user_id` (`user_id`) USING BTREE,
KEY `idx_package_id` (`package_id`) USING BTREE,
KEY `idx_validity_end` (`validity_end`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户权限表';
Reference in New Issue View Git Blame Copy Permalink