Node.js 连接 mysql 数据库

目录

一、安装驱动

二、连接数据库

1、数据库连接的配置信息

数据库连接参数说明

2、封装 mysql 的执行语句

3、后端路由文件 

三、数据库操作( CURD )

1、查询数据

2、插入数据

3、更新数据

4、删除数据

4、获取受影响的行数

5、获取更改的行数

6、多语句查询

7、事务

四、Exception Safety 类型重铸

五、具体操作演示

入口文件

后端路由文件

数据库配置文件 

封装 mysql 查询函数

启动项目

六、查看数据库

七、调用接口

参考文档

---

一、安装驱动

npm install mysql

npm install --save mysql2

mysql的兼容升级npm包mysql2，mysql2兼容mysql的api语法。

使用起来没有太大的差别，但是mysql2在性能上面提升很大。

 

二、连接数据库

1、数据库连接的配置信息

// db.config.js

// 数据库连接配置
module.exports = {
	mysql: {
		host: 'localhost', // 主机地址 （默认：localhost）
		user: 'root', // 数据库用户名
		password: 'root', // 数据库密码
		database: 'schoolmarket', // 数据库名
		port: '3306' // 端口号 （默认：3306）
	}
}

数据库连接参数说明

参数	描述
host	主机地址 （默认：localhost）
user	用户名
password	密码
port	端口号 （默认：3306）
database	数据库名
charset	连接字符集（默认：’UTF8_GENERAL_CI’，注意字符集的字母都要大写）
localAddress	此IP用于TCP连接（可选）
socketPath	连接到unix域路径，当使用 host 和 port 时会被忽略
timezone	时区（默认：’local’）
connectTimeout	连接超时（默认：不限制；单位：毫秒）
stringifyObjects	是否序列化对象
typeCast	是否将列值转化为本地JavaScript类型值 （默认：true）
queryFormat	自定义query语句格式化方法
supportBigNumbers	数据库支持bigint或decimal类型列时，需要设此option为true （默认：false）
bigNumberStrings	supportBigNumbers和bigNumberStrings启用 强制bigint或decimal列以JavaScript字符串类型返回（默认：false）
dateStrings	强制timestamp,datetime,data类型以字符串类型返回，而不是JavaScript Date类型（默认：false）
debug	开启调试（默认：false）
multipleStatements	是否许一个query中有多个MySQL语句 （默认：false）
flags	用于修改连接标志
ssl	使用ssl参数（与crypto.createCredenitals参数格式一至）或一个包含ssl配置文件名称的字符串，目前只捆绑Amazon RDS的配置文件

 

2、封装 mysql 的执行语句

// db.js

// 使用mysql2
const mysql = require('mysql2');
// 引入mysql配置文件
const dbConfig = require('./db.config');

module.exports = {
    query: function(sql, params, callback) {

        // 每次使用的时候需要创建链接，数据操作完成之后要关闭连接
        const connection = mysql.createConnection(dbConfig)

        connection.connect(function(err) {
            if (err) {
                throw err
            }

            // 执行数据操作
            connection.query(sql, params, function(err, results, fields) {

                if (err) {
                    throw err
                }

                // 将查询出来的数据返回给回调函数
                callback &&
                    callback(
                        results ? JSON.parse(JSON.stringify(results)) : null,
                        fields ? JSON.parse(JSON.stringify(fields)) : null
                    )

                // 停止链接数据库，必须在查询语句后，不然一调用这个方法，就直接停止链接，数据操作就会失败
                connection.end(function(err) {
                    if (err) {
                        console.log('关闭数据库连接失败！')
                        throw err
                    }
                })
            })
        })
    },
}

3、后端路由文件 

// 引入数据库封装对象
var db = require('./db.js');

// 引入express包
var express = require('express');

//创建路由器对象
var router = express.Router();

// 配置路由对象
router.get('/userList', (req, res, next) => {
    // sql查询user表
    db.query('select * from list', [], function(results, fields) {
        // 以json的形式返回
        res.json({ results })
    })
})

 

三、数据库操作( CURD )

1、查询数据

