vue+Nodejs+Koa搭建前后端系统（五）--Nodejs中使用数据库

连接数据库

1.开启mysql服务

以管理员身份运行cmd，输入：

net start mysql

2.登录 root用户、创建新用户、赋予新用户权限

如果你用root用户作为node的连接用户，这一步可以略过。

（1）登录root：

mysql -u root -p

（2）创建新用户admin：

 CREATE USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY '611252';

其中，admin是用户名，611252是密码。

（3）赋予admin用户权限：

 GRANT all privileges ON xiaoyang.* TO 'admin'@'localhost';

xiaoyang.*表示 xiaoyang数据库下的所有表。

（4）刷新权限
对用户做了权限变更之后，一定记得重新加载一下权限，将权限信息从内存中写入数据库。

flush privileges;

3.在node项目中安装mysql插件

npm install mysql

mysql插件npm地址：点这里

4.在node项目的app.js文件中编写连接数据库代码

/**mysql连接 START*/
const mysql = require("mysql");
const connection = mysql.createConnection({
  host: "localhost",//数据库主机
  port: 4200,//数据库端口
  user: "admin",//用户名
  password: "611252",//密码
  database: "xiaoyang",//连接的数据库名
});
connection.connect(function (err) {
  if (err) {
    console.log(`mysql connnect error:${err.stack}`);
    return;
  }
  console.log(`mysql connected as id ${connection.threadId}`);
});
/**mysql连接 END*/

启动node项目，即可连接数据库了。

连接数据库遇到的问题

当你用root用户连接时，可能会遇到连接报错：

mysql connnect error:Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

这是因为MySQL 8默认采用 caching_sha2_password 加密方式，而node的mysql模块并未完全支持MySQL 8支持该加密方式。
解决方式就是，修改root的密码并采用MySQL native password加密方式：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '611252';

如果要在创建新用户时使用MySQL native password加密方式，有两种方式：
一种方式是创建用户是时指明MySQL native password加密方式（前面用到的）：

 CREATE USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY '611252';

另一种就是修改my.ini文件配置：
（1）在[mysqld]中添加默认验证方式

[mysqld]
default_authentication_plugin=mysql_native_password 

（2）重启mysql服务

net stop mysql & net start mysql

（3）登录mysql root用户
（4）创建新用户

 CREATE USER 'admin'@'localhost' IDENTIFIED BY '611252'

（5）赋予用户权限
（6）刷新权限

操作数据库

创建表

一般的WEB项目需求就是对数据库表的增删改查，所以首先需要手动创建一张表。
以用户表为例，创建create_user表，其中有id（用户id）、username（用户名）、password（用户密码）、create_time（创建时间）列。
创建新表有三种方式：

一种是在cmd终端登录mysql，然后在cmd中写sql语句：
1.登录mysql客户端

mysql -u admin -p

2.启用(xioayang)数据库

use xiaoyang

3.编写sql语句

CREATE TABLE
    IF NOT EXISTS create_user(
        id int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '用户id',
        username VARCHAR(20) COMMENT '用户名',
        password VARCHAR(32) COMMENT '用户密码',
        create_time TIMESTAMP NOT NULL COMMENT '创建时间'
    ) COMMENT '创建的用户表';

另一种创建一个sql文件，在文件中写sql语句，然后在终端执行该文件：

1.登录mysql客户端： 同上
2.启用(xioayang)数据库： 同上
3.新建一个sql文件： 我创建了createTable.sql文件，路径为F:\test\vue_node\hello-node\server2\mysql\createTable\createTable.sql
4.在sql文件中编写sql语句： 语句同上
5.在mysql客户端执行sql文件： source F:/test/vue_node/hello-node/server2/mysql/createTable/createTable.sql;

还有一种方式就是利用mysql可视化工具创建表，这里不做详细介绍，可自行百度。

将mysql操作对象加入ctx对象中

const mysql = require("mysql");
const connection = mysql.createConnection({
  host: "localhost",
  port: 4200,
  user: "root",
  password: "611252",
  database: "xiaoyang",
});
connection.connect(function (err) {
  if (err) {
    console.log(`mysql connnect error:${err.stack}`);
    return;
  }
  console.log(`mysql connected as id ${connection.threadId}`);
});
app.context.db = connection;

app.context 是 ctx对象 的原型。您可以通过编辑 app.context 为 ctx 添加其他属性。这对于将 ctx
添加到整个应用程序中使用的属性或方法非常有用，这可能会更加有效（不需要中间件）和/或 更简单（更少的
require()），而更多地依赖于ctx，这可以被认为是一种反模式。

