为什么选择 mysql

市面上流行的数据库很多，在稳定性、性能、拓展性等方面有各自的优势，按照数据组织方式区分与两类

  1. 关系型数据库：由二维表及其之间的联系组织数据
    1. mysql/mariadb
    2. Oracle
    3. SQL Server
    4. DB2
    5. PostgreSQL
  2. NoSQL：利用文档、键值对等对数据进行组织
    1. mongoDB
    2. Hypertable
    3. Hbase
    4. redis/memcache
    5. CouchDB
    6. Neo4j

两者在不同领域都有广泛的应用，并且在互相学习，但关系型数据是理解数据库的基础，因为先发优势在各企业应用中普及度比 NoSQL 高很多，mysql 免费、稳定、高性能的特性让其成为最流行的关系型数据库

mysql 安装

mysql 程序可以在官网下载安装，Mac 用户可以使用 homebrew 安装

  1. brew install mysql

安装完成后可以在命令行工具启动、操作 mysql 数据库，基础操作可以参考 MySQL入门——看这一篇就够了，但命令行操作比较复杂，可以使用图形化工具管理 mysql 数据库，对于简单的管理 mysql 官方提供的 workbench 和 phpMyAdmin 等选择很多

环境准备

  1. 使用 root 登录，创建一个普通用户 sunluyong ，赋予用户操作数据库的权限（一般不会在程序中使用 root 账户）
  2. 使用普通账户登录，创建 schema（数据库） demo ，后面的示例需要连接此数据库
  3. 在 demo 数据库中创建 Table user 
    1. CREATE TABLE `user` (
    2. `id` int NOT NULL AUTO_INCREMENT COMMENT ''用户 ID，主键'',
    3. `name` varchar(50) NOT NULL COMMENT ''用户名称'',
    4. `config` tinytext COMMENT ''用户配置'',
    5. `deleted` int unsigned NOT NULL DEFAULT ''0'' COMMENT ''记录是否被删除'',
    6. `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ''创建时间'',
    7. `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ''最后修改时间'',
    8. PRIMARY KEY (`id`)
    9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    user 表的结构非常简单
id 自增长数字，用户 ID，做为表的主键
name 用户名
config 用户的一些配置信息，可以为空
deleted 该条记录是否被软删除，0: 未删除 1: 已删除
created_at 本条数据的创建时间，数据库自动插入 CURRENT_TIMESTAMP
updated_at 最后一次修改时间，每次该条数据 update 时候数据库自动更新

环境准备好后就可以在 egg.js 中连接数据库，进行数据的 CRUD 操作了

配置插件

在 egg.js 中操作 mysql 需要使用 egg-mysql 插件，配置方式和模板引擎插件的方式一致

1. 安装插件

  1. npm i --save egg-mysql

2. 开启插件

修改 config/plugin.js

  1. exports.mysql = {
  2.   enable: true,
  3.   package: 'egg-mysql',
  4. };

3. 数据库连接配置

修改 config/config.default.js

  1. config.mysql = {
  2.   client: {
  3.     host: 'localhost',
  4.     port: '3306',
  5.     user: 'sunluyong',
  6.     password: '123456',
  7.     database: 'demo',
  8.   },
  9.   // 在 this.app 上挂载 mysql 对象
  10.   app: true,
  11. };

在代码中写明文密码是极度危险的处理方式，直接配置是为了 demo 方便，线上需要使用加密手段

多环境配置

完成上面的配置就可以在本地测试了，但很多时候本地测试数据库和线上数据库并不一致，egg.js 通过 /config 目录下不同文件名的方式提供了多环境配置的支持

  1. config
  2. |- config.default.js
  3. |- config.prod.js
  4. |- config.unittest.js
  5. |- config.local.js

config.default.js 为默认的配置文件，所有环境都会加载这个配置文件，一般也会作为开发环境的默认配置文件

当指定 env 时会同时加载对应的配置文件，并覆盖默认配置文件的同名配置。如 prod 环境会加载 config.prod.js 和 config.default.js 文件，config.prod.js 会覆盖 config.default.js 的同名配置

因此可以创建 /config/config.prod.js 对线上环境进行特殊配置，在 egg.js 启动时候可以通过 EGG_SERVER_ENV 自定义环境

CRUD

CRUD 是 Create、Read、Update、Delete 的缩写，是数据库最常用的四种操作，egg-mysql 对应的操作也非常简单

  1. this.app.mysql.insert(表名, 数据对象)
  2. this.app.mysql.get(表名, where 条件 })
  3. await this.app.mysql.query(sql 语句, [sql 语句中的变量])
  4. this.app.mysql.update(表名, 更新字段对象, where 条件)
  5. this.app.mysql.delete(表名, where 条件)

看一下官网示例

  1. // 插入
  2. await this.app.mysql.insert('posts', { title: 'Hello World' }); 
  3. => INSERT INTO `posts` (`title`) VALUES('Hello World');
  5. // 查询
  6. await this.app.mysql.get('posts', { id: 12 });
  7. => SELECT * FROM `posts` WHERE `id` = 12;
  9. // 自定义 SQL 语句
  10. await this.app.mysql.query('update posts set hits = (hits + ?) where id = ?', [1, postId]);
  11. => update posts set hits = (hits + 1) where id = 1;
  13. // 删除
  14. await this.app.mysql.delete('posts', { author: 'fengmk2'});
  15. => DELETE FROM `posts` WHERE `author` = 'fengmk2';
  17. // 更新
  18. const row = {
  19.   name: 'fengmk2',
  20.   otherField: 'other field value',    // any other fields u want to update
  21. };
  23. const options = {
  24.   where: {
  25.     id: 456
  26.   }
  27. };
  28. await this.app.mysql.update('posts', row, options);
  30. => UPDATE `posts` 
  31.      SET `name` = 'fengmk2', `otherField` = 'other field value' 
  32.      WHERE id = 456 ;
  34. // 因为大部分数据库的主键 key 都是 id，所以更新有个快捷的写法
  35. // 只要第一个参数里面包含 `id` 并且不提供第二个参数，就会默认把 id 当做 where 的条件
  36. const row = {
  37.   id: 123,
  38.   name: 'fengmk2',
  39.   otherField: 'other field value',    // any other fields u want to update
  40. };
  41. await this.app.mysql.update('posts', row);