connection.query('SELECT * FROM `books` WHERE `author` = "David"', function (error, results, fields) {
       if(error){
          console.log('[SELECT ERROR] - ',error.message);
          return;
       }
 
       console.log('--------------------------SELECT----------------------------');
       console.log(results);
       console.log('------------------------------------------------------------nn');  
});

connection.query('SELECT * FROM `books` WHERE `author` = ?', ['David'], function (error, results, fields) {
       if(error){
          console.log('[SELECT ERROR] - ',error.message);
          return;
       }
 
       console.log('--------------------------SELECT----------------------------');
       console.log(results);
       console.log('------------------------------------------------------------nn');  
});

2、插入数据

var  addSql = 'INSERT INTO websites(Id, name, url, alexa, country) VALUES(0,?,?,?,?)';
var  addSqlParams = ['菜鸟', 'https://xxx.com','23453', 'CN'];

// 执行插入数据
connection.query(addSql,addSqlParams,function (err, result) {
        if(err){
         console.log('[INSERT ERROR] - ',err.message);
         return;
        }        
 
       console.log('--------------------------INSERT----------------------------');
       //console.log('INSERT ID:',result.insertId);        
       console.log('INSERT ID:',result);        
       console.log('-----------------------------------------------------------------nn');  
});

3、更新数据

var modSql = 'UPDATE websites SET name = ?,url = ? WHERE Id = ?';
var modSqlParams = ['菜鸟', 'https://xxx.com', 6];

// 更新数据
connection.query(modSql,modSqlParams,function (err, result) {
  if(err){
      console.log('[UPDATE ERROR] - ',err.message);
      return;
  }        
  console.log('--------------------------UPDATE----------------------------');
  console.log('UPDATE affectedRows', result.affectedRows);
  console.log('-----------------------------------------------------------------nn');
});

4、删除数据

var delSql = 'DELETE FROM websites where id=6';

// 删除数据
connection.query(delSql, function (err, result) {
        if(err){
          console.log('[DELETE ERROR] - ',err.message);
          return;
        }        
 
       console.log('--------------------------DELETE----------------------------');
       console.log('DELETE affectedRows', result.affectedRows);
       console.log('-----------------------------------------------------------------nn');  
});

4、获取受影响的行数

// 从插入、更新或删除语句中获取受影响的行数
connection.query('DELETE FROM posts WHERE title = "wrong"', function (error, results, fields) {
  if (error) throw error;
  console.log('deleted ' + results.affectedRows + ' rows');
})

5、获取更改的行数

// 从更新语句中获取更改的行数
// changedRows与affectedRows的不同之处在于，值未更改的更新行不被列入changedRows
connection.query('UPDATE posts SET ...', function (error, results, fields) {
  if (error) throw error;
  console.log('changed ' + results.changedRows + ' rows');
})

6、多语句查询

// 多语句查询会被SQL注入，如果确定想使用可以开启
var connection = mysql.createConnection({
    multipleStatements: true
});

7、事务

// 开启一个简单的事务
connection.beginTransaction(function(err) {
  if (err) { throw err; }
  connection.query('INSERT INTO posts SET title=?', title, function (error, results, fields) {
    if (error) {
      return connection.rollback(function() {
        throw error;
      });
    }
 
    var log = 'Post ' + results.insertId + ' added';
 
    connection.query('INSERT INTO log SET data=?', log, function (error, results, fields) {
      if (error) {
        return connection.rollback(function() {
          throw error;
        });
      }
      connection.commit(function(err) {
        if (err) {
          return connection.rollback(function() {
            throw err;
          });
        }
        console.log('success!');
      });
    });
  });
});

 

四、Exception Safety 类型重铸

默认情况下，驱动程序会将 mysql 类型转换为原生 JavaScript 类型。

mysql	javascript
TINYINT	Number
SMALLINT
INT
MEDIUMINT
YEAR
FLOAT
DOUBLE
TIMESTAMP	Date
DATE
DATETIME
TINYBLOB	Buffer
MEDIUMBLOB
LONGBLOB
BLOB
BINARY
VARBINARY
BIT (last byte will be filled with 0 bits as necessary)
char	String
varchar
tinytext
mediumtext
longtext
text
enum
set
decimal
bigint
time
geometry

五、具体操作演示

使用 express-generator 来快速搭建一个项目
 

入口文件

// index.js

