阅读 736
记录第一次用node + mysql做接口

记录第一次用node + mysql做接口

一. 使用navicat建数据库

image.png

1. 下载mysql

  • 安装指南 zhuanlan.zhihu.com/p/37152572 ,这篇文章很详细
  • 安装时,MySQL会提示输入root用户的口令,请务必记清楚。如果怕记不住,就把口令设置为password
  • Windows上,安装时请选择UTF-8编码,以便正确地处理中文。
  • 配置usernamepassword时最好记录下来,避免忘了。

2. 下载navicat

3. navicat连接mysql数据库

  • 用管理员身份运行cmd,输入net start 服务器名称,启动数据库

image.png

  • 启动成功

image.png

  • 打开navicat点击连接mysql

image.png

  • 输入net stop 服务器名称,停止mysql

image.png

二. 使用node + mysql做接口

1. 启动node服务器

  • 随便找个地方运行命令mkdir node-service && cd node-service,新建index.js文件,简单用express就开启一个服务器了
// index.js
const express = require("express");
const app = express();
const port = 3002;
app.listen(port, () => {
  console.log(`Example app listening at http://localhost:${port}`);
});
复制代码

2. node连接本地数据库

  • 引用mysql连接,这样就能连接上本地的数据库了,前提是数据库服务器有开启
const mysql = require("mysql");
const defconfig = {
  host: "localhost",
  user: "root",
  password: "password",
  database: "test", // 数据库名
  port: "3306",
};
const connection = mysql.createConnection(defconfig);
connection.connect((err) => {
  if (err) {
    console.log("数据库连接失败");
    throw err;
  }
});
复制代码
  • 操作数据库,简单查询一下数据库
const querysql = "SELECT * FROM user";
connection.query(querysql, [], (res, fields) => {
  console.log("查询结果:");
  console.log(res);
  console.log("fields: ", fields);
});
复制代码
  • 再简单封装一下,就可以在调用接口的时候直接使用了。
// db.js
const query = (sql, params, callback) => {
  const connection = mysql.createConnection(defconfig);
  connection.connect((err) => {
    if (err) {
      console.log("数据库连接失败");
      throw err;
    }
  });
  const fn = (err, res, fields) => {
    if (err) {
      console.log("数据操作失败: ", err.message);
      throw err;
    }
    //将查询出来的数据返回给回调函数
    callback && callback(res, fields);
    connection.end((err) => {
      if (err) {
        console.log("关闭数据库连接失败!");
        throw err;
      }
    });
  };
  connection.query(sql, params, fn);
};
复制代码

3. 创建增删改查接口

注意:要拿到传过来的数据需要调用app.use(express.json())app.use(express.urlencoded()),代替body-parse解析传入数据

image.png

image.png

const express = require("express");
const app = express();
app.use(express.json());
app.use(express.urlencoded());
复制代码
  • 数据库

image.png

  • 创建查询接口,然后在前端调用接口
app.get("/data", (req, resp) => {
  const querysql = "SELECT * FROM todo_list";
  db.query(querysql, [], (res) => {
    resp.json(res);
  });
});
复制代码
const http = new XMLHttpRequest();
http.onreadystatechange =  () => {
  if (http.status == 200 && http.readyState == 4) {
    // 调用成功后
  }
};
//发送请求
http.open("GET", "http://localhost:3000/data");
http.send();
复制代码
  • 创建插入接口
app.post("/postdata", (req, resp) => {
  console.log("插入数据", req.body);
  const target = req.body.target;
  const querysql = "insert into todo_list(target, status) values(?,?)";
  db.query(querysql, [target, "false"], (res) => {
    resp.json(res);
  });
});
复制代码
const http = new XMLHttpRequest();
var { value, name } = document.getElementById("name");
const params = { [name]: value };
http.open("POST", "http://localhost:3000/postdata");
http.setRequestHeader("Content-type", "application/json;charset=UTF-8");
http.send(JSON.stringify(params));
document.location.reload();
复制代码
  • 创建更新接口
app.put("/putData", (req, resp) => {
  console.log("修改数据:", req.body);
  const target = req.body.target;
  const status = req.body.status;
  let data;
  if (status === "false") data = true;
  if (status === "true") data = false;
  const sql = `update todo_list set status = "${data}" where target = "${target}"`;
  db.query(sql, [], (res) => {
    resp.json(res);
  });
});
复制代码
const http = new XMLHttpRequest();
http.open("PUT", `http://localhost:3000/putData`);
// 传body要设置请求头
http.setRequestHeader(
"Content-type",
"application/json;charset=UTF-8"
);
http.send(JSON.stringify(data));
document.location.reload();
复制代码
  • 删除接口
app.delete("/deleteData", (req, resp) => {
  console.log("删除数据:", req.query);
  const target = req.query.target;
  const querysql = `DELETE FROM todo_list WHERE target = "${target}" `;
  db.query(querysql, [], () => {
    resp.json(req.query);
  });
});
复制代码
const http = new XMLHttpRequest();
http.open("DELETE", `http://localhost:3000/deleteData?target=${data.target}`);
http.send();
document.location.reload();
复制代码

4. 跨域问题

  • 第一种方法,安装cors
//yarn add cors
const cors = require("cors");
app.use(cors());
复制代码
  • 第二种方法,手动实现
app.use((req, res, next) => {
res.header('Access-Control-Allow-Origin', '*')
res.header('Access-Control-Allow-Headers', 'Authorization,X-API-KEY, Origin, X-Requested-With, Content-Type, Accept, Access-Control-Request-Method' )
res.header('Access-Control-Allow-Methods', 'GET, POST, OPTIONS, PATCH, PUT, DELETE')
res.header('Allow', 'GET, POST, PATCH, OPTIONS, PUT, DELETE')
next();
});
复制代码
文章分类
前端
文章标签