49. MySQL 增删改查

连接池搭好了，这一章我们把 CRUD（Create, Read, Update, Delete）逐个过一遍。重点是预处理语句，这是防止 SQL 注入的银弹。

前置准备

确保你已经有一个可用的 MySQL 实例和数据库。用 Docker 起一个很省事：

docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_DATABASE=demo mysql:8

db.mjs（沿用上一章的封装）：

import mysql from 'mysql2/promise';

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

Create：插入数据

import { pool } from './db.mjs';

async function createUser(name, email) {
  const [result] = await pool.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    [name, email]
  );
  console.log('插入 ID：', result.insertId);
  return result.insertId;
}

await createUser('Alice', 'alice@example.com');

execute() 的第一个参数是带 ? 占位符的 SQL，第二个参数是数组，按顺序填充。驱动会自动转义，杜绝注入。

Read：查询数据

查询多条

const [rows] = await pool.execute('SELECT * FROM users');
console.log(rows);
// [{ id: 1, name: 'Alice', email: 'alice@example.com', ... }]

按条件查询单条

const [rows] = await pool.execute(
  'SELECT * FROM users WHERE id = ?',
  [1]
);
const user = rows[0] || null;

只查特定字段

const [rows] = await pool.execute(
  'SELECT id, name FROM users WHERE email = ?',
  ['alice@example.com']
);

Update：更新数据

async function updateUser(id, name) {
  const [result] = await pool.execute(
    'UPDATE users SET name = ? WHERE id = ?',
    [name, id]
  );
  console.log('影响行数：', result.affectedRows);
  return result.affectedRows;
}

await updateUser(1, 'Alice Wang');

Delete：删除数据

async function deleteUser(id) {
  const [result] = await pool.execute(
    'DELETE FROM users WHERE id = ?',
    [id]
  );
  return result.affectedRows;
}

预处理语句防注入

为什么要反复强调 ? 占位符？看一个反面教材：

// 千万不要这么写！
const sql = `SELECT * FROM users WHERE email = '${email}'`;

如果用户输入 email = "' OR '1'='1"，拼接出来的 SQL 变成：

SELECT * FROM users WHERE email = '' OR '1'='1'

条件永远为真，全表数据都泄露了。这就是 SQL 注入。

用 execute() 的占位符机制，用户的输入只会被当作字符串值处理，不会参与 SQL 语法解析：

// 安全写法
await pool.execute('SELECT * FROM users WHERE email = ?', ["' OR '1'='1"]);
// 实际执行的 SQL 中，这段字符串被转义，攻击失效

批量插入

一次性插多条，用 INSERT INTO ... VALUES ?：

const users = [
  ['Bob', 'bob@example.com'],
  ['Carol', 'carol@example.com']
];

await pool.query('INSERT INTO users (name, email) VALUES ?', [users]);

注意这里用 pool.query() 而不是 execute()，因为 execute() 不支持批量占位符的语法扩展。query() 同样会预处理，安全性没问题。

事务

多条 SQL 要么全成功，要么全回滚，用事务包裹：

const conn = await pool.getConnection();
try {
  await conn.beginTransaction();

  await conn.execute('INSERT INTO accounts (user_id, balance) VALUES (?, ?)', [1, 100]);
  await conn.execute('UPDATE accounts SET balance = balance - ? WHERE user_id = ?', [100, 2]);

  await conn.commit();
} catch (err) {
  await conn.rollback();
  throw err;
} finally {
  conn.release();
}

getConnection() 从池子里拿一个专用连接，release() 用完后归还。中间别用 await pool.execute()，否则每条语句可能走不同的连接，事务就失效了。