然后我们就可以在router中使用db对象了：

const router = require("koa-router")();
router.post("/add", (ctx, next) => {
	const sql = `INSERT INTO create_user (username,password,create_time) value('xy','123456',CURRENT_TIMESTAMP())`;
	ctx.db.query(sql, (err, data) => {
      if (err) {
        console.error(`mysql ERROR:${err}`);
        return;
      }
      console.log(`mysql success!`);
    });
})

编写新增用户接口

在/routes/users.js中编写add路由，接口接收参数username和password：

const router = require("koa-router")();

router.post("/add", (ctx, next) => {
  return new Promise((res, rej) => {
    const params = ctx.request.body;
    const sql = `INSERT INTO create_user (username,password,create_time) value('${params.username}','${params.password}',CURRENT_TIMESTAMP())`;
    ctx.db.query(sql, (err, data) => {
      if (err) {
        console.error(`mysql ERROR:${err}`);
        ctx.body = "fail";
        rej();
        return;
      }
      ctx.body = "success";
      res();
    });
  });
});

注意，这里要用Promise，将其变为异步，否则ctx.body返回不了响应中，因为ctx.db.query是异步的。

前端vue请求新增用户

首先，需要查看前端请求的端口是否与node服务器的一致（vite.config.ts），然后在编写请求页面，我在APP.vue中：

<script setup lang="ts">
import axios from "axios";
import { ref } from "vue";
//post请求
function http(url: string, params = {}) {
  return new Promise((resolve, reject) => {
    axios
      .request({
        url: `/nodeApi/${url}`,
        timeout: 8000,
        headers: { "X-Custom-Header": "foobar" },
        method: "post",
        data: params,
      })
      .then((res: any) => {
        console.log(`${url}响应：`, res);
        resolve(res);
      })
      .catch((err) => {
        console.error(`${url}错误信息:`, err);
        reject(err);
      });
  });
}
function addUser() {
  const params = {
    username: username.value,
    password: password.value,
  };
  //这里有user前缀，是因为后端node处理过路由地址为route文件路径（前面文章提到过）
  http("users/add", params);
}
const username = ref("");
const password = ref("");
</script>

<template>
  <button @click="helloNode">hello-node</button>
  <button @click="goodbyeNode">goodbye-node</button>
  <lable>用户名：<input v-model="username" type="text" /></lable>
  <lable>密码：<input v-model="password" type="password" /></lable>
  <button @click="addUser">添加用户</button><br />
</template>

完善页面功能 – 增、删、改、查功能

新增用户功能已编写完成，下面我们把删、改、查功能写出来。直接贴代码

后端node代码（users.js页面）：

const router = require("koa-router")();

//新增用户
router.post("/add", (ctx, next) => {
  return new Promise((res, rej) => {
    const params = ctx.request.body;
    const sql = `INSERT INTO create_user (username,password,create_time) value('${params.username}','${params.password}',CURRENT_TIMESTAMP())`;
    ctx.db.query(sql, (err, data) => {
      if (err) {
        console.error(`mysql ERROR:${err}`);
        ctx.body = "fail";
        rej();
        return;
      }
      ctx.body = "success";
      res();
    });
  });
});
//查看所有用户
router.post("/look", (ctx, next) => {
  return new Promise((res, rej) => {
    const sql = `SELECT * FROM create_user`;
    ctx.db.query(sql, (err, data) => {
      if (err) {
        console.error(`mysql ERROR:${err}`);
        ctx.body = "fail";
        rej();
        return;
      }
      ctx.body = data;
      res();
    });
  });
});
//删除用户
router.post("/del", (ctx, next) => {
  return new Promise((res, rej) => {
    const params = ctx.request.body;
    const sql = `DELETE FROM create_user WHERE id=${params.id}`;
    ctx.db.query(sql, (err, data) => {
      if (err) {
        console.error(`mysql ERROR:${err}`);
        ctx.body = "fail";
        rej();
        return;
      }
      ctx.body = "success";
      res();
    });
  });
});
//修改用户密码
router.post("/update", (ctx, next) => {
  return new Promise((res, rej) => {
    const params = ctx.request.body;
    const sql = `UPDATE create_user SET password='${params.password}' WHERE id=${params.id}`;
    ctx.db.query(sql, (err, data) => {
      if (err) {
        console.error(`mysql ERROR:${err}`);
        ctx.body = "fail";
        rej();
        return;
      }
      ctx.body = "success";
      res();
    });
  });
});

前端VUE代码（App.vue页面）：

