NodeJS操作MySQL数据库

NodeJS操作MySQL

NodeJS是一个后台语言，在创建NodeJS项目的时候也需要进行初始化，就像我们在创建网页项目的时候，要新建img,css以及js文件夹，同时导入兼容或初始化CSS的文件normalize.css,有时候还需要在网页里面设置meta标签，这些都是项目的初始化

NodeJS项目初始化

NodeJS的项目需要初始化，我们在一个空的项目里面去使用发下命令初始化

$ npm init 

当在控制台执行上面的命令以后，我们就会在控制台输入一系列的信息，如项目名称，项目版本，项目描述信息，项目关键字，项目版权等信息

当相关的信息输入完成以后，这个时候项目下来会默认帮我们生成一个package.json文件，这就是项目的初始化配置信息文件，这个文件记录了当前项目的所有信息

package.json文件信息如下

{
  "name": "mysqldemo01",
  "version": "1.0.0",
  "description": "杨标连接mysql测试",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "mysql",
    "nodejs",
    "demo"
  ],
  "author": "杨标",
  "license": "ISC"
}

• name代表项目名称
• version代表项目的版本号
• description代表项目的描述信息
• main代表项目从哪个文件开始执行
• scripts项目的脚本配置信息，非常重要，配置了这个以后，项目可以快速启动（相当于告别第三方人员这个项目怎么启动，或者有哪些命令可以执行）
• keywords关键字
• author作者
• license版信息

如果在平时的开发当中，希望快速的生成上面的信息，我们可以直接在刚刚的命令后面添加一个参数

$ npm init --yes

添加这个参数以后代表不询问用户，直接生成

npm管理NodeJS依赖

在NodeJS的项目里面，我们通过npm来管理它的包模块，可以通过npm install来从服务器下载第三方的模块

$ npm install mysql --save

当我们在后面使用了--save的参数以后，这一次的安装记录就会添加到项目配置文件package.json 里面保存起来

{
  "name": "mysqldemo1",
  "version": "1.0.0",
  "description": "杨标使用NodeJS连接MySQL数据库",
  "main": "app.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "mysql": "^2.17.1"
  }
}

上面的dependencies就代表了这个项目的依赖信息，也就是通过npm安装的包都会在这里

NodeJS连接MySQL

NodeJS如果要连接MySQL需要借助于第三方的依赖包，这个包我们已经通过npm下载好了，现在我们可以直接使用了

导入模块包

NodeJS的模块化使用的是CommonJS，它使用关键字require导入 ，使用moudle.exports导出

let mysql = require("mysql");

通过上面的代码，我们就可以导入mysql的包，得到一个对象

但是按照约定原则，导入的资源是不可更改的，所以我们使用关键字const来定义接收的对象

const mysql=require("mysql");

创建数据库连接

const mysql = require("mysql");

//创建数据库连接
let conn = mysql.createConnection({
    host:"127.0.0.1",
    port:3306,
    user:"root",
    password:"aaabbb",
    database:"test",
    multipleStatements:true
});
//连接mysql数据库 
conn.connect();

• host代表服务器地址
• port代表端口号（Number类型）
• user代表用户名
• password代表密码
• database代表数据库
• multipleStatements开启多条SQL语句执行

当连接完成数据库以后，我们就可以去执行我们的第一条SQL语句了

//第一步：先准备好SQL语句
let strSql="select * from stuinfo";
//第二步：执行SQL语句
conn.query(strSql,(err,result)=>{
    console.log("SQL语句执行完毕");
    //当SQL语句执行失败的时候，会有一个err信息，当SQL语句执行成功以，执行的结果在result里面
    if(err){
        //说明SQL语句执行失败了
        console.log(err);
    }
    else{
        console.log(result);
    }
    
    //关闭数据库连接，资源
    conn.end();
});

当使用conn.query()执行SQL语句的时候，它会有一个回调函数，我们使用箭头函数回调，回调函数里面有两个参数，第一个参数代表执行错误的信息err，第二个参数代表执行成功的结果result

查询结果分析

当执行完查询语句以后，会返回一个对象result ,这是一个数组，里面的每一条记录都是一个对象，格式如下

[ RowDataPacket { id: 1, sid: 'H19040001', sname: '刘幸', ssex: '女', sage: 18 },
RowDataPacket { id: 2, sid: 'H19040002', sname: '杨标', ssex: '男', sage: 30 } ]

