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

文档：

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

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

https://www.planetscale.com

表结构

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:[email protected]/data'

代码实现

import "dotenv/config";
import mysql from "mysql2";

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

  // 自定义占位符
  // https://www.npmjs.com/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)
    );
  }; 
  
  // 异步查询
  connection.asyncQuery = function (sql, params) {
    
    return new Promise((resovle, reject) => {
    
      connection.query(sql, params, function (err, result, fields) {
    
        if (err) {
    
          reject(err);
        } else {
    
          resovle(result);
        }
      });
    });
  };

  return connection;
}

查询示例

// 数据插入
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.asyncQuery(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.asyncQuery(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.asyncQuery(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.asyncQuery(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();
})();

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