Skip to main content

Node.js 连接操作 MySQL

一、Node.js 连接 MySQL​

1、简单示例​

初始化项目：

npm init -y

安装 MySQL2：

npm install --save mysql2

新建 index.js：

index.js
// 导入模块
const mysql = require('mysql2');

// 创建一个数据库连接
const connection = mysql.createConnection({
  // 以下为建立的连接详情
  host: 'localhost',
  user: 'root',
  password: 'xxx',
  port: 3306,
  database: 'myblog'
});

// 简单查询
connection.query(
  'SELECT * FROM users',
  function (err, res) {
    if (err) throw error
    console.log(res); // 结果集
  }
);

// 关闭连接
connection.end();

运行：

node index.js

打印结果：

2、连接选项​

createConnection 可以设置以下选项：

选项说明默认值
host要连接的数据库主机名localhost
port主机端口号3306
localAddress用于 TCP 连接的 IP 地址可选
socketPath链接到 unix 域的路径，在使用 host 和 port 时会被忽略可选
userMySQL 用户名-
passwordMySQL 密码-
database链接到的数据库名可选
charset连接的字符集UTF8_GENERAL_CI
timezoneMySQL 服务器上配置的时区local
connectTimeout连接超时时间，单位毫秒10000
stringifyObjects是否序列化（字符串化）对象false
insecureAuth是否允许旧的身份验证方法连接到数据库实例false
typeCast是否将 column 值转为本地 JS 类型列值true
queryFormat自定义的查询语句格式化函数，点击查看自定义格式-
supportBigNumbers数据库处理大数字（BIGINT 和 DECIMAL）时启用false
bigNumberStrings启用 supportBigNumbers 和 bigNumberStrings 强制大数字（BIGINT 和 DECIMAL）始终以字符串返回false
dateStrings强制日期类型（TIMESTAMP、DATETIME、DATE）作为字符串返回，而非 JS Date 对象false
debug是否开启调试false
trace生成堆栈跟踪 Error 以包括库入口的调用站点（“长堆栈跟踪”）true
multipleStatements每个查询允许多个 MySQL 语句false
flags链接标志-
ssl带有 ssl 参数的对象或包含 ssl 配置文件名的字符串-

3、建立连接​

建立连接的推荐方法：

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'example.org',
  user: 'xxx',
  password: 'xxx'
});

connection.connect(function (err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

也可以通过 query 调用查询来隐式建立连接 ▼

4、query API​

4-1、基本用法​

.query(sqlString, callback)

参数：

  • sqlString：SQL 语句；
  • callback：回调，参数如下 ▼
    • error：如果语句执行期间发生错误，则存储详细错误；
    • results：包含查询的结果；
    • fields：包含结果字段信息。

示例：

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'example.org',
  user: 'xxx',
  password: 'xxx'
});

connection.query(`SELECT * FROM users WHERE username = 'aaa'`, function (error, results, fields) {
  if (error) throw error;
  // ...
});

4-2、使用占位符​

.query(sqlString, values, callback)

使用单个占位符：

connection.query(`SELECT * FROM users WHERE username = ?`, ['aaa'], function (error, results, fields) {
  if (error) throw error;
  // ...
});

使用多个占位符：

connection.query(`SELECT * FROM users WHERE username = ? AND password = ?`, ['aaa', 111], function (error, results, fields) {
  if (error) throw error;
  // ...
});

与 connection.escape() 的方式一样，可以避免 SQL 注入攻击。

注意

语句所有 ? 都会被替换，包括注释和字符串中包含的语句。不同的值类型会以不同方式进行转义，例如：

  • 数字保持不变；
  • 布尔值转换为 true/false
  • 日期对象将转换为 'YYYY-mm-dd HH:ii:ss' 字符串；
  • 缓冲区转换为十六进制字符串，例如 X'0fa5'
  • 字符串安全地逃脱；
  • 数组变成列表，例如 ['a', 'b'] 变成 'a', 'b'
  • 嵌套数组被转换为分组列表（对于批量插入），例如 [['a', 'b'], ['c', 'd']] 变成 ('a', 'b'), ('c', 'd')
  • 具有 toSqlString 方法的对象将 .toSqlString() 被调用，并且返回的值将用作原始 SQL；
  • 对象被转换为对象上 key = 'val' 每个可枚举属性的对。如果属性的值是函数，则跳过它; 如果属性的值是一个对象，则在其上调用 toString() 并使用返回的值；
  • undefined/null 转换为 NULL
  • NaN/Infinity 保持原样，MySQL 不支持并且会报错。

