JS进行sql脚本整理

JS进行sql脚本整理

在项目增量开发时，会出现开发环境因为集成了新功能或修改了旧功能，导致数据库中数据表字段修改或数据已经更新的情况。这时如果其他环境想要使用新功能，除了更新服务程序外，还需要将开发环境新增的数据整理成sql进行数据同步。

如果新增的数据量很大或者一次需要同时更新几张数据表数据的情况，手动整理是非常费力耗时的操作，而且很可能因为疏忽写错sql语法或者数据。

为了避免sql脚本整理错误，需要让机器帮忙进行sql脚本生成。本来想用Java来完成这个功能，但Java编码太繁琐了，所以这里选用NodeJS来完成。

• NodeJS连接Mysql

  因为项目使用的SQL数据库是Mysql，所以NodeJS需要有连接Mysql并操作数据库的能力。这里官方提供了一个mysqljs，只需要通过 npm install mysql进入js项目即可：

  npm install mysql
  

  • js中引入mysqljs模块

    const Mysql = require('mysql');
    

  • 连接配置信息

    const conn = Mysql.createConnection({
            host: '127.0.0.1',//IP
            port: '3306',//端口号
            user: 'user',//用户
            password: 'password',//密码
            database: 'database'//数据库
        });
    

  • 连接

    conn.connect();
    

  • 查询

    查询示例：

    /**
     * @param {string} sql //查询的sql
     * @param {Array} options //占位符
     * @param {Function} callback //查询结果回调
     */
    conn.query(sql, options, (error, results, fields) => {
                if (error) throw error;
              console.log(results);//返回查询结果
            });
    

    返回结果示例：

    //无数据
    []
    //有数据
    [ RowDataPacket {
        id: 363014473,
        name: 'sun',
      AGE: 'uid5866',
        Date: 2020-05-13T02:06:54.000Z,
      isrequired: null,
    } ]
    

    查询无数据时，返回空数组。有数据时，返回RowDataPacket数组，RowDataPacket对象属性与数据库中的字段名-字段值对应，并且字段名与Mysql中存储保持一致。

    通过js的typeof进行测试RowDataPacket对象的字段值类型对应大致如下：数字是number，字符串是string，日期和null是object。

  • 结束连接

    conn.end();
    

