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

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

月光魔力鸭

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


感谢支持!

赞赏支持
提交评论
评论信息 (请文明评论)
暂无评论,快来快来写想法...
推荐
由于国家的行政区划每年都有变化,所以经常需要更新最新的数据,这里提供一个nodejs版本mysql数据存储的抓取示例。
近期有个小工具,需要使用到本地数据库,想使用sqlite ,本来以为是个手到擒来的活... 没成想卡在了开始上。
想做微信/支付宝支付很久了..奈何需要的资质太多,只能慢慢申请,等待,审核..终于下来了。
做了一个阿里云开发者社区自动签到,想着能积攒一些换点啥东西,放在服务器上出现了各种错误。
这事其实很简单,就是调用adb几个命令而已.. 主要是有时候想用的时候总感觉差点啥,东拼西凑才弄出来,这里记录下,哪怕就是贴个地址的,也比记在脑子里强。
前段时间帮朋友下歌放在车上听..结果好多都是ncm格式,伤心 ,搜索了下发现基本上这格式解密有好多昂,可惜UI我都不太想要..决定抄一下,自己做一个。 这里先记录下核心代码,回头补充个UI 做个小程序。
记录下在linux环境下安装phantomjs 的步骤,防止遗忘...
跑了一个千库网的自动签到,在windows上测试的时候好好的,图片也没问题,可是放到linux服务器就不行了,总是登录不上不说,图片都不一样