使用 Node.js 连接 MySQL

概述

当使用 Node.js 开发 Web 应用程序时，经常需要与数据库进行交互来存储和检索数据。MySQL 是一个流行的关系型数据库管理系统，它提供了强大的功能和性能。本文将介绍如何使用Node.js连接MySQL数据库，并展示一些常见的操作示例。

开始

在这里我们将使用 Node.js 的 mysql2 库来连接MySQL数据库。mysql2是一个高性能的MySQL驱动程序，提供了简单而灵活的API，使得在Node.js应用程序中执行数据库操作变得更加容易。

GitHub

安装：

npm install mysql2 --save

接下来我们对 mysql2 库进行简单的封装，以便我们接下来的操作。

import mysql, { FieldPacket, OkPacket, PoolOptions, QueryError, ResultSetHeader, RowDataPacket } from "mysql2";

const poolConfig: PoolOptions = {
  host: "localhost",
  user: "root",
  password: "123456",
  port: 3306,
  database: "test",
  waitForConnections: true,
  connectionLimit: 3,
  queueLimit: 0,
};

export const pool = mysql.createPool(poolConfig);

interface QueryResult {
  status: string;
  error: QueryError;
  result: RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader;
  fields: FieldPacket[];
}

interface PromisePoolQueryFn {
  (sql: string, val?: Array<string | number | boolean|object> | object[]): Promise<QueryResult>;
}

interface PromisePool {
  query: PromisePoolQueryFn;
}

export const promisePoolQuery: PromisePoolQueryFn = (sql, val) => {
  return new Promise((resolve) => {
    pool.query(sql, val, (err: QueryError, results: RowDataPacket[], fields: FieldPacket[]): void => {
      const result: QueryResult = {
        status: "ok",
        error: err,
        result: results,
        fields: fields,
      };
      if (err) {
        result.status = "err";
        resolve(result);
      } else {
        resolve(result);
      }
    });
  });
};

export const promisePool: PromisePool = {
  query: promisePoolQuery,
};

pool.query("SELECT 1", function (err: QueryError, results: RowDataPacket, fields: FieldPacket[]) {
  if (err) {
    console.log(`数据库连接失败:${err}`);
  }
  console.log("数据库连接成功");
});

export { QueryError, RowDataPacket, FieldPacket };

import express, { Request, Response, NextFunction, query } from "express";
import { check, ValidationChain, validationResult } from "express-validator";
import { pool, promisePool, QueryError, RowDataPacket, FieldPacket } from "../../controllers/mysql";

export const router = express.Router();

//过滤器
router.all("*", async (req: Request, res: Response, next: NextFunction) => {
  next();
});

router.post("/", validateUserCreate, async function (req: Request, res: Response) {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    console.log(errors);
    return res.status(400).json({ errors: errors.array() });
  }
  const sql = `INSERT INTO users SET ?`;
  const { age, name, address } = req.body;
  const sqlParams = [{ age, name, address }];
  const result = await promisePool.query(sql, sqlParams);
  console.log(result);
  res.send("ok");
});

当使用mysql2库执行增删改查（CRUD）操作时，可以按照以下示例来编写sql语句：

'INSERT INTO your_table SET ?'

'DELETE FROM your_table WHERE id = ?'

'UPDATE your_table SET ? WHERE id = ?'

'SELECT * FROM your_table WHERE id = ?'