开发博客项目之数据存储
前提:安装 MySQL,并使用图形化界面方便操作(DBeaver、MySQL Workbench 等)
1. 数据库操作(创建和增删改查)
1.1 创建数据库和创建表
可以用 SQL 语句写,但是用图形化界面操作更简单且不容易出错。
右键点击创建一个数据库,名称为 myblog。
右键单击 myblog,创建两个表:
-
博客
-
用户
密码后边需要加密
id 为唯一标识,即为主键,并设置为自动增加,以后插入数据后,自己不用添加 id。
1.2 增删改查
使用数据库 myblog
use myblog;
查看 myblog 内所有表
show tables;
增加数据
INSERT INTO users (username, `password`, realname) VALUES ('admin', '123456', '管理员');
查询数据
查询 users 里所有列:
SELECT * FROM users;
查找特定列的数据,能优化查询性能:
SELECT id, username FROM users;
查询带查询条件,比如查询用户名为 zhangsan 的信息:
SELECT * FROM users WHERE username = 'zhangsan';
and 和 or 搭配,和编程里的意思一样
SELECT * FROM users WHERE username = 'zhangsan' AND password='123456';
SELECT * FROM users WHERE username = 'zhangsan' OR password='123456'
模糊查询,比如查询 username 里带 zhang 的数据
SELECT * FROM users WHERE username LIKE '%zhang%';
结果排序(默认正序):
SELECT * FROM users WHERE password LIKE '%1%' ORDER BY id;
如果要看倒序的结果:
SELECT * FROM users WHERE password LIKE '%1%' ORDER BY id DESC;
更新数据
注意,更新的时候要标注更新的条件,否则所有的行都会更新!
UPDATE users SET realname = '李四2' WHERE username = 'lisi';
删除数据
同样的,删除数据的时候也要标注删除的条件!
DELETE FROM users WHERE username = 'lisi';
一般删除行都不是这样删除的,而是软删除——更新字段。表内有一个字段例如 state ,默认值为 1。来标注是否被删除,如果被删除标记为 0,否则为 1。这样可以很好地做数据恢复,而不是做永久删除。
UPDATE users SET state = 0 WHERE username = 'lisi';
2. nodejs 操作 MySQL
之前用的数据都是模拟数据,后面就不能用模拟的数据了,而是让 API 来操作数据库。
安装 MySQL 第三方库
yarn add mysql
基本使用方式
const mysql = require("mysql");
// 创建连接对象
const con = mysql.createConnection({
host: "localhost",
user: "root",
password: "123456",
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();
传入必须的信息后简历数据库连接,然后执行 SQL 语句,并执行成功或失败的回调。
上面代码输出的结果:
[
RowDataPacket {
id: 1,
username: 'admin',
password: '123456',
realname: '管理员'
},
RowDataPacket {
id: 2,
username: 'zhangsan',
password: '123456',
realname: '张三'
},
RowDataPacket {
id: 4,
username: 'lisi',
password: '123456',
realname: '李四'
}
]
如果 sql 语句如下:
const sql = "UPDATE users SET realname = '李四2' WHERE username = 'lisi';";
则返回结果如下:
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 0,
serverStatus: 34,
warningCount: 0,
message: '(Rows matched: 1 Changed: 1 Warnings: 0',
protocol41: true,
changedRows: 1
}
返回的数据有很多的信息。包括插入时候对应的 id,影响的行数等。
3. nodejs 连接 MySQL 工具实现
上面的 nodejs 连接数据库并且操作数据库的写法是比较费劲的,真正的核心代码是 SQL 操作的代码,因此需要优化一下。
- 增加一个配置文件,将连接所需的配置放在里边:
// conf/db.js
// 获取环境参数
const env = process.env.NODE_ENV;
let MYSQL_CONF = {};
// 开发环境下数据库的地址
if (env === "dev") {
MYSQL_CONF = {
host: "localhost",
user: "root",
password: "123456",
port: "3306",
database: "myblog",
};
}
// 生产环境下数据库的地址
if (env === "production") {
MYSQL_CONF = {
host: "localhost",
user: "root",
password: "123456",
port: "3306",
database: "myblog",
};
}
module.exports = {
MYSQL_CONF,
};
-
创建统一执行 sql 的函数和相关的工具
sql 的执行时异步的,因此用 promise 包裹是最好的。
const mysql = require("mysql"); const { MYSQL_CONF } = require("../conf/db"); // 创建连接对象 const con = mysql.createConnection(MYSQL_CONF); // 开始连接 con.connect(); // 统一执行 SQL 的函数 function exec(sql) { return new Promise((resolve, reject) => { con.query(sql, (err, result) => { if (err) { console.error(err); return; } resolve(result); }); }); } module.exports = { exec, };
4. API 对接 MySQL(博客列表)
写成上面的样子后,需要留意的是,执行 SQL 语句后返回的是 promise,因此得 then 或者 await 之后才能拿到数据。
// router/blog.js
// 获取博客列表
if (method === "GET" && req.path === "/api/blog/list") {
const author = req.query.author || "";
const keyword = req.query.keyword || "";
const result = getList(author, keyword);
// 返回 promise
return result.then((listData) => {
return new SuccessModel(listData);
});
}
// controller/blog.js
const getList = (author, keyword) => {
let sql = `select id, title, content, createtime, author 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);
};
5. API 对接 MySQL(博客列表详情)
// router/blog.js
// 获取博客详情
if (method === "GET" && req.path === "/api/blog/detail") {
const result = getDetail(id);
return result.then((data) => {
return new SuccessModel(data);
});
}
// controller/blog.js
const getDetail = (id) => {
const sql = `select id, title, content, createTime, author from blogs where id = '${id}'`;
return exec(sql).then((rows) => {
// 返回的是数组,提取出数组中的第一项
return rows[0];
});
};
5. API 对接 MySQL (新建博客)
// router/blog.js
// 新建一篇博客
if (method === "POST" && req.path === "/api/blog/new") {
// 新建博客需要获取 author,但是现在登录功能没搞完,还没有
req.body.author = "zhangssan";
const result = newBlog(req.body);
return result.then((data) => {
return new SuccessModel(data);
});
}
// controller/blog.js
const newBlog = (blogData = {}) => {
// blogData 是一个博客对象,包含 title、content、author 属性
blogData = {
...blogData,
createTime: Date.now(),
id: 3, // 表示新建博客,插入到数据表里面的 id
};
const { title, content, author, createTime } = blogData;
const sql = `
insert into blogs (title, content, createTime, author)
values
('${title}', '${content}', ${createTime}, '${author}');`;
return exec(sql).then((insertData) => {
// promise 返回插入的值对应的 id
return {
id: insertData.insertId,
};
});
};
6. API 对接 MySQL(更新博客)
// router/blog.js
// 更新一篇博客
if (method === "POST" && req.path === "/api/blog/update") {
const result = updateBlog(id, req.body);
return result.then((val) => {
if (val) {
return new SuccessModel();
} else {
return new ErrorModel("更新博客失败");
}
});
}
// controller/blog.js
const updateBlog = (id, blogData = {}) => {
const { title, content } = blogData;
const sql = `
update blogs set
title = '${title}', content = '${content}'
where
id = ${id}
`;
return exec(sql).then((updatedData) => {
if (updatedData.affectedRows > 0) {
return true;
}
return false;
});
};
7. API 对接 MySQL(删除博客)
// router/blog.js
// 删除一篇博客
if (method === "POST" && req.path === "/api/blog/del") {
// 登录功能未完成,先用假数据
const author = "zhangsan";
const result = delBlog(id, author);
return result.then((val) => {
if (val) {
return new SuccessModel();
} else {
return new ErrorModel("删除博客失败");
}
});
}
// controller/blog.js
const delBlog = (id, author) => {
const sql = `
delete from blogs
where id = '${id}' and author = '${author}';
`
return exec(sql).then((delData) => {
if (delData.affectedRows > 0) {
return true;
}
return false;
});
};
8. API 对接 MySQL(用户登录)
// controller/user.js
const loginCheck = (username, password) => {
const sql = `
select username, realname from users where
username = '${username}' and password = '${password}';
`;
return exec(sql).then((rows) => {
return rows[0] || {};
});
};
// router/user.js
// 登录
if (method === "POST" && req.path === "/api/user/login") {
const { username, password } = req.body;
const result = loginCheck(username, password);
return result.then((data) => {
if (data.username) {
return new SuccessModel();
}
return new ErrorModel("登录失败");
});
}
因为上边的 router 都是返回的 promise,而之前的假数据是同步的,因此 router 返回的数据需要通过 then 或者 await 接收。
// app.js
const serverHandle = async (req, res) => {
// 设置返回格式 JSON
res.setHeader("Content-type", "application/json");
// 获取 path
const url = req.url;
req.path = url.split("?")[0];
// 解析 query
req.query = qs.parse(url.split("?")[1]);
// 解析 POST data 后放在 req.body 内
const postData = await getPostData(req);
req.body = postData;
// 处理 blog 路由
const blogData = await handleBlogRouter(req, res);
if (blogData) {
res.end(JSON.stringify(blogData));
// 需 return,否则会继续往下执行
return;
}
// 处理 user 路由
const userData = await handleUserRouter(req, res);
if (userData) {
res.end(JSON.stringify(userData));
return;
}
// 未命中路由:纯文本返回 404 信息
res.writeHead(404, { "content-type": "text/plain" });
res.write("404 not found");
res.end();
};