这个结果把数据的每一页都当成一个属性名，这一列的值当成了属性值。所以当我们需要对里在的结果进行处理的时候，只需要按照常规的对象操作就可以了

如果执行是统计计数等这种情况，可以通过如下方式

function query2(){
    let strSql="select count(*) 'totalCount' from stuinfo";
    conn.query(strSql,(err,result)=>{
        if(err){
            console.log(err);
        }
        else{
            console.log(result);
            console.log(result[0].totalCount);
        }
        conn.end();
    });
}

结果如下

[ RowDataPacket { totalCount: 2 } ]

多条SQL语句执行

MySQL数据库本身是支持多条SQL语句执行的，我们在NodeJS里面如果需要执行多条SQL语句，必须在配置项里面添加一个新的配置multipleStatements:true

const mysql = require("mysql");
//创建数据库连接
let conn = mysql.createConnection({
   host:"127.0.0.1",
   port:3306,
   user:"root",
   password:"aaabbb",
   database:"test",
   multipleStatements:true    //关键在这里，开启多条SQL语句执行
});
//连接mysql数据库 
conn.connect();

当开启多条SQL语句执行以后，我们就可以执行下面的代码了

let strSql=`
        select * from classinfo;
        select * from stuinfo;
    `;
conn.query(strSql,(err,result)=>{
    if(err){
        console.log(err);
    }
    else{
        console.log(result);
    }
    conn.end();
});

在定义strSql的时候，我们使用的模板字符串，这里面有两条SQL语句，每条SQL语句使用分号;结束，我们去执行就不会有问题了

执行结果如下

[ 
  [ 
    RowDataPacket { id: 1, cid: 'H1904', cname: '前端19年04班' },
    RowDataPacket { id: 2, cid: 'J1908', cname: 'Java19年08班' },
    RowDataPacket { id: 3, cid: 'J1919', cname: 'Java19年09班' }
  ],
  [ 
    RowDataPacket { id: 1, sid: 'H19040001', sname: '刘幸', ssex: '女', sage: 18 },
    RowDataPacket { id: 2, sid: 'H19040002', sname: '杨标', ssex: '男', sage: 30 } 
  ] 
]

它的结果是一个二维数组，在这个二维数组里面，每一项都是一个结果，第一项代表第一个SQL语句执行的结果，第二项代表第二条SQL语句执行的结果，依冷类推

//第一个结果
result[0];   
result[0][2].cid;
//第二个结果
result[1];
result[1][0].sname;

带参数SQL语句执行

在刚刚的代码里面，我们所有的SQL语句都是没有参数的，如果这个SQL语句有条件了，并且条件不固定 ，这个时候怎么处理呢？

它的处理方式与JAVA非常相似，都是在SQL语句里面用?去替代参数，然后在执行的过程当中，使用具体的值去替代?

function query4(sid){
    let strSql="select * from stuinfo where sid=?";
    conn.query(strSql,[sid],(err,result)=>{
        if(err){
            console.log(err);
        }
        else{
            console.log(result);
        }
        conn.end();
    });
}
query4("H19040001");

1. SQL语句里面的参数使用了?

2. 在conn.query()方法里面，参数由原来的2个变成3个，第二个参数是一个数组，里面装是SQL语句里面要替换成?的具体值

新增修改与删除的SQL语句执行

查询语句的SQL与其它类型的SQL是不一样的，查询语句的SQL返回的是一个数组，因为查询有结果，而在执行新增，修改与删除SQL语句的时候，它返回的都是一个受影响的行数，所以，这个时候，我们得到的result对象是不一样的

新增操作

function insert(sid,sname,ssex,sage){
    let strSql="insert into stuinfo (sid,sname,ssex,sage) values (?,?,?,?)";
    conn.query(strSql,[sid,sname,ssex,sage],(err,result)=>{
        if(err){
            console.log(err);
        }
        else{
            console.log(result);
        }
        conn.end();
    });
}
insert("H19040006","张三","男",23);

得到的result结果如下:

OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 13,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

• affectedRows代表受影响的行数
• insertId代表的意思则是如果当前表里有自增长键，则返回的是这个自增长的id

---

修改操作

修改的操作与新增的操作是一样的，返回一个对象

function update(sname,id){
    let strSql="update stuinfo set sname=? where id=?";
    conn.query(strSql,[sname,id],(err,result)=>{
        if(err){
            console.log(err);
        }
        else{
            console.log(result);
        }
        conn.end();
    });
}

