MongoDB 基础

MongoDB教程 https://www.yiibai.com/mongodb

Node.js 操作 MongoDB

var mongodb = require('mongodb');
var MongoClient = mongodb.MongoClient;
var db;

MongoClient.connect("mongodb://localhost:27017/test1705candel", function(err, database) {
  if(err) throw err;

  db = database;
});

module.exports = {
	insert: function(_collection, _data, _callback){
		var i = db.collection(_collection).insert(_data).then(function(result){
			_callback(result);
		});
	},
	select: function(_collection, _condition, _callback){
		var i = db.collection(_collection).find(_condition || {}).toArray(function(error, dataset){
			_callback({status: true, data: dataset});
		})
	}
}

MySql 基础

node-mysql

npm install mysql

新建sql.js

var mysql = require('mysql');
var connection = mysql.createConnection({
	host: 'localhost',
	user: 'wscats',
	password: '123456789',
	database: 'asm'
});
connection.connect();//连接数据库
connection.query('select * from news', function(err, rows, fields) {
	if(err) throw err;
	console.log('The news is: ', rows[0]);
});//执行SQL语句
connection.end();//关闭连接。

连接基本参数

参数	用法
host	主机名，localhost代表本地
user Mysql用户
password	密码
database	连接的数据库

执行文件

node sql

注意如果我们要在每一次查询数据库后connection.end()关闭一次连接，那我们需要mysql.createConnection()创建一个新的connection，也就是每一次的开关都是用唯一一个connection来实现

var connection;
function createConnection() {
	connection = mysql.createConnection({
		host: 'localhost',
		user: 'laoxie',
		password: '12345678',
		database: 'asm'
	});
}

增删查改分页

注意sql语句不要写错语法

//增加记录
connection.query('insert into news (title ,text) values ("wscats" , "eno")');

//删除记录
connection.query('delete from news where title = "wscats"');

// 修改记录
connection.query('update news set text = "eno" where title = "wscats"');

//查找记录
connection.query('select * from news', function(err, rows, fields) {
	if(err) throw err;
	console.log('The news is: ', rows[0]);
});

//查询记录
var arr = [];
connection.query("select * from news", function selectTable(err, rows, fields) {
	if(err) {
		throw err;
	}
	if(rows) {
		for(var i = 0; i < rows.length; i++) {
			console.log("第" + i + "条", "id: " + rows[i].id, "title: " + rows[i].title, "text: " + rows[i].text);
			//把数据组装成数组对象
			var obj = {};
			obj.id = rows[i].id;
			obj.title = rows[i].title;
			obj.text = rows[i].text;
			arr.push(obj);
		}
	}
	console.log(arr);
});

//查询记录
connection.query('select * from news where id = 2', function(err, rows, fields) {
	if(err) throw err;
	console.log('The news is: ', rows[0]);
});

//分页
//取前5条数据
select * from table limit 0,5
//or
select * from table limit 5
//取第11条到第15条数据，共5条
select * from table limit 10,5
//格式
select * from table limit offset,rows
offset指定要返回的第一行的偏移量，rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)

封装成模块

最后我们可以把它封装成一个模块导出，在其他主模块中调用注意我在每个原型链的函数结尾处都会调用一个connection.end()方法，这个方法connection.connect()对应，一个开始，一个结束

配合await和async的封装

let mysql = require('mysql');
let config = require('./config.json');
let pool = mysql.createPool({
    connectionLimit: 10,
    host: config.host,
    user: config.user,
    port: config.port,
    password: config.password,
    database: config.database
});
let connect = () => {
    return new Promise((resolve, reject) => {
        pool.getConnection((err, connection) => {
            !err ? resolve(connection) : reject(err)
        });
    })
}

let find = (table, params) => {
    return new Promise(async (resolve, reject) => {
        let connection = await connect();
        connection.query(`SELECT * FROM ${table} ${params?'where ?':''}`, [{
            ...params
        }], (err, results, fields) => {
            !err ? resolve(results) : reject(err)
            connection.release();
        });
    })
}

let insert = (table, params) => {
    return new Promise(async (resolve, reject) => {
        let connection = await connect();
        connection.query(`INSERT INTO ${table} SET ?`, [{
            ...params
        }], (err, results, fields) => {
            !err ? resolve(results) : reject(err)
            connection.release();
        })
    })
}

let del = (table, params) => {
    return new Promise(async (resolve, reject) => {
        let connection = await connect();
        connection.query(`DELETE FROM ${table} WHERE ?`, [{
            ...params
        }], (err, results, fields) => {
            !err ? resolve(results) : reject(err)
            connection.release();
        })
    })
}

let update = (table, params1, params2) => {
    return new Promise(async (resolve, reject) => {
        let connection = await connect();
        connection.query(`UPDATE ${table} SET ? WHERE ?`, [{
            ...params1
        }, {
            ...params2
        }], (err, results, fields) => {
            !err ? resolve(results) : reject(err)
            connection.release();
        })
    })
}
module.exports = {
    connect,
    find,
    insert,
    del,
    update
}

断线重连

因为mysql连接时间长的话会自动断掉，可以封装一个断线重连的接口

const mysql = require("mysql");
function createConnection() {
	let connection = mysql.createConnection({
		// 域名
		host: 'localhost',
		// 用户名
		user: 'wscats',
		// 密码
		password: '12345678',
		// 数据库
		database: 'corrine'
	});
	//连接错误，2秒重试
	connection.connect((err) => {
		if(err) {
			console.log('error when connecting to db:', err);
			setTimeout(createConnection, 2000);
		}
	});
	connection.on('error', function(err) {
		console.log('db error', err);
		// 如果是连接断开，自动重新连接
		if(err.code === 'PROTOCOL_CONNECTION_LOST') {
			createConnection();
		} else {
			throw err;
		}
	});
	return connection
}
module.exports = createConnection();

自动断线

建议用下面这一段来实现mysql的自动连接和自动断开，那就不会出现too many connections的错误提醒了

var query = function(sql, params, callback) {
	var connection = mysql.createConnection({
		// 域名
		host: 'localhost',
		// 用户名
		user: 'wscats',
		// 密码
		password: '12345678',
		// 数据库
		database: 'corrine'
	});
	//连接错误，2秒重试  
	connection.connect(function(err) {
		if(err) {
			console.log("error when connecting to db:", err);
			setTimeout(query, 2000);
		} else {
			var q = connection.query(sql, params, function(error, results, fields) {
				//关闭连接  
				connection.end();
				//事件驱动回调  
				callback(error, results, fields);
			});
			console.log("sql:::" + q.sql);
		}
	});
	connection.on("error", function(err) {
		console.log("db error", err);
		// 如果是连接断开，自动重新连接  
		if(err.code === "PROTOCOL_CONNECTION_LOST") {
			query();
		} else {
			throw err;
		}
	});
}

Node.js 操作 MySql

var mysql = require('mysql');

//创建连接池
var pool  = mysql.createPool({
  host     : 'localhost',
  user     : 'root',
  password : 'root',
  port: 3306,
  database: '1000phone',
  multipleStatements: true
});


module.exports = {
	select: function(tsql, callback){
		pool.query(tsql, function(error, rows){
      if(rows.length > 1){
        callback({rowsCount: rows[1][0]['rowsCount'], data: rows[0]});
      } else {
        callback(rows);
      }
		})
	}
}