SpringBoot + MySQL 8.0 疫苗预约系统数据库设计：5张核心表与3大业务约束详解

SpringBootMySQL疫苗预约系统数据库设计

于 2026-07-08 09:50:27 修改

·本内容遵循CC 4.0 BY-SA版权协议

SpringBoot + MySQL 8.0 疫苗预约系统数据库设计：5张核心表与3大业务约束详解

1. 系统架构与数据库设计概述

在公共卫生数字化进程中，疫苗预约系统已成为提升接种效率的关键工具。一个稳健的数据库设计需要同时满足高并发预约、数据一致性和业务规则约束等核心需求。本方案采用SpringBoot 2.7 + MySQL 8.0技术栈，通过五张核心数据表构建完整的业务模型。

典型业务场景示例：

用户同时段并发预约同一疫苗
接种后库存自动更新
预约状态的多级流转（待审核→已预约→已完成）

2. 核心数据表结构设计

2.1 疫苗信息表（vaccine）

SQL

CREATE TABLE `vaccine` (

`id` BIGINT PRIMARY KEY AUTO_INCREMENT,

`name` VARCHAR(100) NOT NULL COMMENT '疫苗名称',

`manufacturer` VARCHAR(200) NOT NULL COMMENT '生产厂商',

`batch_number` VARCHAR(50) NOT NULL COMMENT '批次号',

`description` TEXT COMMENT '疫苗说明',

`stock` INT UNSIGNED DEFAULT 0 COMMENT '当前库存',

`status` TINYINT DEFAULT 1 COMMENT '状态(1:可用 0:停用)',

`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.2 接种点表（vaccination_site）

SQL

CREATE TABLE `vaccination_site` (

`id` BIGINT PRIMARY KEY AUTO_INCREMENT,

`name` VARCHAR(200) NOT NULL COMMENT '接种点名称',

`address` VARCHAR(500) NOT NULL,

`contact_phone` VARCHAR(20) NOT NULL,

`business_hours` VARCHAR(100) COMMENT '营业时间',

`longitude` DECIMAL(10,7) COMMENT '经度',

`latitude` DECIMAL(10,7) COMMENT '纬度',

`max_daily_capacity` INT UNSIGNED COMMENT '日最大接种量'

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.3 预约时段表（time_slot）

SQL

CREATE TABLE `time_slot` (

`id` BIGINT PRIMARY KEY AUTO_INCREMENT,

`site_id` BIGINT NOT NULL COMMENT '接种点ID',

`start_time` DATETIME NOT NULL COMMENT '时段开始时间',

`end_time` DATETIME NOT NULL COMMENT '时段结束时间',

`max_appointments` INT UNSIGNED NOT NULL COMMENT '最大预约数',

`booked_count` INT UNSIGNED DEFAULT 0 COMMENT '已预约数',

FOREIGN KEY (`site_id`) REFERENCES `vaccination_site`(`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.4 用户预约表（appointment）

SQL

CREATE TABLE `appointment` (

`id` BIGINT PRIMARY KEY AUTO_INCREMENT,

`user_id` BIGINT NOT NULL COMMENT '用户ID',

`vaccine_id` BIGINT NOT NULL COMMENT '疫苗ID',

`time_slot_id` BIGINT NOT NULL COMMENT '时段ID',

`status` TINYINT DEFAULT 0 COMMENT '0:待确认 1:已预约 2:已完成 3:已取消',

`qr_code` VARCHAR(100) COMMENT '预约二维码',

`cancel_reason` VARCHAR(500) COMMENT '取消原因',

`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,

FOREIGN KEY (`vaccine_id`) REFERENCES `vaccine`(`id`),

FOREIGN KEY (`time_slot_id`) REFERENCES `time_slot`(`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.5 接种记录表（vaccination_record）

SQL

CREATE TABLE `vaccination_record` (

`id` BIGINT PRIMARY KEY AUTO_INCREMENT,

`appointment_id` BIGINT NOT NULL COMMENT '预约ID',

`nurse_id` BIGINT COMMENT '接种护士ID',

`vaccination_time` DATETIME COMMENT '实际接种时间',

`lot_number` VARCHAR(50) COMMENT '接种批号',

`adverse_reaction` TEXT COMMENT '不良反应记录',

FOREIGN KEY (`appointment_id`) REFERENCES `appointment`(`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

表关系示意图：

TEXT

vaccination_site ← time_slot → appointment → vaccination_record

↑

vaccine ↗

3. 三大核心业务约束实现

3.1 防重复预约约束

业务需求：同一用户对同种疫苗只能存在一个有效预约（状态为待确认或已预约）

实现方案：

SQL

-- 唯一索引实现

ALTER TABLE `appointment`

ADD UNIQUE INDEX `idx_user_vaccine` (`user_id`, `vaccine_id`, `status`)

WHERE `status` IN (0, 1);

-- SpringBoot服务层校验

@Transactional

public AppointmentResult createAppointment(AppointmentRequest request) {

// 检查是否存在有效预约

long count = appointmentRepository.countByUserIdAndVaccineIdAndStatusIn(

request.getUserId(),

request.getVaccineId(),

Arrays.asList(Status.PENDING, Status.CONFIRMED)

);

if (count > 0) {

throw new BusinessException("已存在该疫苗的有效预约");

}

// 继续创建预约...

}

3.2 库存一致性约束

业务需求：预约确认后需扣减库存，取消预约需恢复库存

实现方案：

SQL

-- 使用触发器确保数据一致性

DELIMITER //

CREATE TRIGGER `after_appointment_update`

AFTER UPDATE ON `appointment`

FOR EACH ROW

BEGIN

IF NEW.status = 1 AND OLD.status = 0 THEN

-- 预约确认，扣减库存

UPDATE vaccine SET stock = stock - 1 WHERE id = NEW.vaccine_id;

ELSEIF NEW.status = 3 AND OLD.status = 1 THEN

-- 预约取消，恢复库存

UPDATE vaccine SET stock = stock + 1 WHERE id = NEW.vaccine_id;

END IF;

END//

DELIMITER ;

3.3 状态流转约束

业务需求：预约状态必须按待确认→已预约→已完成/已取消顺序流转

实现方案：

JAVA

// 使用状态机模式实现

public enum AppointmentStatus {

PENDING(0, Set.of(1, 3)),

CONFIRMED(1, Set.of(2, 3)),

COMPLETED(2, Set.of()),

CANCELLED(3, Set.of());

private final int code;

private final Set<Integer> allowedTransitions;

// 校验状态转换合法性

public static void validateTransition(int from, int to) {

AppointmentStatus fromStatus = Arrays.stream(values())

.filter(s -> s.code == from)

.findFirst()

.orElseThrow();

if (!fromStatus.allowedTransitions.contains(to)) {

throw new IllegalStateException("非法状态转换");

}

4. 高并发场景优化策略

4.1 预约时段库存控制

问题场景：热门时段可能出现超卖

解决方案：

SQL

-- 乐观锁实现

UPDATE time_slot

SET booked_count = booked_count + 1

WHERE id = ? AND booked_count < max_appointments;

4.2 分布式锁防重复提交

JAVA

// Redisson分布式锁示例

public AppointmentResult createAppointmentWithLock(AppointmentRequest request) {

String lockKey = "appointment:user:" + request.getUserId();

RLock lock = redissonClient.getLock(lockKey);

try {

boolean locked = lock.tryLock(3, 10, TimeUnit.SECONDS);

if (!locked) {

throw new BusinessException("系统繁忙，请稍后重试");

}

return createAppointment(request);

} finally {

lock.unlock();

}

4.3 读写分离配置

YAML

# application.yml

spring:

datasource:

master:

url: jdbc:mysql://master-host:3306/vaccine_db

username: root

password: master-pwd

slave:

url: jdbc:mysql://slave-host:3306/vaccine_db

username: root

password: slave-pwd

5. 数据安全与审计

5.1 敏感数据加密

JAVA

// 身份证号加密存储

@Column(name = "id_card")

@Convert(converter = CryptoConverter.class)

private String idCardNumber;

// 自定义加密转换器

public class CryptoConverter implements AttributeConverter<String, String> {

private static final String KEY = "secure-key-123";

@Override

public String convertToDatabaseColumn(String attribute) {

return AES.encrypt(attribute, KEY);

}

@Override

public String convertToEntityAttribute(String dbData) {

return AES.decrypt(dbData, KEY);

}

5.2 数据变更审计

SQL

-- 审计日志表

CREATE TABLE `audit_log` (

`id` BIGINT PRIMARY KEY AUTO_INCREMENT,

`table_name` VARCHAR(50) NOT NULL,

`record_id` BIGINT NOT NULL,

`operation` VARCHAR(20) NOT NULL COMMENT 'INSERT/UPDATE/DELETE',

`old_value` JSON COMMENT '变更前数据',

`new_value` JSON COMMENT '变更后数据',

`operator` VARCHAR(50) COMMENT '操作人',

`operation_time` DATETIME DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB;

5.3 隐私数据脱敏

JAVA

// 使用Jackson注解实现响应脱敏

public class UserDTO {

@JsonSerialize(using = MobileDesensitizer.class)

private String mobile;

@JsonSerialize(using = IdCardDesensitizer.class)

private String idCard;

}

// 手机号脱敏处理器

public class MobileDesensitizer extends JsonSerializer<String> {

@Override

public void serialize(String value, JsonGenerator gen, SerializerProvider provider) {

gen.writeString(value.replaceAll("(\\d{3})\\d{4}(\\d{4})", "$1****$2"));

}