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

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

月光魔力鸭

2023-03-02 16:15 阅读 298 喜欢 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


感谢支持!

赞赏支持
提交评论
评论信息 (请文明评论)
暂无评论,快来快来写想法...
推荐
最近有客户提出了这么一个需求:微信dat文件在解码后的图片无法按照时间进行排序。 是的,解码后的文件的时间都是解码的时间,由于软件比较多,当时没做自动更新,所以在这里做一个小工具,可以将对应的解码后的图片的时间修改为微信dat文件对应的时间
当我们想实现一个自己的库或模块后,发布的话,需要发布到npm上才能下载。以下是具体步骤
通过imap 来接收邮箱新邮件,类似客户端系列,不过比较简单的,目前只有新邮件,后续也可以通过这个来做一个自己的邮件客户端。
关于js的编译和压缩,之前做过一个小工具了,主要就是自己项目成员大都没有这部分的技能,导致发布的时候总需要去编译压缩下.. 最终做了个命令行小工具.. 问题不在这里,前一阵子做压缩的时候发现压缩后竟然是undefined.最终才发现是es6的语法问题。
前段时间做了个微信小程序反编译的小东西,不过因为功能不全,没加分包处理,正好处理下加上,又考虑到后续可能的更新情况,准备上手增加下更新功能。
最近一直在围绕着我的小电脑在转,基本都是这方面的问题。在没有公网IP的情况下,这个问题就是我怎么才能通过域名访问到我家中的服务器。
做了一个阿里云开发者社区自动签到,想着能积攒一些换点啥东西,放在服务器上出现了各种错误。
学习爬虫的时候突然有想到想做一个音乐播放小站,可以给自己或朋友听,但是音乐哪里来呢??想到自己常听的豆瓣FM,就越发的想把这些音乐都拿下来,因此有了下文通过豆瓣FM批量抓取上万首音乐,目前已经3W+。