nodejs实现比对两个数据库之间的差异

nodejs实现比对两个数据库之间的差异

月光魔力鸭

2023-03-02 16:15 阅读 270 喜欢 0

产品版本更新的时候经常会有一些数据库的差异,如果版本管理好的话,一步一步升级即可.. 但是如果好久没更新的话,还是有很多不确定的,只能挨着比对表和字段。比对了一次就烦了,写了这么一个工具,查询差异表和字段并给出sql语句。

mysql数据库比对工具

nodejs实现

依赖: thinksql

直接放代码

/**
 * 比对两个数据库之间的表结构等差异化,并给出对应的sql 变更
 * 用于版本升级数据库变更
 */
const thinksql = require('thinksql');
const fs = require('fs');
let sqlFile = './diff.sql';//存储差异sql文件
/**
 * 主数据库(开发最全的或者当前版本的)
 */
var masterDB = {
  database: '数据库名称',
  host: 'localhost',
  port: '3306',
  user: 'root',
  password: 'root',
  modelName: 'mysql',
  debounce:false,//防止同语句返回同结果
  // logSql:true
};

/**
 * 需要更新的目标数据库
 */
var updateDB = {
  database: '另一个数据库名称',
  host: 'localhost',
  port: '3306',
  user: 'root',
  password: 'root',
  modelName: 'mysql2',
  debounce:false,
  // logSql:true
};


/**
 * 比对目标
 * 1.表差异
 * 2.字段差异
 * 3.字段类型及注释差异
 */

class DBDiff{

  constructor(master,update) {
    this.master = this.getModel(master);
    this.update = this.getModel(update);

    return this;
  }
  //获取model
  getModel(dbConfig) {
    return thinksql(dbConfig);
  }
  //获取表名
  async getTables(model) {
    let rst = await model.model('').query('show tables');
    return rst.map(t => {let tn = '';for (let k in t) {tn = t[k]}return tn;});
  }
  //获取表结构
  async getColumn(model,tableName) {
    let rst = await model.model('').query(`select * from information_schema.columns where table_schema='${model.config.database}' and table_name='${tableName}'`);
    return rst;
  }
  //根据字段编写建表语句
  getCreateTableSql(tableName, columnList) {
    //获取主键
    columnList.sort((a, b) => {
      return a.ORDINAL_POSITION - b.ORDINAL_POSITION;
    });
    let primaryKey = '';
    let keySql = '';
    columnList.forEach(t => {
      if (t.COLUMN_KEY == 'PRI') {
        primaryKey = t.COLUMN_NAME;
      }
      keySql += `\`${t.COLUMN_NAME}\` ${t.COLUMN_TYPE} ${t.COLLATION_NAME ? 'CHARACTER SET '+t.CHARACTER_SET_NAME+' COLLATE '+t.COLLATION_NAME+'' : ''} ${t.IS_NULLABLE == 'NO' ? ' NOT NULL ' : ' NULL '} ${t.COLUMN_KEY == 'PRI'||t.IS_NULLABLE=='NO' ? '' : 'DEFAULT '+(t.COLUMN_DEFAULT ? t.COLUMN_DEFAULT : 'NULL')}  COMMENT '${t.COLUMN_COMMENT}', \r\n`
    });

    keySql += `PRIMARY KEY (\`${primaryKey}\`) USING BTREE`;
    

    let sql = `
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS \`${tableName}\`;
CREATE TABLE \`${tableName}\` (
${keySql}
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
    `;
    return sql;
  }
  //表字段差异修改
  getColumnSql(tableName,masterList,updateList) {
    //1.有没有,类型是否一致
    var map = {};
    let sql = '';
    updateList.forEach(t => {
      map[t.COLUMN_NAME] = t;
    })
    for (let t of masterList){
      if(!map[t.COLUMN_NAME]){
        //不存在该字段,增加字段数据
        sql += `alter table ${tableName} add column ${t.COLUMN_NAME} ${t.COLUMN_TYPE} default ${t.COLUMN_DEFAULT} comment '${t.COLUMN_COMMENT}';\r\n`;
      } else {
        //判断是否一致
        var tt = map[t.COLUMN_NAME];
        if (t.COLUMN_TYPE != tt.COLUMN_TYPE || t.COLUMN_COMMENT != tt.COLUMN_COMMENT) {
          sql += `alter table ${tableName} modify column ${t.COLUMN_NAME} ${t.COLUMN_TYPE} default ${t.COLUMN_DEFAULT} comment '${t.COLUMN_COMMENT}';\r\n`
        }
      }
    }
    return sql;
  }
  async output(sql,comment) {
    fs.appendFileSync(sqlFile, '-- ' + comment + '\r\n' + sql + '\r\n');
  }

  async start() {

    let masterTables = await this.getTables(this.master);
    let updateTables = await this.getTables(this.update);
    let updateMap = {};
    for (let tableName of updateTables){
      let columnList = await this.getColumn(this.update, tableName);
      updateMap[tableName] = columnList;
    }

    for (let tableName of masterTables){
      console.log(`比对: ${tableName}`)
      let masterColumnList = await this.getColumn(this.master, tableName);
      
      if (!updateMap[tableName]) {
        //表不存在,增加建表语句
        let sql = this.getCreateTableSql(tableName, masterColumnList);
        this.output(sql,'表不存在:'+tableName)
      } else {
        //如果存在则对比字段差异
        let sql = this.getColumnSql(tableName,masterColumnList, updateMap[tableName]);
        this.output(sql, '表字段差异:' + tableName);
      }
    }
  }
}

async function start() {
  let dif = new DBDiff(masterDB, updateDB);
  await dif.start();
  process.exit(0);
}
start().catch(console.error);

最后生成的内容如下:

差异sql


最近写的属实少了点,尽量多写点,哪怕水呢,先上量,在上质。

转载请注明出处: https://chrunlee.cn/article/diff-database-column.html


感谢支持!

赞赏支持
提交评论
评论信息 (请文明评论)
暂无评论,快来快来写想法...
推荐
做了一个阿里云开发者社区自动签到,想着能积攒一些换点啥东西,放在服务器上出现了各种错误。
开始入手webpack ,直接看的官方文档和demo,对于自动刷新这部分还是希望通过express 加载插件来控制,但是文档没有提供,经过参考github上其他高玩的套路,最终整理了一个基础的配置版本。
今天写文章,突然发现自己常用的素材站换成了webp格式的图片.. 可惜本站还没准备加这个支持,所以准备加个webp转jpg的小功能,继续使用啦。
在使用marked来做md解析的时候,部分解析规则可能并不是很如意,比如说,我在md中写了a标签,但是这些标签都是在当前页面替换的,而我想要的是新打开窗口。
由于只是做个测试,这里使用了expresss简单搭建了个后台服务,提供文件断点下载。
通过pm2来实现nodejs应用的集群,不过我之前没做session共享,导致.. 登录不上啊 啊啊啊,无奈,又重新对redis进行了集成。
在平时nodejs练习过程中,可能会安装多个不同版本的nodejs,那么我们如何来轻松的管理和切换呢?推荐你一个nvm来试试水
也不知道咋回事 ... 哈哈,忽然想研究下磁力网站,其实并不是很想懂里面的原理,只是搞不明白他们的资源是从哪里来的..很是纳闷?