Node.js MySQL
连接池、参数化查询、事务、以及防止 SQL 注入。
Key Points永远用参数化查询（`?` 占位），永远从连接池取连接，用完 `release`。
装驱动
Bash
npm i mysql2Use mysql2, not mysql: it supports Promises, prepared statements and pooling, and is faster. The mysql package has not been updated in years.
连接池
JavaScript
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: '127.0.0.1',
user: 'root',
password: 'secret',
database: 'demo',
waitForConnections: true,
connectionLimit: 10,
charset: 'utf8mb4',
});
const [rows] = await pool.query('SELECT 1 + 1 AS sum');
console.log(rows);Output
[ { sum: 2 } ]charset: 'utf8mb4' matters — MySQL's default utf8 is 3-byte only, so emoji fail with Incorrect string value.
增删改查
JavaScript
// 建表
await pool.query(`
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
`);
// 插入（参数化，防注入）
const [r] = await pool.query(
'INSERT INTO users (name, email) VALUES (?, ?)',
['张三', 'zhang@example.com']
);
console.log('新 id', r.insertId, '影响行数', r.affectedRows);
// 查询
const [users] = await pool.query('SELECT id, name FROM users WHERE name LIKE ?', ['%张%']);
console.log(users);
// 更新
await pool.query('UPDATE users SET name = ? WHERE id = ?', ['张三丰', 1]);
// 删除
await pool.query('DELETE FROM users WHERE id = ?', [1]);Output
新 id 1 影响行数 1
[ { id: 1, name: '张三' } ]Never concatenate SQL:
'SELECT * FROM users WHERE name = "' + name + '"'. A name of ' OR 1=1 -- dumps the table. ? placeholders are escaped by the driver — the only correct form.事务
JavaScript
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
await conn.query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [100, 1]);
await conn.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [100, 2]);
await conn.commit();
console.log('转账成功');
} catch (e) {
await conn.rollback();
console.error('回滚', e.message);
} finally {
conn.release(); // 必须放 finally，否则连接泄漏
}Output
转账成功A transaction must use one connection. Two pool.query calls may land on different connections, making the transaction meaningless. Hence getConnection() and release() in finally.
连接池参数
| Option | Meaning |
|---|---|
connectionLimit | 最大连接数，默认 10 |
waitForConnections | 池满时排队还是报错 |
queueLimit | 排队上限，0 表示不限 |
enableKeepAlive | 保持长连接，避免被服务端断开 |
timezone | 时区，建议 '+08:00' |
Bigger
connectionLimit is not better. MySQL's max_connections defaults to 151, so several service instances with 20 connections each will exhaust it. Budget as instances × pool < 0.8 × max_connections.装驱动
Bash
npm i mysql2用 mysql2 而不是 mysql：它支持 Promise、预处理语句和连接池，性能也更好。mysql 包已多年不更新。
连接池
JavaScript
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: '127.0.0.1',
user: 'root',
password: 'secret',
database: 'demo',
waitForConnections: true,
connectionLimit: 10,
charset: 'utf8mb4',
});
const [rows] = await pool.query('SELECT 1 + 1 AS sum');
console.log(rows);Output
[ { sum: 2 } ]charset: 'utf8mb4' 很重要 —— 默认的 utf8 在 MySQL 里只能存三字节字符，存 emoji 会报 Incorrect string value。
增删改查
JavaScript
// 建表
await pool.query(`
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
`);
// 插入（参数化，防注入）
const [r] = await pool.query(
'INSERT INTO users (name, email) VALUES (?, ?)',
['张三', 'zhang@example.com']
);
console.log('新 id', r.insertId, '影响行数', r.affectedRows);
// 查询
const [users] = await pool.query('SELECT id, name FROM users WHERE name LIKE ?', ['%张%']);
console.log(users);
// 更新
await pool.query('UPDATE users SET name = ? WHERE id = ?', ['张三丰', 1]);
// 删除
await pool.query('DELETE FROM users WHERE id = ?', [1]);Output
新 id 1 影响行数 1
[ { id: 1, name: '张三' } ]不要用字符串拼接 SQL：
'SELECT * FROM users WHERE name = "' + name + '"'。一个 name = "' OR 1=1 --" 就能拖走全表。? 占位符由驱动做转义，是唯一正确的写法。事务
JavaScript
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
await conn.query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [100, 1]);
await conn.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [100, 2]);
await conn.commit();
console.log('转账成功');
} catch (e) {
await conn.rollback();
console.error('回滚', e.message);
} finally {
conn.release(); // 必须放 finally，否则连接泄漏
}Output
转账成功事务必须用同一个连接。从池里 query 两次可能落到两个连接上，事务就形同虚设。所以要先 getConnection()，最后 release()。
连接池参数
| 参数 | 含义 |
|---|---|
connectionLimit | 最大连接数，默认 10 |
waitForConnections | 池满时排队还是报错 |
queueLimit | 排队上限，0 表示不限 |
enableKeepAlive | 保持长连接，避免被服务端断开 |
timezone | 时区，建议 '+08:00' |
connectionLimit 不是越大越好。MySQL 的 max_connections 默认 151，多个服务实例各自开 20 个连接就会打满。按「实例数 × 池大小 < max_connections × 0.8」来算。