閱讀(2.8k) 書(shū)簽贊(0) 我要糾錯(cuò)

Egg 與 MySQL

2020-02-06 14:11 更新

在 Web 應(yīng)用方面 MySQL 是最常見(jiàn)，最好的關(guān)系型數(shù)據(jù)庫(kù)之一。非常多網(wǎng)站都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫(kù)。

egg-mysql

框架提供了 egg-mysql 插件來(lái)訪(fǎng)問(wèn) MySQL 數(shù)據(jù)庫(kù)。這個(gè)插件既可以訪(fǎng)問(wèn)普通的 MySQL 數(shù)據(jù)庫(kù)，也可以訪(fǎng)問(wèn)基于 MySQL 協(xié)議的在線(xiàn)數(shù)據(jù)庫(kù)服務(wù)。

安裝與配置

安裝對(duì)應(yīng)的插件 egg-mysql ：

$ npm i --save egg-mysql

開(kāi)啟插件：

// config/plugin.js
exports.mysql = {
  enable: true,
  package: 'egg-mysql',
};

在 config/config.${env}.js 配置各個(gè)環(huán)境的數(shù)據(jù)庫(kù)連接信息。

單數(shù)據(jù)源

如果我們的應(yīng)用只需要訪(fǎng)問(wèn)一個(gè) MySQL 數(shù)據(jù)庫(kù)實(shí)例，可以如下配置：

// config/config.${env}.js
exports.mysql = {
  // 單數(shù)據(jù)庫(kù)信息配置
  client: {
    // host
    host: 'mysql.com',
    // 端口號(hào)
    port: '3306',
    // 用戶(hù)名
    user: 'test_user',
    // 密碼
    password: 'test_password',
    // 數(shù)據(jù)庫(kù)名
    database: 'test',
  },
  // 是否加載到 app 上，默認(rèn)開(kāi)啟
  app: true,
  // 是否加載到 agent 上，默認(rèn)關(guān)閉
  agent: false,
};

使用方式：

await app.mysql.query(sql, values); // 單實(shí)例可以直接通過(guò) app.mysql 訪(fǎng)問(wèn)

多數(shù)據(jù)源

如果我們的應(yīng)用需要訪(fǎng)問(wèn)多個(gè) MySQL 數(shù)據(jù)源，可以按照如下配置：

exports.mysql = {
  clients: {
    // clientId, 獲取client實(shí)例，需要通過(guò) app.mysql.get('clientId') 獲取
    db1: {
      // host
      host: 'mysql.com',
      // 端口號(hào)
      port: '3306',
      // 用戶(hù)名
      user: 'test_user',
      // 密碼
      password: 'test_password',
      // 數(shù)據(jù)庫(kù)名
      database: 'test',
    },
    db2: {
      // host
      host: 'mysql2.com',
      // 端口號(hào)
      port: '3307',
      // 用戶(hù)名
      user: 'test_user',
      // 密碼
      password: 'test_password',
      // 數(shù)據(jù)庫(kù)名
      database: 'test',
    },
    // ...
  },
  // 所有數(shù)據(jù)庫(kù)配置的默認(rèn)值
  default: {

  },

  // 是否加載到 app 上，默認(rèn)開(kāi)啟
  app: true,
  // 是否加載到 agent 上，默認(rèn)關(guān)閉
  agent: false,
};

使用方式：

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

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

動(dòng)態(tài)創(chuàng)建

我們可以不需要將配置提前申明在配置文件中，而是在應(yīng)用運(yùn)行時(shí)動(dòng)態(tài)的從配置中心獲取實(shí)際的參數(shù)，再來(lái)初始化一個(gè)實(shí)例。

// {app_root}/app.js
module.exports = app => {
  app.beforeStart(async () => {
    // 從配置中心獲取 MySQL 的配置
    // { host: 'mysql.com', port: '3306', user: 'test_user', password: 'test_password', database: 'test' }
    const mysqlConfig = await app.configCenter.fetch('mysql');
    app.database = app.mysql.createInstance(mysqlConfig);
  });
};

Service 層

由于對(duì) MySQL 數(shù)據(jù)庫(kù)的訪(fǎng)問(wèn)操作屬于 Web 層中的數(shù)據(jù)處理層，因此我們強(qiáng)烈建議將這部分代碼放在 Service 層中維護(hù)。

下面是一個(gè) Service 中訪(fǎng)問(wèn) MySQL 數(shù)據(jù)庫(kù)的例子。

更多 Service 層的介紹，可以參考 Service

