愿你坚持不懈,努力进步,进阶成自己理想的人

—— 2017.09, 写给3年后的自己

Egg学习笔记(五):MySQL

在Web应用中,MySQL是最为常用的关系型数据库之一,Egg中则提供了egg-mysql用以驱动MySQL数据库的使用

一、安装与配置

和其他插件一样,首先需要通过npm引入,然后在config/plugin.js里进行配置:

npm install egg-mysql -S

开启插件:

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

若应用只需要使用一个MySQL数据库实例(单数据源),那么可以这么配置:

// app/config.[env].js
exports.mysql = {
    client: {
        host: 'localhost',
        port: 3306,
        user: 'user',
        password: 'pasword',
        database: 'databaseName'
    },
    app: true,      // 是否加载到app上
    agent: false    // 是否加载到agent上
}

使用也很简单,直接可以通过await app.mysql.query(sql, values)这种方式使用
若应用需要访问多个MySQL实例,那么则可以这么配置:

exports.mysql = {
    default: { ... }, // 所有数据库的默认配置 
    clients: {
        db1: { ... }
        db2: { ... }
    },
    app: true,
    agent: false
}

使用示例则如下:

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

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

此外,还支持动态创建方式,可以在运行时动态地从配置中心获取实际的参数,如:

// app.js
module.exports = app => {
    app.beforeStart(async () => {
        const config = await app.configCenter.fetch('mysql')
        app.database = app.mysql.createInstance(config)
    })
}


二、Service层

对数据库的操作,应该属于数据处理层的职责,因此建议将此部分代码置于Service层中维护,即形成:

Controller -> Service -> MySQL


三、CRUD接口

使用Egg提供的API,进行MySQL中的CRUD操作如下:

1、Create

const result = await this.app.mysql.insert('posts', {
    title: 'Hello, world'
})
// 可以通过 result.affectedRows 判断操作是否成功

2、Read

查询操作,可以使用get()/select()方法便捷地查询,如下:

  • 查询单条记录
await this.app.mysql.get('posts', { id: 12 })
// 相当于:SELECT * FROM `posts` WHERE `id` = '12' LIMIT 0,1
  • 查询全表
await this.app.mysql.select('posts')
// 相当于:SELECT * FROM `posts`
  • 条件查询与结果定制
await this.app.mysql.select('posts', {
    where: {
        status: 'draft',
        author: ['author1', 'author2']
    },
    columns: ['author', 'title'],
    orders: [
        ['created_at', 'desc'],
        ['id', 'desc']
    ],
    limit: 10,
    offset: 0
})
// 相当于:SELECT author, title FROM `posts` WHERE status = 'draft' AND author IN ('author1', 'author2') ORDER BY created_at DESC, id DESC LIMIT 0, 10

3、Update

更新操作,可以使用update()方法,如:

// 可以传入主键ID,则会根据主键ID查找并更新
const row = {
    id: 123,
    name: 'xxx',
    modifiedAt: this.app.mysql.literals.now // 相当于`NOW()`
}
const result = await this.app.mysql.update('posts', row)
// 相当于:
// UPDATE `posts` SET name = 'xxx', modifiedAt = NOW() WHERE id = '123'
// 同样可以用 result.affectedRows 来判断成功与否

4、Delete

可以使用delete()方法,如下:

const result = await this.app.mysql.delete('posts', {
    author: 'xxx'
})
// 相当于:DELETE FROM `posts` WHERE `author` = 'xxx'

5、直接执行SQL

插件也支持直接执行SQL,不过推荐尽量少直接执行SQL,以免引起SQL注入问题。若自己拼接SQL,可以使用mysql.escape方法、
以下是直接执行的示例:

await this.app.mysql.query(
    'UPDATE `posts` SET hits = (hits + ?) WHERE id = ?',
    [1, 123]
)


四、事务

Egg中也提供了对MySQL事务支持的接口,其支持两种方式的事务使用:

  • 手动控制:beginTransactioncommitrollback操作均有开发者完全控制,可以做到细粒度的控制,如下:
const conn = await app.mysql.beginTransaction()
try {
    await conn.insert(table, row1)
    await conn.update(table, row2)
    await conn.commit()
} catch (err) {
    await conn.rollback()
    throw err
}
  • 自动控制:提供了beginTransactionScope(scope, ctx)API,可以将SQL执行逻辑置于scope这一async函数中,还可以通过ctx传入当前请求的上下文对象,这样子可以保证即便在出现事务嵌套的情况下,一次请求中只有一个激活状态的事务,如下:
const result = await app.mysql.beginTransactionScope(async conn => {
    await conn.insert(table, row1)
    await conn.update(table, row2)
    return {
        success: true
    }
}, ctx)

这种方式的优点在于:易用且不易犯错,对开发者而言是透明的。但是缺点则是整个事务要么成功,要么失败回滚,不能进行细粒度的控制


五、表达式(Literal)

如果需要调用MySQL的内置函数,则可以使用Literal

1、内置表达式

  • NOW(),通过app.mysql.literals.now获取

2、自定义表达式

以调用MySQL内置CONCAT(s1, ...sn)函数为例:

const Literal = this.app.mysql.literals.Literal
await this.app.mysql.insert('someTable', {
    id: 123,
    fullname: new Literal(`CONCAT("James", "Bond")`)
})
// 相当于:
// INSERT INTO `someTable` (id, fullname) VALUES(123, CONCAT("James", "Bond"))