Node.js mysql2 连接 MySQL

npm install mysql2 express js-yaml

数据库配置 db.config.yaml

db:
  host: localhost
  port: 3306
  user: root
  password: '123456'       # 一定要用字符串
  database: xiaoman

Express + mysql2 完整 CRUD

import mysql2 from 'mysql2/promise'
import fs from 'node:fs'
import jsyaml from 'js-yaml'
import express from 'express'

// 读取配置
const yaml = fs.readFileSync('./db.config.yaml', 'utf8')
const config = jsyaml.load(yaml)

// 创建连接（promise 版本）
const sql = await mysql2.createConnection({ ...config.db })

const app = express()
app.use(express.json())

// 查询全部
app.get('/', async (req, res) => {
  const [data] = await sql.query('SELECT * FROM user')
  res.send(data)
})

// 单个查询（参数化防注入）
app.get('/user/:id', async (req, res) => {
  const [row] = await sql.query(`SELECT * FROM user WHERE id = ?`, [req.params.id])
  res.send(row)
})

// 新增
app.post('/create', async (req, res) => {
  const { name, age, hobby } = req.body
  await sql.query(`INSERT INTO user(name, age, hobby) VALUES (?, ?, ?)`, [name, age, hobby])
  res.send({ ok: 1 })
})

// 更新
app.post('/update', async (req, res) => {
  const { name, age, hobby, id } = req.body
  await sql.query(`UPDATE user SET name = ?, age = ?, hobby = ? WHERE id = ?`, [name, age, hobby, id])
  res.send({ ok: 1 })
})

// 删除
app.post('/delete', async (req, res) => {
  await sql.query(`DELETE FROM user WHERE id = ?`, [req.body.id])
  res.send({ ok: 1 })
})

app.listen(3000)

关键点

• 使用 mysql2/promise 版本，可用 async/await
• ? 占位符传参防止 SQL 注入，不要直接拼接字符串
• createConnection 创建单连接，生产环境使用 createPool 连接池