在nodejs中使用mysql2库

参考

• QQ群 - Javascript高级爬虫 - 作者自建群，欢迎加入！
• awesome-java-crawler - 作者收集的爬虫相关工具和资料
• 一个帮你自动创建阿里云抢占式实例并开启网络加速的脚本 - 自动创建阿里云海外抢占式节点
• CentOS上安装node.js二进制发布包 - CentOS上安装nodejs最新版
• mysql2库github仓库地址
• mysql库github仓库地址

前言

为何使用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