const userApi = require('./api/myApi');
const fs = require('fs');
const path = require('path');
// body-parser - node.js 中间件，用于处理 JSON, Raw, Text 和 URL 编码的数据
const bodyParser = require('body-parser');

// 引入express包
const express = require('express');
// 创建web服务器
const app = express();

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended: false}));

// 后端api路由
app.use('/api/user', userApi);

// 监听端口
app.listen(3000);
console.log('success listen at port: 3000......');

后端路由文件

// api.js

// 引入封装 mysql 查询函数
var db = require('./db');
// 引入express包
var express = require('express');

// 创建路由器对象
var router = express.Router();


// Multer 是一个 node.js 中间件，用于处理 multipart/form-data 类型的表单数据，它主要用于上传文件
var multer = require('multer');

// 格式化时间模块Silly-datetime
var datetime = require('silly-datetime');

var fs = require('fs');
var path = require('path')
var UUID = require('uuid')

// multer 自定义存储的方式
var storage = multer.diskStorage({  
    // 保存路径
    destination: function (req, file, cb) {
        // 注意这里的文件路径,不是相对路径，直接填写从项目根路径开始写就行了
        cb(null, 'static/public/uploads')  
    },  
    // 保存在 destination 中的文件名
    filename: function (req, file, cb) {  
        var str = file.originalname.split('.');  
        cb(null, UUID.v1() + '.' + str[1]);  
    }  
})

var upload = multer({storage: storage})


// 设置返回response
var jsonWrite = function (res, ret) {
    if (typeof ret === 'undefined') {
        res.json({
            code: '1',
            msg: '操作失败'
        });
    } else {
        console.log('ret', ret)
        res.json(ret);
    }
};

// 增加用户接口
router.post('/addUser', (req, res) => {
  let params = req.body;
  db.query("select * from user where user_id=?", [params.id], function (err, result) {
    if (err) {
      console.log(err);
    }

    if (result) {
      if (result.length > 0) {
        jsonWrite(res, {
          code: -1,
          msg: '该账号已注册！'
        });
      } else {
        db.query("INSERT INTO user(user_id,user_nick,gender,password) VALUES(?,?,?,?)", [params.id, params.nick, params.gender, params.password], function (err, result) {
          if (err) {
            console.log(err);
          }

          if (result) {
            jsonWrite(res, {
              code: 200,
              msg: '注册用户成功！'
            });
          }
        })
      }
    }
  })

});

数据库配置文件 

// db.config.js

// 数据库连接配置
module.exports = {
	mysql: {
		host: 'localhost', // 主机地址 （默认：localhost）
		user: 'root', // 数据库用户名
		password: 'root', // 数据库密码
		database: 'schoolmarket', // 数据库名
		port: '3306' // 端口号 （默认：3306）
	}
}

封装 mysql 查询函数

// db.js

// 使用mysql2
const mysql = require('mysql2');
// 引入mysql配置文件
const dbConfig = require('./db.config');

module.exports = {
    query: function(sql, params, callback) {

        // 每次使用的时候需要创建链接，数据操作完成之后要关闭连接
        const connection = mysql.createConnection(dbConfig)

        connection.connect(function(err) {
            if (err) {
                throw err
            }

            // 执行数据操作
            connection.query(sql, params, function(err, results, fields) {

                if (err) {
                    throw err
                }

                // 将查询出来的数据返回给回调函数
                callback &&
                    callback(
                        results ? JSON.parse(JSON.stringify(results)) : null,
                        fields ? JSON.parse(JSON.stringify(fields)) : null
                    )

                // 停止链接数据库，必须在查询语句后，不然一调用这个方法，就直接停止链接，数据操作就会失败
                connection.end(function(err) {
                    if (err) {
                        console.log('关闭数据库连接失败！')
                        throw err
                    }
                })
            })
        })
    },
}

 

启动项目

server是入口文件 index.js 的文件夹的名字

node server

六、查看数据库

可以使用 navicat premium 等数据库可视化工具进行查看操作数据库。

七、调用接口

可以使用浏览器或 postman 来调用接口查看返回的情况。

参考文档

Node.js 连接 MySQL | 菜鸟教程

mysql – npm

mysql2 – npm