数据库设计

创建/定义表

数据库各个表的Schema定义在 src/models/*.mts 中。我们使用 knex 这个 sql builder 来定义库表结构。以 news 表为例：

import { createTableIfNotExist } from "#scripts/DatabaseUtils";

// 创建新闻表
export async function createTableNewsIfNotExist(): Promise<void> {
  await createTableIfNotExist({
    tableName: "news",
    createTable: (table) => {
      table.string("title", 255).notNullable().comment("新闻标题");
      table.text("content_md").notNullable().comment("Markdown格式的新闻内容");
      table.text("content_html").comment("转换后的HTML格式内容");
      table.text("summary").comment("摘要文本");
      table
        .enum("status", ["draft", "published", "deleted"])
        .defaultTo("draft")
        .comment("状态（草稿、已发布、已下线）");
      table.integer("views").defaultTo(0).comment("浏览量");
      table.string("thumbnail", 255).comment("封面图片链接");
    },
  });
}

上面的代码定义了一个 news 表，显示地定义了以下字段：

• title: 新闻标题，字符串类型，最大长度255，不允许为空。
• content_md: 新闻内容，Markdown格式，文本类型，不允许为空。
• content_html: 转换后的HTML格式内容，文本类型。
• summary: 摘要文本，文本类型。
• status: 新闻状态，枚举类型，包含 "draft"（草稿）、"published"（已发布）、"deleted"（已下线），默认值为 "draft"。
• views: 浏览量，整数类型，默认值为0。
• thumbnail: 封面图片链接，字符串类型，最大长度255。

提示

createTableIfNotExist 方法默认会自动帮你定义 id、created_at, updated_at 字段，不要再显示定义。

如果你不需要这个默认行为，可以通过传入 disableId、disableCreatedAt 或 disableUpdatedAt 字段来覆盖默认行为。

async function createTableIfNotExist(
  params: ParamsCreateTableIfNotExist,
): Promise<void>;

interface ParamsCreateTableIfNotExist {
  tableName: string;
  createTable: (table: import("knex").Knex.TableBuilder) => void;
  disableId?: boolean; // 是否禁用默认的 id 主键列，默认为 false
  disableCreatedAt?: boolean; // 是否禁用默认的 created_at 列，默认为 false
  disableUpdatedAt?: boolean; // 是否禁用默认的 updated_at 列，默认为 false
}

数据表迁移

使用 Knex.js 来给已经存在的表添加字段，你可以通过编写一个迁移（migration）来实现。迁移是一种对数据库模式进行版本控制的方法，它允许你以一种安全且可重复的方式修改数据库结构。下面是一个简单的步骤指南，帮助你为 user 表添加新字段：

1. 创建一个新的迁移文件

首先，你需要创建一个新的迁移文件。可以通过运行以下命令来生成一个新的迁移文件：

npx knex migrate:make add_new_fields_to_user_table --knexfile knexfile.mts --env production -x mts --stub node_modules/knex/lib/migrations/migrate/stub/ts.stub

这将为你创建一个新的迁移文件在你的 migrations 目录下。

2. 编辑迁移文件

打开刚创建的迁移文件，并编辑 up 方法来添加新的字段到 user 表中。例如，如果你想添加一个名为 age 的整数类型字段和一个名为 status 的字符串类型字段，可以这样做：

exports.up = function (knex) {
  return knex.schema.table("user", function (table) {
    table.integer("age"); // 添加 age 字段
    table.string("status"); // 添加 status 字段
  });
};

exports.down = function (knex) {
  return knex.schema.table("user", function (table) {
    table.dropColumn("age"); // 移除 age 字段
    table.dropColumn("status"); // 移除 status 字段
  });
};

up 方法定义了如何应用更改，而 down 方法则定义了如何回滚这些更改。这是非常有用的，以防你需要撤销所做的更改。

3. 运行迁移

完成迁移文件的编辑后，你可以通过运行以下命令来执行迁移并更新数据库：

npx knex migrate:latest --knexfile knexfile.mts --env production

这将会根据你编写的迁移文件对数据库进行相应的修改。

注意事项

• 在对生产数据库进行任何更改之前，请确保你在测试环境中进行了充分的测试。
• 如果你的表中有大量数据，或者你正在添加带有默认值或非空约束的新字段，可能需要特别小心，因为这可能会导致长时间的锁定或数据丢失风险。在这种情况下，考虑先添加字段而不设置默认值，然后再根据需求更新现有记录的值，最后如果需要的话再添加非空约束。

在 Knex 中，回滚（rollback）迁移指的是撤销最近一次或多次执行的迁移操作，恢复数据库到之前的状态。回滚是通过执行 down 方法中定义的操作来实现的，这个方法与 up 方法相对应，定义了如何撤销迁移所带来的更改。

如何执行回滚

1. 回滚最近一次迁移

   如果你想撤销最近一次运行的迁移，可以使用以下命令：

   npx knex migrate:rollback

   这个命令会执行最近一次迁移对应的 down 方法，从而撤销该次迁移的所有更改。

2. 回滚到指定的迁移版本

   如果需要回滚到某个特定的迁移版本，你可以使用 --to 参数指定目标迁移文件的时间戳或名称：

   npx knex migrate:rollback --to <timestamp_or_migration_name>

3. 回滚所有迁移

   要一次性回滚所有迁移，可以重复调用 migrate:rollback 直到所有的迁移都被撤销。不过，Knex 本身没有直接提供一次性回滚所有迁移的命令。你可以编写一个简单的脚本循环执行 migrate:rollback 直到没有更多迁移可回滚为止。

确保正确的 down 方法

为了能够成功回滚，确保你的每个迁移文件都正确实现了 down 方法。例如，如果你的 up 方法中添加了新字段并重命名了旧字段，那么 down 方法应该做相反的操作：删除新字段，并根据需要重新创建旧字段。这里是一个示例：

exports.up = function (knex) {
  return knex.schema
    .table("user", function (table) {
      table.string("newField1");
      table.string("newField2");
    })
    .then(() => {
      return knex.raw(
        "UPDATE user SET newField1 = oldField1, newField2 = oldField2",
      );
    })
    .then(() => {
      return knex.schema.table("user", function (table) {
        table.dropColumn("oldField1");
        table.dropColumn("oldField2");
      });
    });
};

exports.down = function (knex) {
  return knex.schema
    .table("user", function (table) {
      table.string("oldField1");
      table.string("oldField2");
    })
    .then(() => {
      return knex.raw(
        "UPDATE user SET oldField1 = newField1, oldField2 = newField2",
      );
    })
    .then(() => {
      return knex.schema.table("user", function (table) {
        table.dropColumn("newField1");
        table.dropColumn("newField2");
      });
    });
};

注意事项

• 数据一致性：在进行回滚时，特别是涉及到数据移动的操作（如字段重命名），请务必小心以确保数据的一致性和完整性。

• 备份数据：在执行回滚操作前，建议对数据库进行完整备份，以防出现任何意外错误导致的数据丢失。

通过遵循上述步骤和注意事项，你可以有效地管理和回滚数据库迁移，保持数据库结构和数据的安全与稳定。

数据库查询注意事项

ONLY_FULL_GROUP_BY 模式

MySQL 5.7+ 默认启用了 ONLY_FULL_GROUP_BY 模式，该模式要求在使用聚合函数（如 count()、sum() 等）时，SELECT 列表中的非聚合列必须出现在 GROUP BY 子句中，或者使用聚合函数包装。

分页查询的正确写法

在进行分页查询时，需要分别执行列表查询和计数查询，避免在同一个查询中同时使用 select * 和 count()。

错误示例：

// ❌ 错误：会生成 select *, count(*) as total，违反 ONLY_FULL_GROUP_BY 规则
const list = await query.limit(limit).offset(offset);
const totalResult = await query.clone().count({ total: "*" }).first();

正确示例：

// ✅ 正确：分别执行列表查询和计数查询
const [list, totalResult] = await Promise.all([
  query.clone().limit(limit).offset(offset),
  query
    .clone()
    // 清除之前的 select，避免 select * 与 count() 冲突
    .clearSelect()
    .count("id as count")
    .first(),
]);

const total = totalResult ? Number(totalResult.count) : 0;

关键要点

1. 使用 Promise.all 并行执行：列表查询和计数查询可以并行执行，提升性能
2. 使用 .clearSelect()：在计数查询前清除之前的 select("*")，避免与非聚合列冲突
3. 使用 count("id as count")：而不是 count({ total: "*" })，更明确且符合规范
4. 从结果中读取字段：计数查询返回的字段名是 count，需要正确读取

参考实现

可以参考以下文件中的实现：

• src/services/balanceWithdrawAudit.mts - getWithdrawAuditList 函数
• src/services/file.mts - queryFileList 函数
• src/services/balance.mts - getBalanceTransactions 函数

Knex 类型定义维护

在使用 Knex 进行数据库操作时，为了获得完整的 TypeScript 类型支持，需要维护 typings/knex.d.ts 文件。该文件定义了所有数据库表的类型映射，确保在使用 knex("table_name") 时能够获得正确的类型提示。

维护规则

1. 新增数据表时

当在 src/models/ 目录下创建新的表模型后，需要：

1. 在 typings/ 目录下创建类型定义文件（如果还没有）

   • 文件名格式：table-name.d.ts（使用 kebab-case）
   • 定义表的接口类型，继承 CreatedAndUpdatedAt（如果表有 created_at 和 updated_at 字段）

2. 在 typings/knex.d.ts 中添加表定义

   • 在 Tables 接口中添加基础类型映射：table_name: TableType
   • 如果表有 id 和 created_at/updated_at 字段，还需添加复合类型：table_name_composite: CompositeTableType<TableType>
   • 对于中间表（没有 id 字段），只添加基础类型映射，不添加 _composite 版本

示例：

// typings/article-category.d.ts
interface ArticleCategory extends CreatedAndUpdatedAt {
  id: number;
  name: string;
  slug: string;
  // ... 其他字段
}

// typings/knex.d.ts
declare module "knex/types/tables.js" {
  interface Tables {
    // ... 其他表
    article_category: ArticleCategory;
    // ... 其他表

    // 复合类型（用于插入和更新操作）
    article_category_composite: CompositeTableType<ArticleCategory>;
  }
}

2. 删除数据表时

当删除表模型文件后，需要：

1. 从 typings/knex.d.ts 中移除表定义

   • 移除基础类型映射（如 table_name: TableType）
   • 移除复合类型映射（如 table_name_composite: CompositeTableType<TableType>）

2. 删除对应的类型定义文件

   • 删除 typings/table-name.d.ts 文件（如果存在）

3. 表名映射注意事项

• 表名必须完全一致：knex.d.ts 中的表名必须与模型文件中的 tableName 完全一致
• 注意大小写：表名区分大小写，确保与数据库中的实际表名一致
• 注意下划线：使用下划线分隔单词（如 article_category，不是 articleCategory）

示例：

// src/models/Picture.mts
tableName: "pic"; // 注意：表名是 "pic"，不是 "picture"

// typings/knex.d.ts
interface Tables {
  pic: Picture; // 必须使用 "pic"，不能使用 "picture"
  pic_composite: CompositeTableType<Picture>;
}

4. 中间表处理

对于多对多关系的中间表（如 article_tag_relation），通常没有 id 字段，只有关联字段：

// typings/article.d.ts
interface ArticleTagRelation {
  article_id: number;
  tag_id: number;
}

// typings/knex.d.ts
interface Tables {
  article_tag_relation: ArticleTagRelation; // 只添加基础类型，不添加 _composite
}

检查清单

在增删数据表后，请确保：

• [ ] 在 typings/knex.d.ts 中添加/移除了对应的表定义
• [ ] 表名与模型文件中的 tableName 完全一致
• [ ] 如果表有 id 和 created_at/updated_at 字段，添加了 _composite 类型
• [ ] 如果删除了表，同时删除了对应的类型定义文件
• [ ] 代码通过 TypeScript 编译检查，没有类型错误