当前位置：首页 > news >正文

SpringBoot 3 + Vue 3全栈竞赛管理系统开发实战

news 2026/7/31 2:53:43

最近在开发教育类管理系统时，发现很多学校对竞赛管理的需求越来越精细化。传统的Excel登记方式不仅效率低下，还容易出错。基于SpringBoot 3和Vue 3的全栈竞赛管理系统正好能解决这些问题，从报名审核到成绩统计实现全流程数字化管理。

本文将完整分享这套竞赛管理系统的开发全过程，包含环境搭建、核心功能实现、数据库设计等关键环节。无论你是需要毕业设计素材的在校学生，还是正在寻找Java实战项目的开发者，都能从中获得可直接复用的代码和配置方案。

1. 系统架构与技术选型

竞赛管理系统采用前后端分离架构，后端使用SpringBoot 3提供RESTful API，前端使用Vue 3构建用户界面，数据库选用MySQL 8.0。

1.1 技术栈说明

后端技术栈：

SpringBoot 3.0.0：提供核心Web框架和自动配置
Spring Security 6.0：负责系统安全和权限控制
MyBatis-Plus 3.5.0：简化数据库操作
MySQL 8.0：数据存储
Maven 3.8+：项目构建和依赖管理

前端技术栈：

Vue 3.2+：响应式前端框架
Element Plus 2.3.0：UI组件库
Axios 1.3.0：HTTP请求库
Vue Router 4.0：路由管理

1.2 系统模块划分

系统主要包含以下核心模块：

用户管理：管理员、教师、学生三级权限体系
竞赛管理：竞赛创建、发布、修改、删除
报名管理：学生报名、审核、分组
成绩管理：成绩录入、统计、排名
消息通知：系统公告、个人消息推送

2. 开发环境准备

在开始编码前，需要确保开发环境配置正确。以下是详细的环境要求说明。

2.1 基础环境配置

操作系统要求：

Windows 10/11 或 macOS 10.15+
Linux Ubuntu 18.04+（推荐用于生产环境）

Java环境配置：

# 检查Java版本 java -version # 应该输出：openjdk version "17.0.2" 2022-01-18 # 设置JAVA_HOME环境变量（Windows） set JAVA_HOME=C:\Program Files\Java\jdk-17 set PATH=%JAVA_HOME%\bin;%PATH% # Linux/macOS配置 export JAVA_HOME=/usr/lib/jvm/java-17-openjdk export PATH=$JAVA_HOME/bin:$PATH

Node.js环境：

# 检查Node.js版本 node --version # 应该 >= 16.0.0 npm --version # 应该 >= 8.0.0

2.2 数据库准备

创建数据库和配置用户权限：

-- 创建数据库 CREATE DATABASE competition_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 创建专用用户 CREATE USER 'comp_user'@'%' IDENTIFIED BY 'SecurePassword123!'; GRANT ALL PRIVILEGES ON competition_db.* TO 'comp_user'@'%'; FLUSH PRIVILEGES; -- 使用数据库 USE competition_db;

2.3 IDE和工具推荐

后端开发：

IntelliJ IDEA Ultimate（推荐）
或 Eclipse with Spring Tools Suite
必备插件：Lombok、MyBatisX

前端开发：

Visual Studio Code（推荐）
必备插件：Volar、TypeScript Vue Plugin、Element Plus Helper

3. 后端项目搭建

接下来从零开始搭建SpringBoot 3后端项目，包含完整的配置和代码示例。

3.1 项目结构创建

使用Spring Initializr创建项目基础结构：

# 使用curl创建项目骨架 curl https://start.spring.io/starter.zip \ -d dependencies=web,security,mybatis,mysql \ -d type=maven-project \ -d language=java \ -d bootVersion=3.0.0 \ -d baseDir=competition-backend \ -d groupId=com.education \ -d artifactId=competition-system \ -d name=competition-system \ -d description="学科竞赛管理系统" \ -d packageName=com.education.competition \ -d packaging=jar \ -d javaVersion=17 \ -o competition-backend.zip

解压后项目结构如下：

