Node.js：mysql2.js的基本操作增删改查

文档：

• https://www.npmjs/package/mysql2
• https://www.npmjs/package/mysql

如果本地没有安装MySQL，可以使用免费的在线数据库替代

https://www.planetscale

表结构

create table table_user(
    id int primary key auto_increment, 
    name varchar(20) not null, 
    age int not null default 0
);

安装依赖

node -v
v16.14.0

pnpm i dotenv mysql2 

package.json

{
  "type": "module",
  "dependencies": {
    "dotenv": "^16.0.1",
    "mysql2": "^2.3.3"
  }
}

配置文件
.env

DATABASE_URL='mysql://root:123456@127.0.0.1/data'

代码实现

// pnpm i mysql2
import mysql from 'mysql2'

export function getConnection(config) {
  const connection = mysql.createConnection(config)

  // 自定义占位符
  // https://www.npmjs/package/mysql#custom-format
  connection.config.queryFormat = function (query, values) {
    if (!values) return query
    return query.replace(
      /\:(\w+)/g,
      function (txt, key) {
        if (values.hasOwnProperty(key)) {
          return this.escape(values[key])
        }
        return txt
      }.bind(this)
    )
  }

  // 查询列表
  function queryAsync(sql, params) {
    return new Promise((resovle, reject) => {
      connection.query(sql, params, function (err, result, fields) {
        if (err) {
          reject(err)
        } else {
          resovle(result)
        }
      })
    })
  }

  // 查询单个对象
  async function queryOneAsync(sql, params) {
    const res = await queryAsync(sql, params)
    if (res && res.length > 0) {
      return res[0]
    } else {
      return null
    }
  }

  // 同步查询
  connection.queryAsync = queryAsync
  connection.queryOneAsync = queryOneAsync

  return connection
}

查询示例

import "dotenv/config";

// 数据插入
async function insertRow(connection) {
  let sql = "INSERT into table_user (name, age) VALUES (:name, :age)";
  let params = { name: "Tom", age: 23 };

  let result = await connection.queryAsync(sql, params);

  console.log(result);

  //   ResultSetHeader {
  //     fieldCount: 0,
  //     affectedRows: 1,
  //     insertId: 1,
  //     info: '',
  //     serverStatus: 2,
  //     warningStatus: 0
  // }
}

// 数据修改
async function updateRow(connection) {
  let sql = "UPDATE table_user SET name = :name, age = :age WHERE id = :id";
  let params = { name: "Jack", age: 24, id: 1 };

  let result = await connection.queryAsync(sql, params);

  console.log(result);

  //   ResultSetHeader {
  //     fieldCount: 0,
  //     affectedRows: 1,
  //     insertId: 0,
  //     info: 'Rows matched: 1  Changed: 1  Warnings: 0',
  //     serverStatus: 2,
  //     warningStatus: 0,
  //     changedRows: 1
  //   }
}

// 数据查询
async function selectRow(connection) {
  let sql = "SELECT * FROM table_user WHERE id = :id";
  let params = { id: 1 };

  let result = await connection.queryAsync(sql, params);

  console.log(result);
  // [ { id: 1, name: 'Jack', age: 24 } ]
}

// 数据删除
async function deleteRow(connection) {
  let sql = "delete from table_user WHERE id = :id";
  let params = { id: 1 };

  let result = await connection.queryAsync(sql, params);

  console.log(result);
  // ResultSetHeader {
  //     fieldCount: 0,
  //     affectedRows: 1,
  //     insertId: 0,
  //     info: '',
  //     serverStatus: 2,
  //     warningStatus: 0
  //   }
}

(async () => {
  const connection = getConnection(process.env.DATABASE_URL);

  // await insertRow(connection);
  // await updateRow(connection);
  // await selectRow(connection);
  await deleteRow(connection);
  
  // 断开连接
  connection.end();
})();

promise

const mysql = require("mysql2/promise");

let config = {
  host: "127.0.0.1",
  port: 3306,
  user: "root",
  password: "123456",
  database: "data",
};

(async () => {
  const connection = await mysql.createConnection(config);

  let sql = "select * from tb_user";

  const [rows, fields] = await connection.query(sql);

  console.log(rows);

  await connection.end();
})();

输出

[
  {
    id: 1,
    name: '费阳',
    phone: '13777763170',
    profession: null,
    age: 27,
    status: 1,
    email: 'wyao@gmail'
  }
]

参考
Node.js 连接 MySQL-菜鸟教程