示例

接下来写一个用户管理的示例展示

  1. 接口使用 RESTful 风格，egg.js 提供了内置的 RESRful API 实现方式，为了完整演示，示例自定义实现

  2. 非 get 请求需要处理 csrf token，防止 csrf 攻击，细节可以参考 egg.js 安全

    添加路由

    在示例中会使用到 user 相关的 5 个接口和 3 个页面，在 app/router.js 文件中添加路由

    1. // user api
    2. app.get('/api/user/list', 'api.user.list');
    3. app.get('/api/user/:id', 'api.user.get');
    4. app.post('/api/user', 'api.user.create');
    5. app.put('/api/user/:id', 'api.user.edit');
    6. app.delete('/api/user/:id', 'api.user.delete');
    8. // user page
    9. app.get('/user/create', 'user.create');
    10. app.get('/user/edit/:id', 'user.edit');
    11. app.get('/user/list', 'user.list');

    https://github.com/Samaritan89/egg-demo/blob/v2/app/router.js#L7

    创建 controller、service、view 文件

    根据路由创建对应的文件

  • app/controller/api/user.js
  • app/controller/user.js
  • app/service/user.js
  • app/view/user/create.hbs
  • app/view/user/edit.hbs
  • app/view/user/list.hbs

    创建用户功能实现

    1. 创建用户视图

    app/view/user/create.hbs 核心代码
    1. <form method="POST" action="/api/user?_csrf={{csrf}}" enctype="application/x-www-form-urlencoded">
    2. <div class="form-row">
    3.   <label>用户名：</label>
    4.   <input type="text" name="name" />
    5. </div>
    6. <div class="form-row">
    7.   <label>用户配置：</label>
    8.   <textarea name="config"></textarea>
    9. </div>
    10. <div class="form-row">
    11.   <input type="submit" value="创建用户" />
    12. </div>
    13. </form>

    需要在 form action url 参数添加 _csrf 参数

完整代码：https://github.com/Samaritan89/egg-demo/blob/v2/app/view/user/create.hbs

2. 页面 controller

app/controller/user.js 添加 create 方法响应视图

  1. async create() {
  2.   await this.ctx.render('user/create', {
  3.     csrf: this.ctx.csrf,
  4.   });
  5. }

完整代码：https://github.com/Samaritan89/egg-demo/blob/v2/app/controller/user.js#L6

3. 创建用户 api controller

app/controller/api/user.js 添加方法 create 

  1. async create() {
  2.   const { name, config } = this.ctx.request.body;
  4.   const result = await this.service.user.insert({
  5.     name,
  6.     config,
  7.   });
  9.   this.ctx.body = {
  10.     userId: result.insertId,
  11.   };
  12. }

完整代码：https://github.com/Samaritan89/egg-demo/blob/v2/app/controller/api/user.js#L26

4. 创建用户 service

app/service/user.js 添加方法 insert 

  1. async insert(user) {
  2.   const result = await this.app.mysql.insert('user', user);
  3.   return result;
  4. }

完整代码：https://github.com/Samaritan89/egg-demo/blob/v2/app/service/user.js#L33

这样就可以添加用户了，其它集中操作非常类似，不再重复介绍，感兴趣可以看看获取用户列表的 service，稍微复杂一些

  1. async list(pageSize, pageNo, orderBy = 'id', order = 'ASC') {
  3.     let sql = 'select id, name, config from user ';
  4.     sql += 'where deleted = 0 ';
  5.     sql += 'order by ? ? ';
  6.     sql += 'limit ?,?;';
  8.     const offset = pageSize * (pageNo - 1);
  9.     const users = await this.app.mysql.query(sql, [ orderBy, order, offset, pageSize ]);
  11.     const totalNumRow = await this.app.mysql.query('select count(id) as totalNum from user where deleted = 0;');
  13.     return {
  14.       users,
  15.       pages: {
  16.         pageNo,
  17.         pageSize,
  18.         total: totalNumRow[0].totalNum,
  19.       },
  20.     };
  21.   }

完整示例：https://github.com/Samaritan89/egg-demo/blob/v2/app/service/user.js#L11

事务

很多时候做一个业务操作需要对数据库中多个表做处理，但其中一个的操作出现错误就需要把所有操作返回，保证数据的准确性，这就是数据库中【事务】的概念
对于一个事务来讲，主要有 beginTransaction、commit 或 rollback，分别代表事务的开始，成功和失败回滚，

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

手工操作

  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. }

自动控制

  1. const result = await app.mysql.beginTransactionScope(async conn => {
  2.   // 不要自定 commit 或者回滚，出现错误时候回自动处理
  3.   await conn.insert(table, row1);
  4.   await conn.update(table, row2);
  5.   return { success: true };
  6. }, ctx); // ctx 是当前请求的上下文，如果是在 service 文件中，可以从 `this.ctx` 获取到

在复杂场景合理使用事务能够让业务系统更加稳定、可靠

附

  1. MySQL 使用 138 张图带你 MySQL 入门