• MySQL
  • egg-mysql
    • 安装与配置
      • 单数据源
      • 多数据源
      • 动态创建
  • Service 层
  • 如何编写 CRUD 语句
    • Create
    • Read
    • Update
    • Delete
  • 直接执行 sql 语句
  • 使用事务
    • 手动控制
    • 自动控制：Transaction with scope
  • 表达式(Literal)
    • 内置表达式
    • 自定义表达式

    MySQL

    在 Web 应用方面 MySQL 是最常见，最好的关系型数据库之一。非常多网站都选择 MySQL 作为网站数据库。

    egg-mysql

    框架提供了 egg-mysql 插件来访问 MySQL 数据库。这个插件既可以访问普通的 MySQL 数据库，也可以访问基于 MySQL 协议的在线数据库服务。

    安装与配置

    安装对应的插件 egg-mysql ：

    1. $ npm i --save egg-mysql

    开启插件：

    1. // config/plugin.js
    2. exports.mysql = {
    3.   enable: true,
    4.   package: 'egg-mysql',
    5. };

    在 config/config.${env}.js 配置各个环境的数据库连接信息。

    单数据源

    如果我们的应用只需要访问一个 MySQL 数据库实例，可以如下配置：

    1. // config/config.${env}.js
    2. exports.mysql = {
    3.   // 单数据库信息配置
    4.   client: {
    5.     // host
    6.     host: 'mysql.com',
    7.     // 端口号
    8.     port: '3306',
    9.     // 用户名
    10.     user: 'test_user',
    11.     // 密码
    12.     password: 'test_password',
    13.     // 数据库名
    14.     database: 'test',
    15.   },
    16.   // 是否加载到 app 上，默认开启
    17.   app: true,
    18.   // 是否加载到 agent 上，默认关闭
    19.   agent: false,
    20. };

    使用方式：

    1. await app.mysql.query(sql, values); // 单实例可以直接通过 app.mysql 访问

    多数据源

    如果我们的应用需要访问多个 MySQL 数据源，可以按照如下配置：

    1. exports.mysql = {
    2.   clients: {
    3.     // clientId, 获取client实例，需要通过 app.mysql.get('clientId') 获取
    4.     db1: {
    5.       // host
    6.       host: 'mysql.com',
    7.       // 端口号
    8.       port: '3306',
    9.       // 用户名
    10.       user: 'test_user',
    11.       // 密码
    12.       password: 'test_password',
    13.       // 数据库名
    14.       database: 'test',
    15.     },
    16.     db2: {
    17.       // host
    18.       host: 'mysql2.com',
    19.       // 端口号
    20.       port: '3307',
    21.       // 用户名
    22.       user: 'test_user',
    23.       // 密码
    24.       password: 'test_password',
    25.       // 数据库名
    26.       database: 'test',
    27.     },
    28.     // ...
    29.   },
    30.   // 所有数据库配置的默认值
    31.   default: {
    33.   },
    35.   // 是否加载到 app 上，默认开启
    36.   app: true,
    37.   // 是否加载到 agent 上，默认关闭
    38.   agent: false,
    39. };

    使用方式：

    1. const client1 = app.mysql.get('db1');
    2. await client1.query(sql, values);
    4. const client2 = app.mysql.get('db2');
    5. await client2.query(sql, values);

    动态创建

    我们可以不需要将配置提前申明在配置文件中，而是在应用运行时动态的从配置中心获取实际的参数，再来初始化一个实例。

    1. // {app_root}/app.js
    2. module.exports = app => {
    3.   app.beforeStart(async () => {
    4.     // 从配置中心获取 MySQL 的配置
    5.     // { host: 'mysql.com', port: '3306', user: 'test_user', password: 'test_password', database: 'test' }
    6.     const mysqlConfig = await app.configCenter.fetch('mysql');
    7.     app.database = app.mysql.createInstance(mysqlConfig);
    8.   });
    9. };

    Service 层

    由于对 MySQL 数据库的访问操作属于 Web 层中的数据处理层，因此我们强烈建议将这部分代码放在 Service 层中维护。

    下面是一个 Service 中访问 MySQL 数据库的例子。

    更多 Service 层的介绍，可以参考 Service

    1. // app/service/user.js
    2. class UserService extends Service {
    3.   async find(uid) {
    4.     // 假如 我们拿到用户 id 从数据库获取用户详细信息
    5.     const user = await this.app.mysql.get('users', { id: 11 });
    6.     return { user };
    7.   }
    8. }

    之后可以通过 Controller 获取 Service 层拿到的数据。

    1. // app/controller/user.js
    2. class UserController extends Controller {
    3.   async info() {
    4.     const ctx = this.ctx;
    5.     const userId = ctx.params.id;
    6.     const user = await ctx.service.user.find(userId);
    7.     ctx.body = user;
    8.   }
    9. }

    如何编写 CRUD 语句

    下面的语句若没有特殊注明，默认都书写在 app/service 下。

    Create

    可以直接使用 insert 方法插入一条记录。

    1. // 插入
    2. const result = await this.app.mysql.insert('posts', { 'Hello World' }); // 在 post 表中，插入 title 为 Hello World 的记录
    4. => INSERT INTO `posts`(`title`) VALUES('Hello World');
    6. console.log(result);
    7. =>
    8. {
    9.   fieldCount: 0,
    10.   affectedRows: 1,
    11.   insertId: 3710,
    12.   serverStatus: 2,
    13.   warningCount: 2,
    14.   message: '',
    15.   protocol41: true,
    16.   changedRows: 0
    17. }
    19. // 判断插入成功
    20. const insertSuccess = result.affectedRows === 1;

    Read

    可以直接使用 get 方法或 select 方法获取一条或多条记录。select 方法支持条件查询与结果的定制。

    • 查询一条记录
    1. const post = await this.app.mysql.get('posts', { id: 12 });
    3. => SELECT * FROM `posts` WHERE `id` = 12 LIMIT 0, 1;
    • 查询全表
    1. const results = await this.app.mysql.select('posts');
    3. => SELECT * FROM `posts`;
    • 条件查询和结果定制
    1. const results = await this.app.mysql.select('posts', { // 搜索 post 表
    2.   where: { status: 'draft', author: ['author1', 'author2'] }, // WHERE 条件
    3.   columns: ['author', 'title'], // 要查询的表字段
    4.   orders: [['created_at','desc'], ['id','desc']], // 排序方式
    5.   limit: 10, // 返回数据量
    6.   offset: 0, // 数据偏移量
    7. });
    9. => SELECT `author`, `title` FROM `posts`
    10.   WHERE `status` = 'draft' AND `author` IN('author1','author2')
    11.   ORDER BY `created_at` DESC, `id` DESC LIMIT 0, 10;

    Update

    可以直接使用 update 方法更新数据库记录。

    1. // 修改数据，将会根据主键 ID 查找，并更新
    2. const row = {
    3.   id: 123,
    4.   name: 'fengmk2',
    5.   otherField: 'other field value',    // any other fields u want to update
    6.   modifiedAt: this.app.mysql.literals.now, // `now()` on db server
    7. };
    8. const result = await this.app.mysql.update('posts', row); // 更新 posts 表中的记录
    10. => UPDATE `posts` SET `name` = 'fengmk2', `modifiedAt` = NOW() WHERE id = 123 ;
    12. // 判断更新成功
    13. const updateSuccess = result.affectedRows === 1;
    15. // 如果主键是自定义的 ID 名称，如 custom_id，则需要在 `where` 里面配置
    16. const row = {
    17.   name: 'fengmk2',
    18.   otherField: 'other field value',    // any other fields u want to update
    19.   modifiedAt: this.app.mysql.literals.now, // `now()` on db server
    20. };
    22. const options = {
    23.   where: {
    24.     custom_id: 456
    25.   }
    26. };
    27. const result = await this.app.mysql.update('posts', row, options); // 更新 posts 表中的记录
    29. => UPDATE `posts` SET `name` = 'fengmk2', `modifiedAt` = NOW() WHERE custom_id = 456 ;
    31. // 判断更新成功
    32. const updateSuccess = result.affectedRows === 1;

    Delete

    可以直接使用 delete 方法删除数据库记录。

    1. const result = await this.app.mysql.delete('posts', {
    2.   author: 'fengmk2',
    3. });
    5. => DELETE FROM `posts` WHERE `author` = 'fengmk2';

    直接执行 sql 语句

    插件本身也支持拼接与直接执行 sql 语句。使用 query 可以执行合法的 sql 语句。

    注意！！我们极其不建议开发者拼接 sql 语句，这样很容易引起 sql 注入！！

    如果必须要自己拼接 sql 语句，请使用 mysql.escape 方法。

    参考 preventing-sql-injection-in-node-js

    1. const postId = 1;
    2. const results = await this.app.mysql.query('update posts set hits = (hits + ?) where id = ?', [1, postId]);
    4. => update posts set hits = (hits + 1) where id = 1;

    使用事务

    MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等。这时候使用事务处理可以方便管理这一组操作。一个事务将一组连续的数据库操作，放在一个单一的工作单元来执行。该组内的每个单独的操作是成功，事务才能成功。如果事务中的任何操作失败，则整个事务将失败。

    一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）、Durability（可靠性）

    • 原子性：确保事务内的所有操作都成功完成，否则事务将被中止在故障点，以前的操作将回滚到以前的状态。
    • 一致性：对于数据库的修改是一致的。
    • 隔离性：事务是彼此独立的，不互相影响
    • 持久性：确保提交事务后，事务产生的结果可以永久存在。

    因此，对于一个事务来讲，一定伴随着 beginTransaction、commit 或 rollback，分别代表事务的开始，成功和失败回滚。

    egg-mysql 提供了两种类型的事务。

    手动控制

    • 优点：beginTransaction, commit 或 rollback 都由开发者来完全控制，可以做到非常细粒度的控制。
    • 缺点：手写代码比较多，不是每个人都能写好。忘记了捕获异常和 cleanup 都会导致严重 bug。
    1. const conn = await app.mysql.beginTransaction(); // 初始化事务
    3. try {
    4.   await conn.insert(table, row1);  // 第一步操作
    5.   await conn.update(table, row2);  // 第二步操作
    6.   await conn.commit(); // 提交事务
    7. } catch (err) {
    8.   // error, rollback
    9.   await conn.rollback(); // 一定记得捕获异常后回滚事务！！
    10.   throw err;
    11. }

    自动控制：Transaction with scope

    • API：beginTransactionScope(scope, ctx)
      • scope: 一个 generatorFunction，在这个函数里面执行这次事务的所有 sql 语句。
      • ctx: 当前请求的上下文对象，传入 ctx 可以保证即便在出现事务嵌套的情况下，一次请求中同时只有一个激活状态的事务。
    • 优点：使用简单，不容易犯错，就感觉事务不存在的样子。
    • 缺点：整个事务要么成功，要么失败，无法做细粒度控制。
    1. const result = await app.mysql.beginTransactionScope(async conn => {
    2.   // don't commit or rollback by yourself
    3.   await conn.insert(table, row1);
    4.   await conn.update(table, row2);
    5.   return { success: true };
    6. }, ctx); // ctx 是当前请求的上下文，如果是在 service 文件中，可以从 `this.ctx` 获取到
    7. // if error throw on scope, will auto rollback

    表达式(Literal)

    如果需要调用 MySQL 内置的函数（或表达式），可以使用 Literal。

    内置表达式

    • NOW()：数据库当前系统时间，通过 app.mysql.literals.now 获取。
    1. await this.app.mysql.insert(table, {
    2.   create_time: this.app.mysql.literals.now,
    3. });
    5. => INSERT INTO `$table`(`create_time`) VALUES(NOW())

    自定义表达式

    下例展示了如何调用 MySQL 内置的 CONCAT(s1, ...sn) 函数，做字符串拼接。

    1. const Literal = this.app.mysql.literals.Literal;
    2. const first = 'James';
    3. const last = 'Bond';
    4. await this.app.mysql.insert(table, {
    5.   id: 123,
    6.   fullname: new Literal(`CONCAT("${first}", "${last}"`),
    7. });
    9. => INSERT INTO `$table`(`id`, `fullname`) VALUES(123, CONCAT("James", "Bond"))