NodeJS 操作数据库

Question

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);
      }
        })
    }
}