南风知我意的技术博客

一、mysql

1.mysql 介绍、安装和使用

介绍
- 关系型数据库、轻量
- 下载地址：https://dev.mysql.com/downloads/mysql
安装
- 执行安装
- 过程中需要输入 root 用户名的密码，要记住这个密码
- mysql workbench
  - 操作 mysql 的客户端，可视化操作
  - 下载地址：https://dev.mysql.com/downloads/workbench

操作数据库

建库
- 创建 myblog 数据库
- 执行 show databases; 查询

建表

user表

sql

  CREATE TABLE myblog.users (
      id int auto_increment not NULL,
      username VARCHAR(20) not NULL,
      password VARCHAR(20) not NULL,
      realname VARCHAR(10) not NULL,
      primary key(`id`)
  )
  ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

blog表

sql

  CREATE TABLE myblog.blogs (
      id INT auto_increment NOT NULL,
      title VARCHAR(50) NOT NULL,
      content LONGTEXT NOT NULL,
      createtime BIGINT(20) NOT NULL default 0,
      author VARCHAR(20) NOT NULL,
      CONSTRAINT blogs_PK PRIMARY KEY (id)
  )
  ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

表操作

增删改查

sql

select version()
use myblog;
show tables;
INSERT into users (username, password, realname) values('zhangsan', '123', '李四');
INSERT into users (username, password, realname) values('lisi', '123', '李四');
SELECT * FROM users u where u.username like '%zhang%';
UPDATE users set realname = '李四2' WHERE username = 'lisi'; 
DELETE FROM users where username = 'lisi';
INSERT into blogs (title, content, createtime, author) values('标题A', '内容A', 1649155401215, 'zhangsan');
INSERT into blogs (title, content, createtime, author) values('标题B', '内容B', 1649155401220, 'lisi');

2.nodejs 连接 mysql

安装
shell
```
npm i mysql -S
```

用法

const mysql = require('mysql');

// 创建连接对象
const con = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'root',
  port: '3306',
  database: 'myblog'
})

// 开始连接
con.connect()

// 执行 sql 语句
const sql = 'select * from users';
con.query(sql, (err, result) => {
  if (err) {
    console.log(err)
    return;
  }
  console.log(result)
})

// 关闭连接
con.end()

项目中使用

新建 src/config/db.js，存储mysql的配置信息

const env = process.env.NODE_ENV;

// 配置
let MYSQL_CONF

if (env === 'dev') {
  MYSQL_CONF = {
    host: 'localhost',
    user: 'root',
    password: 'root',
    port: '3306',
    database: 'myblog'
  }
}

if (env === 'production') {
  MYSQL_CONF = {
    host: 'localhost',
    user: 'root',
    password: 'root',
    port: '3306',
    database: 'myblog'
  }
}
module.exports = {
  MYSQL_CONF
}

新建 src/db/mysql.js，数据库操作动作封装

const mysql = require('mysql');
const { MYSQL_CONF } = require('../config/db');

// 创建连接对象
const con = mysql.createConnection(MYSQL_CONF);

// 开始连接
con.connect()

// 统一执行 sql 的函数
function exec(sql) {
  const promise = new Promise((resolve, reject) => {
    con.query(sql, (err, result) => {
      if (err) {
        reject(err)
        return;
      }
      resolve(result)
    })
  })
  return promise;
}

module.exports = {
  exec,
  escape: mysql.escape
}

实际报错处理
- node第一次连接mysql，报错Client does not support authentication protocol requested by server的问题
  原因：在项目里面通过npm install 安装的mysql和最新版本MySQL加密方式不同，导致连接失败。
  在最新下载的MySql客户端版本使用的是caching_sha2_password加密方式，所以默认创建的root用户和密码都是这个加密方式。而npm包里的mysql模块还是使用原来的mysql_native_password加密方式，两者不互通，连接会报错。
  解决方法：将mysql的用户密码从caching_sha2_password加密方式改回mysql模块能支持的 mysql_native_password加密方式 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

3.API 连接 mysql

controller 中sql 业务编写

const { exec, escape } = require('../db/mysql')

const getList = (author, keyword) => {
  author = escape(author);
  keyword = escape(keyword);
  let sql = `select * from blogs where 1=1 `
  if (author) {
    sql += `and author = ${author} `
  }
  if (keyword) {
    sql += `and title like %${keyword}% `
  }
  sql += `order by createtime desc;`
  return exec(sql)
}

const getDetail = (id) => {
  const sql = `select * from blogs where id = '${id}'`;
  return exec(sql).then(rows => {
    return rows[0];
  })
}

const newBlog = (blogData = {}) => {
  const { title, content, author } = blogData;
  const createTime = Date.now();
  const sql = `
    insert into blogs (title, content, createtime, author)
    values ('${title}', '${content}', '${createTime}', '${author}')
  `
  return exec(sql).then(data => {
    return {
      id: data.insertId
    }
  })
}
const updateBlog = (id, blogData = {}) => {
  const {title, content} = blogData;
  const sql = `
    update blogs set title='${title}', content='${content}' where id='${id}'
  `
  return exec(sql).then(data => {
    if (data.affectedRows > 0) {
      return true
    }
    return false
  })
}

