获取当前数据库(mysql)所有表的字段内容

把数据库中所有的表字段都取出,存成.json文件备用.

db为自定义数据库操作类

/*
数据库各表结构生成类
取出各表字段类型,存成JSON格式,用来配合schema类
 */
global.rootPath = __dirname;
var db = require("./extend_modules/db/db").DBManager;
var fs = require("fs");
var outJson = '{';
function getColumns() {
    var columnXml = "";
    var sql = "select TABLE_NAME,column_name,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_COMMENT from information_schema.`COLUMNS` " +
        "where TABLE_SCHEMA=? order by TABLE_NAME,ORDINAL_POSITION";
    var values = ["main"];
    db.selectDB(sql, values, function (err, result) {
        if (err) {
            console.log("error");
        } else if (!result[0] || result[0].length < 0) {
            console.log("empty");
        } else {
            var json = JSON.parse(JSON.stringify(result));
            var tablename = "";
            for (var p in json) {
                if(json[p].TABLE_NAME!=tablename) {
                    tablename = json[p].TABLE_NAME;
                    if(columnXml) {
                        outJson += columnXml + '}'+(p>0?",":"");
                        columnXml = '';
                    }
                    outJson += '"' + json[p].TABLE_NAME + '":{';
                }
                columnXml += columnXml?",":"";
                columnXml += '"' + json[p].column_name + '":{"type":"' + json[p].DATA_TYPE + '","length":"' + json[p].CHARACTER_MAXIMUM_LENGTH + '"' +
                    ',"isnull":"' + json[p].IS_NULLABLE + '","comment":"' + json[p].COLUMN_COMMENT + '"}';
            }
            if(columnXml) {
                outJson += columnXml+'}';
            }
            outJson+="}";
            writeJsonFile(outJson);
        }
    });
}
//把各表数据结构写入db.json文件
var writeJsonFile = function(body){
    fs.writeFile('./db.json', body, function (err) {
        if (err) throw err;
        console.log('It\'s saved!');
    });
}
getColumns();

db.js

/*公用数据库连接模块*/
var mysql = require("mysql");
var Util = require("../util/util").Util;
var Config = require("../core/config").Config;

var rConfig = Config.DBConfig.reader;

var rPool = mysql.createPool(rConfig);
var DBManager = {};
DBManager.selectDB = function(sql,values,callback){
    rPool.getConnection(function(connErr, connection){
        console.log(sql+":"+values);
        if(connErr){
            callback.apply(null,[connErr, null]);
            connection.release();
            return;
        }
        connection.query(sql,values,function(queryErr, rows){
            callback.apply(null, [queryErr,rows]);
            connection.release();
        });
    });
}

module.exports.DBManager = DBManager;

config.js

var fs = require("fs");
var configFile = fs.readFileSync(global.rootPath+"/config.json","utf8");
var Config = JSON.parse(configFile);

Config.getDBConfig = function(){
    return Config.DBConfig;
}

module.exports.Config = Config;

config.json

{
  "DBConfig": {
    "reader": {
      "connectionLimit": 10,
      "host": "localhost",
      "user": "root",
      "password": "root",
      "database": "main"
    }
  }
}