【笔记】Nodejs操作MySQL数据库

发表于 更新于 阅读次数：

前言

Nodejs操作MySQL数据库

mysql

下载依赖

1
npm install mysql

获取数据库连接

不使用连接池

1
2
3
4
5
6
7
8
9
const mysql = require("mysql");

const connection = mysql.createConnection({
  host: "127.0.0.1",
  port: 3306,
  user: "root",
  password: "",
  database: ""
});
销毁数据库连接
1
connection.destroy();

使用连接池

connectionLimit：最大连接数

1
2
3
4
5
6
7
8
9
10
const mysql = require("mysql");

const connection = mysql.createPool({
  host: "127.0.0.1",
  port: 3306,
  user: "root",
  password: "",
  database: "",
  connectionLimit: 1
});
销毁数据库连接
1
connection.end();

对数据的操作

results.affectedRows：受影响行数

不使用预处理

回调模式
新增
1
2
3
connection.query("INSERT INTO <table> (<field_name>) VALUES (<value>)", function (err, results, fields) {
  ...
});
1
2
3
connection.query("INSERT INTO <table> (<field_name>) VALUES (?)", ["<value>"], function (err, results, fields) {
  ...
});
1
2
3
connection.query("INSERT INTO <table> SET ?", {"<field_name>": "<value>"}, function (err, results, fields) {
  ...
});
删除
1
2
3
connection.query("DELETE FROM <table> WHERE <field_name>=<value>", function (err, results, fields) {
  ...
});
1
2
3
connection.query("DELETE FROM <table> WHERE <field_name>=?", ["<value>"], function (err, results, fields) {
  ...
});
修改
1
2
3
connection.query("UPDATE <table> SET <field_name>=<value>", function (err, results, fields) {
  ...
});
1
2
3
connection.query("UPDATE <table> SET <field_name>=?", ["<value>"], function (err, results, fields) {
  ...
});
1
2
3
connection.query("UPDATE <table> SET ?", {"<field_name>": "<value>"}, function (err, results, fields) {
  ...
});
查询
1
2
3
connection.query("SELECT * FROM <table> WHERE <field_name>=<value>", function (err, results, fields) {
  ...
});
1
2
3
connection.query("SELECT * FROM <table> WHERE <field_name>=?", ["<value>"], function (err, results, fields) {
  ...
});

mysql2

下载依赖

1
npm install mysql2

获取数据库连接

不使用连接池

1
2
3
4
5
6
7
8
9
const mysql2 = require("mysql2");

const connection = mysql2.createConnection({
  host: "127.0.0.1",
  port: 3306,
  user: "root",
  password: "",
  database: ""
});
销毁数据库连接
1
connection.destroy();

使用连接池

connectionLimit：最大连接数

1
2
3
4
5
6
7
8
9
10
const mysql2 = require("mysql2");

const connection = mysql2.createPool({
  host: "127.0.0.1",
  port: 3306,
  user: "root",
  password: "",
  database: "",
  connectionLimit: 1
});
销毁数据库连接
1
connection.end();

对数据的操作

不使用预处理

回调模式

传送门

