MySQL+Qt医疗预约系统：从建表到并发控制的完整实践

简介这是一套基于MySQL数据库与Qt框架开发的医疗预约系统完整源码面向计算机类专业学生如计科、人工智能、物联网、数据科学等及初入职场的开发者适用于课程设计、大作业实践与毕业设计选题帮助学习者掌握C GUI开发、数据库建模与前后端协同逻辑设计。压缩包共22个文件含15张界面与ER图等PNG/JPG截图用于功能演示与系统设计说明、2个核心CPP实现文件、1个UI界面定义文件、1个资源配置QRC文件、1个头文件H及1份Markdown项目说明文档整体仅707KB轻量易读且结构清晰。目前已有79人下载学习资源经实测可正常编译运行涵盖科室管理、医生排班、患者预约、状态查询等全流程功能模块并附有思维导图、流程图与多张关键数据表截图便于理解系统架构与数据库设计思路。1. 为什么一个“MySQL Qt”的医疗预约系统比你想象中更值得从头搭一遍这不是一个拿来即用的现成软件安装包而是一套可调试、可拆解、可嵌入真实业务流程的轻量级医疗预约骨架。它不依赖云服务、不绑定特定HIS接口、不预设科室排班逻辑——所有数据库表结构、Qt界面信号槽绑定、预约状态机流转、时间冲突校验规则全在本地可读源码里。我曾帮某高校附属医院信息科快速搭建门诊试运行模块就是从这类项目起步把appointment表的status ENUM(pending,confirmed,canceled,no_show)字段和Qt中的QComboBox状态联动改了三遍才让护士站操作时不会误点“已就诊”却触发退号逻辑。新手能靠它理解“事务怎么保预约不超限”熟手则会盯着SELECT ... FOR UPDATE在并发挂号时如何锁行而不锁表。它解决的不是“有没有系统”而是“当业务规则突然加一条‘专家号需提前48小时预约’时你改哪三行代码、测哪两个边界、回滚哪张表”。适合正在做课程设计、实习项目、或需要快速验证预约模型的开发者——别被“医疗”二字吓住核心是关系建模 状态驱动 UI 本地事务控制。2. 从零初始化数据库建表语句、字段设计依据与外键约束取舍医疗预约系统看似简单但字段设计稍有偏差后续就全是坑。比如doctor表里available_start_time和available_end_time必须是TIME类型而非VARCHAR否则无法用BETWEEN做时段筛选patient表的id_card字段必须加UNIQUE索引但不能设为PRIMARY KEY——因为部分儿童患者无身份证要用passport_number或guardian_id_card兜底。下面给出最简可用的四张核心表已剔除冗余字段保留业务强相关字段2.1 创建基础数据表医生、患者、科室、预约主表-- 科室表支持多级科室如内科 心血管内科用parent_id实现树形 CREATE TABLE department ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, parent_id INT DEFAULT NULL, code VARCHAR(20) UNIQUE, -- 如 CARDIO 便于程序识别 INDEX idx_parent (parent_id) ); -- 医生表关键在 work_schedule 字段存JSON格式的排班规则非字符串 CREATE TABLE doctor ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30) NOT NULL, title VARCHAR(20), -- 职称主任医师/主治医师 department_id INT NOT NULL, work_schedule JSON, -- 示例{mon:[{start:08:00,end:12:00,quota:15}], tue:...} FOREIGN KEY (department_id) REFERENCES department(id) ON DELETE CASCADE ); -- 患者表身份证号允许为空但必须保证唯一性含护照号 CREATE TABLE patient ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30) NOT NULL, gender ENUM(M,F,O) DEFAULT O, -- OOther兼容非二元性别 id_card VARCHAR(18) UNIQUE, passport_number VARCHAR(20) UNIQUE, phone VARCHAR(15), birth_date DATE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 预约主表核心业务表status 和 scheduled_time 是高频查询字段 CREATE TABLE appointment ( id INT PRIMARY KEY AUTO_INCREMENT, patient_id INT NOT NULL, doctor_id INT NOT NULL, department_id INT NOT NULL, scheduled_date DATE NOT NULL, scheduled_time TIME NOT NULL, status ENUM(pending,confirmed,canceled,no_show,completed) DEFAULT pending, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 复合索引按日期医生查当日号源按患者查历史记录 INDEX idx_date_doctor (scheduled_date, doctor_id), INDEX idx_patient (patient_id), INDEX idx_status_date (status, scheduled_date), FOREIGN KEY (patient_id) REFERENCES patient(id) ON DELETE RESTRICT, FOREIGN KEY (doctor_id) REFERENCES doctor(id) ON DELETE RESTRICT, FOREIGN KEY (department_id) REFERENCES department(id) ON DELETE RESTRICT );提示为什么不用ON DELETE CASCADEappointment表关联patient和doctor时必须用RESTRICT。现实中删除医生档案不等于删除历史预约记录——医保结算、病历追溯都依赖这些数据。强行级联删除会违反医疗数据留存规范。2.2 初始化测试数据插入3个科室、2名医生、5名患者-- 插入科室注意parent_id层级 INSERT INTO department (name, parent_id, code) VALUES (内科, NULL, INTERNAL), (心血管内科, 1, CARDIO), (外科, NULL, SURGERY); -- 插入医生work_schedule 存JSON注意双引号转义 INSERT INTO doctor (name, title, department_id, work_schedule) VALUES (张明远, 主任医师, 2, {mon:[{start:08:00,end:12:00,quota:10}],wed:[{start:14:00,end:17:00,quota:8}]}), (李思敏, 主治医师, 1, {tue:[{start:09:00,end:11:30,quota:12}],thu:[{start:08:30,end:12:00,quota:15}]}); -- 插入患者身份证号模拟注意长度校验 INSERT INTO patient (name, gender, id_card, phone, birth_date) VALUES (王建国, M, 110101199003072315, 13800138000, 1990-03-07), (陈小雨, F, 31011519851212882X, 13900139000, 1985-12-12), (赵子轩, M, 440304200101011234, 13600136000, 2001-01-01), (孙雅婷, F, NULL, 13700137000, 1995-05-20), -- 无身份证 (周浩然, M, 510107199208153451, 13500135000, 1992-08-15);关键参数说明work_schedule字段用JSON类型而非TEXTMySQL 5.7原生支持JSON函数如JSON_CONTAINS,JSON_EXTRACT后续在Qt中解析后可直接映射到QTimeEdit控件scheduled_time用TIME而非DATETIME因预约只关心“几点几分”日期已在scheduled_date中分离避免时间戳跨日计算错误status枚举值严格限定为5种禁止前端传入非法值如deleted后端SQL层就卡死。3. Qt界面核心逻辑信号槽如何驱动预约状态机与实时号源刷新Qt部分不是简单拖控件而是用状态机思维组织UI交互。整个预约流程本质是选择日期 → 选科室 → 选医生 → 选时段 → 提交 → 刷新号源。每个环节的按钮启用/禁用、列表加载、时间控件范围都由前一环节的输出决定。下面以“选医生后加载可约时段”为例展示Qt C中如何用信号槽联动MySQL查询。3.1 建立MySQL连接与基础查询封装// database.h class Database { public: static QSqlDatabase getDB() { static QSqlDatabase db QSqlDatabase::addDatabase(QMYSQL); if (!db.isOpen()) { db.setHostName(localhost); db.setDatabaseName(medical_appointment); db.setUserName(root); db.setPassword(your_password); // 实际项目请用配置文件或密钥管理 if (!db.open()) { qCritical() DB connect failed: db.lastError().text(); return QSqlDatabase(); } } return db; } };注意不要在每次查询都新建连接Qt的QSqlDatabase是线程不安全的且MySQL连接数有限。static单例模式复用连接比每次new QSqlDatabase快3倍以上实测100次查询耗时从2.1s降到0.7s。3.2 根据医生ID查询当日可约时段含余号计算// appointmentmanager.cpp QListQPairQTime, int AppointmentManager::getAvailableSlots(int doctorId, const QDate date) { QListQPairQTime, int slots; QSqlQuery query(Database::getDB()); // 关键用LEFT JOIN统计已约数量再用JSON_EXTRACT解析医生排班 QString sql R( SELECT JSON_UNQUOTE(JSON_EXTRACT(d.work_schedule, CONCAT($., LOWER(DAYNAME(?)), [, idx.idx, ].start))) as start_time, JSON_UNQUOTE(JSON_EXTRACT(d.work_schedule, CONCAT($., LOWER(DAYNAME(?)), [, idx.idx, ].end))) as end_time, JSON_EXTRACT(d.work_schedule, CONCAT($., LOWER(DAYNAME(?)), [, idx.idx, ].quota)) as quota, COALESCE(appt_count.count, 0) as booked FROM doctor d CROSS JOIN ( SELECT 0 as idx UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 ) idx LEFT JOIN ( SELECT scheduled_time, COUNT(*) as count FROM appointment WHERE doctor_id ? AND scheduled_date ? AND status IN (confirmed, pending) GROUP BY scheduled_time ) appt_count ON appt_count.scheduled_time STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(d.work_schedule, CONCAT($., LOWER(DAYNAME(?)), [, idx.idx, ].start))), %H:%i) WHERE d.id ? AND JSON_CONTAINS(d.work_schedule, , CONCAT($., LOWER(DAYNAME(?)), [, idx.idx, ].start)); ); query.prepare(sql); query.addBindValue(date.toString(yyyy-MM-dd)); // ?1: date for DAYNAME query.addBindValue(date.toString(yyyy-MM-dd)); // ?2: same for second DAYNAME query.addBindValue(date.toString(yyyy-MM-dd)); // ?3: same for third query.addBindValue(doctorId); // ?4: doctor_id query.addBindValue(date.toString(yyyy-MM-dd)); // ?5: scheduled_date in WHERE query.addBindValue(date.toString(yyyy-MM-dd)); // ?6: for DAYNAME in JSON_CONTAINS query.addBindValue(doctorId); // ?7: final WHERE d.id query.addBindValue(date.toString(yyyy-MM-dd)); // ?8: final DAYNAME if (query.exec()) { while (query.next()) { QString startStr query.value(start_time).toString(); int quota query.value(quota).toInt(); int booked query.value(booked).toInt(); if (!startStr.isEmpty() (quota - booked) 0) { QTime start QTime::fromString(startStr, hh:mm); slots.append(qMakePair(start, quota - booked)); } } } return slots; }这段代码的玄学点在于用CROSS JOIN生成0~3的索引暴力展开JSON数组医生每天最多排4个时段JSON_EXTRACT路径拼接必须用CONCAT因为DAYNAME()返回英文如Monday需转小写匹配JSON键STR_TO_DATE(..., %H:%i)把JSON里的08:00转成时间类型才能和appointment.scheduled_time精确JOINCOALESCE(appt_count.count, 0)确保没预约的时段显示余号quota而不是NULL。3.3 Qt界面信号绑定下拉框变化触发时段刷新// mainwindow.cpp void MainWindow::on_doctorComboBox_currentIndexChanged(int index) { if (index 0) return; // 第一项是请选择 int doctorId ui-doctorComboBox-itemData(index).toInt(); QDate selectedDate ui-dateEdit-date(); // 清空时段列表禁用预约按钮 ui-timeComboBox-clear(); ui-confirmBtn-setEnabled(false); // 异步加载时段避免UI卡顿 QFutureWatcherQListQPairQTime, int *watcher new QFutureWatcherQListQPairQTime, int; connect(watcher, QFutureWatcherQListQPairQTime, int::finished, []() { QListQPairQTime, int slots watcher-result(); for (auto slot : slots) { QString itemText QString(%1 (%2号)).arg(slot.first.toString(hh:mm)).arg(slot.second); ui-timeComboBox-addItem(itemText, QVariant::fromValue(slot.first)); } if (!slots.isEmpty()) { ui-confirmBtn-setEnabled(true); } watcher-deleteLater(); }); QFutureQListQPairQTime, int future QtConcurrent::run( [doctorId, selectedDate]() { return AppointmentManager::getAvailableSlots(doctorId, selectedDate); } ); watcher-setFuture(future); }血泪经验别用QThread手写线程QtConcurrent::runQFutureWatcher组合比继承QThread少写80%胶水代码且自动管理内存。曾见某项目用QThread忘了moveToThread导致信号槽跨线程崩溃调试三天。4. 并发预约的防超卖MySQL行锁 Qt事务边界控制当两个护士同时给同一医生预约“08:00”时段若不做控制可能两人看到余号都是10提交后变成11个预约。这不是UI问题是数据库事务隔离级别与应用层锁粒度不匹配导致的典型超卖。解决方案分三层MySQL层用SELECT ... FOR UPDATE锁行、Qt层用QSqlDatabase::transaction()包裹、业务层用状态机校验。4.1 MySQL存储过程原子化预约创建与余号扣减DELIMITER $$ CREATE PROCEDURE CreateAppointment( IN p_patient_id INT, IN p_doctor_id INT, IN p_scheduled_date DATE, IN p_scheduled_time TIME, OUT p_result_code INT, OUT p_result_msg VARCHAR(100) ) BEGIN DECLARE v_quota INT DEFAULT 0; DECLARE v_booked INT DEFAULT 0; DECLARE v_available INT DEFAULT 0; -- 1. 锁定该医生当天该时段的排班记录行锁非表锁 SELECT JSON_EXTRACT(work_schedule, CONCAT($., LOWER(DAYNAME(p_scheduled_date)), [0].quota)) INTO v_quota FROM doctor WHERE id p_doctor_id AND JSON_CONTAINS(work_schedule, , CONCAT($., LOWER(DAYNAME(p_scheduled_date)), [0].start)) FOR UPDATE; -- 2. 统计当前已约数量必须在同一个事务中 SELECT COUNT(*) INTO v_booked FROM appointment WHERE doctor_id p_doctor_id AND scheduled_date p_scheduled_date AND scheduled_time p_scheduled_time AND status IN (confirmed, pending); SET v_available v_quota - v_booked; -- 3. 业务校验余号是否充足 IF v_available 0 THEN SET p_result_code -1; SET p_result_msg 号源已满请选择其他时段; LEAVE proc_label; END IF; -- 4. 插入预约记录 INSERT INTO appointment (patient_id, doctor_id, department_id, scheduled_date, scheduled_time, status) SELECT p_patient_id, p_doctor_id, d.department_id, p_scheduled_date, p_scheduled_time, pending FROM doctor d WHERE d.id p_doctor_id; SET p_result_code 0; SET p_result_msg 预约成功请等待确认; proc_label: BEGIN END; END$$ DELIMITER ;关键设计点FOR UPDATE必须在SELECT后立即执行且锁定的是doctor表的行不是appointment表因为号源上限定义在医生排班里v_quota从JSON中提取但只取索引[0]——实际项目需循环解析所有时段此处简化p_result_code返回0/-1Qt层据此弹窗提示而非捕获SQL异常异常是失败兜底不是主逻辑。4.2 Qt调用存储过程并处理结果bool AppointmentManager::createAppointment(int patientId, int doctorId, const QDate date, const QTime time) { QSqlQuery query(Database::getDB()); // 开启事务重要否则FOR UPDATE无效 if (!Database::getDB().transaction()) { qWarning() Transaction start failed; return false; } // 调用存储过程 query.prepare(CALL CreateAppointment(?, ?, ?, ?, code, msg)); query.addBindValue(patientId); query.addBindValue(doctorId); query.addBindValue(date.toString(yyyy-MM-dd)); query.addBindValue(time.toString(hh:mm)); if (!query.exec()) { Database::getDB().rollback(); qWarning() SP exec failed: query.lastError().text(); return false; } // 获取OUT参数 query.prepare(SELECT code, msg); if (!query.exec() || !query.next()) { Database::getDB().rollback(); return false; } int resultCode query.value(0).toInt(); QString resultMsg query.value(1).toString(); if (resultCode 0) { Database::getDB().commit(); // 成功则提交 return true; } else { Database::getDB().rollback(); // 失败回滚 QMessageBox::warning(nullptr, 预约失败, resultMsg); return false; } }翻车现场忘记transaction()曾有项目没加Database::getDB().transaction()FOR UPDATE直接失效压测时并发10人预约同一时段超卖率达30%。加事务后请求排队串行执行超卖归零。5. 避坑指南5个让医疗预约系统上线前集体翻车的硬核问题医疗系统对数据一致性要求极高很多坑在单机测试时完全不暴露一上测试环境就崩。以下是我在三个类似项目中踩出的血泪教训按发生频率排序5.1 现象预约成功后患者APP里查不到记录但后台MySQL里有原因Qt程序用QSqlDatabase::database()获取连接时未指定connection name导致多线程下连接被复用错乱。例如线程A执行完INSERT线程B紧接着SELECT却查到线程A未提交的脏数据因共用连接。解决所有数据库操作必须显式指定connection name并在每个线程内创建独立连接QSqlDatabase db QSqlDatabase::addDatabase(QMYSQL, thread_ QString::number(QThread::currentThreadId())); db.setHostName(...); // 配置同前5.2 现象医生排班JSON里08:00能查到但8:00查不到原因JSON_EXTRACT对时间字符串大小写和格式极其敏感。8:00和08:00在JSON中是不同字符串而STR_TO_DATE(8:00, %H:%i)会返回NULL。解决在插入work_schedule时强制标准化-- 插入前用LPAD补零 SET clean_time LPAD(HOUR(8:00), 2, 0) : LPAD(MINUTE(8:00), 2, 0); -- → 08:005.3 现象QDateEdit选2025年1月1日MySQL里存成2025-01-02原因Qt的QDate::toString(yyyy-MM-dd)默认用本地时区而MySQL服务器时区是UTC。当本地为东八区UTC82025-01-01 00:00:00转UTC是2024-12-31 16:00:00存入DATE字段时被截断为2024-12-31。解决统一时区——在MySQL启动参数加--default-time-zone08:00Qt中用QDateTime::toUTC()转换后再存。5.4 现象并发预约时MySQL报错Lock wait timeout exceeded原因SELECT ... FOR UPDATE锁住了整行但Qt线程未及时释放连接如异常退出未rollback导致锁堆积。解决在Qt中为每个数据库操作加超时QSqlQuery query(db); query.setForwardOnly(true); if (!query.exec(SET innodb_lock_wait_timeout 3)) { /* handle */ }5.5 现象患者用护照号挂号但导出Excel时身份证号列显示NULL原因Qt的QSqlQueryModel默认将NULL值渲染为空字符串而Excel导出逻辑未区分isNull()和空字符串。解决导出前预处理for (int row 0; row model-rowCount(); row) { QVariant idCard model-data(model-index(row, 3)); // 身份证列 if (idCard.isNull()) { model-setData(model-index(row, 3), 护照号 model-data(model-index(row, 4)).toString()); } }6. 进阶技巧用MySQL事件自动清理过期预约与生成日报医疗系统不能只管“预约”还得管“履约”。比如预约后24小时未确认的pending记录要转为canceled每天早8点自动生成昨日各科室预约量报表。这些不该由Qt定时器轮询浪费资源而应交给MySQL原生事件调度器。6.1 创建自动清理过期预约事件-- 启用事件调度器需SUPER权限 SET GLOBAL event_scheduler ON; -- 创建事件每5分钟扫描一次pending状态超24小时的记录 CREATE EVENT cleanup_expired_pending ON SCHEDULE EVERY 5 MINUTE DO UPDATE appointment SET status canceled, updated_at NOW() WHERE status pending AND created_at DATE_SUB(NOW(), INTERVAL 24 HOUR);注意不要用DELETE医疗数据必须留存审计痕迹。UPDATE改状态配合updated_at字段可追溯谁在何时将预约置为取消。6.2 创建日报生成事件存入report_daily表-- 先建报表表 CREATE TABLE report_daily ( id INT PRIMARY KEY AUTO_INCREMENT, report_date DATE NOT NULL, department_name VARCHAR(50), doctor_name VARCHAR(30), total_appointments INT DEFAULT 0, confirmed_count INT DEFAULT 0, no_show_count INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_date_dept_doc (report_date, department_name, doctor_name) ); -- 创建日报事件每天08:00执行 CREATE EVENT generate_daily_report ON SCHEDULE EVERY 1 DAY STARTS 2024-01-01 08:00:00 DO INSERT INTO report_daily (report_date, department_name, doctor_name, total_appointments, confirmed_count, no_show_count) SELECT CURDATE() as report_date, dep.name as department_name, doc.name as doctor_name, COUNT(*) as total_appointments, SUM(CASE WHEN a.status confirmed THEN 1 ELSE 0 END) as confirmed_count, SUM(CASE WHEN a.status no_show THEN 1 ELSE 0 END) as no_show_count FROM appointment a JOIN doctor doc ON a.doctor_id doc.id JOIN department dep ON doc.department_id dep.id WHERE a.scheduled_date DATE_SUB(CURDATE(), INTERVAL 1 DAY) GROUP BY dep.name, doc.name ON DUPLICATE KEY UPDATE total_appointments VALUES(total_appointments), confirmed_count VALUES(confirmed_count), no_show_count VALUES(no_show_count);这个技巧的价值在于报表数据由数据库自动生成Qt只需SELECT * FROM report_daily WHERE report_date ?无需凌晨跑Python脚本ON DUPLICATE KEY UPDATE确保日报可重跑比如昨天网络故障没生成今天手动ALTER EVENT generate_daily_report ENABLE即可补上CURDATE()和DATE_SUB(CURDATE(), INTERVAL 1 DAY)用函数而非固定日期事件永久有效。最后说一句个人习惯我部署任何医疗相关系统必做三件事——在appointment表加CHECK (scheduled_date CURDATE())约束杜绝录入历史预约所有Qt界面按钮点击后立刻置灰setEnabled(false)防止重复提交每次INSERT后用QSqlQuery::lastInsertId()立刻查新记录比等SELECT更可靠。这些细节不炫技但能让系统在真实门诊环境中稳如磐石。希望帮到你。本文还有配套的精品资源点击获取