nodejs 山羊上山系列（3）- nodejs操作mySql数据库

发表于 | 分类于 ， | | 热度: ℃ |
字数统计: 1.5k | 阅读时长 ≈ 6

在上一章节处理http请求我们已经完成了基本的http参数处理，并且抽离了模块，现在我们要使用nodejs链接mysql数据库，完成api接口中的业务处理…

本篇幅主要的技术点

[❌] 使用node原生http模块搭建http-server
[❌] 使用nodemon处理热加载
[❌] 使用cross-env处理不同操作系统环境变量(通过nodejs的process全局变量的process.env.NODE_ENV就可以获取)
[❌] 使用querystring处理get请求的参数
[❌] 使用promise的方式获取post请求body参数的获取
[❌] 使用es6 Class的方式定义响应数据的基础格式，然后对成功响应和失败响应extends基础格式
[✔] mySql常用操作汇总
[✔] nodejs链接本地mysql数据库响应客户端请求
[❌] 登陆校验/登陆信息存储
[❌] nodejs链接redis数据库
[❌] nginx的配置反向代理
[❌] 日志管理
[❌] 信息安全（sql注入/xss攻击/md5信息加密）

数据库基本操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
show databases; // 查看所有的数据库
use databaseName; // 使用某一个具体的数据库
show tables; // 查看数据库中所有的表
select version(); // 查看数据库的版本
不等于使用两个尖括号<> //  update users set state="1" where id<>4
-- show databases; // mysql中使用 --单行备注
/*这是多行备注*/
`关键字需要括起来`
// 插入
insert into TableName (ColumnsA,columnB) values (ColumnAValue,ColumnBValue)
// 全部列查询（有性能问题，不建议使用*）
select * from TableName ;
// 固定列查询
select ColumnA,columnB from TableName;
// 条件查询
select ColumnA from TableName where Column='VALUE'; // select id from users where username='123'
// 多条件交集查询
select ColumnA from TableName where ColumnB='valueB' and ColumnC="valueC"; // select id from users where username='123' and password='321'
// 多条件并集查询
select ColumnA from TableName where ColumnB='valueB' or ColumnC="valueC"; // select id from users where username='123' or password='321'
// 模糊查询
select ColumnA from TableName where ColumnB like '%value%' //  select * from users where username like '%zhang%'
// 排序
select * from TableName order by Column desc // 降序 select * from users where password like '%23%' order by id desc;
select * from TableName order by Column asc // 升序 select * from users where password like '%23%' order by id asc;
// 更新
update TableName set ColumnName='columnValue'
// 多列更新(逗号分割)
update TableName set ColumnNameA='ColumnNameAValue',ColumnNameB="ColumnNameBValue"
// 删除
delete from TableName where ColumnNameA='ColumnNameAValue' // delete from users where username='zhangsi'

mysql忘记root密码的解决办法

  • 1）首先在window系统下的话C:\ProgramData\MySQL\MySQL Server 5.7找打my.ini文件，增加 skip-grant-tables 选项前面曾经介绍过，意思是启动 MySQL 服务的时候跳过权限表认证。 启动后，连接到 MySQL 的 root 将不需要口令。
1
2
// 文件最后增加一句
    skip-grant-tables

  • 2)在mysql安装目录的bin目录下执行 mysql -u root -p，然后不输入密码直接回车进入mysql.

  • 3)用show databases; 查看数据库,在开头说过mysql的密码其实是保存在名为“mysql”的数据库中，所以切换到mysql数据库。

1
use mysql // 切换到mysql数据库
  • 4)修改密码:这里要根据版本来执行不同的SQL语句了，因为版本不同，存储密码的字段可能不相同。5.7以前的版本可以用以下语句更新root密码：
1
update user set password = password('123456') where user='root';
  • 5)如果顺利的话，root的密码应该已经被修改为“123456”，那可以不必往下看了。然而我的mysql版本是5.7.21，所以第一次尝试的时候这里又报错了：
1
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
  • 6）表中没有password字段，可以用desc user;指令看一下果然没有。查资料发得知5.7版本密码保存字段已经改成上图中的“authentication_string”了，于是重新输入update指令重置密码：
1
update user set authentication_string = password('123456') where user='root';

修改成功！！！！！！！

  • 7) 重启mysql
    在mysql安装目录的bin目录下执行cmd命令
    1
    2
    net stop MySQL57; // 关闭mysql服务
    net start MySQL57; // 启动mysql服务

使用nodejs连接mysql数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
const mysql = require('mysql') // 引入mysql
// 定义mysql的配置

let MYSQL_CONFIG = {};

// 读取nodejs本地运行环境
const env = process.env.NODE_ENV;

// 上篇文章中使用cross-env定义了node的运行环境变量
"mock":"cross-env NODE_ENV=dev nodemon ./bin/app.js"

// 如果是本地环境加载本地mysql的配置
if(env=='dev'){
    MYSQL_CONFIG ={
        host:'localhost',
        port:'3306',
        user:'root',
        password:'mysql_2019'
    }
}else{
    MYSQL_CONFIG ={
        host:'线上地址',
        port:'3306',
        user:'root',
        password:'mysql_2019'
    }
}
const db = mysql.createConnection(MYSQL_CONFIG);
// 开始建立连接
db.connect(error=>{
    if(error){
        console.log(error)
        return
    }
});

// 数据库查询时异步的，暂时用promise封装一下，koa原生就支持asyn/await函数处理异步，封装处理sql的函数
const queryDataBase = (sql)=>{
    return new Promise((resolve,reject))=>{
        db.query(sql,(error,result)=>{
            if(error){
                reject(error)
                return
            }
            resolve(result)
        })
    })
}

module.exports = {
    queryDataBase,
    escape:mysql.escape // escape方法对特殊字符进行转码
}
  • 根据业务来封装自己的api,通过sql查询数据库实现业务的增删改查操作

api

防止sql注入

  • 因为在mysql中使用--就是备注，所以当我们在拼接sql的时候，如果对用户输入的参数不做处理，很容易引入sql注入的安全问题。例如:
1
2
3
4
5
6
7
8
9
10
const sql = `select * from users where username='${username}' and password = '${password}'`

// 如果用户正常输入的username='zhangsan' and password = '123'就可以成功登陆
// 加入只知道用户名不知道密码，但是用户名username输入为
username = "zhangsan '--"

sql = "select * from users where username='zhangsan' -- and password = '${password}'"
其实等价于
sql = "select * from users where username='zhangsan'
后面内容就被备注掉了，所以输入任何密码都能登陆

解决sql注入办法：引入escape方法对特殊字符进行转码

1
2
3
4
5
6
7
  //api
const {escape,queryData} = require('../db/mysql.js')
 const login =(req)=>{
     const {username,password} = req.body
     const sql = `select * from users where username = ${escape(username)} and password = ${escape(password)}`
     return queryData(sql)
 }

总结

通过nodejs链接了数据库，首先可以根据配置的环境变量，对本地开发环境和线上环境做不同配置;再就是封装了mysql的查询和escape方法，分别根据sql操作数据库数据和防止sql注入…

初到贵宝地，有钱的给个钱场,没钱的挤一挤给个钱场