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

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

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

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.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)
    )
  }
  // 查询列表
  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();
})();