<script setup lang="ts">
import axios from "axios";
import { ref } from "vue";
//post请求
function http(url: string, params = {}) {
  return new Promise((resolve, reject) => {
    axios
      .request({
        url: `/nodeApi/${url}`,
        timeout: 8000,
        headers: { "X-Custom-Header": "foobar" },
        method: "post",
        data: params,
      })
      .then((res: any) => {
        console.log(`${url}响应：`, res);
        resolve(res);
      })
      .catch((err) => {
        console.error(`${url}错误信息:`, err);
        reject(err);
      });
  });
}
//新增用户
function addUser() {
  const params = {
    username: username.value,
    password: password.value,
  };
  http("users/add", params).then((res) => lookUser());
}
//查看所有用户
function lookUser() {
  const params = {};
  http("users/look", params).then((data: any) => (list.value = data.data));
}
//删除用户
function delUser(id: number) {
  const params = { id: id };
  http("users/del", params).then((data: any) => lookUser());
}
//修改用户密码
function updateUser(id: number) {
  const password = prompt("修改密码", "请输入新密码");
  const params = { id: id, password: password };
  http("users/update", params).then((data: any) => lookUser());
}
const username = ref("");
const password = ref("");
const list = ref<any[]>([]);
lookUser();
</script>

<template>
  <lable>用户名：<input v-model="username" type="text" /></lable>
  <lable>密码：<input v-model="password" type="password" /></lable>
  <button @click="addUser">添加用户</button><br />
  <table>
    <thead>
      <tr>
        <td>用户名</td>
        <td>密码</td>
        <td>创建时间</td>
        <td>操作</td>
      </tr>
    </thead>
    <tbody>
      <tr v-for="i in list" :key="i.id">
        <td>{{ i.username }}</td>
        <td>{{ i.password }}</td>
        <td>{{ i.create_time }}</td>
        <td>
          <button @click="updateUser(i.id)">修改密码</button>
          <button @click="delUser(i.id)">删除</button>
        </td>
      </tr>
    </tbody>
  </table>
</template>

以上代码只是用来演示，并未添加任何限制。

一些代码优化

先只关注node代码的优化。

1.连接数据库，集合！

可以将数据库的一些相关代码封装到插件中，首先就是数据库连接，在 /plugins/ 目录下新建mysql.js文件：

const mysql = require("mysql");
const defaulOpts = {
  host: "localhost",
  port: 4200,
  user: "admin",
  password: "611252",
  database: "xiaoyang",
};
function connection(app, myOpts = {}) {
  const opts = {};
  Object.assign(opts, defaulOpts, myOpts);
  const conn = mysql.createConnection(opts);
  conn.connect(function (err) {
    if (err) {
      console.log(`mysql connnect error:${err.stack}`);
      return;
    }
    console.log(`mysql connected as id ${conn.threadId}`);
  });
  app.context.db = conn;
}

module.exports = {
  connection,
};

在app.js中引入即可：

const Koa = require("koa");
const app = new Koa();
const { connection } = require("./plugins/mysql.js");
//连接数据库
connection(app);

2.优雅的操作数据库

由于ctx.db.query是异步，导致上面增删改查接口中，每个都要用Promise包裹，有些麻烦，可不可以让代码更优雅一些。

解决办法是：可以将ctx.db.query封装起来：
将代码封装在 /plugins/mysql.js 插件中：

const mysql = require("mysql");

function connection(app, myOpts = {}) {
  /**此处代码省略*/
}
//封装ctx.db.query为异步
function query(ctx, sql) {
  return new Promise((res, rej) => {
    ctx.db.query(sql, (err, data) => {
      if (err) {
        rej({ err });
        return;
      }
      res({ data });
    });
  });
}
module.exports = {
  connection,
  query
};

const router = require("koa-router")();
const {query} = require("../plugins/mysql.js");

//查看所有用户
router.post("/look", async (ctx, next) => {
  const sql = `SELECT * FROM create_user`;
  const r = await query(ctx, sql);
  if (r.err) {
    ctx.body = "fail";
    return;
  }
  ctx.body = r.data;
});

还有一种办法就是借助别人的力量co-mysql

npm i co-mysql

/plugins/mysql.js 文件代码:

const mysql = require("mysql");
const co = require("co-mysql");

const defaulOpts = {
  host: "localhost",
  port: 4200,
  user: "admin",
  password: "611252",
  database: "xiaoyang",
};
function connection(app, myOpts = {}) {
  const opts = {};
  Object.assign(opts, defaulOpts, myOpts);
  const conn = mysql.createConnection(opts);
  conn.connect(function (err) {
    if (err) {
      console.log(`mysql connnect error:${err.stack}`);
      return;
    }
    console.log(`mysql connected as id ${conn.threadId}`);
  });
  app.context.db = co(conn);
}

