58 lines
2.2 KiB
SQL
58 lines
2.2 KiB
SQL
-- 添加临床试验表的迁移脚本
|
||
-- 执行此脚本将在现有数据库中添加 clinical_trials 表
|
||
|
||
USE medical_info_system;
|
||
|
||
-- 临床试验表
|
||
CREATE TABLE IF NOT EXISTS clinical_trials (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
inquiry_id BIGINT COMMENT '关联的查询请求ID',
|
||
nct_id VARCHAR(50) NOT NULL COMMENT 'ClinicalTrials.gov唯一标识',
|
||
study_title TEXT COMMENT '研究标题',
|
||
brief_title VARCHAR(500) COMMENT '简短标题',
|
||
official_title TEXT COMMENT '官方标题',
|
||
overall_status VARCHAR(50) COMMENT '研究状态',
|
||
start_date VARCHAR(50) COMMENT '开始日期',
|
||
completion_date VARCHAR(50) COMMENT '完成日期',
|
||
study_type VARCHAR(50) COMMENT '研究类型',
|
||
phase VARCHAR(50) COMMENT '研究阶段',
|
||
enrollment INT COMMENT '入组人数',
|
||
conditions TEXT COMMENT '适应症（JSON数组）',
|
||
interventions TEXT COMMENT '干预措施（JSON数组）',
|
||
sponsor VARCHAR(500) COMMENT '主要研究者/机构',
|
||
collaborators TEXT COMMENT '合作者（JSON数组）',
|
||
locations TEXT COMMENT '研究地点（JSON数组）',
|
||
brief_summary TEXT COMMENT '简要摘要',
|
||
detailed_description LONGTEXT COMMENT '详细描述',
|
||
primary_outcome TEXT COMMENT '主要终点',
|
||
secondary_outcome TEXT COMMENT '次要终点',
|
||
eligibility_criteria TEXT COMMENT '入选标准',
|
||
url VARCHAR(500) COMMENT 'ClinicalTrials.gov链接',
|
||
raw_data LONGTEXT COMMENT '原始API返回数据（JSON格式）',
|
||
created_at DATETIME NOT NULL,
|
||
updated_at DATETIME,
|
||
INDEX idx_inquiry_id (inquiry_id),
|
||
INDEX idx_nct_id (nct_id),
|
||
INDEX idx_overall_status (overall_status),
|
||
INDEX idx_phase (phase),
|
||
INDEX idx_created_at (created_at),
|
||
UNIQUE KEY uk_inquiry_nct (inquiry_id, nct_id),
|
||
FOREIGN KEY (inquiry_id) REFERENCES inquiry_requests(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 验证表是否创建成功
|
||
SELECT
|
||
TABLE_NAME,
|
||
TABLE_ROWS,
|
||
CREATE_TIME
|
||
FROM
|
||
information_schema.TABLES
|
||
WHERE
|
||
TABLE_SCHEMA = 'medical_info_system'
|
||
AND TABLE_NAME = 'clinical_trials';
|
||
|
||
-- 显示表结构
|
||
DESCRIBE clinical_trials;
|
||
|
||
|