gao/gmc_ai
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Files
master
gmc_ai/sql/init/01_init_tables.sql
T
BrunoGaoandClaude 028cf52919 Initial commit 
🤖 Generated with [Claude Code](https://claude.ai/code)

Co-Authored-By: Claude <[email protected]>
2025-10-07 21:22:33 +08:00

435 lines
26 KiB
SQL
Raw Permalink Blame History

-- 光明科学城科技成果转化平台数据库初始化脚本
-- 创建时间: 2024-10-07
-- 版本: 1.0.0
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS `gmc_platform`
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;
USE `gmc_platform`;
-- =============================================================================
-- 1. 系统基础表
-- =============================================================================
-- 系统用户表
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '密码（加密）',
`real_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '真实姓名',
`email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '邮箱',
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '手机号',
`avatar` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '头像URL',
`gender` tinyint DEFAULT '0' COMMENT '性别(0:未知,1:男,2:女)',
`birthday` date COMMENT '生日',
`status` tinyint DEFAULT '1' COMMENT '状态(0:禁用,1:启用)',
`user_type` tinyint NOT NULL COMMENT '用户类型(1:管理员,2:机构用户,3:企业用户,4:个人用户)',
`sso_user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'SSO系统用户ID',
`last_login_time` timestamp NULL DEFAULT NULL COMMENT '最后登录时间',
`last_login_ip` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '最后登录IP',
`login_count` int DEFAULT '0' COMMENT '登录次数',
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '备注',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` tinyint DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_username` (`username`),
UNIQUE KEY `uk_email` (`email`),
UNIQUE KEY `uk_phone` (`phone`),
KEY `idx_sso_user_id` (`sso_user_id`),
KEY `idx_status` (`status`),
KEY `idx_user_type` (`user_type`),
KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统用户表';
-- 系统角色表
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '角色ID',
`role_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '角色编码',
`role_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '角色名称',
`description` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '角色描述',
`status` tinyint DEFAULT '1' COMMENT '状态(0:禁用,1:启用)',
`sort_order` int DEFAULT '0' COMMENT '排序',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` tinyint DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_role_code` (`role_code`),
KEY `idx_status` (`status`),
KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统角色表';
-- 用户角色关联表
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '关联ID',
`user_id` bigint NOT NULL COMMENT '用户ID',
`role_id` bigint NOT NULL COMMENT '角色ID',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_role` (`user_id`,`role_id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户角色关联表';
-- 系统权限表
DROP TABLE IF EXISTS `sys_permission`;
CREATE TABLE `sys_permission` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '权限ID',
`parent_id` bigint DEFAULT '0' COMMENT '父权限ID',
`permission_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '权限编码',
`permission_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '权限名称',
`permission_type` tinyint NOT NULL COMMENT '权限类型(1:菜单,2:按钮,3:接口)',
`menu_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '菜单URL',
`menu_icon` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '菜单图标',
`api_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'API地址',
`api_method` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'HTTP方法',
`status` tinyint DEFAULT '1' COMMENT '状态(0:禁用,1:启用)',
`sort_order` int DEFAULT '0' COMMENT '排序',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` tinyint DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_permission_code` (`permission_code`),
KEY `idx_parent_id` (`parent_id`),
KEY `idx_permission_type` (`permission_type`),
KEY `idx_status` (`status`),
KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统权限表';
-- 角色权限关联表
DROP TABLE IF EXISTS `sys_role_permission`;
CREATE TABLE `sys_role_permission` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '关联ID',
`role_id` bigint NOT NULL COMMENT '角色ID',
`permission_id` bigint NOT NULL COMMENT '权限ID',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_role_permission` (`role_id`,`permission_id`),
KEY `idx_role_id` (`role_id`),
KEY `idx_permission_id` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色权限关联表';
-- 系统字典表
DROP TABLE IF EXISTS `sys_dict`;
CREATE TABLE `sys_dict` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '字典ID',
`dict_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '字典类型',
`dict_key` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '字典键',
`dict_value` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '字典值',
`dict_label` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '字典标签',
`description` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '描述',
`sort_order` int DEFAULT '0' COMMENT '排序',
`status` tinyint DEFAULT '1' COMMENT '状态(0:禁用,1:启用)',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` tinyint DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_dict_type_key` (`dict_type`,`dict_key`),
KEY `idx_dict_type` (`dict_type`),
KEY `idx_status` (`status`),
KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统字典表';
-- =============================================================================
-- 2. 机构相关表
-- =============================================================================
-- 系统机构表
DROP TABLE IF EXISTS `sys_institution`;
CREATE TABLE `sys_institution` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '机构ID',
`parent_id` bigint DEFAULT '0' COMMENT '上级机构ID',
`institution_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '机构编码',
`institution_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '机构名称',
`institution_type` tinyint NOT NULL COMMENT '机构类型(1:科研院所,2:高等院校,3:企业,4:服务机构)',
`legal_representative` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '法定代表人',
`contact_person` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '联系人',
`contact_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '联系电话',
`contact_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '联系邮箱',
`address` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '机构地址',
`postal_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '邮政编码',
`website` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '官方网站',
`logo` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '机构logo',
`introduction` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '机构简介',
`business_scope` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '业务范围',
`registered_capital` decimal(15,2) COMMENT '注册资本',
`established_date` date COMMENT '成立日期',
`certification_status` tinyint DEFAULT '0' COMMENT '认证状态(0:未认证,1:认证中,2:已认证,3:认证失败)',
`certification_time` timestamp NULL DEFAULT NULL COMMENT '认证时间',
`status` tinyint DEFAULT '1' COMMENT '状态(0:禁用,1:启用)',
`sort_order` int DEFAULT '0' COMMENT '排序',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` tinyint DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_institution_code` (`institution_code`),
KEY `idx_parent_id` (`parent_id`),
KEY `idx_institution_type` (`institution_type`),
KEY `idx_certification_status` (`certification_status`),
KEY `idx_status` (`status`),
KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统机构表';
-- =============================================================================
-- 3. 分类相关表
-- =============================================================================
-- 系统分类表
DROP TABLE IF EXISTS `sys_category`;
CREATE TABLE `sys_category` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '分类ID',
`parent_id` bigint DEFAULT '0' COMMENT '父分类ID',
`category_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '分类编码',
`category_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '分类名称',
`category_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '分类类型',
`level` int DEFAULT '1' COMMENT '分类层级',
`icon` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '分类图标',
`description` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '分类描述',
`sort_order` int DEFAULT '0' COMMENT '排序',
`status` tinyint DEFAULT '1' COMMENT '状态(0:禁用,1:启用)',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` tinyint DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_category_code` (`category_code`),
KEY `idx_parent_id` (`parent_id`),
KEY `idx_category_type` (`category_type`),
KEY `idx_level` (`level`),
KEY `idx_status` (`status`),
KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统分类表';
-- =============================================================================
-- 4. 科技成果相关表
-- =============================================================================
-- 科技成果表
DROP TABLE IF EXISTS `tech_achievement`;
CREATE TABLE `tech_achievement` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '成果ID',
`title` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '成果名称',
`subtitle` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '副标题',
`abstract` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '成果摘要',
`description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '详细描述',
`category_id` bigint COMMENT '一级分类ID',
`sub_category_id` bigint COMMENT '二级分类ID',
`tech_fields` json COMMENT '技术领域标签',
`industry_fields` json COMMENT '应用行业标签',
`keywords` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '关键词',
`maturity_level` tinyint COMMENT '技术成熟度(1-9级)',
`innovation_level` tinyint COMMENT '创新程度(1-5级)',
`application_scope` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '应用范围',
`patent_count` int DEFAULT '0' COMMENT '相关专利数量',
`paper_count` int DEFAULT '0' COMMENT '相关论文数量',
`institution_id` bigint NOT NULL COMMENT '所属机构ID',
`principal_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '负责人姓名',
`principal_title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '负责人职务',
`contact_person` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '联系人',
`contact_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '联系电话',
`contact_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '联系邮箱',
`investment_need` decimal(15,2) COMMENT '所需投资金额',
`cooperation_mode` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '合作方式',
`market_prospect` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '市场前景',
`expected_revenue` decimal(15,2) COMMENT '预期收益',
`cover_image` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '封面图片',
`images` json COMMENT '相关图片',
`videos` json COMMENT '相关视频',
`attachments` json COMMENT '附件信息',
`status` tinyint DEFAULT '0' COMMENT '状态(0:草稿,1:待审核,2:已发布,3:已下架)',
`audit_status` tinyint DEFAULT '0' COMMENT '审核状态(0:未审核,1:审核中,2:审核通过,3:审核拒绝)',
`audit_comment` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '审核意见',
`auditor_id` bigint COMMENT '审核人ID',
`audit_time` timestamp NULL DEFAULT NULL COMMENT '审核时间',
`view_count` int DEFAULT '0' COMMENT '浏览次数',
`favorite_count` int DEFAULT '0' COMMENT '收藏次数',
`match_count` int DEFAULT '0' COMMENT '匹配次数',
`cooperation_count` int DEFAULT '0' COMMENT '合作次数',
`embedding_vector` json COMMENT '向量表示',
`ai_score` decimal(5,3) COMMENT 'AI评估分数',
`ai_analysis` json COMMENT 'AI分析结果',
`creator_id` bigint NOT NULL COMMENT '创建人ID',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`published_at` timestamp NULL DEFAULT NULL COMMENT '发布时间',
`deleted` tinyint DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
PRIMARY KEY (`id`),
KEY `idx_title` (`title`(100)),
KEY `idx_category` (`category_id`,`sub_category_id`),
KEY `idx_institution` (`institution_id`),
KEY `idx_maturity` (`maturity_level`),
KEY `idx_status` (`status`,`audit_status`),
KEY `idx_creator` (`creator_id`),
KEY `idx_published` (`published_at` DESC),
KEY `idx_stats` (`view_count` DESC,`favorite_count` DESC),
FULLTEXT KEY `idx_content` (`title`,`abstract`,`description`,`keywords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='科技成果表';
-- =============================================================================
-- 5. 技术需求相关表
-- =============================================================================
-- 技术需求表
DROP TABLE IF EXISTS `tech_requirement`;
CREATE TABLE `tech_requirement` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '需求ID',
`title` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '需求标题',
`description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '需求详细描述',
`technical_requirement` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '技术要求',
`expected_effect` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '预期效果',
`category_id` bigint COMMENT '技术领域ID',
`sub_category_id` bigint COMMENT '子领域ID',
`tech_fields` json COMMENT '技术领域标签',
`industry` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '所属行业',
`application_scenario` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '应用场景',
`keywords` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '关键词',
`urgency_level` tinyint NOT NULL COMMENT '紧急程度(1-5级)',
`difficulty_level` tinyint COMMENT '技术难度(1-5级)',
`expected_maturity` tinyint COMMENT '期望成熟度(1-9级)',
`cooperation_mode` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '期望合作方式',
`budget_min` decimal(15,2) COMMENT '预算下限',
`budget_max` decimal(15,2) COMMENT '预算上限',
`budget_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '预算说明',
`expected_duration` int COMMENT '期望完成周期(天)',
`deadline` date COMMENT '最后期限',
`milestone_requirements` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '里程碑要求',
`company_id` bigint NOT NULL COMMENT '发布企业ID',
`contact_person` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '联系人',
`contact_title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '联系人职务',
`contact_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '联系电话',
`contact_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '联系邮箱',
`region_preference` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '地域偏好',
`location_requirement` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '地点要求',
`status` tinyint DEFAULT '1' COMMENT '状态(0:草稿,1:发布中,2:匹配中,3:进行中,4:已完成,5:已关闭)',
`audit_status` tinyint DEFAULT '0' COMMENT '审核状态',
`close_reason` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '关闭原因',
`view_count` int DEFAULT '0' COMMENT '浏览次数',
`apply_count` int DEFAULT '0' COMMENT '申请次数',
`match_count` int DEFAULT '0' COMMENT '匹配次数',
`embedding_vector` json COMMENT '向量表示',
`ai_analysis` json COMMENT 'AI分析结果',
`creator_id` bigint NOT NULL COMMENT '创建人ID',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`published_at` timestamp NULL DEFAULT NULL COMMENT '发布时间',
`deleted` tinyint DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
PRIMARY KEY (`id`),
KEY `idx_title` (`title`(100)),
KEY `idx_category` (`category_id`,`sub_category_id`),
KEY `idx_company` (`company_id`),
KEY `idx_urgency` (`urgency_level`),
KEY `idx_budget` (`budget_min`,`budget_max`),
KEY `idx_deadline` (`deadline`),
KEY `idx_status` (`status`,`audit_status`),
KEY `idx_creator` (`creator_id`),
FULLTEXT KEY `idx_content` (`title`,`description`,`technical_requirement`,`keywords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='技术需求表';
-- =============================================================================
-- 6. 匹配相关表
-- =============================================================================
-- 匹配记录表
DROP TABLE IF EXISTS `matching_record`;
CREATE TABLE `matching_record` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '匹配记录ID',
`requirement_id` bigint NOT NULL COMMENT '需求ID',
`achievement_id` bigint NOT NULL COMMENT '成果ID',
`match_type` tinyint NOT NULL COMMENT '匹配类型(1:AI推荐,2:用户搜索,3:人工匹配)',
`match_algorithm` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '匹配算法版本',
`semantic_score` decimal(5,3) COMMENT '语义相似度得分',
`technical_score` decimal(5,3) COMMENT '技术匹配得分',
`business_score` decimal(5,3) COMMENT '商业匹配得分',
`final_score` decimal(5,3) NOT NULL COMMENT '最终综合得分',
`match_reason` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '匹配原因说明',
`match_details` json COMMENT '详细匹配信息',
`confidence_level` decimal(5,3) COMMENT '置信度',
`cooperation_stage` tinyint DEFAULT '1' COMMENT '合作阶段(1:推荐,2:关注,3:接触,4:洽谈,5:签约,6:合作,7:完成,8:终止)',
`stage_update_time` timestamp NULL DEFAULT NULL COMMENT '阶段更新时间',
`broker_id` bigint COMMENT '技术经纪人ID',
`requirement_user_id` bigint COMMENT '需求方用户ID',
`achievement_user_id` bigint COMMENT '成果方用户ID',
`first_contact_time` timestamp NULL DEFAULT NULL COMMENT '首次联系时间',
`last_contact_time` timestamp NULL DEFAULT NULL COMMENT '最后联系时间',
`contact_count` int DEFAULT '0' COMMENT '联系次数',
`communication_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '沟通备注',
`requirement_feedback` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '需求方反馈',
`achievement_feedback` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '成果方反馈',
`satisfaction_score` tinyint COMMENT '满意度评分(1-5)',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` tinyint DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_requirement_achievement` (`requirement_id`,`achievement_id`),
KEY `idx_requirement` (`requirement_id`),
KEY `idx_achievement` (`achievement_id`),
KEY `idx_score` (`final_score` DESC),
KEY `idx_stage` (`cooperation_stage`),
KEY `idx_broker` (`broker_id`),
KEY `idx_created` (`created_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='匹配记录表';
-- =============================================================================
-- 7. 行为分析表
-- =============================================================================
-- 用户行为分析表
DROP TABLE IF EXISTS `user_behavior`;
CREATE TABLE `user_behavior` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '行为ID',
`user_id` bigint NOT NULL COMMENT '用户ID',
`behavior_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '行为类型',
`target_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '目标类型',
`target_id` bigint COMMENT '目标ID',
`behavior_data` json COMMENT '行为详细数据',
`session_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '会话ID',
`ip_address` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'IP地址',
`user_agent` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '用户代理',
`referrer` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '来源页面',
`duration` int COMMENT '持续时间(秒)',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发生时间',
PRIMARY KEY (`id`),
KEY `idx_user_behavior` (`user_id`,`behavior_type`),
KEY `idx_target` (`target_type`,`target_id`),
KEY `idx_session` (`session_id`),
KEY `idx_created` (`created_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户行为分析表';
-- =============================================================================
-- 8. 系统日志表
-- =============================================================================
-- 系统操作日志表
DROP TABLE IF EXISTS `sys_operation_log`;
CREATE TABLE `sys_operation_log` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '日志ID',
`user_id` bigint COMMENT '操作用户ID',
`username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '用户名',
`operation_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型',
`operation_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作名称',
`method` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '请求方法',
`request_uri` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '请求URI',
`request_method` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'HTTP方法',
`request_params` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '请求参数',
`response_result` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '响应结果',
`error_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '错误信息',
`execution_time` int COMMENT '执行时间(毫秒)',
`ip_address` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'IP地址',
`user_agent` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '用户代理',
`status` tinyint DEFAULT '1' COMMENT '状态(0:失败,1:成功)',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_operation_type` (`operation_type`),
KEY `idx_status` (`status`),
KEY `idx_created_at` (`created_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统操作日志表';
SET FOREIGN_KEY_CHECKS = 1;
Reference in New Issue View Git Blame Copy Permalink