# **下载并安装数据库**
# **node项目中使用mysql**
## **安装依赖 mysql2**
```
npm install mysql2 --save
```
## **创建配置文件**
在config文件夹先新建mysql.config.js
```
// 导入mysql模块
const mysql = require("mysql2");
// 2、建立与MySQL数据库的连接
let connection = mysql.createConnection({
// 数据库的IP地址
host: "127.0.0.1",
// 登录数据库的账号
port: 3306,
user: "root",
// 登录db的密码
password: "123456",
// 指定要操作哪个数据库
database: "mysqltest",
});
module.exports = connection;
```
## **在app.js中引入，并链接数据库**
```
const connection = require("./config/mysql.config");
connection.connect((err) => {
if (err) throw err;
console.log("Connected!");
});
```
## **创建service**
在services下新建UserService.mysql.js
```
const connection = require("../../config/mysql.config");
const UserService = {
login: async ({ username, password }, error_cb, success_cb) => {
console.log("username, password: ", username, password);
// 1-查询数据
const sqlStr = "select * from users where username=?";
connection.query(sqlStr, username, (err, result) => {
console.log("result: ", result);
// 查询失败
if (err) return error_cb();
// 查询成功
if (result.length === 0) return error_cb();
const user = result[0];
// 2-校验密码
const isMatch = user.password === password;
if (!isMatch) error_cb();
// 3-登录成功
success_cb(user);
});
},
}
module.exports = UserService;
```
## **创建controller**
在controllers文件夹下新建UserController.mysql.js
```
const UserServiceByMysql = require("../../services/admin/UserServiceByMysql");
const jwt = require("../../util/JWT");
const UserControllerMysql = {
login: async (req, res) => {
UserServiceByMysql.login(
req.body,
() => {
res.send({ code: 401, msg: "用户名或密码不匹配" });
},
(user) => {
const token = jwt.generate(
{
name: user.username,
id: user.id,
},
"7d"
);
res.header("Authorization", `Bearer ${token}`);
res.send({ code: 200, msg: "登录成功", data: { ...user } });
}
);
},
};
module.exports = UserControllerMysql;
```
## **路由引入**
```
const UserControllerMysql = require("../../controllers/admin/UserController.mysql");
...
router.post("/admin/user/login", UserControllerMysql .login); // 登录
```
## **封装**
util文件夹下新建mysql.js
```
// 导入mysql模块
const mysql = require("mysql2");
// 2、建立与MySQL数据库的连接
let connection = mysql.createConnection({
// 数据库的IP地址
host: "127.0.0.1",
// 登录数据库的账号
port: 3306,
user: "root",
// 登录db的密码
password: "123456",
// 指定要操作哪个数据库
database: "mysqltest",
});
connection.on("error", (err) => {
console.error("数据库连接失败：" + err.stack);
});
connection.on("close", () => {
console.log("数据库连接已断开");
});
connection.on("connect", () => {
console.log("数据库连接成功");
});
connection.connect((err) => {
if (err) throw err;
console.log("Connected!");
});
module.exports = connection;
```
## **使用示例**
```
const connection = require("./util/mysql");
const sqlStr = "select * from users where username=?";
connection.query(sqlStr, "admin", (err, result) => {
// 查询失败
if (err) return console.log(1);
// 查询成功
if (result.length === 0) return console.log(2);
const user = result[0];
// 2-校验密码
const isMatch = user.password === "123456";
if (!isMatch) console.log(3);
// 3-登录成功
console.log(user);
});
```