competition-backend/ ├── src/ │ ├── main/ │ │ ├── java/com/education/competition/ │ │ │ ├── controller/ # 控制器层 │ │ │ ├── service/ # 业务层 │ │ │ ├── mapper/ # 数据访问层 │ │ │ ├── entity/ # 实体类 │ │ │ ├── dto/ # 数据传输对象 │ │ │ ├── config/ # 配置类 │ │ │ └── CompetitionApplication.java │ │ └── resources/ │ │ ├── application.yml │ │ └── mapper/ # MyBatis XML文件 │ └── test/ # 测试代码 ├── pom.xml └── README.md

3.2 Maven依赖配置

完整的pom.xml依赖配置：

<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>3.0.0</version> <relativePath/> </parent> <groupId>com.education</groupId> <artifactId>competition-system</artifactId> <version>1.0.0</version> <name>competition-system</name> <description>学科竞赛管理系统</description> <properties> <java.version>17</java.version> <mybatis-plus.version>3.5.3</mybatis-plus.version> <jwt.version>0.11.5</jwt.version> </properties> <dependencies> <!-- Spring Boot Starter --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-security</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-validation</artifactId> </dependency> <!-- 数据库相关 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.32</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>${mybatis-plus.version}</version> </dependency> <!-- 工具类 --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>io.jsonwebtoken</groupId> <artifactId>jjwt-api</artifactId> <version>${jwt.version}</version> </dependency> <dependency> <groupId>io.jsonwebtoken</groupId> <artifactId>jjwt-impl</artifactId> <version>${jwt.version}</version> </dependency> <dependency> <groupId>io.jsonwebtoken</groupId> <artifactId>jjwt-jackson</artifactId> <version>${jwt.version}</version> </dependency> <!-- 测试 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <excludes> <exclude> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclude> </excludes> </configuration> </plugin> </plugins> </build> </project>

3.3 数据库配置

application.yml配置文件：

server: port: 8080 servlet: context-path: /api spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/competition_db?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8 username: comp_user password: SecurePassword123! jackson: date-format: yyyy-MM-dd HH:mm:ss time-zone: GMT+8 mybatis-plus: configuration: map-underscore-to-camel-case: true log-impl: org.apache.ibatis.logging.stdout.StdOutImpl global-config: db-config: id-type: auto logic-delete-field: deleted logic-delete-value: 1 logic-not-delete-value: 0 logging: level: com.education.competition.mapper: debug

3.4 核心实体类设计

设计竞赛管理系统的主要数据库表结构：

// 文件路径：src/main/java/com/education/competition/entity/User.java package com.education.competition.entity; import com.baomidou.mybatisplus.annotation.*; import lombok.Data; import java.time.LocalDateTime; @Data @TableName("sys_user") public class User { @TableId(type = IdType.AUTO) private Long id; private String username; private String password; private String realName; private Integer userType; // 0-管理员 1-教师 2-学生 private String email; private String phone; @TableField(fill = FieldFill.INSERT) private LocalDateTime createTime; @TableField(fill = FieldFill.INSERT_UPDATE) private LocalDateTime updateTime; @TableLogic private Integer deleted; } // 文件路径：src/main/java/com/education/competition/entity/Competition.java @Data @TableName("competition") public class Competition { @TableId(type = IdType.AUTO) private Long id; private String competitionName; private String description; private LocalDateTime startTime; private LocalDateTime endTime; private Integer maxParticipants; private Integer currentParticipants; private Integer status; // 0-未开始 1-进行中 2-已结束 private Long creatorId; @TableField(fill = FieldFill.INSERT) private LocalDateTime createTime; } // 文件路径：src/main/java/com/education/competition/entity/Registration.java @Data @TableName("registration") public class Registration { @TableId(type = IdType.AUTO) private Long id; private Long competitionId; private Long studentId; private Integer status; // 0-待审核 1-已通过 2-已拒绝 private String applicationReason; private LocalDateTime applyTime; private LocalDateTime auditTime; private Long auditorId; }

3.5 数据访问层实现

使用MyBatis-Plus简化数据库操作：

// 文件路径：src/main/java/com/education/competition/mapper/CompetitionMapper.java package com.education.competition.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.education.competition.entity.Competition; import org.apache.ibatis.annotations.Select; import java.util.List; public interface CompetitionMapper extends BaseMapper<Competition> { @Select("SELECT c.*, u.real_name as creator_name " + "FROM competition c " + "LEFT JOIN sys_user u ON c.creator_id = u.id " + "WHERE c.status = #{status} " + "ORDER BY c.create_time DESC") List<Competition> selectCompetitionWithCreator(Integer status); } // 文件路径：src/main/java/com/education/competition/mapper/UserMapper.java public interface UserMapper extends BaseMapper<User> { @Select("SELECT * FROM sys_user WHERE username = #{username} AND deleted = 0") User selectByUsername(String username); }

