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

验证清单：

1. 四个演示账号均可登录。
2. 新建工单后停止并重新启动 API，该工单仍存在。
3. 打开预置“处理中”“待确认”“已关闭”工单，状态时间线分别包含相符步骤。
4. 改回 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 实现相同契约，通过环境变量切换，避免持久化配置阻塞业务开发。种子数据包含完整状态日志，保证演示详情与当前状态一致。