• 字段值转化

  由于目的是为了将查询到的结果转化为sql语句，又根据上述查询返回的结果，字段名本身与Mysql中对应，并且类型已经是string，不需要进行加工，所以只需要将返回的字段值转化为正确的string值即可。

  const Moment = require('moment');//导入日期格式化模块，npm install moment
  /**
   * @param {*} e //字段值
   * @return {string} v
   */
  function toSqlValue(e) {
      let v = '';
      if (typeof (e) == 'string') {
          if (e.search(/'/) != -1)
              v += '"' + e + '"';//"'e'"
          else
              v += '\'' + e + '\'';//'e' 或 '"e"'
      } else if (typeof (e) == 'number') {
          v += e;//e
      } else if (typeof (e) == 'object') {
          if (e == null) {
              v += 'NULL';//NULL
          }else if(e instanceof Date){
              v += Moment().format('YYYY-MM-DD HH:mm:ss');//'YYYY-MM-DD HH:mm:ss'
          }
      }
      return v;
  }
  

• INSERT语句生成

  Mysql中INSERT的语法大致如下：

  INSERT INTO `table_name` (`column1`,`column2`, ...) VALUES ('value1', 'value2', ...)
  

  INSERT语句生成比较简单，只需要遍历RowDataPacket对象，将属性依次填入column，将属性值转化后填入value即可。

  /**
   * @param {string} table //表名
   * @return {object} row //RowDataPacket对象
   */
  function insertSql(table, row){
      let sql = 'INSERT INTO `' + table + '`(';
      for (col in row) {
          sql += '\`' + col + '\`, ';
      }
      sql = sql.substring(0, sql.lastIndexOf(','));
      sql += ') VALUES (';
      for (col in row) {
          sql += toSqlValue(row[col]) + ', ';
      }
      sql = sql.substring(0, sql.lastIndexOf(','));
      sql += ');'
      return sql;
  }
  function bathInsertSql(table, rows){
      let sql = '';
      for (let i = 0; i < rows.length; i++) {
          sql += insertSql(table, rows[i]) + '\r\n';
      }
      return sql;
  }
  

• UPDATE语句生成

  Mysql中UPDATE的语法大致如下：

  UPDATE `table_name` SET `column1` = 'value1',`column2` = 'value2', ... 
  WHERE `column1` = 'value1' AND `column2` = 'value2'
  

  UPDATE中的WHERE可能会有其他复杂情况，但目前本人没有遇到复杂的场景，所以只实习了上述的UPDATE语句生成。

  /**
   *
   * @param {string} table //表名
   * @param {Array} updateCols //更新字段
   * @param {object} row //RowDataPacket对象
   * @param {Array} whereCols //条件
   * @param {Array} whereConects //连接词
   */
  function updateSql(table, updateCols, row, whereCols, whereConects){
      let sql = 'UPDATE `' + table + '` SET ';
      for (let i = 0; i < updateCols.length; i++) {
          let col = updateCols[i];
          sql += '`' + col + '` = ' + toSqlValue(row[col]) + ', ';
      }
      sql = sql.substring(0, sql.lastIndexOf(','));
      if (whereCols == undefined) {
          sql += ';';
      } else {
          sql += '\r\n';
          sql += 'WHERE ';
          let wclen = whereConects.length;
          for (let i = 0; i < wclen; i++) {
              let col = whereCols[i];
              let con = whereConects[i];
              sql += '`' + col + '` = ' + toSqlValue(row[col]) + ' ' + con + ' ';
          }
          let col = whereCols[whereCols.length - 1];
          sql += '`' + col + '` = ' + toSqlValue(row[col]) + ';\r\n';
      }
      return sql;
  }
  

• 创建mapper模块

  //mapper.js
  const mapper = {
      tableName:{
          getTableNameByID: `select * from table_name where id = ?`,
      	... ...
      },
      ... ...
  };
  
  module.exports = mapper;//模块导出
  

• 创建table模块

  //table.js
  const table = {
      tableName: `table_name`,
      ... ...
  }
  
  module.exports = table;//模块导出
  

• sql整理（只提供INSERT生成示例，UPDATE同理）

  利用上述提供的方法可以已经可以进行简单的sql整理了，如下：

  const Mysql = require('mysql');
  const fs = require('fs');
  const Util = require('./utils');//包含上述字段值转化、ISNERT和UPDATE语句生成方法
  const Table = require('./table');
  const Sql = require('./mapper');
  const Config = require('./config');//一些配置信息
  
  const conn = Mysql.createConnection(Config.mysql);
  conn.connect();
  let id = Config.tableName.id;
  conn.query(Sql.tableName.gettableNameByID, [id], (error, results, fields) => {
      if (error) throw error;
      let inserts = Util.bathInsertSql(Table.tableName, results);
      //生成sql文件
      fs.writeFile(Config.FILE_PATH, inserts, 'utf8', (err) => {
          if (err) throw err;
          console.log(`${Config.FILE_PATH}脚本生成完成`);
      });
      conn.end();//必须关闭，否则代码不会停止
  });
  

  ！但是，对于一次需要同时更新几张表的情况，往往会出现一个查询语句的条件依赖于上一个查询语句的结果值。这时会出现query的回调中又嵌套着一个或者几个query，这时Mysql连接断开的时机不太好判断，代码会变得非常不好维护。

  conn.query(Sql.tableName1.gettableName1ByID, [id], (error, results, fields) => {
      if (error) throw error;
     	//生成脚本...
      ... ...
      for(let r in results){
          conn.query(Sql.tableName2.gettableName2ByName, [results[r][name]], (error, results, fields) => {
     		 if (error) throw error;
              //生成脚本...
              ... ...
  		});
      }
  });
  

  优化：

  JS中通过async关键字申明异步方法，async function fn(){}，这类方法会将返回值封装为Promise对象作为返回。其中还有await关键字（需要在async方法中使用），代码执行值await处时，将会等待await的表达式执行结束后再往下执行，将异步代码转为同步执行。

  • 使用Promise对象对query方法改造

    Promise的回调函数有resolve和reject两个参数，resolve用于将内部异步方法执行完成后的需要的结果值传递至Promise对象，reject则是将代码错误传递至Promise对象。

    function query(sql, options) {
        return new Promise((resolve, reject) => {
            conn.query(sql, options, (error, results, fields) => {
                if (error) throw reject(error);
                resolve(results);
            });
        });
    }
    

  • 回调嵌套优化

    //将sql整理整合为一个方法
    async function generate(id, callback) {
        let inserts = '';
        let table1 = await query(Sql.tableName1.gettableName1ByID, [id]);//代码将会阻塞直到获得query结果
        inserts += `${Util.bathInsertSql(Table.tableName1, table1)}\r\n`;
        for(let r in results){
            let table2 = await query(Sql.tableName2.gettableName2ByName, [results[r][name]]);
            inserts += `${Util.bathInsertSql(Table.tableName2, table2)}\r\n`;
        }
        callback(inserts);
    }
    
    generate(ID, (res) => {
        conn.end();//这时只需要在方法执行结束后关闭连接即可
        fs.writeFile(Config.FILE_PATH, res, 'utf8', (err) => {
            if (err) throw err;
            console.log(`${Config.FILE_PATH}脚本生成完成`);
        });
    });