4. 前端Vue3项目搭建

前端项目使用Vite快速创建Vue3应用，集成Element Plus组件库。

4.1 项目初始化

# 使用Vite创建Vue3项目 npm create vue@latest competition-frontend cd competition-frontend # 安装必要依赖 npm install element-plus @element-plus/icons-vue npm install axios vue-router@4 pinia npm install sass --save-dev # 启动开发服务器 npm run dev

4.2 项目结构设计

competition-frontend/ ├── public/ # 静态资源 ├── src/ │ ├── api/ # API接口 │ ├── assets/ # 资源文件 │ ├── components/ # 公共组件 │ ├── router/ # 路由配置 │ ├── stores/ # 状态管理 │ ├── views/ # 页面组件 │ ├── utils/ # 工具函数 │ ├── App.vue # 根组件 │ └── main.js # 入口文件 ├── package.json └── vite.config.js

4.3 前端配置文件

vite.config.js配置：

import { defineConfig } from 'vite' import vue from '@vitejs/plugin-vue' import { resolve } from 'path' // https://vitejs.dev/config/ export default defineConfig({ plugins: [vue()], resolve: { alias: { '@': resolve(__dirname, 'src') } }, server: { port: 3000, proxy: { '/api': { target: 'http://localhost:8080', changeOrigin: true, rewrite: (path) => path.replace(/^\/api/, '/api') } } } })

main.js入口文件配置：

import { createApp } from 'vue' import App from './App.vue' import router from './router' import { createPinia } from 'pinia' // Element Plus引入 import ElementPlus from 'element-plus' import 'element-plus/dist/index.css' import * as ElementPlusIconsVue from '@element-plus/icons-vue' const app = createApp(App) // 注册所有图标 for (const [key, component] of Object.entries(ElementPlusIconsVue)) { app.component(key, component) } app.use(createPinia()) app.use(router) app.use(ElementPlus) app.mount('#app')

4.4 路由配置

router/index.js路由配置：

import { createRouter, createWebHistory } from 'vue-router' const routes = [ { path: '/', redirect: '/login' }, { path: '/login', name: 'Login', component: () => import('@/views/Login.vue') }, { path: '/dashboard', name: 'Dashboard', component: () => import('@/views/Dashboard.vue'), meta: { requiresAuth: true } }, { path: '/competition', name: 'Competition', component: () => import('@/views/competition/CompetitionList.vue'), meta: { requiresAuth: true } }, { path: '/competition/detail/:id', name: 'CompetitionDetail', component: () => import('@/views/competition/CompetitionDetail.vue'), meta: { requiresAuth: true } }, { path: '/registration', name: 'Registration', component: () => import('@/views/registration/RegistrationList.vue'), meta: { requiresAuth: true } } ] const router = createRouter({ history: createWebHistory(), routes }) // 路由守卫 router.beforeEach((to, from, next) => { const token = localStorage.getItem('token') if (to.meta.requiresAuth && !token) { next('/login') } else { next() } }) export default router

5. 核心功能实现

下面详细实现竞赛管理系统的几个核心功能模块。

5.1 用户登录认证

后端登录接口实现：

// 文件路径：src/main/java/com/education/competition/controller/AuthController.java package com.education.competition.controller; import com.education.competition.entity.User; import com.education.competition.service.UserService; import com.education.competition.utils.JwtUtil; import com.education.competition.vo.LoginVo; import com.education.competition.vo.Result; import lombok.RequiredArgsConstructor; import org.springframework.web.bind.annotation.*; import javax.validation.Valid; import java.util.HashMap; import java.util.Map; @RestController @RequestMapping("/auth") @RequiredArgsConstructor public class AuthController { private final UserService userService; private final JwtUtil jwtUtil; @PostMapping("/login") public Result<Map<String, Object>> login(@Valid @RequestBody LoginVo loginVo) { User user = userService.authenticate(loginVo.getUsername(), loginVo.getPassword()); if (user == null) { return Result.error("用户名或密码错误"); } String token = jwtUtil.generateToken(user.getUsername()); Map<String, Object> data = new HashMap<>(); data.put("token", token); data.put("userInfo", user); return Result.success(data); } @PostMapping("/logout") public Result<String> logout() { // 实际项目中可能需要在服务端维护token黑名单 return Result.success("退出成功"); } } // 文件路径：src/main/java/com/education/competition/vo/LoginVo.java @Data public class LoginVo { @NotBlank(message = "用户名不能为空") private String username; @NotBlank(message = "密码不能为空") private String password; }

