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
);

  

 

  
1
  
2
  
3
  
4
  
5
 

安装依赖

node -v
v16.14.0

pnpm i dotenv mysql2 

  

 

  
1
  
2
  
3
  
4
 

package.json

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

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
 

配置文件
.env

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

  

 

  
1
 

代码实现

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;
}


  

 

  
 
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
  
29
  
30
  
31
  
32
  
33
  
34
  
35
  
36
  
37
 

查询示例

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


  

 

  
 
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
  
29
  
30
  
31
  
32
  
33
  
34
  
35
  
36
  
37
  
38
  
39
  
40
  
41
  
42
  
43
  
44
  
45
  
46
  
47
  
48
  
49
  
50
  
51
  
52
  
53
  
54
  
55
  
56
  
57
  
58
  
59
  
60
  
61
  
62
  
63
  
64
  
65
  
66
  
67
  
68
  
69
  
70
  
71
  
72
  
73
  
74
  
75
  
76
  
77
  
78
  
79
 

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

文章来源: pengshiyu.blog.csdn.net，作者：彭世瑜，版权归原作者所有，如需转载，请联系作者。

原文链接：pengshiyu.blog.csdn.net/article/details/126232027