# **下載并安裝數據庫**
# **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);
});
```