前端登录页面实现：

<!-- 文件路径：src/views/Login.vue --> <template> <div class="login-container"> <el-card class="login-card"> <template #header> <div class="login-header"> <h2>竞赛管理系统</h2> </div> </template> <el-form :model="loginForm" :rules="loginRules" ref="loginFormRef"> <el-form-item prop="username"> <el-input v-model="loginForm.username" placeholder="请输入用户名" prefix-icon="User" size="large" /> </el-form-item> <el-form-item prop="password"> <el-input v-model="loginForm.password" type="password" placeholder="请输入密码" prefix-icon="Lock" size="large" show-password /> </el-form-item> <el-form-item> <el-button type="primary" size="large" style="width: 100%" :loading="loading" @click="handleLogin" > 登录 </el-button> </el-form-item> </el-form> </el-card> </div> </template> <script setup> import { ref, reactive } from 'vue' import { useRouter } from 'vue-router' import { ElMessage } from 'element-plus' import { login } from '@/api/auth' const router = useRouter() const loginFormRef = ref() const loading = ref(false) const loginForm = reactive({ username: '', password: '' }) const loginRules = { username: [{ required: true, message: '请输入用户名', trigger: 'blur' }], password: [{ required: true, message: '请输入密码', trigger: 'blur' }] } const handleLogin = async () => { if (!loginFormRef.value) return try { const valid = await loginFormRef.value.validate() if (!valid) return loading.value = true const response = await login(loginForm) if (response.code === 200) { localStorage.setItem('token', response.data.token) localStorage.setItem('userInfo', JSON.stringify(response.data.userInfo)) ElMessage.success('登录成功') router.push('/dashboard') } } catch (error) { ElMessage.error(error.message || '登录失败') } finally { loading.value = false } } </script> <style scoped> .login-container { display: flex; justify-content: center; align-items: center; min-height: 100vh; background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); } .login-card { width: 400px; } .login-header { text-align: center; color: #409EFF; } </style>

5.2 竞赛管理功能

后端竞赛控制器实现：

// 文件路径：src/main/java/com/education/competition/controller/CompetitionController.java package com.education.competition.controller; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.education.competition.entity.Competition; import com.education.competition.service.CompetitionService; import com.education.competition.vo.Result; import lombok.RequiredArgsConstructor; import org.springframework.web.bind.annotation.*; @RestController @RequestMapping("/competition") @RequiredArgsConstructor public class CompetitionController { private final CompetitionService competitionService; @GetMapping("/list") public Result<Page<Competition>> getCompetitionList( @RequestParam(defaultValue = "1") Integer page, @RequestParam(defaultValue = "10") Integer size, @RequestParam(required = false) Integer status) { Page<Competition> competitionPage = competitionService.getCompetitionPage(page, size, status); return Result.success(competitionPage); } @PostMapping("/create") public Result<String> createCompetition(@RequestBody Competition competition) { boolean success = competitionService.createCompetition(competition); return success ? Result.success("创建成功") : Result.error("创建失败"); } @PutMapping("/update/{id}") public Result<String> updateCompetition(@PathVariable Long id, @RequestBody Competition competition) { competition.setId(id); boolean success = competitionService.updateCompetition(competition); return success ? Result.success("更新成功") : Result.error("更新失败"); } @DeleteMapping("/delete/{id}") public Result<String> deleteCompetition(@PathVariable Long id) { boolean success = competitionService.deleteCompetition(id); return success ? Result.success("删除成功") : Result.error("删除失败"); } }

前端竞赛列表页面：

