Node.js数据库操作：MySQL实战教程

安装 MySQL 模块

在 Node.js 中进行 MySQL 数据库操作，首先需要安装 mysql 模块。可以通过 npm（Node Package Manager）来完成安装。在项目目录下打开终端，执行以下命令：

npm install mysql

这会在项目的 node_modules 目录下安装 mysql 模块，并将其添加到 package.json 文件的依赖中。

连接 MySQL 数据库

安装好 mysql 模块后，就可以在 Node.js 代码中引入并使用它来连接数据库。以下是一个简单的连接示例：

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test'
});

connection.connect((err) => {
  if (err) {
    console.error('连接数据库失败: ', err.stack);
    return;
  }
  console.log('已连接到数据库，线程 ID 为: ', connection.threadId);
});

在上述代码中：

首先通过 require('mysql') 引入 mysql 模块。
使用 mysql.createConnection() 方法创建一个数据库连接对象，传入包含数据库连接信息的配置对象，其中 host 是数据库服务器地址，user 是用户名，password 是密码，database 是要连接的数据库名。
调用 connection.connect() 方法来建立实际的数据库连接。如果连接过程中出现错误，err 参数会包含错误信息，否则会在控制台打印出成功连接的信息以及连接的线程 ID。

执行 SQL 查询

连接到数据库后，最常见的操作就是执行 SQL 查询。下面分别介绍几种常见的查询操作。

查询数据

假设我们有一个 users 表，结构如下：

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  age INT
);

要查询所有用户数据，可以使用以下代码：

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test'
});

connection.connect();

const sql = 'SELECT * FROM users';

connection.query(sql, (err, results, fields) => {
  if (err) throw err;
  console.log('查询结果: ', results);
});

connection.end();

在上述代码中：

定义了一个 SQL 查询语句 SELECT * FROM users，表示选择 users 表中的所有数据。
使用 connection.query() 方法执行查询，第一个参数是 SQL 语句，第二个参数是回调函数。回调函数的 err 参数表示执行过程中可能出现的错误，results 是查询结果，fields 包含了查询结果的字段信息。
最后调用 connection.end() 方法关闭数据库连接。

如果要查询特定条件的数据，例如查询年龄大于 18 岁的用户，可以修改 SQL 语句如下：

const sql = 'SELECT * FROM users WHERE age > 18';

插入数据

要向 users 表中插入新数据，可以使用 INSERT INTO 语句。以下是示例代码：

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test'
});

connection.connect();

const newUser = { name: 'John', age: 25 };
const sql = 'INSERT INTO users SET?';

connection.query(sql, newUser, (err, result) => {
  if (err) throw err;
  console.log('插入成功，插入 ID 为: ', result.insertId);
});

connection.end();

在上述代码中：

定义了一个包含新用户数据的对象 newUser。
SQL 语句 INSERT INTO users SET? 中的 ? 是占位符，mysql 模块会根据第二个参数（这里是 newUser 对象）来替换占位符并构建实际的 SQL 语句。
执行查询后，result.insertId 会返回插入数据的自增 ID。

更新数据

更新 users 表中用户的信息，例如将名为 John 的用户年龄更新为 26，可以使用以下代码：

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test'
});

connection.connect();

const updateUser = { age: 26 };
const condition = { name: 'John' };
const sql = 'UPDATE users SET? WHERE?';

connection.query(sql, [updateUser, condition], (err, result) => {
  if (err) throw err;
  console.log('更新成功，受影响的行数为: ', result.affectedRows);
});

connection.end();

在上述代码中：

定义了 updateUser 对象包含要更新的字段和值，condition 对象定义了更新的条件。
SQL 语句 UPDATE users SET? WHERE? 中有两个占位符，mysql 模块会根据 query 方法的第二个参数数组中的两个对象分别替换这两个占位符。
result.affectedRows 返回受更新操作影响的行数。

删除数据

要删除 users 表中年龄大于 30 岁的用户，可以使用以下代码：

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test'
});

connection.connect();

const condition = { age: { $gt: 30 } };
const sql = 'DELETE FROM users WHERE?';

connection.query(sql, condition, (err, result) => {
  if (err) throw err;
  console.log('删除成功，受影响的行数为: ', result.affectedRows);
});

connection.end();

在上述代码中：

定义了 condition 对象来指定删除条件，这里使用了 $gt 表示大于。
SQL 语句 DELETE FROM users WHERE? 中的占位符会被 condition 对象替换。
result.affectedRows 返回受删除操作影响的行数。

