全栈技术博客-文章详情

koa操作mysql数据库

最后更新时间：2025-08-25 20:53:07

koa mysql nodejs

### 一、数据库连接
```js
const mysqlDb = require("mysql");
const config = require("./dbconfig");
let options = {
  host: config.config.db_host,    --主机名
  user: config.config.db_user,	  --用户名	
  password: config.config.db_passwd,	--用户密码
  database: config.config.db_name,	--数据库名
  multipleStatements: true,		--启用多线池
};
 var pool = mysqlDb.createPool(options);
 exports.query = function (sql, values) {
    return new Promise((resolve, reject) => {
      pool.getConnection(function (err, connection) {
        if (err) {
          reject(err)
          //console.log(err, "数据库连接失败");
          resolve({
            status: 500,
          });
        } else {
          //console.log("数据库连接成功");
          connection.query(sql, values, (err, results) => {
            if (err) {
              reject(err);
              resolve({
                status: 400
              });
            } else {
              connection.release();
              resolve({
                status: 200,
                results,
              });
              //resolve(rows)
            }
            //connection.release() // 释放连接池
          });
        }
      })
    })
  }
```
### 二、数据库操作

* --user 数据库表名
#### 1.数据库查询

##### 精准查询  
```sql
select * from user where title   --查询一title查询所有数据
select * from user where title='今日新闻'   --查询title为今日新闻的一条数据
```
##### 多条件查询
```sql
select * from user where title='今日新闻' and age='28'
select * from user where title='今日新闻' or age='28'
```
##### 模糊查询
```sql
select * from user where title like '%龙%'   --查询出所有含有“龙”的记录
select * from user where realname like '%文%' and realname like '%龙%'  --所查询字段 + like '%龙%' and 所查询字段 + like '%文%'
    
```

#### 2、具体数据库查询
```js
  let ctx_query = ctx.request.body; 			--接受post取值
 
  let pageSize = ctx_query.size-1;			--每页显示的条数
  let page = ctx_query.page ? (ctx_query.page - 1) * pageSize : 0;   --页数
  //查询数据库
  let sql = "select * from list"; 			--不带分页的查询
  let total = await db.query(sql); 			--总页数

if (ctx_query.country==''&& ctx_query.title) {
    sql += ` where title like '%${ctx_query.title}%' limit ${page},${pageSize}`;
  }else if (ctx_query.country && ctx_query.title=='') {
    sql += ` where country like '%${ctx_query.country}%' limit ${page},${pageSize}`;
  }else if (ctx_query.country == "" && ctx_query.title=="") {
    sql += ` limit ${page},${pageSize}`;
  } else {
    sql += ` where title like '%${ctx_query.title}%' and country like'%${ctx_query.country}%' limit ${page},${pageSize}`;
  }
  console.log('sql',sql)
  const result = await db.query(sql);
```

#### 3.数据库插入数据
```js
INSERT INTO users ( id, user,passwd) VALUES (12,'zhaofang','111111' );
let query = ctx.request.body; 	--接受post传值
let sql = "INSERT INTO list";   	--list为表名
sql += ` (title, date,content,name,country,city) VALUES ('${query.title}','${query.date}','${query.content}','${query.name}','${query.country}','${query.city}' )`;
const results = await db.query(sql) 	--连接数据库操作
```

#### 4.数据库修改数据
```js
let query = ctx.request.body;		--接受post传值
let sql = "UPDATE list SET";
sql += ` title='${query.title}',date='${query.date}',content='${query.content}',name='${query.name}',country='${query.country}',city='${query.city}' where id='${query.id}'`;
const results = await db.query(sql) 	--连接数据库操作
```
#### 5.数据库删除
```js
let query = ctx.query; 		--接受del传值
let sql = `DELETE FROM list where id='${query.id}'`;
const results = await db.query(sql);
```

假设主键或者唯一索引为 good_id
收到客户端{pageNo:5,pagesize:10}
select * from table where good_id > (pageNo-1)*pageSize limit pageSize;
–返回good_id为40到50之间的数据

基于数据再排序
当需要返回的信息为顺序或者倒序时，对上面的语句基于数据再排序。order by ASC/DESC 顺序或倒序 默认为顺序

select * from table where good_id > (pageNo-1)*pageSize order by good_id limit pageSize;
–返回good_id为40到50之间的数据,数据依据good_id顺序排列

koa操作mysql数据库

留言条数·0