<!-- 文件路径：src/views/competition/CompetitionList.vue --> <template> <div class="competition-container"> <el-card> <template #header> <div class="header-actions"> <span>竞赛列表</span> <el-button type="primary" @click="handleCreate"> <el-icon><Plus /></el-icon> 新建竞赛 </el-button> </div> </template> <!-- 搜索条件 --> <el-form :model="searchForm" inline> <el-form-item label="竞赛状态"> <el-select v-model="searchForm.status" placeholder="请选择状态"> <el-option label="全部" value=""></el-option> <el-option label="未开始" :value="0"></el-option> <el-option label="进行中" :value="1"></el-option> <el-option label="已结束" :value="2"></el-option> </el-select> </el-form-item> <el-form-item> <el-button type="primary" @click="loadCompetitions">搜索</el-button> <el-button @click="resetSearch">重置</el-button> </el-form-item> </el-form> <!-- 数据表格 --> <el-table :data="competitionList" v-loading="loading"> <el-table-column prop="competitionName" label="竞赛名称" min-width="200" /> <el-table-column prop="description" label="描述" min-width="300" show-overflow-tooltip /> <el-table-column prop="startTime" label="开始时间" width="180"> <template #default="{ row }"> {{ formatDate(row.startTime) }} </template> </el-table-column> <el-table-column prop="currentParticipants" label="参赛人数" width="100" /> <el-table-column prop="status" label="状态" width="100"> <template #default="{ row }"> <el-tag :type="getStatusType(row.status)"> {{ getStatusText(row.status) }} </el-tag> </template> </el-table-column> <el-table-column label="操作" width="200" fixed="right"> <template #default="{ row }"> <el-button link type="primary" @click="handleView(row.id)">查看</el-button> <el-button link type="warning" @click="handleEdit(row)">编辑</el-button> <el-button link type="danger" @click="handleDelete(row.id)">删除</el-button> </template> </el-table-column> </el-table> <!-- 分页 --> <div class="pagination-container"> <el-pagination v-model:current-page="pagination.current" v-model:page-size="pagination.size" :total="pagination.total" @current-change="handlePageChange" layout="total, sizes, prev, pager, next, jumper" :page-sizes="[10, 20, 50, 100]" /> </div> </el-card> <!-- 新建/编辑对话框 --> <competition-dialog v-model="dialogVisible" :competition="currentCompetition" @success="handleDialogSuccess" /> </div> </template> <script setup> import { ref, reactive, onMounted } from 'vue' import { useRouter } from 'vue-router' import { ElMessage, ElMessageBox } from 'element-plus' import { getCompetitionList, deleteCompetition } from '@/api/competition' import CompetitionDialog from './components/CompetitionDialog.vue' const router = useRouter() const loading = ref(false) const dialogVisible = ref(false) const currentCompetition = ref(null) const searchForm = reactive({ status: '' }) const pagination = reactive({ current: 1, size: 10, total: 0 }) const competitionList = ref([]) const loadCompetitions = async () => { try { loading.value = true const params = { page: pagination.current, size: pagination.size, ...searchForm } const response = await getCompetitionList(params) if (response.code === 200) { competitionList.value = response.data.records pagination.total = response.data.total } } catch (error) { ElMessage.error('获取竞赛列表失败') } finally { loading.value = false } } const handleCreate = () => { currentCompetition.value = null dialogVisible.value = true } const handleEdit = (competition) => { currentCompetition.value = { ...competition } dialogVisible.value = true } const handleView = (id) => { router.push(`/competition/detail/${id}`) } const handleDelete = async (id) => { try { await ElMessageBox.confirm('确定要删除这个竞赛吗？', '提示', { type: 'warning' }) const response = await deleteCompetition(id) if (response.code === 200) { ElMessage.success('删除成功') loadCompetitions() } } catch (error) { // 用户取消删除 } } const handleDialogSuccess = () => { dialogVisible.value = false loadCompetitions() } const handlePageChange = (page) => { pagination.current = page loadCompetitions() } const resetSearch = () => { Object.keys(searchForm).forEach(key => { searchForm[key] = '' }) loadCompetitions() } const formatDate = (dateString) => { return new Date(dateString).toLocaleString() } const getStatusText = (status) => { const statusMap = { 0: '未开始', 1: '进行中', 2: '已结束' } return statusMap[status] || '未知' } const getStatusType = (status) => { const typeMap = { 0: 'info', 1: 'success', 2: 'warning' } return typeMap[status] || 'info' } onMounted(() => { loadCompetitions() }) </script> <style scoped> .header-actions { display: flex; justify-content: space-between; align-items: center; } .pagination-container { margin-top: 20px; text-align: right; } </style>

6. 数据库设计与优化