update("小乖乖",13);

结果如下

OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 1  Changed: 1  Warnings: 0',
  protocol41: true,
  changedRows: 1 }

第二种结果

OkPacket {
 fieldCount: 0,
 affectedRows: 1,
 insertId: 0,
 serverStatus: 2,
 warningCount: 0,
 message: '(Rows matched: 1  Changed: 0  Warnings: 0',
 protocol41: true,
 changedRows: 0 }

• affectedRows代表受影响的行
• changedRows代表改变的行数，当改变前和改变后是相同的，则结果为0

---

删除操作

删除操作与上面两种操作方式一样

function deleteData(sex){
    let strSql="delete from stuinfo where ssex=?";
    conn.query(strSql,[sex],(err,result)=>{
        if(err){
            console.log(err);
        }
        else{
            console.log(result);
        }
        conn.end();
    });
}

deleteData("女");

返回的结果

OkPacket {
  fieldCount: 0,
  affectedRows: 2,
  insertId: 0,
  serverStatus: 34,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

• affectedRows代表受影响的行数

在数据库的操作过程当中，我们把查询语句分为一类，把修改，新增与删除分为一类

---

如果这两种类型的SQL语句去混合执行的时候，我们要看一下它返回的结果

let strSql=`
update stuinfo set sage=sage+1 where sage<30;
select * from stuinfo;
`;
conn.query(strSql,(err,result)=>{
    if(err){
        console.log(err);
    }
    else{
        console.log(result);
    }
    conn.end();
})

返回的结果如下

[ OkPacket {
    fieldCount: 0,
    affectedRows: 2,
    insertId: 0,
    serverStatus: 42,
    warningCount: 0,
    message: '(Rows matched: 2  Changed: 2  Warnings: 0',
    protocol41: true,
    changedRows: 2 },
  [ RowDataPacket { id: 2, sid: 'H19040002', sname: '杨标', ssex: '男', sage: 31 },
    RowDataPacket { id: 3, sid: 'H19040003', sname: '向继秘', ssex: '男', sage: 24 },
    RowDataPacket { id: 13, sid: 'H19040006', sname: '小乖乖', ssex: '男', sage: 24 } ] ]

在返回的result 结果里面，它仍然是一个二维数组，数组每次一项是第一条更新SQL语句的结果，第二项则是查询SQL语句的结果

软删除（逻辑删除）

在正常的开发和设计工作里面，可能会涉及到删除的操作，但是删除是一个非常危险的操作，同时当关系型数据库里面表与表之间构建成主外键约束以后就不能够再删除对象，所以这个时候，我们需要采用另一种方式来删除，这种方式通过逻辑删除（也叫软删除）

现在我们将数据库的字段重新定义

在这一个 isDel 上面给它一个默认值false,通过isDel这一列我们来判断这一条数据是否是处理删除状态

在后在工作当中设计数据库的时候，都要保证有这一列，并且要设置默认值为false

一旦使用了软删除以后，在接下来的操作过程当中，无论是进行什么操作，最后都要加一个限制条件isDel=false,如下所示

select * from stuinfo where isDel=false ;

这个时候通过查询得到的就是没有被删除的状态

如果需要删除某一条数据 ，则已经不能够使用delete语句了,转而使用update关键字来进行，而需要进行如下操作

update stuinfo set isDel=true where id=1;

使用软删除的好处

1. 防止数据删除失误 ，如果软删除可以恢复数据，只要把isDel设置成false
2. 通过这种方式，我们可以在这里去操作主外键关系表

使用软删除的缺点

1. 数据冗余量大，删除以后的数据还保存在数据库里面
2. 操作会变得麻烦，每次查询的时候都添加一个限定条件isDel=false

MySQL操作的封装

在项目的开发过各当中，我们经常会使用各种各样的数据库操作，这个时候，我们可以把数据库的连接，以及查询的方法封装成一个对象，在开发过程当中直接调用，现将代码整理如下

目录结构

第一步：在config文件夹下面创建 dbConfig.js文件，这个文件记录了数据库连接的配置信息

/**
 * @description  数据库配置信息
 * @exports dbConfig 数据库配置对象
 */

const dbConfig = {
    host: "127.0.0.1",
    port: 3306,
    user: "root",
    password: "aaabbb",
    database: "test",
    multipleStatements: true
}

module.exports=dbConfig;

第二步：在utils 的文件夹下面创建DBUtil.js文件，用于封装数据库的常用操作

const dbConfig=require("../config/dbConfig.js");
const mysql=require("mysql");

/**
 * @name DBUtil.js 
 * @description 数据库操作核心
 * @requires [dbConfig,mysql] 数据库配置信息，mysql操作包
 */


/**
 * @class DBUtil
 * @description 数据库操作核心对象
 * @version 1.0
 * @author 杨标
 */
class DBUtil{
    /**
     * @name getConn 获取数据库连接
     * @returns {object} 返回数据库连接
     */
    static getConn(){
        let conn=mysql.createConnection(dbConfig);
        conn.connect();
        return conn;
    }
    /**
     * @name executeSql 执行某一条SQL语句
     * @param {String} strSql 要执行的SQL语句
     * @param {Array} params  执行这条SQL语句的参数
     * @returns {Promise} 返回一个promise的承诺
     */
    static executeSql(strSql,params=[]){
        // 这resolve代表成功的方法 reject代表失败的方法
        // Promise的写法是固定写法，这是其中的一种写法
        let promise=new Promise((resolve,reject)=>{
            //第一步：先获取数据库的连接
            let conn=DBUtil.getConn();
            //第二步：执行SQL语句
            conn.query(strSql,params,(err,result)=>{
                if(err){
                    //代表执行失败  指catch
                    reject(err); 
                }
                else{
                    //代表成功  成功以后继续操作 指then
                    resolve(result);
                }
                conn.end();
            });
        });

        return promise;  //返回这个“承诺”

    }
}
module.exports=DBUtil;

• 在封装的过程当中，我们使用了class对象进行封装，同时使用了静态方法
• 在方法executeSql里面，我们使用了Promise 对象进行处理，该对外主要用于对JS里面的异步操作进行封装，这个对象内部有3个状态，常用的为resolve成功状态和reject失败状态，还有一个等待状态(pending)。它是一个构造函数，需要new进行调用，同时在创建的过程当中，它接收一个回调方法，回调方法里有两个参数resolve与reject 分别代表成功以后的操作与失败以后的操作new Promise((resolve,reject)=>{})
• 在方法的最后，我们返回了这个创建好promise对象

promise是JS目前最好的异步解决方法，它可以将异步转换为同步操作

第三步：在app.js里调用封装的方法进行测试

const DBUtil = require("./utils/DBUtil.js");

function query1(){
    let strSql="select * from stuinfo where isDel=false";
    //当调用这个方法的时候，如果这个方法返回是promise，则有两个方法会执行
    //成功会自动执行then里面的回调方法
    //失败自动执行catch 里面的回调方法
    DBUtil.executeSql(strSql).then(result=>{
        console.log("我执行成功了");
        console.log(result);
    }).catch(err=>{
        console.log("我执行失败了");
        console.log(err);
    })
}
query1();

因为之前已经封装了executeSql这个方法，所以我们在调用这个方法的时候，它返回了一个promise 的“承诺”给我们，这个承诺如果内部执行的是resolve()成功，则自动调用then()里面的回调方法，如果承诺内部执行是reject()失败，则自动调用catch()里面的回调方法

---

上面是promise的常规用法，我们使用promise解决了刚刚的回调问题，但是仍然在外面还有两个回调，我们可以使用使用ES6里面的另一各解决方法，直接将异步转为同步

异步执行，同步是等待

//异步转同步
async function query2() {
    try {
        let strSql = "select * from stuinfo123 where isDel=false";
        let result = await DBUtil.executeSql(strSql);
        // await等待的是then里面的结果 也就是resove里面的结果
        console.log(result);
        //如果失败会向系统抛异常
    }
    catch(err){
        //这就是catch里面的东西，也相当于reject里面的结果
        console.log("失败就在这里");
        console.log(err);
    }
}

• Promise的内部是通过三种状态来切换的，最初是pending等待状态
• await可以等待Promise的结果，但它等待到的只能是成功resolve的结果
• 如果Promise的内部发生了reject 则会向系统抛异步 ，我们可以通过try...catch去捕捉到这个异步
• await必须在方法里使用，是因为一旦使用了这个await就必须配置async这个关键字一起使用，两者缺一不可

所以Promise+await/async是JS里面，异步转同步的最好方法

---