处理事务

在数据库操作中，事务是一组作为单个逻辑工作单元执行的数据库操作。如果这组操作中的任何一个操作失败，整个事务将被回滚，即所有操作都不会生效。以下是一个在 Node.js 中使用 MySQL 进行事务处理的示例。

假设我们有两个表 accounts 和 transactions，accounts 表存储账户信息，transactions 表记录账户之间的交易。结构如下：

CREATE TABLE accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  balance DECIMAL(10, 2)
);

CREATE TABLE transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  from_account_id INT,
  to_account_id INT,
  amount DECIMAL(10, 2),
  FOREIGN KEY (from_account_id) REFERENCES accounts(id),
  FOREIGN KEY (to_account_id) REFERENCES accounts(id)
);

现在要实现一个从一个账户向另一个账户转账的功能，这个过程需要使用事务来确保数据的一致性。

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test'
});

connection.connect();

connection.beginTransaction((err) => {
  if (err) throw err;

  const fromAccountId = 1;
  const toAccountId = 2;
  const amount = 100.00;

  // 从转出账户扣除金额
  const sql1 = 'UPDATE accounts SET balance = balance -? WHERE id =?';
  connection.query(sql1, [amount, fromAccountId], (err, result1) => {
    if (err) {
      connection.rollback(() => {
        throw err;
      });
    }

    // 向转入账户增加金额
    const sql2 = 'UPDATE accounts SET balance = balance +? WHERE id =?';
    connection.query(sql2, [amount, toAccountId], (err, result2) => {
      if (err) {
        connection.rollback(() => {
          throw err;
        });
      }

      // 记录交易
      const sql3 = 'INSERT INTO transactions (from_account_id, to_account_id, amount) VALUES (?,?,?)';
      connection.query(sql3, [fromAccountId, toAccountId, amount], (err, result3) => {
        if (err) {
          connection.rollback(() => {
            throw err;
          });
        }

        connection.commit((err) => {
          if (err) {
            connection.rollback(() => {
              throw err;
            });
          }
          console.log('转账成功');
        });
      });
    });
  });
});

connection.end();

在上述代码中：

首先调用 connection.beginTransaction() 方法开始一个事务。
依次执行三个 SQL 操作：从转出账户扣除金额、向转入账户增加金额、记录交易。
如果任何一个操作出现错误，通过 connection.rollback() 方法回滚事务，即撤销之前执行的所有操作。
如果所有操作都成功，通过 connection.commit() 方法提交事务，使所有操作生效。

连接池

在实际应用中，如果频繁地创建和销毁数据库连接，会消耗大量的系统资源，影响应用的性能。连接池可以解决这个问题，它预先创建一定数量的数据库连接，并将这些连接保存在池中。当应用需要与数据库交互时，从连接池中获取一个连接，使用完毕后再将连接放回池中，而不是每次都创建和销毁连接。

在 Node.js 的 mysql 模块中，可以很方便地使用连接池。以下是一个示例：

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test'
});

pool.getConnection((err, connection) => {
  if (err) throw err;

  const sql = 'SELECT * FROM users';
  connection.query(sql, (err, results, fields) => {
    connection.release();
    if (err) throw err;
    console.log('查询结果: ', results);
  });
});

在上述代码中：

使用 mysql.createPool() 方法创建一个连接池，connectionLimit 参数指定了连接池中的最大连接数。
通过 pool.getConnection() 方法从连接池中获取一个连接，回调函数的第一个参数 err 表示获取连接过程中可能出现的错误，connection 是获取到的数据库连接对象。
执行完 SQL 查询后，调用 connection.release() 方法将连接释放回连接池，而不是关闭连接。这样可以复用连接，提高系统性能。

处理大结果集

当查询返回的结果集非常大时，如果一次性将所有数据加载到内存中，可能会导致内存不足的问题。在 Node.js 与 MySQL 交互中，可以通过流（Stream）的方式来逐行处理大结果集，避免内存问题。

以下是一个示例：

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test'
});

connection.connect();

const query = connection.query('SELECT * FROM large_table');

query
 .on('result', (row) => {
    // 处理每一行数据
    console.log('处理行数据: ', row);
  })
 .on('end', () => {
    console.log('所有数据处理完毕');
    connection.end();
  });

在上述代码中：

