03数据库操作

一、项目初始化与环境配置

在后端项目根目录（server/）下执行初始化命令：

1
npx prisma init

执行后，系统会在 server/ 目录下自动生成以下目录和文件：

prisma/ 目录：存放数据库配置文件。
- schema.prisma：Prisma 的核心数据模型与配置文件。
prisma.config.ts：Prisma 的全局配置文件。
.env：环境变量配置文件（用于存放数据库连接串等敏感信息）。

二、 Prisma 全局配置与数据库选型

进入 prisma/schema.prisma 文件，首先配置生成器和数据源：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
generator client {
  provider     = "prisma-client-js"
  output       = "../libs/shared/src/generated/prisma"
  moduleFormat = "cjs" // 显式指定模块格式为 CommonJS
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

1. 核心配置项解析

output：指定 Prisma Client 及代码声明文件、类型提示文件的生成目录。本架构将其导出至公共库 libs/shared 中以便复用。
moduleFormat = "cjs" (关键点)：
版本差异说明：在 Prisma 7.0+ 及更高版本中，默认生成的模块格式已调整为 ESM。由于 NestJS 编译打包后的文件遵循 CJS (CommonJS) 规范，直接使用 ESM 会引发模块引入冲突。因此，必须显式将其改回 cjs。

2. 数据库选型：为什么选择 PostgreSQL 而不是 MySQL？

原生枚举支持：PostgreSQL 天然支持真正的 enum 类型；而 MySQL 的枚举在底层实质上是一堆字符串，表现力和性能稍逊。
丰富的数据类型：PostgreSQL 原生支持对象（JSON/JSONB）、数组（Array）以及纯粹的布尔值（True/False）；而 MySQL 存储对象和数组时通常需要转换为字符串（或使用特定的 JSON 类型，但生态支持略逊于 PG）。
性能优势：在处理复杂查询、高并发以及大规模数据写入（如本项目的海量单词导入）时，PostgreSQL 具有更强的性能和稳定性。

三、数据模型设计与业务逻辑详解

1. 用户表 (`User`) 与 ID 策略对比

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
model User {
  id              String           @id @default(cuid()) // 用户ID (cuid方案)
  name            String                                // 用户名
  email           String?          @unique              // 邮箱 (可选，唯一)
  phone           String           @unique              // 手机号 (唯一)
  address         String?                               // 地址 (可选)
  password        String                                // 密码
  avatar          String?                               // 头像 (可选)
  wordNumber      Int              @default(0)          // 累计掌握单词数量
  dayNumber       Int              @default(0)          // 累计打卡天数
  createdAt       DateTime         @default(now())      // 创建时间
  updatedAt       DateTime         @updatedAt           // 更新时间 (任何字段变更时自动刷新)
  lastLoginAt     DateTime?                             // 最后登录时间 (可选)
  
  // 一对多关联关系
  wordBookRecords WordBookRecord[]                      // 一个用户拥有多个单词学习记录
  paymentRecords  PaymentRecord[]                       // 一个用户拥有多个支付记录
  courseRecords   CourseRecord[]                        // 一个用户拥有多个课程购买记录
}

💡 装饰器与修饰符说明

?：代表该字段为可选字段（Nullable），在数据库中允许为 NULL。
@id：声明该字段为表的主键。
@unique：声明唯一约束，该列的值在全表不可重复。
@default(now())：在创建数据时自动填入当前服务器时间。
@updatedAt：自动化钩子，只要该行数据的任何一个字段发生变更，底层会自动更新该时间。

🛡️ 主键自增策略对比 (`autoincrement` vs `uuid` vs `cuid`)

autoincrement() (类如 1, 2, 3…)：
- 缺点：容易被爬虫根据 ID 规律遍历数据；在分布式多库多表架构下无法保证唯一性。
uuid() (全球唯一哈希)：
- 缺点：由 MAC 地址、时间戳等生成，由于内部字符无序，导致数据库聚簇索引离散，在高并发写入和排序时性能一般。
cuid() (推荐)：
- 优点：专门为水平扩展和现代 Web 应用设计的唯一 ID。它基于时间戳和计数器生成，天生支持排序。在保持全球唯一性的同时，避免了索引碎片化，底层读写性能最佳。

2. 单词模块：多对多关系的拆解与索引优化

用户与单词之间是多对多关系（一个用户可以背多个单词，一个单词可以被多个用户背）。这里通过引入中间表 WordBookRecord 将其拆解为两个一对多关系。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
// 单词学习记录（用户-单词 中间表）
model WordBookRecord {
  id        String   @id @default(cuid())
  wordId    String   // 单词ID
  userId    String   // 用户ID 
  isMaster  Boolean  @default(false)      // 是否掌握
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // 建立关联，并设置级联删除 (Cascade)：用户或单词被删时，对应记录自动清除
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  word      WordBook @relation(fields: [wordId], references: [id], onDelete: Cascade)

  @@unique([userId, wordId]) // 联合唯一索引：防止同一个用户对同一个单词产生重复记录
}

// 单词词库表
model WordBook {
  id              String           @id @default(cuid())
  word            String                                // 单词文本
  phonetic        String?                               // 音标
  definition      String?                               // 英文定义
  translation     String?                               // 中文翻译
  pos             String?                               // 词性
  collins         String?                               // 柯林斯星级
  oxford          String?                               // 是否牛津核心词汇
  tag             String?                               // 标签原始字符串
  bnc             String?                               // BNC 英国国家语料库词频
  frq             String?                               // FRQ 频率
  exchange        String?                               // 时态/复数等变形项
  
  // 考试级别标签（拆分为布尔值，便于多条件组合筛选）
  zk              Boolean?                              // 中考
  gk              Boolean?                              // 高考
  cet4            Boolean?                              // 四级
  cet6            Boolean?                              // 六级
  ky              Boolean?                              // 考研
  toefl           Boolean?                              // 托福
  ielts           Boolean?                              // 雅思
  gre             Boolean?                              // GRE
  
  createdAt       DateTime         @default(now())
  updatedAt       DateTime         @updatedAt
  wordBookRecords WordBookRecord[]

  // 索引优化
  @@index([word])         // 针对单个单词查询优化
  @@index([tag])          // 针对按分类标签查询优化
  @@index([word, tag])    // 复合索引：应对“查某个分类下的特定单词”的高频联合查询场景
}

💡 索引优化逻辑

在 WordBook 表中添加 @@index。因为后续业务中会有大量基于 word、tag 或两者组合的检索请求。提前建立索引可以避免全表扫描，极大提升查询效率。

3. 支付与课程模块：四表联查与状态流转

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
// 交易状态枚举
enum TradeStatus {
  NOT_PAY           // 未支付 (系统自定义的初始状态，非支付宝返回)
  WAIT_BUYER_PAY    // 等待买家付款 (支付宝对齐状态)
  TRADE_CLOSED      // 交易关闭
  TRADE_SUCCESS     // 交易成功 (可退款状态)
  TRADE_FINISHED    // 交易结束 (不可退款状态)
}

// 支付记录表
model PaymentRecord {
  id            String         @id @default(cuid())
  userId        String         // 用户ID
  tradeNo       String?        // 支付宝官方交易号 (支付成功后异步回调写入)
  outTradeNo    String         @unique              // 商户系统的唯一订单号
  amount        Decimal                             // 支付金额 (使用 Decimal 保证高精度，避免浮点数精度丢失)
  subject       String                              // 支付标题
  body          String                              // 支付详情描述
  tradeStatus   TradeStatus    @default(NOT_PAY)    // 交易状态
  sendPayTime   DateTime?                           // 实际支付时间
  createdAt     DateTime       @default(now())
  updatedAt     DateTime       @updatedAt
  
  user          User           @relation(fields: [userId], references: [id], onDelete: Cascade)
  courseRecords CourseRecord[]                      // 关联的课程记录
  
  @@index([tradeNo]) // 针对支付宝交易号建立索引，方便后续回调与对账查询
}

// 课程记录表（用户-课程 购买状态中间表）
model CourseRecord {
  id              String         @id @default(cuid())
  userId          String
  courseId        String
  isPurchased     Boolean        @default(false)     // 是否已成功购买解锁
  createdAt       DateTime       @default(now())
  updatedAt       DateTime       @updatedAt
  paymentRecordId String?                             // 关联的支付记录ID (可选，未下单前或异常处理)
  
  paymentRecord   PaymentRecord? @relation(fields: [paymentRecordId], references: [id], onDelete: Cascade)
  user            User           @relation(fields: [userId], references: [id], onDelete: Cascade)
  course          Course         @relation(fields: [courseId], references: [id], onDelete: Cascade)

  @@unique([userId, courseId]) // 联合唯一：一个用户对一门课程只能有一条购买记录
}

// 课程基础信息表
model Course {
  id            String         @id @default(cuid())
  name          String         // 课程名称
  value         String         // 课程唯一标识/Key
  description   String?        // 课程简介
  teacher       String         // 主讲老师
  url           String         // 课程视频/资源播放链接
  price         Decimal        // 课程标准定价
  createdAt     DateTime       @default(now())
  updatedAt     DateTime       @updatedAt
  courseRecords CourseRecord[]
}

🛡️ 核心业务架构设计解析

双订单号设计 (outTradeNo 与 tradeNo) 的必要性：
- outTradeNo 是我方系统生成的订单号；tradeNo 是支付宝侧生成的流水号。
- 兜底机制：一旦我方系统发生故障、数据库数据受损或订单号失效，只要有支付宝官方的 tradeNo，我们依然可以通过官方渠道为用户办理退款或实施人工对账，这是金融级支付方案的必备容错逻辑。
为何引入 CourseRecord 中间表：
- Course 表是静态的、公共的，所有人看到的课程内容、老师、价格完全一致。
- 我们绝不能直接修改 Course 表去记录购买状态。引入 CourseRecord 可以将“用户”与“课程”解耦，用来动态记录特定用户对特定课程的生命周期状态（是否购买、学习进度等）。
支付回调与事务（Transaction）流转链路：
- 步骤1：用户选定课程点击下单 $\rightarrow$ 系统创建 PaymentRecord（状态为 NOT_PAY）和 CourseRecord（isPurchased: false）。
- 步骤2：用户调起支付宝并完成支付 $\rightarrow$ 支付宝异步通知（Notify）我方后端接口。
- 步骤3：后端捕获回调数据，获取 outTradeNo 和 tradeNo。
- 步骤4（核心）：开启数据库事务。将 PaymentRecord 的状态改为 TRADE_SUCCESS，同时将关联的 CourseRecord 中的 isPurchased 改为 true。事务确保这两步操作要么同时成功，要么同时失败，杜绝“付了钱却没解锁课程”的账实不符问题。
四表级联查询的长处：由于模型间建立了清晰的关联链（User $\rightarrow$ PaymentRecord $\rightarrow$ CourseRecord $\rightarrow$ Course），使用 Prisma 可以非常轻松地实现四表联查。Prisma 会自动将这种深层嵌套的关系平铺、序列化为规整的 JSON 树状结构直接返回给前端，极大地简化了聚合查询的代码量。

四、数据库连接与 NestJS 集成

1. 依赖安装

在后端安装 Prisma、PostgreSQL 驱动及环境变量管理插件：

1
pnpm add dotenv prisma @prisma/client @prisma/adapter-pg

2. 环境变量配置 (`.env`)

配置 PostgreSQL 的标准连接串（需提前在数据库软件中创建名为 english 的库）：

1
DATABASE_URL="postgresql://postgres:123456@localhost:5432/english"

3. 执行数据库迁移与客户端生成

1
2
3
4
5
# 1. 将 schema 文件的结构同步到物理数据库中，并生成初始化迁移记录
npx prisma migrate dev --name init

# 2. 根据最新的 schema 重新生成本地的 Prisma Client 代码和类型提示
npx prisma generate

4. 在 NestJS 中封装 Prisma 服务

在 libs/shared/src/prisma/prisma.service.ts 中编写单例服务，利用 @prisma/adapter-pg 驱动驱动连接：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import { Injectable, OnModuleInit, OnModuleDestroy } from '@nestjs/common';
import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from '../generated/prisma/client';
import { Pool } from 'pg';
import 'dotenv/config';

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy {
  constructor() {
    // 创建 pg 连接池
    const pool = new Pool({ connectionString: process.env.DATABASE_URL });
    const adapter = new PrismaPg(pool);
    
    // 将适配器传入父类构造函数
    super({ adapter });
  }

  async onModuleInit() {
    // 在 NestJS 模块初始化时建立连接
    await this.$connect();
  }

  async onModuleDestroy() {
    // 在应用程序关闭时安全释放连接
    await this.$disconnect();
  }
}

五、海量数据导入脚本（77万条单词 CSV 离线流式写入）

该脚本用于将大型 ecdict.csv（GBK编码）的数据，通过流式读取、编码转换、批量缓存等策略高效安全地写入到 PostgreSQL 中。

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
const fs = require('fs');
const readline = require('readline'); 
const iconv = require('iconv-lite'); 
const { Pool } = require('pg');
const { PrismaClient } = require('./generated/prisma/client'); 
const { PrismaPg } = require('@prisma/adapter-pg');

// 初始化带适配器的 Prisma 实例
const pool = new Pool({ connectionString: 'postgres://postgres:123456@localhost:5432/english' });
const adapter = new PrismaPg(pool);
const prisma = new PrismaClient({ adapter });

/**
 * 健壮的 CSV 单行解析函数
 * 规避了解析内容中包含英文逗号（如 "hello, world"）导致的错位问题
 */
function parseCSVLine(line) {
    const result = [];
    let current = '';
    let inQuotes = false;

    for (let i = 0; i < line.length; i++) {
        const char = line[i];

        if (char === '"') {
            inQuotes = !inQuotes; // 进入或跳出双引号区域
        } else if (char === ',' && !inQuotes) {
            result.push(current);
            current = '';
        } else {
            current += char;
        }
    }
    result.push(current);
    return result;
}

/**
 * 将原始的 tag 字符串（例如 "zk gk cet4"）解析为模型所需的布尔字典对象
 */
function parseTagToBoolean(tagValue) {
    const tags = tagValue ? tagValue.split(' ').filter(t => t.trim() !== '') : [];
    
    return {
        zk: tags.includes('zk'),
        gk: tags.includes('gk'),
        cet4: tags.includes('cet4'),
        cet6: tags.includes('cet6'),
        ky: tags.includes('ky'),
        toefl: tags.includes('toefl'),
        ielts: tags.includes('ielts'),
        gre: tags.includes('gre')
    };
}

/**
 * 流式读取大型 CSV 并执行分批批量插入
 */
async function readLargeCSV(filePath) {
    let lineCount = 0;
    let headers = [];
    let batch = [];
    const BATCH_SIZE = 2000; // 核心性能调优：每 2000 条数据执行一次批量写入
    let totalInserted = 0;

    console.log('开始读取CSV文件并插入数据库...\n');

    try {
        // 创建文件读取流，并通过 iconv-lite 将国标码 (GBK) 动态转码为标准 UTF-8 流
        const fileStream = fs.createReadStream(filePath)
            .pipe(iconv.decodeStream('gbk'));

        const rl = readline.createInterface({
            input: fileStream,
            crlfDelay: Infinity
        });

        for await (const line of rl) {
            lineCount++;

            // 第一行：提取表头
            if (lineCount === 1) {
                headers = line.split(',');
                console.log('表头字段:', headers);
                console.log('='.repeat(60));
                continue;
            }

            // 解析当前行
            const values = parseCSVLine(line);
            const rowData = {};
            headers.forEach((header, index) => {
                rowData[header] = values[index] || '';
            });

            // 解析标签为布尔值形式
            const booleanFields = parseTagToBoolean(rowData.tag);

            // 组装符合 Prisma 规范的数据对象
            const wordData = {
                word: rowData.word || '',
                phonetic: rowData.phonetic || null,
                definition: rowData.definition || null,
                translation: rowData.translation || null,
                pos: rowData.pos || null,
                collins: rowData.collins || null,
                oxford: rowData.oxford || null,
                tag: rowData.tag || null,
                bnc: rowData.bnc || null,
                frq: rowData.frq || null,
                exchange: rowData.exchange || null,
                ...booleanFields
            };

            batch.push(wordData);

            // 触发分批写入
            if (batch.length >= BATCH_SIZE) {
                await prisma.wordBook.createMany({
                    data: batch,
                    skipDuplicates: true // 核心：遇到主键冲突或唯一性冲突自动跳过，保证底层健壮不崩溃
                });
                totalInserted += batch.length;
                console.log(`[进度提示] 已成功写入 ${totalInserted.toLocaleString()} 条单词数据...`);
                batch = []; // 清空缓存区
            }
        }

        // 插入尾部剩余的不足 BATCH_SIZE 的数据
        if (batch.length > 0) {
            await prisma.wordBook.createMany({
                data: batch,
                skipDuplicates: true
            });
            totalInserted += batch.length;
        }

        console.log('\n' + '='.repeat(60));
        console.log(` 报告：导入圆满完成！`);
        console.log(`总共扫描解析文件行数: ${(lineCount - 1).toLocaleString()} 行`);
        console.log(`实际成功落库单词总数: ${totalInserted.toLocaleString()} 条`);
        console.log('='.repeat(60));

    } catch (error) {
        console.error(' 致命错误:', error.message);
        throw new Error(`读取CSV文件失败: ${error.message}`);
    } finally {
        // 断开连接，释放资源池
        await prisma.$disconnect();
        await pool.end();
    }
}

// 启动执行
readLargeCSV('ecdict.csv');

Photo by Pawel Czerwinski on Unsplash