13 接入 MySQL:让数据在服务重启后仍存在
13 接入 MySQL:让数据在服务重启后仍存在
本章你会做出什么
在前十二章,内存模式已经能完整演示项目。本章是持久化加分项:使用 Docker Compose 启动 MySQL,建立表结构和演示数据,并通过环境变量将存储实现从 MemoryStore 切换为 MysqlStore。
先理解三个概念
1. 表、主键与外键
- 表保存同类数据,例如
users保存用户。 - 主键
id唯一标识一行。 - 外键表示关联,例如
tickets.creator_id指向创建人的users.id。
2. 内存模式与 MySQL 模式
业务 Service 依赖的是 TicketStore 接口。只要 MemoryStore 和 MysqlStore 都实现相同方法,路由和前端不需要随数据库变化而改写。
3. Docker Compose
Docker 能在容器中运行数据库,避免手动安装 MySQL。Compose 文件记录镜像、端口、密码和数据卷,团队成员可使用相同配置。
本章最终目录变化
servicedesk-practice/
docker-compose.yml
server/
.env.example
sql/schema.sql
src/
store/mysql-store.ts
scripts/init-mysql.ts
一步一步操作
第 1 步:安装 Docker Desktop 和 MySQL 驱动
安装 Docker Desktop 并确认程序已启动。然后在项目根目录运行:
npm install -w server mysql2
mkdir server\sql
mkdir server\src\scripts
第 2 步:创建数据库容器配置
新建根目录 docker-compose.yml:
services:
mysql:
image: mysql:8.4
container_name: servicedesk-mysql
environment:
MYSQL_DATABASE: servicedesk
MYSQL_USER: servicedesk
MYSQL_PASSWORD: servicedesk
MYSQL_ROOT_PASSWORD: root
ports:
- '3306:3306'
volumes:
- servicedesk_mysql:/var/lib/mysql
healthcheck:
test: ['CMD-SHELL', 'mysqladmin ping -h 127.0.0.1 -uservicedesk -pservicedesk']
interval: 5s
timeout: 5s
retries: 20
volumes:
servicedesk_mysql:
启动数据库:
docker compose up -d mysql
docker compose ps
等待状态显示为健康后继续。
第 3 步:扩展环境变量
server/.env.example 完整内容:
PORT=3001
JWT_SECRET=replace-this-in-production
DB_DRIVER=memory
MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_USER=servicedesk
MYSQL_PASSWORD=servicedesk
MYSQL_DATABASE=servicedesk
复制本地配置并将驱动改成 MySQL:
Copy-Item server\.env.example server\.env
在 server/.env 中修改:
DB_DRIVER=mysql
不要提交带有真实生产密码的 .env。
将第 4 章的 server/src/config.ts 替换为可以读取数据库配置的完整版本:
import 'dotenv/config'
export interface AppConfig {
port: number
jwtSecret: string
dbDriver: 'memory' | 'mysql'
mysql: {
host: string
port: number
user: string
password: string
database: string
}
}
export const getConfig = (): AppConfig => ({
port: Number(process.env.PORT ?? 3001),
jwtSecret: process.env.JWT_SECRET ?? 'servicedesk-demo-secret',
dbDriver: process.env.DB_DRIVER === 'mysql' ? 'mysql' : 'memory',
mysql: {
host: process.env.MYSQL_HOST ?? '127.0.0.1',
port: Number(process.env.MYSQL_PORT ?? 3306),
user: process.env.MYSQL_USER ?? 'servicedesk',
password: process.env.MYSQL_PASSWORD ?? 'servicedesk',
database: process.env.MYSQL_DATABASE ?? 'servicedesk'
}
})
第 4 步:创建表结构
关键文件完整代码
server/sql/schema.sql
CREATE TABLE IF NOT EXISTS roles (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
code VARCHAR(30) NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL,
description VARCHAR(255) NOT NULL
);
CREATE TABLE IF NOT EXISTS permissions (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
code VARCHAR(80) NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL,
type VARCHAR(20) NOT NULL,
route_path VARCHAR(100) NULL
);
CREATE TABLE IF NOT EXISTS role_permissions (
role_id BIGINT NOT NULL,
permission_id BIGINT NOT NULL,
PRIMARY KEY (role_id, permission_id),
FOREIGN KEY (role_id) REFERENCES roles(id),
FOREIGN KEY (permission_id) REFERENCES permissions(id)
);
CREATE TABLE IF NOT EXISTS users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
role_id BIGINT NOT NULL,
status TINYINT NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (role_id) REFERENCES roles(id)
);
CREATE TABLE IF NOT EXISTS ticket_categories (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
enabled TINYINT NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS tickets (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
ticket_no VARCHAR(30) NOT NULL UNIQUE,
title VARCHAR(100) NOT NULL,
description TEXT NOT NULL,
category_id BIGINT NOT NULL,
priority VARCHAR(20) NOT NULL,
status VARCHAR(30) NOT NULL,
creator_id BIGINT NOT NULL,
assignee_id BIGINT NULL,
due_at DATETIME NOT NULL,
accepted_at DATETIME NULL,
resolved_at DATETIME NULL,
closed_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES ticket_categories(id),
FOREIGN KEY (creator_id) REFERENCES users(id),
FOREIGN KEY (assignee_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS ticket_comments (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
ticket_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
content TEXT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (ticket_id) REFERENCES tickets(id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS ticket_status_logs (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
ticket_id BIGINT NOT NULL,
from_status VARCHAR(30) NULL,
to_status VARCHAR(30) NOT NULL,
operator_id BIGINT NOT NULL,
remark VARCHAR(500) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (ticket_id) REFERENCES tickets(id),
FOREIGN KEY (operator_id) REFERENCES users(id)
);
server/src/store/mysql-store.ts(替换完整文件)
MysqlStore 的每个方法与 MemoryStore 对应,只是数组查询换成参数化 SQL。下面文件完整覆盖登录、后台配置、工单读写、评论和状态日志,问号占位可避免将用户输入直接拼进语句。
import { createPool, type Pool, type ResultSetHeader, type RowDataPacket } from 'mysql2/promise'
import { PERMISSIONS } from '../constants.js'
import type { AppConfig } from '../config.js'
import type {
Category,
Pagination,
Permission,
RoleCode,
Ticket,
TicketComment,
TicketLog,
TicketQuery,
TicketStatus,
UserQuery,
UserRecord
} from '../types.js'
import type { TicketStore } from './store.js'
type MysqlConfig = AppConfig['mysql']
type SqlValue = string | number | boolean | Date | null
export class MysqlStore implements TicketStore {
private readonly pool: Pool
constructor(config: MysqlConfig) {
this.pool = createPool({ ...config, waitForConnections: true, connectionLimit: 10, dateStrings: true })
}
async initialize() {
await this.pool.query('SELECT 1')
}
async findUserById(id: number) {
const [rows] = await this.pool.execute<RowDataPacket[]>('SELECT * FROM users WHERE id = ?', [id])
return rows[0] ? this.mapUser(rows[0]) : undefined
}
async findUserByUsername(username: string) {
const [rows] = await this.pool.execute<RowDataPacket[]>('SELECT * FROM users WHERE username = ?', [username])
return rows[0] ? this.mapUser(rows[0]) : undefined
}
async listUsers(query: UserQuery) {
const where: string[] = []
const values: SqlValue[] = []
if (query.keyword) {
where.push('(u.name LIKE ? OR u.username LIKE ?)')
values.push(`%${query.keyword}%`, `%${query.keyword}%`)
}
if (query.roleCode) {
where.push('r.code = ?')
values.push(query.roleCode)
}
if (query.status !== undefined) {
where.push('u.status = ?')
values.push(query.status)
}
const condition = where.length ? `WHERE ${where.join(' AND ')}` : ''
const [countRows] = await this.pool.execute<RowDataPacket[]>(
`SELECT COUNT(*) AS total FROM users u JOIN roles r ON r.id = u.role_id ${condition}`,
values
)
const [rows] = await this.pool.execute<RowDataPacket[]>(
`SELECT u.* FROM users u JOIN roles r ON r.id = u.role_id ${condition} ORDER BY u.created_at DESC LIMIT ? OFFSET ?`,
[...values, query.pageSize, (query.page - 1) * query.pageSize]
)
return {
list: rows.map((row) => this.mapUser(row)),
page: query.page,
pageSize: query.pageSize,
total: Number(countRows[0].total)
}
}
async createUser(input: Omit<UserRecord, 'id' | 'createdAt' | 'updatedAt'>) {
const [result] = await this.pool.execute<ResultSetHeader>(
'INSERT INTO users (username, password_hash, name, email, role_id, status) VALUES (?, ?, ?, ?, ?, ?)',
[input.username, input.passwordHash, input.name, input.email, input.roleId, input.status]
)
return (await this.findUserById(result.insertId))!
}
async updateUser(id: number, input: Partial<Pick<UserRecord, 'name' | 'email' | 'roleId' | 'status'>>) {
const columns: string[] = []
const values: SqlValue[] = []
const mapping = { name: 'name', email: 'email', roleId: 'role_id', status: 'status' } as const
for (const [key, column] of Object.entries(mapping)) {
const value = input[key as keyof typeof mapping]
if (value !== undefined) {
columns.push(`${column} = ?`)
values.push(value)
}
}
if (!columns.length) return this.findUserById(id)
const [result] = await this.pool.execute<ResultSetHeader>(
`UPDATE users SET ${columns.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
[...values, id]
)
return result.affectedRows ? this.findUserById(id) : undefined
}
async listRoles() {
const [rows] = await this.pool.execute<RowDataPacket[]>(
`SELECT r.*, GROUP_CONCAT(p.code ORDER BY p.id) AS permission_codes
FROM roles r
LEFT JOIN role_permissions rp ON rp.role_id = r.id
LEFT JOIN permissions p ON p.id = rp.permission_id
GROUP BY r.id ORDER BY r.id`
)
return rows.map((row) => ({
id: Number(row.id),
code: row.code as RoleCode,
name: row.name,
description: row.description,
permissionCodes: row.permission_codes ? String(row.permission_codes).split(',') : []
}))
}
async updateRolePermissions(roleId: number, permissionCodes: string[]) {
const connection = await this.pool.getConnection()
try {
await connection.beginTransaction()
await connection.execute('DELETE FROM role_permissions WHERE role_id = ?', [roleId])
if (permissionCodes.length) {
await connection.query(
`INSERT INTO role_permissions (role_id, permission_id)
SELECT ?, id FROM permissions WHERE code IN (${permissionCodes.map(() => '?').join(', ')})`,
[roleId, ...permissionCodes]
)
}
await connection.commit()
} catch (error) {
await connection.rollback()
throw error
} finally {
connection.release()
}
return (await this.listRoles()).find((role) => role.id === roleId)
}
async listPermissions(): Promise<Permission[]> {
const [rows] = await this.pool.execute<RowDataPacket[]>('SELECT * FROM permissions ORDER BY id')
return rows.length
? rows.map((row) => ({
id: Number(row.id),
code: row.code,
name: row.name,
type: row.type,
routePath: row.route_path ?? undefined
}))
: PERMISSIONS
}
async listCategories(includeDisabled: boolean) {
const [rows] = await this.pool.execute<RowDataPacket[]>(
`SELECT * FROM ticket_categories ${includeDisabled ? '' : 'WHERE enabled = 1'} ORDER BY id`
)
return rows.map((row) => this.mapCategory(row))
}
async createCategory(name: string) {
const [result] = await this.pool.execute<ResultSetHeader>('INSERT INTO ticket_categories (name, enabled) VALUES (?, 1)', [
name
])
const [rows] = await this.pool.execute<RowDataPacket[]>('SELECT * FROM ticket_categories WHERE id = ?', [result.insertId])
return this.mapCategory(rows[0])
}
async updateCategory(id: number, input: Partial<Pick<Category, 'name' | 'enabled'>>) {
const columns: string[] = []
const values: SqlValue[] = []
if (input.name !== undefined) {
columns.push('name = ?')
values.push(input.name)
}
if (input.enabled !== undefined) {
columns.push('enabled = ?')
values.push(input.enabled ? 1 : 0)
}
if (columns.length)
await this.pool.execute(`UPDATE ticket_categories SET ${columns.join(', ')} WHERE id = ?`, [...values, id])
const [rows] = await this.pool.execute<RowDataPacket[]>('SELECT * FROM ticket_categories WHERE id = ?', [id])
return rows[0] ? this.mapCategory(rows[0]) : undefined
}
async listTickets(query: TicketQuery): Promise<Pagination<Ticket>> {
const where: string[] = []
const values: SqlValue[] = []
if (query.scope === 'none') where.push('1 = 0')
if (query.scope === 'created') {
where.push('t.creator_id = ?')
values.push(query.scopeUserId ?? null)
}
if (query.scope === 'assigned') {
where.push('t.assignee_id = ?')
values.push(query.scopeUserId ?? null)
}
if (query.keyword) {
where.push('(t.title LIKE ? OR t.ticket_no LIKE ?)')
values.push(`%${query.keyword}%`, `%${query.keyword}%`)
}
if (query.status) {
where.push('t.status = ?')
values.push(query.status)
}
if (query.priority) {
where.push('t.priority = ?')
values.push(query.priority)
}
if (query.assigneeId) {
where.push('t.assignee_id = ?')
values.push(query.assigneeId)
}
const condition = where.length ? `WHERE ${where.join(' AND ')}` : ''
const [countRows] = await this.pool.execute<RowDataPacket[]>(`SELECT COUNT(*) AS total FROM tickets t ${condition}`, values)
const [rows] = await this.pool.execute<RowDataPacket[]>(
`${this.ticketSelect()} ${condition} ORDER BY t.created_at DESC LIMIT ? OFFSET ?`,
[...values, query.pageSize, (query.page - 1) * query.pageSize]
)
return {
list: rows.map((row) => this.mapTicket(row)),
page: query.page,
pageSize: query.pageSize,
total: Number(countRows[0].total)
}
}
async findTicketById(id: number) {
const [rows] = await this.pool.execute<RowDataPacket[]>(`${this.ticketSelect()} WHERE t.id = ?`, [id])
return rows[0] ? this.mapTicket(rows[0]) : undefined
}
async createTicket(input: Omit<Ticket, 'id' | 'ticketNo'>) {
const ticketNo = `WO${input.createdAt.slice(0, 10).replaceAll('-', '')}${String(Date.now()).slice(-5)}`
const [result] = await this.pool.execute<ResultSetHeader>(
`INSERT INTO tickets
(ticket_no, title, description, category_id, priority, status, creator_id, assignee_id, due_at, accepted_at, resolved_at, closed_at, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
ticketNo,
input.title,
input.description,
input.categoryId,
input.priority,
input.status,
input.creatorId,
input.assigneeId,
input.dueAt,
input.acceptedAt,
input.resolvedAt,
input.closedAt,
input.createdAt,
input.updatedAt
]
)
return (await this.findTicketById(result.insertId))!
}
async updateTicket(id: number, input: Partial<Ticket>) {
const mapping: Record<string, string> = {
title: 'title',
description: 'description',
categoryId: 'category_id',
priority: 'priority',
status: 'status',
assigneeId: 'assignee_id',
dueAt: 'due_at',
acceptedAt: 'accepted_at',
resolvedAt: 'resolved_at',
closedAt: 'closed_at'
}
const columns: string[] = []
const values: SqlValue[] = []
Object.entries(mapping).forEach(([field, column]) => {
if (Object.prototype.hasOwnProperty.call(input, field)) {
columns.push(`${column} = ?`)
values.push((input[field as keyof Ticket] ?? null) as SqlValue)
}
})
if (columns.length) {
await this.pool.execute(`UPDATE tickets SET ${columns.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`, [
...values,
id
])
}
return this.findTicketById(id)
}
async listComments(ticketId: number) {
const [rows] = await this.pool.execute<RowDataPacket[]>(
`SELECT c.*, u.name AS user_name FROM ticket_comments c JOIN users u ON u.id = c.user_id
WHERE c.ticket_id = ? ORDER BY c.created_at ASC`,
[ticketId]
)
return rows.map((row) => ({
id: Number(row.id),
ticketId: Number(row.ticket_id),
userId: Number(row.user_id),
userName: row.user_name,
content: row.content,
createdAt: this.iso(row.created_at)
}))
}
async addComment(input: Omit<TicketComment, 'id'>) {
const [result] = await this.pool.execute<ResultSetHeader>(
'INSERT INTO ticket_comments (ticket_id, user_id, content, created_at) VALUES (?, ?, ?, ?)',
[input.ticketId, input.userId, input.content, input.createdAt]
)
const comments = await this.listComments(input.ticketId)
return comments.find((comment) => comment.id === result.insertId)!
}
async listLogs(ticketId: number) {
const [rows] = await this.pool.execute<RowDataPacket[]>(
`SELECT l.*, u.name AS operator_name FROM ticket_status_logs l JOIN users u ON u.id = l.operator_id
WHERE l.ticket_id = ? ORDER BY l.created_at DESC`,
[ticketId]
)
return rows.map((row) => ({
id: Number(row.id),
ticketId: Number(row.ticket_id),
fromStatus: row.from_status as TicketStatus | null,
toStatus: row.to_status as TicketStatus,
operatorId: Number(row.operator_id),
operatorName: row.operator_name,
remark: row.remark,
createdAt: this.iso(row.created_at)
}))
}
async addLog(input: Omit<TicketLog, 'id'>) {
const [result] = await this.pool.execute<ResultSetHeader>(
`INSERT INTO ticket_status_logs (ticket_id, from_status, to_status, operator_id, remark, created_at)
VALUES (?, ?, ?, ?, ?, ?)`,
[input.ticketId, input.fromStatus, input.toStatus, input.operatorId, input.remark, input.createdAt]
)
return { ...input, id: result.insertId }
}
private ticketSelect() {
return `SELECT t.*, c.name AS category_name, creator.name AS creator_name, assignee.name AS assignee_name
FROM tickets t
JOIN ticket_categories c ON c.id = t.category_id
JOIN users creator ON creator.id = t.creator_id
LEFT JOIN users assignee ON assignee.id = t.assignee_id`
}
private mapUser(row: RowDataPacket): UserRecord {
return {
id: Number(row.id),
username: row.username,
passwordHash: row.password_hash,
name: row.name,
email: row.email,
roleId: Number(row.role_id),
status: Number(row.status) as 0 | 1,
createdAt: this.iso(row.created_at),
updatedAt: this.iso(row.updated_at)
}
}
private mapCategory(row: RowDataPacket): Category {
return { id: Number(row.id), name: row.name, enabled: Boolean(row.enabled), createdAt: this.iso(row.created_at) }
}
private mapTicket(row: RowDataPacket): Ticket {
return {
id: Number(row.id),
ticketNo: row.ticket_no,
title: row.title,
description: row.description,
categoryId: Number(row.category_id),
categoryName: row.category_name,
priority: row.priority,
status: row.status,
creatorId: Number(row.creator_id),
creatorName: row.creator_name,
assigneeId: row.assignee_id === null ? null : Number(row.assignee_id),
assigneeName: row.assignee_name,
dueAt: this.iso(row.due_at),
acceptedAt: row.accepted_at ? this.iso(row.accepted_at) : null,
resolvedAt: row.resolved_at ? this.iso(row.resolved_at) : null,
closedAt: row.closed_at ? this.iso(row.closed_at) : null,
createdAt: this.iso(row.created_at),
updatedAt: this.iso(row.updated_at)
}
}
private iso(value: string | Date) {
return new Date(value).toISOString()
}
}
server/src/scripts/init-mysql.ts(替换完整文件)
初始化会重建演示数据。预置的处理中、待确认和已关闭工单都会写入与当前状态匹配的完整时间线:
import { readFile } from 'node:fs/promises'
import { hash } from 'bcryptjs'
import { createConnection, type ResultSetHeader } from 'mysql2/promise'
import { DEFAULT_ROLES, PERMISSIONS, SLA_HOURS } from '../constants.js'
import { getConfig } from '../config.js'
import type { TicketPriority, TicketStatus } from '../types.js'
const config = getConfig().mysql
const connection = await createConnection({ ...config, multipleStatements: true })
const schema = await readFile(new URL('../../sql/schema.sql', import.meta.url), 'utf8')
await connection.query(schema)
await connection.query('SET FOREIGN_KEY_CHECKS = 0')
await connection.query(
'TRUNCATE TABLE ticket_status_logs; TRUNCATE TABLE ticket_comments; TRUNCATE TABLE tickets; TRUNCATE TABLE ticket_categories; TRUNCATE TABLE role_permissions; TRUNCATE TABLE users; TRUNCATE TABLE permissions; TRUNCATE TABLE roles;'
)
await connection.query('SET FOREIGN_KEY_CHECKS = 1')
for (const role of DEFAULT_ROLES) {
await connection.execute('INSERT INTO roles (id, code, name, description) VALUES (?, ?, ?, ?)', [
role.id,
role.code,
role.name,
role.description
])
}
for (const permission of PERMISSIONS) {
await connection.execute('INSERT INTO permissions (id, code, name, type, route_path) VALUES (?, ?, ?, ?, ?)', [
permission.id,
permission.code,
permission.name,
permission.type,
permission.routePath ?? null
])
}
for (const role of DEFAULT_ROLES) {
for (const permissionCode of role.permissionCodes) {
await connection.execute(
'INSERT INTO role_permissions (role_id, permission_id) SELECT ?, id FROM permissions WHERE code = ?',
[role.id, permissionCode]
)
}
}
const passwordHash = await hash('123456', 8)
const users = [
['user', '张三', 'user@demo.com', 1],
['agent', '王工', 'agent@demo.com', 2],
['supervisor', '李主管', 'supervisor@demo.com', 3],
['admin', '系统管理员', 'admin@demo.com', 4]
] as const
for (const [username, name, email, roleId] of users) {
await connection.execute(
'INSERT INTO users (username, password_hash, name, email, role_id, status) VALUES (?, ?, ?, ?, ?, 1)',
[username, passwordHash, name, email, roleId]
)
}
for (const name of ['账号权限', '网络问题', '设备故障', '软件安装', '其他问题']) {
await connection.execute('INSERT INTO ticket_categories (name, enabled) VALUES (?, 1)', [name])
}
const daysAgo = (days: number, hours = 9) => {
const date = new Date()
date.setDate(date.getDate() - days)
date.setHours(hours, 0, 0, 0)
return date
}
const minutesAfter = (date: Date, minutes: number) => new Date(date.getTime() + minutes * 60 * 1000)
const seedTickets: Array<{
title: string
description: string
categoryId: number
priority: TicketPriority
status: TicketStatus
creatorId: number
assigneeId: number | null
createdAt: Date
}> = [
{
title: '办公网络无法连接',
description: '三楼工位网络无法获取地址,请协助检查。',
categoryId: 2,
priority: 'HIGH',
status: 'PROCESSING',
creatorId: 1,
assigneeId: 2,
createdAt: daysAgo(0, 8)
},
{
title: '申请设计软件安装权限',
description: '新项目需要安装设计工具。',
categoryId: 4,
priority: 'LOW',
status: 'PENDING',
creatorId: 1,
assigneeId: null,
createdAt: daysAgo(1)
},
{
title: '邮箱账号无法登录',
description: '重置密码后仍提示认证失败。',
categoryId: 1,
priority: 'MEDIUM',
status: 'WAITING_CONFIRM',
creatorId: 1,
assigneeId: 2,
createdAt: daysAgo(2)
},
{
title: '会议室投屏设备故障',
description: 'HDMI 接入后无显示。',
categoryId: 3,
priority: 'MEDIUM',
status: 'CLOSED',
creatorId: 3,
assigneeId: 2,
createdAt: daysAgo(4)
},
{
title: 'VPN 连接间歇中断',
description: '远程办公时频繁断线。',
categoryId: 2,
priority: 'HIGH',
status: 'CLOSED',
creatorId: 1,
assigneeId: 2,
createdAt: daysAgo(6)
}
]
for (const [index, ticket] of seedTickets.entries()) {
const acceptedAt = ticket.status === 'PENDING' ? null : minutesAfter(ticket.createdAt, 30)
const resolvedAt = ['WAITING_CONFIRM', 'CLOSED'].includes(ticket.status) ? minutesAfter(acceptedAt!, 60) : null
const closedAt = ticket.status === 'CLOSED' ? minutesAfter(resolvedAt!, 30) : null
const updatedAt = closedAt ?? resolvedAt ?? acceptedAt ?? ticket.createdAt
const dueAt = new Date(ticket.createdAt.getTime() + SLA_HOURS[ticket.priority] * 60 * 60 * 1000)
const ticketNo = `WO${ticket.createdAt.toISOString().slice(0, 10).replaceAll('-', '')}${String(index + 1).padStart(4, '0')}`
const [result] = await connection.execute<ResultSetHeader>(
`INSERT INTO tickets
(ticket_no, title, description, category_id, priority, status, creator_id, assignee_id, due_at, accepted_at, resolved_at, closed_at, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
ticketNo,
ticket.title,
ticket.description,
ticket.categoryId,
ticket.priority,
ticket.status,
ticket.creatorId,
ticket.assigneeId,
dueAt,
acceptedAt,
resolvedAt,
closedAt,
ticket.createdAt,
updatedAt
]
)
const logs: Array<[TicketStatus | null, TicketStatus, number, string, Date]> = [
[null, 'PENDING', ticket.creatorId, '提交工单', ticket.createdAt]
]
if (acceptedAt) logs.push(['PENDING', 'PROCESSING', 3, '分配给王工', acceptedAt])
if (resolvedAt) logs.push(['PROCESSING', 'WAITING_CONFIRM', ticket.assigneeId ?? 2, '问题已处理,等待用户确认', resolvedAt])
if (closedAt) logs.push(['WAITING_CONFIRM', 'CLOSED', ticket.creatorId, '确认关闭工单', closedAt])
for (const [fromStatus, toStatus, operatorId, remark, createdAt] of logs) {
await connection.execute(
`INSERT INTO ticket_status_logs (ticket_id, from_status, to_status, operator_id, remark, created_at)
VALUES (?, ?, ?, ?, ?, ?)`,
[result.insertId, fromStatus, toStatus, operatorId, remark, createdAt]
)
}
}
console.log('MySQL schema and demo accounts initialized. Password for all demo accounts: 123456')
await connection.end()
为 server/package.json 增加初始化命令:
{
"scripts": {
"db:init": "tsx src/scripts/init-mysql.ts"
}
}
按环境变量选择 Store
将 server/src/index.ts 替换为完整文件:
import { createApp } from './app.js'
import { getConfig } from './config.js'
import { MemoryStore } from './store/memory-store.js'
import { MysqlStore } from './store/mysql-store.js'
const config = getConfig()
const store = config.dbDriver === 'mysql' ? new MysqlStore(config.mysql) : new MemoryStore()
await store.initialize()
createApp(store, config).listen(config.port, () => {
console.log(`ServiceDesk API listening at http://localhost:${config.port}/api/v1`)
console.log(`Database driver: ${config.dbDriver}`)
})
启动并验证
docker compose up -d mysql
Copy-Item server\.env.example server\.env
# 编辑 server\.env,将 DB_DRIVER 改为 mysql
npm run db:init -w server
npm run dev
验证清单:
- 四个演示账号均可登录。
- 新建工单后停止并重新启动 API,该工单仍存在。
- 打开预置“处理中”“待确认”“已关闭”工单,状态时间线分别包含相符步骤。
- 改回
DB_DRIVER=memory后,仍可使用无数据库的默认演示方式。
常见报错与原因
| 现象 | 原因 | 修正 |
|---|---|---|
docker 不是命令 |
Docker Desktop 未安装或未启动 | 安装并启动 Docker Desktop |
连接 ECONNREFUSED 3306 |
MySQL 还未健康或端口冲突 | 执行 docker compose ps 检查 |
| 表不存在 | 未运行初始化脚本 | 执行 npm run db:init -w server |
| 时间线与状态不匹配 | 种子只写了 tickets 当前状态 | 初始化时同步写完整日志 |
本章完成清单
- 内存模式仍然可用。
- MySQL 容器启动并能初始化表与演示数据。
- API 不因切换存储方式而修改接口路径。
- 数据重启后仍存在,预置时间线正确。
面试时这一章能怎么讲
我先基于 Store 接口用内存数据完成主流程,再补充 MySQL 实现相同契约,通过环境变量切换,避免持久化配置阻塞业务开发。种子数据包含完整状态日志,保证演示详情与当前状态一致。