// app/service/user.js
class UserService extends Service {
  async find(uid) {
    // 假如 我們拿到用戶(hù) id 從數(shù)據(jù)庫(kù)獲取用戶(hù)詳細(xì)信息
    const user = await this.app.mysql.get('users', { id: 11 });
    return { user };
  }
}

之后可以通過(guò) Controller 獲取 Service 層拿到的數(shù)據(jù)。

// app/controller/user.js
class UserController extends Controller {
  async info() {
    const ctx = this.ctx;
    const userId = ctx.params.id;
    const user = await ctx.service.user.find(userId);
    ctx.body = user;
  }
}

如何編寫(xiě) CRUD 語(yǔ)句

下面的語(yǔ)句若沒(méi)有特殊注明，默認(rèn)都書(shū)寫(xiě)在 app/service 下。

Create

可以直接使用 insert 方法插入一條記錄。

// 插入
const result = await this.app.mysql.insert('posts', { title: 'Hello World' }); // 在 post 表中，插入 title 為 Hello World 的記錄

=> INSERT INTO `posts`(`title`) VALUES('Hello World');

console.log(result);
=>
{
  fieldCount: 0,
  affectedRows: 1,
  insertId: 3710,
  serverStatus: 2,
  warningCount: 2,
  message: '',
  protocol41: true,
  changedRows: 0
}

// 判斷插入成功
const insertSuccess = result.affectedRows === 1;

Read

可以直接使用 get 方法或 select 方法獲取一條或多條記錄。select 方法支持條件查詢(xún)與結(jié)果的定制。

查詢(xún)一條記錄

const post = await this.app.mysql.get('posts', { id: 12 });

=> SELECT * FROM `posts` WHERE `id` = 12 LIMIT 0, 1;

查詢(xún)?nèi)?/li>

const results = await this.app.mysql.select('posts');

=> SELECT * FROM `posts`;

條件查詢(xún)和結(jié)果定制

const results = await this.app.mysql.select('posts', { // 搜索 post 表
  where: { status: 'draft', author: ['author1', 'author2'] }, // WHERE 條件
  columns: ['author', 'title'], // 要查詢(xún)的表字段
  orders: [['created_at','desc'], ['id','desc']], // 排序方式
  limit: 10, // 返回?cái)?shù)據(jù)量
  offset: 0, // 數(shù)據(jù)偏移量
});

=> SELECT `author`, `title` FROM `posts`
  WHERE `status` = 'draft' AND `author` IN('author1','author2')
  ORDER BY `created_at` DESC, `id` DESC LIMIT 0, 10;

Update

可以直接使用 update 方法更新數(shù)據(jù)庫(kù)記錄。

// 修改數(shù)據(jù)，將會(huì)根據(jù)主鍵 ID 查找，并更新
const row = {
  id: 123,
  name: 'fengmk2',
  otherField: 'other field value',    // any other fields u want to update
  modifiedAt: this.app.mysql.literals.now, // `now()` on db server
};
const result = await this.app.mysql.update('posts', row); // 更新 posts 表中的記錄

=> UPDATE `posts` SET `name` = 'fengmk2', `modifiedAt` = NOW() WHERE id = 123 ;

// 判斷更新成功
const updateSuccess = result.affectedRows === 1;

// 如果主鍵是自定義的 ID 名稱(chēng)，如 custom_id，則需要在 `where` 里面配置
const row = {
  name: 'fengmk2',
  otherField: 'other field value',    // any other fields u want to update
  modifiedAt: this.app.mysql.literals.now, // `now()` on db server
};

const options = {
  where: {
    custom_id: 456
  }
};
const result = await this.app.mysql.update('posts', row, options); // 更新 posts 表中的記錄

=> UPDATE `posts` SET `name` = 'fengmk2', `modifiedAt` = NOW() WHERE custom_id = 456 ;

// 判斷更新成功
const updateSuccess = result.affectedRows === 1;

Delete

可以直接使用 delete 方法刪除數(shù)據(jù)庫(kù)記錄。

const result = await this.app.mysql.delete('posts', {
  author: 'fengmk2',
});

=> DELETE FROM `posts` WHERE `author` = 'fengmk2';

直接執(zhí)行 sql 語(yǔ)句

插件本身也支持拼接與直接執(zhí)行 sql 語(yǔ)句。使用 query 可以執(zhí)行合法的 sql 語(yǔ)句。

注意?。∥覀儤O其不建議開(kāi)發(fā)者拼接 sql 語(yǔ)句，這樣很容易引起 sql 注入??！

如果必須要自己拼接 sql 語(yǔ)句，請(qǐng)使用 mysql.escape 方法。

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

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

=> update posts set hits = (hits + 1) where id = 1;

使用事務(wù)

MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。比如說(shuō)，在人員管理系統(tǒng)中，你刪除一個(gè)人員，你既需要?jiǎng)h除人員的基本資料，也要?jiǎng)h除和該人員相關(guān)的信息，如信箱，文章等等。這時(shí)候使用事務(wù)處理可以方便管理這一組操作。一個(gè)事務(wù)將一組連續(xù)的數(shù)據(jù)庫(kù)操作，放在一個(gè)單一的工作單元來(lái)執(zhí)行。該組內(nèi)的每個(gè)單獨(dú)的操作是成功，事務(wù)才能成功。如果事務(wù)中的任何操作失敗，則整個(gè)事務(wù)將失敗。

一般來(lái)說(shuō)，事務(wù)是必須滿(mǎn)足4個(gè)條件（ACID）： Atomicity（原子性）、Consistency（一致性）、Isolation（隔離性）、Durability（可靠性）

原子性：確保事務(wù)內(nèi)的所有操作都成功完成，否則事務(wù)將被中止在故障點(diǎn)，以前的操作將回滾到以前的狀態(tài)。
一致性：對(duì)于數(shù)據(jù)庫(kù)的修改是一致的。
隔離性：事務(wù)是彼此獨(dú)立的，不互相影響
持久性：確保提交事務(wù)后，事務(wù)產(chǎn)生的結(jié)果可以永久存在。

因此，對(duì)于一個(gè)事務(wù)來(lái)講，一定伴隨著 beginTransaction、commit 或 rollback，分別代表事務(wù)的開(kāi)始，成功和失敗回滾。

egg-mysql 提供了兩種類(lèi)型的事務(wù)。

手動(dòng)控制

優(yōu)點(diǎn)：beginTransaction, commit 或 rollback 都由開(kāi)發(fā)者來(lái)完全控制，可以做到非常細(xì)粒度的控制。
缺點(diǎn)：手寫(xiě)代碼比較多，不是每個(gè)人都能寫(xiě)好。忘記了捕獲異常和 cleanup 都會(huì)導(dǎo)致嚴(yán)重 bug。

const conn = await app.mysql.beginTransaction(); // 初始化事務(wù)

try {
  await conn.insert(table, row1);  // 第一步操作
  await conn.update(table, row2);  // 第二步操作
  await conn.commit(); // 提交事務(wù)
} catch (err) {
  // error, rollback
  await conn.rollback(); // 一定記得捕獲異常后回滾事務(wù)！！
  throw err;
}

自動(dòng)控制：Transaction with scope

API：beginTransactionScope(scope, ctx)scope: 一個(gè) generatorFunction，在這個(gè)函數(shù)里面執(zhí)行這次事務(wù)的所有 sql 語(yǔ)句。ctx: 當(dāng)前請(qǐng)求的上下文對(duì)象，傳入 ctx 可以保證即便在出現(xiàn)事務(wù)嵌套的情況下，一次請(qǐng)求中同時(shí)只有一個(gè)激活狀態(tài)的事務(wù)。
優(yōu)點(diǎn)：使用簡(jiǎn)單，不容易犯錯(cuò)，就感覺(jué)事務(wù)不存在的樣子。
缺點(diǎn)：整個(gè)事務(wù)要么成功，要么失敗，無(wú)法做細(xì)粒度控制。

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 是當(dāng)前請(qǐng)求的上下文，如果是在 service 文件中，可以從 `this.ctx` 獲取到
// if error throw on scope, will auto rollback

表達(dá)式(Literal)

如果需要調(diào)用 MySQL 內(nèi)置的函數(shù)（或表達(dá)式），可以使用 Literal。

內(nèi)置表達(dá)式

NOW()：數(shù)據(jù)庫(kù)當(dāng)前系統(tǒng)時(shí)間，通過(guò) app.mysql.literals.now 獲取。

await this.app.mysql.insert(table, {
  create_time: this.app.mysql.literals.now,
});

=> INSERT INTO `$table`(`create_time`) VALUES(NOW())

自定義表達(dá)式

下例展示了如何調(diào)用 MySQL 內(nèi)置的 CONCAT(s1, ...sn) 函數(shù)，做字符串拼接。

const Literal = this.app.mysql.literals.Literal;
const first = 'James';
const last = 'Bond';
await this.app.mysql.insert(table, {
  id: 123,
  fullname: new Literal(`CONCAT("${first}", "${last}"`),
});

=> INSERT INTO `$table`(`id`, `fullname`) VALUES(123, CONCAT("James", "Bond"))

以上內(nèi)容是否對(duì)您有幫助：

← Egg 國(guó)際化（I18n）

Egg 與 Sequelize →

寫(xiě)筆記

我要補(bǔ)充