合理的数据库设计是系统性能的基础，以下是核心表结构和优化建议。

6.1 完整数据库表结构

-- 用户表 CREATE TABLE sys_user ( id BIGINT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名', password VARCHAR(100) NOT NULL COMMENT '密码', real_name VARCHAR(50) NOT NULL COMMENT '真实姓名', user_type TINYINT NOT NULL DEFAULT 2 COMMENT '用户类型：0-管理员 1-教师 2-学生', email VARCHAR(100) COMMENT '邮箱', phone VARCHAR(20) COMMENT '手机号', create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted TINYINT DEFAULT 0 COMMENT '逻辑删除：0-未删除 1-已删除', INDEX idx_username (username), INDEX idx_user_type (user_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表'; -- 竞赛表 CREATE TABLE competition ( id BIGINT AUTO_INCREMENT PRIMARY KEY, competition_name VARCHAR(200) NOT NULL COMMENT '竞赛名称', description TEXT COMMENT '竞赛描述', start_time DATETIME NOT NULL COMMENT '开始时间', end_time DATETIME NOT NULL COMMENT '结束时间', max_participants INT DEFAULT 0 COMMENT '最大参赛人数', current_participants INT DEFAULT 0 COMMENT '当前参赛人数', status TINYINT DEFAULT 0 COMMENT '状态：0-未开始 1-进行中 2-已结束', creator_id BIGINT NOT NULL COMMENT '创建人ID', create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_status (status), INDEX idx_creator (creator_id), INDEX idx_time_range (start_time, end_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='竞赛表'; -- 报名表 CREATE TABLE registration ( id BIGINT AUTO_INCREMENT PRIMARY KEY, competition_id BIGINT NOT NULL COMMENT '竞赛ID', student_id BIGINT NOT NULL COMMENT '学生ID', status TINYINT DEFAULT 0 COMMENT '状态：0-待审核 1-已通过 2-已拒绝', application_reason TEXT COMMENT '申请理由', apply_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间', audit_time DATETIME COMMENT '审核时间', auditor_id BIGINT COMMENT '审核人ID', INDEX idx_competition (competition_id), INDEX idx_student (student_id), INDEX idx_status (status), UNIQUE KEY uk_competition_student (competition_id, student_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报名表'; -- 成绩表 CREATE TABLE score ( id BIGINT AUTO_INCREMENT PRIMARY KEY, competition_id BIGINT NOT NULL COMMENT '竞赛ID', student_id BIGINT NOT NULL COMMENT '学生ID', score DECIMAL(5,2) COMMENT '得分', ranking INT COMMENT '排名', judge_comment TEXT COMMENT '评委评语', judge_id BIGINT COMMENT '评委ID', create_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_competition (competition_id), INDEX idx_student (student_id), INDEX idx_score (score DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成绩表';

6.2 数据库性能优化建议

索引优化策略
- 为经常查询的字段建立复合索引
- 避免在索引列上使用函数或计算
- 定期分析慢查询日志优化SQL
分表分库考虑
- 当单表数据超过500万时考虑分表
- 按年份或竞赛类型进行水平分表
- 读写分离提升查询性能
SQL优化技巧
- 使用EXPLAIN分析查询计划
- 避免SELECT *，只查询需要的字段
- 使用连接查询替代子查询

7. 系统部署与运维

完成开发后，需要将系统部署到生产环境。

7.1 后端部署配置

Docker部署配置：

# Dockerfile FROM openjdk:17-jdk-slim # 设置工作目录 WORKDIR /app # 复制jar包 COPY target/competition-system-1.0.0.jar app.jar # 暴露端口 EXPOSE 8080 # 启动命令 ENTRYPOINT ["java", "-jar", "app.jar", "--spring.profiles.active=prod"]

docker-compose.yml配置：

version: '3.8' services: mysql: image: mysql:8.0 container_name: competition-mysql environment: MYSQL_ROOT_PASSWORD: root123456 MYSQL_DATABASE: competition_db MYSQL_USER: comp_user MYSQL_PASSWORD: SecurePassword123! ports: - "3306:3306" volumes: - mysql_data:/var/lib/mysql - ./sql/init.sql:/docker-entrypoint-initdb.d/init.sql networks: - competition-net backend: image: competition-backend:1.0.

查看全文

http://www.jsqmd.com/news/1297627/