Promise
新增
1
2
3
4
5
connection.promise().query("INSERT INTO <table> (<field_name>) VALUES (<value>)").then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
1
2
3
4
5
connection.promise().query("INSERT INTO <table> (<field_name>) VALUES (?)", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
1
2
3
4
5
connection.promise().query("INSERT INTO <table> SET ?", {"<field_name>": "<value>"}).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
删除
1
2
3
4
5
connection.promise().query("DELETE FROM <table> WHERE <field_name>=<value>").then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
1
2
3
4
5
connection.promise().query("DELETE FROM <table> WHERE <field_name>=?", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
修改
1
2
3
4
5
connection.promise().query("UPDATE <table> SET <field_name>=<value>").then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
1
2
3
4
5
connection.promise().query("UPDATE <table> SET <field_name>=?", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
1
2
3
4
5
connection.promise().query("UPDATE <table> SET ?", {"<field_name>": "<value>"}).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
查询
1
2
3
4
5
connection.promise().query("SELECT * FROM <table> WHERE <field_name>=<value>").then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
1
2
3
4
5
connection.promise().query("SELECT * FROM <table> WHERE <field_name>=?", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});

使用预处理

回调模式
新增
1
2
3
connection.execute("INSERT INTO <table> (<field_name>) VALUES (?)", ["<value>"], function (err, results, fields) {
  ...
});
1
2
3
connection.execute("INSERT INTO <table> SET ?", {"<field_name>": "<value>"}, function (err, results, fields) {
  ...
});
删除
1
2
3
connection.execute("DELETE FROM <table> WHERE <field_name>=?", ["<value>"], function (err, results, fields) {
  ...
});
修改
1
2
3
connection.execute("UPDATE <table> SET <field_name>=?", ["<value>"], function (err, results, fields) {
  ...
});
1
2
3
connection.execute("UPDATE <table> SET ?", {"<field_name>": "<value>"}, function (err, results, fields) {
  ...
});
查询
1
2
3
connection.execute("SELECT * FROM <table> WHERE <field_name>=?", ["<value>"], function (err, results, fields) {
  ...
});
Promise
新增
1
2
3
4
5
connection.promise().execute("INSERT INTO <table> (<field_name>) VALUES (?)", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
1
2
3
4
5
connection.promise().execute("INSERT INTO <table> SET ?", {"<field_name>": "<value>"}).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
删除
1
2
3
4
5
connection.promise().execute("DELETE FROM <table> WHERE <field_name>=?", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
修改
1
2
3
4
5
connection.promise().execute("UPDATE <table> SET <field_name>=?", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
1
2
3
4
5
connection.promise().execute("UPDATE <table> SET ?", {"<field_name>": "<value>"}).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
查询
1
2
3
4
5
connection.promise().execute("SELECT * FROM <table> WHERE <field_name>=?", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});

mysql2/promise

下载依赖

1
npm install mysql2

获取数据库连接

不使用连接池

1
2
3
4
5
6
7
8
9
const mysql2Promise = require("mysql2/promise");

const connection = mysql2Promise.createConnection({
  host: "127.0.0.1",
  port: 3306,
  user: "root",
  password: "",
  database: ""
});
销毁数据库连接
1
connection.destroy();

使用连接池

connectionLimit：最大连接数

1
2
3
4
5
6
7
8
9
10
const mysql2 = require("mysql2/promise");

const connection = mysql2Promise.createPool({
  host: "127.0.0.1",
  port: 3306,
  user: "root",
  password: "",
  database: "",
  connectionLimit: 1
});
销毁数据库连接
1
connection.end();

对数据的操作

不使用预处理

Promise
新增
1
2
3
4
5
connection.query("INSERT INTO <table> (<field_name>) VALUES (?)", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
1
2
3
4
5
connection.query("INSERT INTO <table> SET ?", {"<field_name>": "<value>"}).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
删除
1
2
3
4
5
connection.query("DELETE FROM <table> WHERE <field_name>=?", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
修改
1
2
3
4
5
connection.query("UPDATE <table> SET <field_name>=?", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
1
2
3
4
5
connection.query("UPDATE <table> SET ?", {"<field_name>": "<value>"}).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
查询
1
2
3
4
5
connection.query("SELECT * FROM <table> WHERE <field_name>=?", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});

使用预处理

Promise
新增
1
2
3
4
5
connection.execute("INSERT INTO <table> (<field_name>) VALUES (?)", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
1
2
3
4
5
connection.execute("INSERT INTO <table> SET ?", {"<field_name>": "<value>"}).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
删除
1
2
3
4
5
connection.execute("DELETE FROM <table> WHERE <field_name>=?", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
修改
1
2
3
4
5
connection.execute("UPDATE <table> SET <field_name>=?", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
1
2
3
4
5
connection.execute("UPDATE <table> SET ?", {"<field_name>": "<value>"}).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});
查询
1
2
3
4
5
connection.execute("SELECT * FROM <table> WHERE <field_name>=?", ["<value>"]).then(function ([results, fields]) {
  ...
}).catch(function (err) {
  ...
});

完成

参考文献

哔哩哔哩——黑马程序员
CSDN——默默花上开