MySQL

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

安装

$ yarn add egg-mysql

配置MySQL

启用MySQL

config/plugin.js

exports.mysql = {
  enable: true,
  package: 'egg-mysql',
};

配置单一数据源

config/config.${env}.js

exports.mysql = {
  client: {
    host: 'localhost',
    port: '3306',
    user: 'root',
    password: 'root',
    database: 'test',
  },
  app: true, // 是否加载到 app 上,默认开启
  agent: false, // 是否加载到 agent 上,默认关闭
};

使用:

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

示例:

const Service = require('egg').Service;
module.exports = class extends Service {
  async find(id) {
    const result = await this.app.mysql.query('select * from article where id = ?', [ id ]);
    return result;
  }
}

多数据源的配置

config/config.${env}.js

exports.mysql = {
  clients: {
    // clientId, 获取client实例,需要通过 app.mysql.get('clientId') 获取
    db1: {
      database: 'test1',
    },
    db2: {
      database: 'test2',
    },
  },
  // 所有数据库配置的默认值
  default: {
    host: 'localhost',
    port: '3306',
    user: 'root',
    password: 'root',
  },
  app: true, // 是否加载到 app 上,默认开启
  agent: false, // 是否加载到 agent 上,默认关闭
};

使用:

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

示例:

const Service = require('egg').Service;
module.exports = class extends Service {
  async find(id) {
    const client1 = this.app.mysql.get('db1');
    const res1 = await client1.query('select * from article where id = ?', [ id ]);
    const client2 = this.app.mysql.get('db2');
    const res2 = await client2.query('select * from user where id = ?', [ id ]);
    return {
      res1,
      res2,
    };
  }
};

CURD

增加

const result = await this.app.mysql.insert('article', { title: 'Hello World' });

转化为SQL:

INSERT INTO `article`(`title`) VALUES('Hello World')

result:

{
  "fieldCount": 0,
  "affectedRows": 1,
  "insertId": 7,
  "serverStatus": 2,
  "warningCount": 0,
  "message": "",
  "protocol41": true,
  "changedRows": 0
}

判断插入成功

const insertSuccess = result.affectedRows === 1;

删除

const result = await this.app.mysql.delete('article', {
  id: 7,
});

转化为SQL:

"DELETE FROM `article1` WHERE `id` = 7

result:

{
  "fieldCount": 0,
  "affectedRows": 1,
  "insertId": 0,
  "serverStatus": 2,
  "warningCount": 0,
  "message": "",
  "protocol41": true,
  "changedRows": 0
}

修改

修改数据,将会根据主键 ID 查找,并更新

const row = {
  id: 2,
  title: '学无止境',
  author: '全在昱',
  category: '其他',
  updatedAt: this.app.mysql.literals.now, // `now()` on db server
};
const result = await this.app.mysql.update('article', row); // 更新 article 表中的记录

转化为SQL:

UPDATE `article` SET `title` = '学无止境', `author` = '全在昱', `category` = '其他', `updatedAt` = now() WHERE `id` = 2

result:

{
  "fieldCount": 0,
  "affectedRows": 1,
  "insertId": 0,
  "serverStatus": 2,
  "warningCount": 0,
  "message": "(Rows matched: 1  Changed: 1  Warnings: 0",
  "protocol41": true,
  "changedRows": 1
}

判断更新成功

const updateSuccess = result.affectedRows === 1;

如果更新条件不为主键, 或主键名不为id,如 custom_id,则需要在 where 里面配置

const row = {
  author: '小昱',
};
const options = {
  where: {
    category: '日志',
  },
};
const result = await this.app.mysql.update('article', row, options);

转化为SQL:

UPDATE `article` SET `author` = '小昱' WHERE `category` = '日志'

查询

查询单条数据

const user = await this.app.mysql.get('users', { id: 1 });

转化为SQL:

SELECT * FROM `users` WHERE `id` = 1 LIMIT 0, 1

查询多条数据

const result = await this.app.mysql.select('article');

转化为SQL:

SELECT * FROM `article`;

通过条件查询多条:

const result = await this.app.mysql.select('article', { // 搜索 post 表
  where: { category: '日志', author: [ '全在昱', 'xiaoyu' ] }, // WHERE 条件
  columns: [ 'author', 'title', 'category' ], // 要查询的表字段
  orders: [[ 'id', 'desc' ]], // 排序方式
  limit: 10, // 返回数据量
  offset: 1, // 数据偏移量
});

转化为SQL:

SELECT `author`, `日志`, `category` FROM `article`
  WHERE `category` = 'draft' AND `author` IN('全在昱', 'xiaoyu')
  ORDER BY `id` DESC LIMIT 1, 10;

事务

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

一般来说,事务是必须满足4个条件(ACID): Atomicity(原子性)、Consistency(一致性)、Isolation(隔离性)、Durability(可靠性)

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

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

手动控制

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

自动控制

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

表达式(Literal)

如果需要调用 MySQL 内置的函数(或表达式),可以使用 Literal。

如:

  • app.mysql.literals.now 转换为MySQL内置函数 now()

MIT Licensed | Copyright © 2018-present 滇ICP备16006294号

Design by Quanzaiyu | Power by VuePress