sql实现跨库查询

前提: 前端开发负责写前端页面以及node开发,node是基于egg.js,node的orm用的是sequelize

这里是有 modelMysqlName和modelLogMysqlName得到得两个库

本地得话在config/config.local.js里面写了sequelize对象里面去定义

  /**
   * 用户金币兑换游戏币记录表
   * @param { Object } body 传入的参数
   * @param { Object } body.time 时间的参数
   * @param { String } body.userName 用户名称
   * @param { String } body.userId 用户id
   * @param { String } type 判断是不是导出
   * @return { Object } list 是列表 total是筛选后的总数
   */
  async gameCoinExchangeRecord({ limit, offset, time, userName = '', userId = '' }, type) {
    const modelMysqlName = this.config.sequelize.datasources[0].database // 这里拿到配置得库
    const modelLogMysqlName = this.config.sequelize.datasources[1].database // 这里拿到配置得库
    // 关联base表
    const baseSql = `LEFT OUTER JOIN ${modelMysqlName}.hx_user_base_info AS baseInfo 
    on exchange.user_id = baseInfo.user_id
    WHERE exchange.user_id LIKE '%${userId}%' ${time}
    ${userName ? `AND baseInfo.nickname LIKE '%${userName}%'` : ''}`
    const list = await this.app.model.query( // 这里要把需要得数据一起拿到nickname AS userName等
      `SELECT id, conf_exchange_id, income AS sendGameCoin, nickname AS userName, expend AS exchangeCostGold, 
      exchange.user_id AS userId, created_at AS exchangeTime FROM ${modelLogMysqlName}.hx_user_gold_exchange_record AS 
      exchange ${baseSql} ${type !== 'exportExcel' ? `LIMIT ${offset}, ${limit}` : ''}`, { type: 'SELECT' }
    )
    // 获取数量
    const count = await this.ctx.model.query(`
    select count(exchange.id) as total FROM ${modelLogMysqlName}.hx_user_gold_exchange_record as exchange 
    ${baseSql}`, { type: 'SELECT' })

    return { list, total: count[0].total }
  }

如何定义?

module.exports = () => {
  // 企业微信相关配置
  return {
    // tars
    sequelize: {
      datasources: [{
        delegate: 'xx',
        dialect: 'xx',
        baseDir: 'xx',
        username: 'xx',
        password: 'xx',
        host: 'xx',
        port: xx,
        database: 'xx',
        timezone: 'xx',
        define: {
          freezeTableName: true, // 防止修改表名为复数
          underscored: true, // 防止驼峰式字段被默认转为下划线
        },
      },
      {
        delegate: 'xx',
        dialect: 'xx',
        baseDir: 'xx',
        username: 'xx',
        password: 'xx',
        host: 'xx',
        port: xx,
        database: 'xx',
        timezone: 'xx',
        define: {
          freezeTableName: true, // 防止修改表名为复数
          underscored: true, // 防止驼峰式字段被默认转为下划线
        },
      }],
    },
  }
}