执行 connection.query() 方法返回一个 Query 对象。
通过监听 Query 对象的 result 事件，在每次接收到一行数据时，会触发该事件，在事件处理函数中可以处理每一行数据。
监听 end 事件，当所有数据都被处理完毕后，会触发该事件，在事件处理函数中可以执行一些收尾操作，例如关闭数据库连接。

安全性考虑

在进行数据库操作时，安全性至关重要。以下是一些常见的安全注意事项。

防止 SQL 注入

SQL 注入是一种常见的安全漏洞，攻击者通过在输入字段中插入恶意的 SQL 语句，从而改变 SQL 查询的逻辑，获取敏感数据或执行恶意操作。在 Node.js 中使用 mysql 模块时，可以通过使用占位符来防止 SQL 注入。

例如，假设我们有一个根据用户名查询用户信息的功能，错误的实现方式如下：

const username = req.query.username;
const sql = `SELECT * FROM users WHERE name = '${username}'`;
connection.query(sql, (err, results) => {
  //...
});

这种方式容易受到 SQL 注入攻击，如果用户输入的 username 为 '; DROP TABLE users; --，则实际执行的 SQL 语句将变为：

SELECT * FROM users WHERE name = ''; DROP TABLE users; --';

这会导致 users 表被删除。

正确的方式是使用占位符：

const username = req.query.username;
const sql = 'SELECT * FROM users WHERE name =?';
connection.query(sql, [username], (err, results) => {
  //...
});

这样 mysql 模块会对 username 进行转义，防止恶意 SQL 语句的执行。

密码安全

在存储用户密码时，绝不能以明文形式存储。应该使用密码哈希函数对密码进行加密存储，例如使用 bcrypt 模块。以下是一个简单的示例：

const bcrypt = require('bcrypt');

const password = 'userPassword';
bcrypt.hash(password, 10, (err, hash) => {
  if (err) throw err;
  // 将 hash 存储到数据库中
  console.log('加密后的密码: ', hash);
});

在用户登录验证时，使用 bcrypt.compare() 方法来比较用户输入的密码和数据库中存储的哈希值：

const inputPassword = req.body.password;
const storedHash = '从数据库中获取的哈希值';
bcrypt.compare(inputPassword, storedHash, (err, result) => {
  if (err) throw err;
  if (result) {
    console.log('密码匹配，登录成功');
  } else {
    console.log('密码不匹配，登录失败');
  }
});

数据库权限管理

确保数据库用户具有最小的权限，只授予其执行所需操作的权限。例如，如果一个应用只需要读取数据，那么数据库用户只应该有 SELECT 权限，而不应该有 INSERT、UPDATE、DELETE 等权限。同时，定期更新数据库用户的密码，以提高安全性。

性能优化

除了使用连接池和处理大结果集等方式来优化性能外，还有其他一些方面可以考虑。

索引优化

合理使用索引可以大大提高查询性能。例如，如果经常根据 users 表的 name 字段进行查询，可以为 name 字段创建索引：

CREATE INDEX idx_name ON users (name);

这样在执行 SELECT * FROM users WHERE name = 'John' 这样的查询时，数据库可以更快地定位到所需的数据。

查询优化

编写高效的 SQL 查询语句也非常重要。避免使用全表扫描，尽量使用覆盖索引，合理使用 JOIN 操作等。例如，在多表查询时，确保 JOIN 条件正确，避免产生笛卡尔积等低效的查询结果。

缓存

对于一些不经常变化的数据，可以使用缓存来减少数据库的压力。例如，可以使用 Redis 作为缓存，将经常查询的数据库结果缓存到 Redis 中，下次查询时先从缓存中获取数据，如果缓存中没有再查询数据库，并将结果更新到缓存中。

错误处理

在进行数据库操作时，要妥善处理各种可能出现的错误。除了在 connection.query() 等方法的回调函数中处理错误外，还可以对连接对象添加 error 事件监听器，以捕获连接过程中可能出现的错误。

connection.on('error', (err) => {
  console.error('数据库连接错误: ', err);
});

这样当连接出现意外断开等错误时，能够及时捕获并进行处理，避免应用程序出现未处理的异常而崩溃。

通过以上内容，你应该对 Node.js 中使用 MySQL 进行数据库操作有了较为全面的了解。从基本的连接、查询，到事务处理、安全和性能优化等方面，这些知识和技巧可以帮助你开发出高效、安全的数据库驱动的 Node.js 应用程序。在实际开发中，要根据具体的业务需求和场景，灵活运用这些技术，不断优化和改进应用的性能和稳定性。