const deleteBlog = (id, author) => {
  const sql = `delete from blogs where id = '${id}' and author='${author}'`
  return exec(sql).then(data => {
    if (data.affectedRows > 0) {
      return true
    }
    return false
  })
}


module.exports = {
  getList,
  getDetail,
  newBlog,
  updateBlog,
  deleteBlog
}

const { exec } = require('../db/mysql')

const login = (username, password) => {
  const sql = `
    select username, realname from users where username='${username}' and password='${password}'
  `;
  return exec(sql).then(rows => {
    return rows[0] || {}
  })
}

module.exports = {
  login
}

router 中调用 controller

const { getList, getDetail, newBlog, updateBlog, deleteBlog } = require('../controller/blog');
const { SuccessModel, ErrorModel } = require('../model/resModel');

const handlerBlogRouter = (req, res) => {
  const {method, path } = req
  const id = req.query.id;
  // 获取博客列表
  if (method === 'GET' && path === '/api/blog/list') {
    const author = req.query.author || '';
    const keyword = req.query.keyword || '';
    // const listData = getList(author, keyword);
    // return new SuccessModel(listData);
    const result = getList(author, keyword);
    return result.then(listData => {
      return new SuccessModel(listData);
    })

  }
  // 获取博客详情
  if (method === 'GET' && path === '/api/blog/detail') {
    // const data = getDetail(id);
    // return new SuccessModel(data);
    const result = getDetail(id);
    return result.then(data => {
      return new SuccessModel(data);
    })
  }
  // 新建一篇博客
  if (method === 'POST' && path === '/api/blog/new') {
    // const data = newBlog(req.body);
    // return new SuccessModel(data);
    req.body.author = 'zhangsan'; // 假数据
    const result = newBlog(req.body);
    return result.then(data => {
      return new SuccessModel(data);
    })
  }
  // 更新一篇博客
  if (method === 'POST' && path === '/api/blog/update') {
    const result = updateBlog(id, req.body);
    return result.then(data => {
      if (data) {
        return new SuccessModel();
      } else {
        return new ErrorModel('更新博客失败')
      }
    })
  }
  // 删除一篇博客
  if (method === 'POST' && path === '/api/blog/delete') {
    const author = 'zhangsan'; // 假数据
    const result = deleteBlog(id, author);
    return result.then(data => {
      if (data) {
        return new SuccessModel();
      } else {
        return new ErrorModel('删除博客失败')
      }
    })
  }
}
module.exports = handlerBlogRouter

const { login } = require('../controller/user')
const { SuccessModel, ErrorModel } = require('../model/resModel');
const handlerUserRouter = (req, res) => {
  const { method, path } = req
  // 登录
  if (method === 'POST' && path === '/api/user/login') {
    const { username, password } = req.body;
    const result = login(username, password);
    return result.then(data => {
      if (data.username) {
        return new SuccessModel('登录成功')
      }
      return new ErrorModel('登录失败')
    })
  }
}

module.exports = handlerUserRouter

app.js 改造

const { getList, getDetail, newBlog, updateBlog, deleteBlog } = require('../controller/blog');
const { SuccessModel, ErrorModel } = require('../model/resModel');

const handlerBlogRouter = (req, res) => {
  const {method, path } = req
  const id = req.query.id;
  // 获取博客列表
  if (method === 'GET' && path === '/api/blog/list') {
    const author = req.query.author || '';
    const keyword = req.query.keyword || '';
    // const listData = getList(author, keyword);
    // return new SuccessModel(listData);
    const result = getList(author, keyword);
    return result.then(listData => {
      return new SuccessModel(listData);
    })

  }
  // 获取博客详情
  if (method === 'GET' && path === '/api/blog/detail') {
    // const data = getDetail(id);
    // return new SuccessModel(data);
    const result = getDetail(id);
    return result.then(data => {
      return new SuccessModel(data);
    })
  }
  // 新建一篇博客
  if (method === 'POST' && path === '/api/blog/new') {
    // const data = newBlog(req.body);
    // return new SuccessModel(data);
    req.body.author = 'zhangsan'; // 假数据
    const result = newBlog(req.body);
    return result.then(data => {
      return new SuccessModel(data);
    })
  }
  // 更新一篇博客
  if (method === 'POST' && path === '/api/blog/update') {
    const result = updateBlog(id, req.body);
    return result.then(data => {
      if (data) {
        return new SuccessModel();
      } else {
        return new ErrorModel('更新博客失败')
      }
    })
  }
  // 删除一篇博客
  if (method === 'POST' && path === '/api/blog/delete') {
    const author = 'zhangsan'; // 假数据
    const result = deleteBlog(id, author);
    return result.then(data => {
      if (data) {
        return new SuccessModel();
      } else {
        return new ErrorModel('删除博客失败')
      }
    })
  }
}
module.exports = handlerBlogRouter

总结：
- nodejs 连接 mysql，如何执行 sql 语句
- 根据 NODE_ENV 区分配置
- 封装 exec 函数，API 使用 exec 操作数据库

一、mysql ​

1.mysql 介绍、安装和使用 ​

2.nodejs 连接 mysql ​

3.API 连接 mysql ​

一、mysql

1.mysql 介绍、安装和使用

2.nodejs 连接 mysql

3.API 连接 mysql