module.exports = {
  connection,
};

然后在 /routes/users.js 文件中：

const router = require("koa-router")();

router.post("/look", async (ctx, next) => {
  const sql = `SELECT * FROM create_user`;
  try {
    ctx.body = await ctx.db.query(sql);
  } catch (e) {
    ctx.body = "fail";
  }
  /**
  或者也可以这样使用
  ctx.db.query(sql).then(res=>ctx.body=res).catch(e=>ctx.body="fail")
  */
});
router.post("/del", async (ctx, next) => {
  const params = ctx.request.body;
  const sql = `DELETE FROM create_user WHERE id=${params.id}`;
  try {
    await ctx.db.query(sql);
    ctx.body = "success";
  } catch (e) {
    ctx.body = "fail";
  }
});

3.路由你能不能简单点

想把路由配置页的书写格式变成像填写配置项那样（参考vue-router）。比如 /routes/user.js 可以写成这样：

module.exports = [
  {
    url: "/add",
    methods: "post",
    actions: async (ctx, next) => {
      const params = ctx.request.body;
      const sql = `INSERT INTO create_user (username,password,create_time) value('${params.username}','${params.password}',CURRENT_TIMESTAMP())`;
      try {
        await ctx.db.query(sql);
        ctx.body = "success";
      } catch (e) {
        ctx.body = "fail";
      }
    },
  },
  {
    url: "/look",
    methods: "post",
    actions: async (ctx, next) => {
      const sql = `SELECT * FROM create_user`;
      try {
        ctx.body = await ctx.db.query(sql);
      } catch (e) {
        ctx.body = "fail";
      }
    },
  },
  {
    url: "/del",
    methods: "post",
    actions: async (ctx, next) => {
      const params = ctx.request.body;
      const sql = `DELETE FROM create_user WHERE id=${params.id}`;
      try {
        await ctx.db.query(sql);
        ctx.body = "success";
      } catch (e) {
        ctx.body = "fail";
      }
    },
  },
  {
    url: "/update",
    methods: "post",
    actions: async (ctx, next) => {
      const params = ctx.request.body;
      const sql = `UPDATE create_user SET password='${params.password}' WHERE id=${params.id}`;
      try {
        await ctx.db.query(sql);
        ctx.body = "success";
      } catch (e) {
        ctx.body = "fail";
      }
    },
  },
];

要想实现这样的写法，只需要修改 /plugins/loadRoutes.js 一点点代码：

const fs = require("fs");
const path = require("path");
const defaultOptions = {
  extname: [".js"],
  root: "routes/",
  prefixIgnore: ["main.js"],
};
const routesDir = path.resolve(defaultOptions.root);
function loadRoutes(app, dirPath) {
  fs.readdir(dirPath, { withFileTypes: true }, function (err, files) {
    if (err) {
      console.warn(err, "读取文件夹错误！");
    } else {
      files.forEach(function (dirent) {
        const currentPath = path.join(dirPath, dirent.name);
        if (dirent.isDirectory()) {
          loadRoutes(app, currentPath);
        } else if (dirent.isFile()) {
          const relativePath = path.relative(__dirname, currentPath);
          const extname = path.extname(relativePath);
          if (defaultOptions.extname.includes(extname)) {
          
          	/** delete */
            // const router = require(relativePath);
            /** delete */
            
            /**new */
            const routes = require(relativePath);
            let router = null;
            if (Array.isArray(routes)) {
              //新写法
              router = require("koa-router")();
              routes.forEach((item) => {
                router[item.methods](item.url, item.actions);
              });
            } else {
              //原写法
              router = require(relativePath);
            }
            /**new */

            const extnameReg = new RegExp(defaultOptions.extname.join("|"));
            const prefixIgnore = defaultOptions.prefixIgnore.some((s) => {
              const ignoeSrc = path.resolve(defaultOptions.root, s);
              return currentPath.indexOf(ignoeSrc) === 0;
            });
            if (!prefixIgnore) {
              const routerNamespace = path
                .relative(routesDir, currentPath)
                /**new (将反斜杠处理成斜杠) */
                .replace(/\\/gim, "/")
                /**new */
                .replace(extnameReg, "");
              router.prefix("/" + routerNamespace);
            }
            app.use(router.routes(), router.allowedMethods());
          }
        }
      });
    }
  });
}

