高效导入sql语句工具

一,背景

因为最近项目需要迁移数据库,于是我问了一下后台开发导入数据要多久?结果他说要一个小时,我觉得时间太长了,于是我搜索了一下有关快速插入sql的知识,从而用Node js 写这个小工具,方便快速导入sql。

二,原理

因为我们导出Sql数据是一行一条插入语句,执行sql时候按照每行执行一次,这样子导致写入特别多,大量消耗在网络传输中,于是我们只要把多行插入语句整合一条语句,但这里要知道mysql的一条sql最大长度是1M,所以我合并成一条时候需要注意长度,我用Node js写的工具已经处理这个问题。

三,代码

/**
 * 一个sql多张表处理,针对整体处理
 */
 const fs = require('fs')
 var sql_path = 'd:/t3.sql'
 var sava_sql_path = 'd:/batch.sql'
 const max_sql_len = 1024*1000   //mysql单个最大包1m,怕其他字节占用,我就用1000 比 1024小一些
 var global_sort_table_name = []        //全局表名,用来按照顺序插入
 
 //读取mysql文件
 function read_mysql_file(path){
     let data = fs.readFileSync(path)
     if(data.length > 0){
         data = data.toString()
     }
     return data
 }
 
 //基本解析
 //返回对应的数据
 /**
  * 
  * @param {string} data 
  * @returns 
  */
 function parse(data){
     let line_insert_key = 'INSERT INTO'
     let arrya = data.split('\n')
     let new_inert_sql = []
     var m = new Map();
     arrya.forEach(element => {
         if(element.indexOf(line_insert_key) != -1){
             let real_data = get_insert_sql_value(element)
             if(real_data){
                 if(m.has(real_data.table_name)){
                    let array = m.get(real_data.table_name)
                    array.push(real_data.data)
                 }else{
                    let  array = []
                    array.push(real_data.data)
                    global_sort_table_name.push(real_data.table_name)
                    m.set(real_data.table_name, array)
                 }
             }
         }
     });

     return m
 }
 
 /**
  * 获取插入的sql的语句
  * @param {String} inert_sql 
  */
 function get_insert_sql_value(insert_sql){
     let begin_key = 'VALUES ('
     let end_key = ');'
     let start_pos = insert_sql.indexOf(begin_key)
     let end_pos = insert_sql.indexOf(end_key)

     if(start_pos > 0 && end_pos > start_pos){
         start_pos = start_pos+begin_key.length
         let len = end_pos - start_pos
         
         let insert_right_value = insert_sql.substr(start_pos, len)
         let insert_left_value = insert_sql.substr(0, start_pos - 1)
         let table_name = get_insert_table_name(insert_left_value)

         return {
             table_name: table_name,
             data: insert_right_value
         }
     }
     return null
 }

 //获取插入的表
 function get_insert_table_name(insert_left_value){
    let begin_key = 'INSERT INTO `'
    let end_key = '` VALUES'

    if(insert_left_value && insert_left_value.indexOf('INSERT INTO') >= 0){
        let start_pos = insert_left_value.indexOf(begin_key)
        let end_pos = insert_left_value.indexOf(end_key)
        if(start_pos >= 0 && end_pos > start_pos){
            start_pos = start_pos + begin_key.length
            let len = end_pos - start_pos
            let table_name = insert_left_value.substr(start_pos, len)
            return table_name
        }
    }
    return ""
 }
 
 //创建新的插入数组
 /**
  * 
  * @param {Map} data 
  * @returns 
  */
 function create_new_insert_array(data){
    var data_array
     const template = "INSERT INTO `[表名]` VALUES"
     let current_insert_sql = []
     var new_insert_sql = template
 
     for(let ele of global_sort_table_name){
        data_array = data.get(ele)
        new_insert_sql = template
        let insert_comment = '-- ----------------------------\n'
        insert_comment += '-- ' + 'Records of ' + ele + '\n'
        insert_comment += '-- ----------------------------\n'
        new_insert_sql = insert_comment + new_insert_sql
        new_insert_sql = new_insert_sql.replace('[表名]',ele)
        

        for(let index = 0; index < data_array.length; index++){
            new_insert_sql += '(' + data_array[index]
            new_insert_sql += ')'
            if(new_insert_sql.length + 500 >= max_sql_len || (index+1 == data_array.length)){
                new_insert_sql+= ";"
                current_insert_sql.push(new_insert_sql)
                new_insert_sql = template
                new_insert_sql = new_insert_sql.replace('[表名]',ele)
            }else{
                new_insert_sql += ','
            }
        }
     }
 
     return current_insert_sql
 }
 
 /**
  * 解析完成sql对象
  * 新的文件路径
  * @param {*} sql_object 
  * @param {*} new_file_path 
  */
 function create(data_array, new_file_path){
     let new_data_string = ""
     data_array.forEach(element=>{
         new_data_string += element
         new_data_string += '\n'
     })
     fs.writeFileSync(new_file_path,new_data_string)
 }
 
 function init(){
     console.log("请设置自己原始sql路径和生成sql路径")
     let data = read_mysql_file(sql_path)
     let r = parse(data)
     let data_array = create_new_insert_array(r)
     create(data_array, sava_sql_path)
     console.log('生成' + sava_sql_path + ' 成功')
 }
 
 init()
 

四,使用

你使用需要修改sql_path改成你自己路径

node xxx.js 即可,成功输出文档到sava_sql_path路径

校验数据不否正确,可以对比2份数据库的sql数据即可