在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事务支持的接口,其支持两种方式的事务使用:
- 手动控制:
beginTransaction
、commit
、rollback
操作均有开发者完全控制,可以做到细粒度的控制,如下:
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"))