mysql获取当前月的所有数据库,获取当前数据库(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"

}

}

}