【Express】mysql2 操作 MySQL 数据库

db.config.yaml

db:
  user: root
  password: 'root'
  host: localhost
  port: 3306
  database: my_db_01

import express from "express";
import fs from "fs";
import mysql2 from "mysql2/promise";
import jsyaml from 'js-yaml';

const app = express()

const yaml = fs.readFileSync('./db.config.yaml', 'utf8')
// 将yaml 解析为一个对象
const config = jsyaml.load(yaml)
// console.log(config)
// mysql2.createConnection({
//     host: 'localhost',
//     user: 'root',
//     password: 'root',
//     database: 'my_db_01'
// })
const sql = await mysql2.createConnection({
    ...config.db
})
app.get('/', async (req, res) => {
    const [data] = await sql.query('select * from user')
    res.send(data)
})

app.listen(3000, () => {
    console.log('3000端口服务端已启动')
})

注意：异步请求数据库如果import mysql2 from "mysql2";会报错：You have tried to call .then(), .catch(), or invoked await on the result of query that is not a promise, which is a programming error. Try calling con.promise().query(), or require ('mysql2/promise') instead of 'mysql2' for a promise-compatible version of the query interface. 此时改为：import mysql2 from "mysql2/promise";即可

增删改查业务的实现：

import express from "express";
import fs from "fs";
import mysql2 from "mysql2/promise";
import jsyaml from 'js-yaml';

const app = express()
app.use(express.json())
const yaml = fs.readFileSync('./db.config.yaml', 'utf8')
// 将yaml 解析为一个对象
const config = jsyaml.load(yaml)
// console.log(config)
// mysql2.createConnection({
//     host: 'localhost',
//     user: 'root',
//     password: 'hxg20021126',
//     database: 'my_db_01'
// })
const sql = await mysql2.createConnection({
    ...config.db
})
//查询全部
app.get('/', async (req, res) => {
    const [data] = await sql.query('select * from user')
    res.send(data)
})
//单个查询
app.get('/user/:id', async (req, res) => {
    // const [data]  = await sql.query(`// select * from user where id=${req.params.id}`) //两种写法
    const [data] = await sql.query(`select * from user where id = ?`, [req.params.id])
    res.send(data)
})
//新增接口
app.post('/create', async (req, res) => {
    const {name, age, address} = req.body
    await sql.query(`insert into user(NAME, AGE, ADDRESS) values(?,?,?)`, [name, age, address])
    res.send({ok: 1})
})
//编辑
app.post('/update',async (req,res)=> {
    const {name, age, address,id} = req.body
    await sql.query(`update user set NAME=?, AGE=?, ADDRESS=? where id=?`,[name, age, address,id])
    res.send({ok: 1})
})
//删除
app.post('/delete',async (req,res)=> {
    const {id} = req.body
    await sql.query(`delete from user where id=?`,[id])
    res.send({ok: 1})
})
app.listen(3000, () => {
    console.log('3000端口服务端已启动')
})

### 查询所有
GET http://localhost:3000/ HTTP/1.1

<> 2024-03-12T211116.200.json


### 查询单个
GET http://localhost:3000/user/1 HTTP/1.1

<> 2024-03-12T213049.200.json
<> 2024-03-12T213038.200.json

### 新增
POST http://localhost:3000/create HTTP/1.1
Content-Type: application/json

{
  "name": "zhangsan",
  "age": 12,
  "address": "北京"
}

<> 2024-03-12T230815.200.json

### 更新
POST http://localhost:3000/update HTTP/1.1
Content-Type: application/json

{
  "name": "lisi",
  "age": 21,
  "address": "上海",
  "id": 4
}

<> 2024-03-12T231432.200.json

### 删除
POST http://localhost:3000/delete HTTP/1.1
Content-Type: application/json

{
  "id": 4
}

<> 2024-03-12T231943.200.json