module.exports = function (app, opt = {}) {
  Object.assign(defaultOptions, opt);
  loadRoutes(app, routesDir);
};

上面代码中/**new */表示在原代码上新增的代码，/** delete */ 表示删除的原代码。上述修改使路由的页面兼容以前写法。

如果你没有看过《vue+Nodejs+Koa搭建前后端系统（三）–koa-generator项目优化修改》，你需要检查app.js中是否加载了全部路由：

const load = require("./plugins/loadRoutes.js");
//加载全部路由
load(app);

4.路由和mysql你们不能在一起

现在，路由页面还是不够清爽，因为接口逻辑api也写在了路由配置页面中。我需要将其分离。
首先在项目根目录下新建一个module目录，用来存放各个模块的api。然后路由需要哪个api引入就行。

让我们分离一下 /routes/user.js 中的增删改查接口：
新建 /module/user.js ,将逻辑api放入该文件：

//新增用户
async function addUser(ctx, next) {
  const params = ctx.request.body;
  const sql = `INSERT INTO create_user (username,password,create_time) value('${params.username}','${params.password}',CURRENT_TIMESTAMP())`;
  try {
    await ctx.db.query(sql);
    ctx.body = "success";
  } catch (e) {
    ctx.body = "fail";
  }
}
//查看所有用户
async function lookUser(ctx, next) {
  const sql = `SELECT * FROM create_user`;
  try {
    ctx.body = await ctx.db.query(sql);
  } catch (e) {
    ctx.body = "fail";
  }
}
//更新用户密码
async function updateUserPassword(ctx, next) {
  const params = ctx.request.body;
  const sql = `UPDATE create_user SET password='${params.password}' WHERE id=${params.id}`;
  try {
    await ctx.db.query(sql);
    ctx.body = "success";
  } catch (e) {
    ctx.body = "fail";
  }
}
//删除用户
async function delUser(ctx, next) {
  const params = ctx.request.body;
  const sql = `DELETE FROM create_user WHERE id=${params.id}`;
  try {
    await ctx.db.query(sql);
    ctx.body = "success";
  } catch (e) {
    ctx.body = "fail";
  }
}
module.exports = {
  addUser,
  lookUser,
  updateUserPassword,
  delUser,
};

/routes/user.js中引入api：

const {
  addUser,
  lookUser,
  updateUserPassword,
  delUser,
} = require("../module/user");
module.exports = [
  {
    url: "/add",
    methods: "post",
    actions: addUser,
  },
  {
    url: "/look",
    methods: "post",
    actions: lookUser,
  },
  {
    url: "/del",
    methods: "post",
    actions: delUser,
  },
  {
    url: "/update",
    methods: "post",
    actions: updateUserPassword,
  },
];

建议module目录中的文件结构和routes目录中的一致，这样查找方便。

保持数据库连接

mysql在8个小时内没有任何操作，就会自动中断连接
因此，可以每三个小时ping一次数据库，保持数据库连接状态。

修改 /plugin/mysql.js 中的代码：

const mysql = require("mysql");
const co = require("co-mysql");

var pingInterval = null;
const defaulOpts = {
  host: "localhost",
  port: 4200,
  user: "admin",
  password: "611252",
  database: "xiaoyang",
};
function connection(app, myOpts = {}) {
  const opts = {};
  Object.assign(opts, defaulOpts, myOpts);
  const conn = mysql.createConnection(opts);
  conn.connect(function (err) {
    if (err) {
      console.log(`mysql connnect error:${err.stack}`);
      return;
    }
    console.log(`mysql connected as id ${conn.threadId}`);
  });
  //数据库失去连接则重启连接
  conn.on("error", function (err) {
    if (err.code === "PROTOCOL_CONNECTION_LOST") {
      connection(app, myOpts);
    } else {
      throw err;
    }
  });
  // 每隔3小时ping一次数据库，保持数据库连接状态
  clearInterval(pingInterval);
  pingInterval = setInterval(() => {
    conn.ping((err) => {
      if (err) {
        console.log("ping error: " + JSON.stringify(err));
      }
    });
  }, 3600000 * 3);

  app.context.db = co(conn);
}

module.exports = {
  connection,
};

参考资料：
知乎：node+koa+mysql开发
简书：koa 连接数据库
简书：操作mysql数据库 中间件 mysql、co-msql
解决node连接数据库频繁关闭问题
CSDN：nodejs连接mysql突然中断问题解决方案
CSDN：nodejs连接mysql报错Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol re
CSDN：mysql创建新用户并授权