在nodejs中使用mysql2库

rockswang

参考

前言

为何使用mysql2而不是经典的mysql库?主要基于以下原因:

  1. 更高的性能!
  2. 支持PreparedStatement,多次查询性能更高,书写SQL更简单;
  3. 自带Promise包装器,可以直接使用async/await语法;
  4. 绝大部分api和mysql库兼容,意味着mysql的文档和线上资料亦可作为参考。

基本用法

  • 安装
    npm install mysql2
  • 连接数据库

    const conn = await mysql.createConnection({
      host: 'xxxxxxxxxxxxxx.mysql.rds.aliyuncs.com',
      user: '<数据库用户名>',
      password: '<数据库密码>',
      database: '<数据库名称>',
      charset: 'utf8mb4'
    })

    这里字符集用utf8mb4,这样就可以支持emoji表情字符了。

  • 查询例子

    const [rows, fields] = await conn.query(
      'SELECT * FROM `user` where id in (?)',
      [userIds])
  • 插入例子

    const [rs] = await conn.query(
      'insert into `user` values (0,?,?,?,now())',
      [name, password, email])
    const id = rs.insertId // 获取自增ID
  • 预先生成SQL语句

    const sql = conn.format(
      'select * from ?? where ??=?',
      ['group', 'key', id])
    const [[user]] = await conn.query(sql)
  • 基于PrepareStatement的查询

    const [rows] = await conn.execute(
      'SELECT * FROM `user` where id = ?',
      [userId])

说明:

  • query和execute的返回值为一对数据,即查询到的结果集数组,和结果集元数据,可以用rows[n].name形式直接引用结果集中的数据。
  • query和execute的区别在于,query完全在node侧帮你组装SQL,而execute则可以利用MySQL的PrepareStatement机制来预编译SQL语句。
  • execute的优势是数据库原生支持的预编译机制,性能更高。
  • query的优势是更灵活,包括:

    • 可以用??代替表名、字段、索引名等,组装SQL时自动加反引号
    • '?'参数支持数组、对象等,也就是可以用在in从句中,见上面的查询例子

高级特性

连接池

  • 创建连接池
    参数和createConnection基本一致,添加了一些池化控制的参数;具体可参见mysql库文档

    const pool = mysql.createPool({...})
  • 进程退出时自动关闭连接池

    process.on('exit', async (code) => {
      try { await pool.end() } catch (e) {}
    })
  • 简单使用
    直接用pool.query(...)和pool.execute(...)即可,相当于从池中获取一条连接,然后执行查询

事务

基本步骤:

  • 从连接池中获取一条连接
  • 开启事务
  • 执行更新(及查询)
  • 提交事务
  • 归还连接
    例子如下:

    const conn = await pool.getConnection()
    await conn.beginTransaction()
    const [rows] = await conn.execute('select * from `activity` where claimed_by is null order by id limit 1 for update')
    if (rows.length > 0) {
      const code = rows[0].code
      await conn.execute('update `activity` set claimed = ?, claimed_by = ? where id = ?', [new Date(), openid, rows[0].id])
      msg = `恭喜您!成功抢得礼品卡一张!\n兑换码为:${code}\n`
    } else {
      msg = '对不起,此次活动的奖品都发完啦!敬请期待下次活动!'
    }
    sendMsg(msg)
    await conn.commit()
    conn.release()

数据转码

  • mysql/mysql2提供了escape函数用于将javascript数据转码为SQL接受的格式,尤其对于用户输入的数据,必须转码处理后再插入数据库,以防御SQL注入攻击。
  • escape会自动把各种原生数据转换为mysql接受的数据类型,包括:

    • 字符串自动加单引号,字符串中的特殊字符(尤其是单引号)自动转义
    • Date类型转换为'YYYY-mm-dd HH:ii:ss'格式的字符串
    • 数组自动转换成逗号分隔字符串,比如:['a', 'b']转为'a', 'b'
    • undefined/null转为NULL
阅读 12k

玩儿编程
编程也是可以玩儿的!微信公众号“玩儿编程”

To play is Human

1.4k 声望
151 粉丝
0 条评论

To play is Human

1.4k 声望
151 粉丝
文章目录
宣传栏