4-3、使用高级选项​

.query(options, callback)

示例：

connection.query({
  sql: 'SELECT * FROM `books` WHERE `author` = ?',
  timeout: 40000, // 40s
  values: ['David']
}, function (error, results, fields) {
  // ...
});

点击查看详情

5、关闭连接​

关闭数据库连接有两种方法。

end() 方法确保在数据库连接关闭前始终执行所有剩余的查询：

conn.end(function (err) {
  // 处理回调
});

如果要立即强制连接，可以使用 destroy() 方法，它保证不再为连接触发回调或事件：

// 没有回调
conn.destroy()

6、连接池​

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；换句话说，就是通过重用以前的连接来减少连接到 MySQL 服务器的时间。

创建连接池如下：

// 导入模块
const mysql = require('mysql2');

// 创建连接池，设置连接池的参数
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'test',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

该池不会预先创建所有连接，而是根据需要创建它们，直到达到连接限制。

可以像直接连接一样使用池：

pool.query('SELECT * FROM users;', function (err, results, fields) {
  if (err) {
    // 异常处理
    throw err;
  } else {
    // 处理查询数据
    console.log(results);
  }
});

要从池中获取连接，可以使用 getConnection() 方法：

pool.getConnection(function (err, connection) {
  // execute query
  // ...
});

要在完成连接后将其连接到池，可以调用 connection.release()：

// 使用连接
pool.getConnection(function (err, connection) {
  connection.query('SELECT * FROM users;', function (err, results, fields) {
    // 完成连接后，将其释放
    connection.release();

    // 不要在这里使用连接，它已经返回到池中
    if (err) {
      throw err;
    } else {
      console.log(results);
    }
  });
});

点击查看连接池配置及事件

二、Node.js 操作 MySQL​

1、创建 MySQL 表​

创建一个名为 users 的表：

// 连接数据库
connection.connect(function (err) {
  if (err) {
    return console.error('error: ' + err.message);
  }

  const createToDos = `CREATE TABLE
  IF
    NOT EXISTS users (
      id INT PRIMARY KEY auto_increment,
      username VARCHAR ( 20 ) NOT NULL,
      password varchar ( 20 ) NOT NULL
    )`;

  connection.query(createToDos, function (err, results, fields) {
    if (err) {
      throw err;
    }
  });

  connection.end(function (err) {
    if (err) {
      return console.log(err.message);
    }
  });
});

创建的表结构如下：

表数据如下：

2、表数据插入（增）​

点击查看 MySQL 插入语句

2-1、插入一条数据​

const sql = `INSERT INTO users (username, password)
VALUES ('aaa', 111)`;

connection.query(sql);
connection.end();

执行结果：

2-2、占位符插入并返回 ID​

const sql = `INSERT INTO users (username, password)
VALUES (?, ?)`;
const val = ['bbb', 222];

connection.query(sql, val, (err, results, fields) => {
  if (err) {
    throw err;
  }
  console.log(results)
  console.log('Id: ' + results.insertId);
});

connection.end();

执行结果：

控制台打印结果：

2-3、插入多条数据​

const sql = `INSERT INTO users (username, password)
VALUES ?`;
const val = [
  ['ccc', 333],
  ['ddd', 444],
  ['eee', 555]
];

connection.query(sql, [val], (err, results, fields) => {
  // ...
});

connection.end();

执行结果：

3、表数据删除（删）​

点击查看 MySQL 删除语句

const sql = `DELETE FROM users
WHERE id = ?`;

// 清除 ID 为 1 的数据
connection.query(sql, 1, (err, results, fields) => {
  // ...
});

connection.end();

执行结果：

同理可以用数组实现多条数据的删除。

4、表数据更改（改）​

点击查看 MySQL 修改语句

const sql = `UPDATE users
SET username = 'xxx'
WHERE id = ?`;

// 修改 ID 为 2 的数据
connection.query(sql, 2, (err, results, fields) => {
  // ...
});

connection.end();

执行结果：

5、表数据查询（查）​

点击查看 MySQL 查找语句

写法参照上面 query API

示例：

const sql = `SELECT * FROM users
WHERE id = ?`;

// 查找 ID 为 2 的数据
connection.query(sql, 2, (err, results, fields) => {
  if (err) {
    throw err;
  }
  console.log(results)
});

connection.end();

打印输出：