koacms（二） mysql数据表创建以及数据库连接-CSDN博客

本文链接：https://blog.csdn.net/weixin_52380389/article/details/142686456

1、内容分类表

CREATE TABLE `cms_content_sort` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号',
  `acode` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '区域编码',
  `mcode` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '内容模型编码',
  `pcode` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '父编码',
  `scode` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '分类编码',
  `name` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '分类名称',
  `listtpl` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT '列表页模板',
  `contenttpl` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT '内容页模板',
  `status` char(1) CHARACTER SET utf8 NOT NULL DEFAULT '1' COMMENT '状态',
  `outlink` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '转外链接',
  `subname` varchar(200) CHARACTER SET utf8 NOT NULL COMMENT '附加名称',
  `ico` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '分类缩略图',
  `pic` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '分类大图',
  `title` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT 'seo标题',
  `keywords` varchar(200) CHARACTER SET utf8 NOT NULL COMMENT '分类关键字',
  `description` varchar(500) CHARACTER SET utf8 NOT NULL COMMENT '分类描述',
  `filename` varchar(30) CHARACTER SET utf8 NOT NULL COMMENT '自定义文件名',
  `sorting` int(10) unsigned NOT NULL DEFAULT '255' COMMENT '排序',
  `create_user` varchar(30) CHARACTER SET utf8 NOT NULL COMMENT '创建人员',
  `update_user` varchar(30) CHARACTER SET utf8 NOT NULL COMMENT '更新人员',
  `create_time` int(11) NOT NULL COMMENT '创建时间',
  `update_time` int(11) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ay_content_sort_scode` (`scode`),
  KEY `ay_content_sort_pcode` (`pcode`),
  KEY `ay_content_sort_acode` (`acode`),
  KEY `ay_content_sort_mcode` (`mcode`),
  KEY `ay_content_sort_filename` (`filename`),
  KEY `ay_content_sort_sorting` (`sorting`)
) ENGINE=MyISAM AUTO_INCREMENT=172 DEFAULT CHARSET=utf8mb4;

2、内容表

CREATE TABLE `cms_content` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号',
  `acode` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '区域',
  `scode` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '内容栏目',
  `subscode` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '副栏目',
  `title` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '标题',
  `subtitle` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '副标题',
  `filename` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT '自定义文件名',
  `author` varchar(30) CHARACTER SET utf8 NOT NULL COMMENT '作者',
  `source` varchar(30) CHARACTER SET utf8 NOT NULL COMMENT '来源',
  `outlink` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '外链地址',
  `date` int(11) NOT NULL COMMENT '发布日期',
  `ico` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '缩略图',
  `tags` varchar(500) CHARACTER SET utf8 NOT NULL COMMENT 'tag关键字',
  `enclosure` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '附件',
  `keywords` varchar(200) CHARACTER SET utf8 NOT NULL COMMENT '关键字',
  `description` varchar(500) CHARACTER SET utf8 NOT NULL COMMENT '描述',
  `sorting` int(10) unsigned NOT NULL DEFAULT '255' COMMENT '内容排序',
  `status` char(1) CHARACTER SET utf8 NOT NULL DEFAULT '1' COMMENT '状态',
  `istop` char(1) CHARACTER SET utf8 NOT NULL DEFAULT '0' COMMENT '是否置顶',
  `isrecommend` char(1) CHARACTER SET utf8 NOT NULL DEFAULT '0' COMMENT '是否推荐',
  `isheadline` char(1) CHARACTER SET utf8 NOT NULL DEFAULT '0' COMMENT '是否头条',
  `visits` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '访问数',
  `likes` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '点赞数',
  `create_user` varchar(30) CHARACTER SET utf8 NOT NULL COMMENT '创建人员',
  `update_user` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '更新人员',
  `create_time` int(11) NOT NULL COMMENT '创建时间',
  `update_time` int(11) NOT NULL COMMENT '更新时间',
  `tpl` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ay_content_unique` (`sorting`,`istop`,`isrecommend`,`isheadline`,`date`,`id`),
  KEY `ay_content_scode` (`scode`),
  KEY `ay_content_subscode` (`subscode`),
  KEY `ay_content_acode` (`acode`),
  KEY `ay_content_filename` (`filename`),
  KEY `ay_content_date` (`date`),
  KEY `ay_content_sorting` (`sorting`),
  KEY `ay_content_status` (`status`),
  KEY `ay_content_title_index` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=346 DEFAULT CHARSET=utf8mb4;

3、内容关联表

CREATE TABLE `cms_content_content` (
  `cid` int(11) NOT NULL COMMENT '内容id',
  `content` longtext CHARACTER SET utf8 COMMENT '正文内容',
  PRIMARY KEY (`cid`) USING BTREE,
  UNIQUE KEY `cid` (`cid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章内容表';

4、内容模型表

CREATE TABLE `cms_models` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '模型id',
  `mcode` int(10) DEFAULT NULL COMMENT '模型code',
  `model_name` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT '模型名称',
  `table_name` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT '模型对应拓展表名',
  `urlname` varchar(100) CHARACTER SET utf8 DEFAULT NULL COMMENT 'URL名称',
  `type` tinyint(1) NOT NULL DEFAULT '2' COMMENT '是否列表类型',
  `list_tpl` varchar(60) CHARACTER SET utf8 DEFAULT NULL COMMENT '列表栏目模板',
  `content_tpl` varchar(60) CHARACTER SET utf8 DEFAULT NULL COMMENT '内容页模板',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：1=开启 2=禁用 3=删除',
  `issystem` tinyint(1) DEFAULT '2' COMMENT '是否系统模型：1=是，2=否',
  `sorting` int(11) NOT NULL DEFAULT '50' COMMENT '排序',
  `create_time` int(11) DEFAULT NULL COMMENT '发布时间',
  `update_time` int(11) DEFAULT NULL COMMENT '修改时间',
  `delete_time` int(11) DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4;

5、模型关联字段表

CREATE TABLE `cms_models_field` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT COMMENT '字段id',
  `field_cname` varchar(60) CHARACTER SET utf8 NOT NULL COMMENT '字段中文名称',
  `field_ename` varchar(60) CHARACTER SET utf8 NOT NULL COMMENT '字段英文名称',
  `field_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '字段类型：1：单行文本 2：单选安按钮 3：复选框 4：下拉菜单 5：文本域 6：附件 7：浮点 8：整型 9：长文本类型 longtext ',
  `field_values` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '可选值',
  `models_id` mediumint(9) NOT NULL COMMENT '所属模型',
  `create_time` int(11) DEFAULT NULL COMMENT '发布时间',
  `update_time` int(11) DEFAULT NULL,
  `sorting` int(11) NOT NULL DEFAULT '50' COMMENT '排序',
  `status` tinyint(1) DEFAULT '1',
  `field_default` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '默认值',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `model_id` (`models_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4;

6、轮播图分类表

CREATE TABLE `cms_ad_type` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT COMMENT '广告位id',
  `name` varchar(60) NOT NULL COMMENT '广告位名称',
  `width` smallint(6) NOT NULL COMMENT '广告位宽度',
  `height` smallint(6) NOT NULL COMMENT '广告位高度',
  `create_time` int(11) DEFAULT NULL COMMENT '发布时间',
  `update_time` int(11) DEFAULT NULL,
  `sorting` int(11) NOT NULL DEFAULT '50' COMMENT '排序',
  `status` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

7、轮播图表

CREATE TABLE `cms_ad` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT '广告id',
  `ad_name` varchar(60) NOT NULL COMMENT '广告名称',
  `ad_ename` varchar(100) NOT NULL DEFAULT '1' COMMENT '广告名称英文',
  `ad_desc` varchar(100) NOT NULL DEFAULT '1' COMMENT '广告描述',
  `ad_src` varchar(255) NOT NULL COMMENT '广告图片地址',
  `ad_link` varchar(100) DEFAULT NULL COMMENT '广告链接地址',
  `ad_type_id` smallint(6) NOT NULL COMMENT '所属广告位',
  `create_time` int(11) DEFAULT NULL COMMENT '发布时间',
  `update_time` int(11) DEFAULT NULL,
  `sorting` int(11) NOT NULL DEFAULT '50' COMMENT '排序',
  `status` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `adpos_id` (`ad_type_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=81 DEFAULT CHARSET=utf8;

8、管理员表

CREATE TABLE `cms_admin` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `username` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户名',
  `nickname` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '昵称',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '密码',
  `salt` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '密码盐',
  `avatar` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '头像',
  `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '电子邮箱',
  `mobile` varchar(13) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '手机号',
  `loginfailure` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '失败次数',
  `login_time` int(10) DEFAULT NULL COMMENT '登录时间',
  `loginip` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '登录IP',
  `create_time` int(10) DEFAULT NULL COMMENT '创建时间',
  `update_time` int(10) DEFAULT NULL COMMENT '更新时间',
  `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Session标识',
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '状态',
  `group_id` int(11) DEFAULT NULL COMMENT '权限组',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `username` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='管理员表';

9、权限三张表

CREATE TABLE `cms_auth_group` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `title` char(100) NOT NULL DEFAULT '',
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `rules` varchar(255) NOT NULL DEFAULT '',
  `roleCode` varchar(50) DEFAULT NULL COMMENT '角色标识',
  `comments` varchar(255) DEFAULT NULL COMMENT '备注',
  `tenantId` int(10) DEFAULT NULL,
  `create_time` int(11) DEFAULT NULL,
  `update_time` int(11) DEFAULT NULL,
  `delete_time` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;


CREATE TABLE `cms_auth_group_access` (
  `uid` mediumint(8) unsigned NOT NULL,
  `group_id` mediumint(8) unsigned NOT NULL,
  UNIQUE KEY `uid_group_id` (`uid`,`group_id`) USING BTREE,
  KEY `uid` (`uid`) USING BTREE,
  KEY `group_id` (`group_id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;


CREATE TABLE `cms_auth_rule` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `title` char(80) DEFAULT '',
  `type` tinyint(1) DEFAULT '1',
  `status` tinyint(1) DEFAULT '1',
  `condition` char(100) DEFAULT '',
  `pid` mediumint(9) DEFAULT '0' COMMENT '上级规则id 0表示顶级规则',
  `icon` varchar(50) DEFAULT NULL COMMENT '图标名称',
  `hide` tinyint(1) DEFAULT '0' COMMENT '菜单是否显示',
  `sorting` int(10) DEFAULT '99' COMMENT '排序',
  `create_time` int(11) DEFAULT NULL,
  `update_time` int(11) DEFAULT NULL,
  `path` varchar(255) DEFAULT NULL,
  `delete_time` int(11) DEFAULT NULL,
  `component` varchar(255) DEFAULT NULL,
  `authority` varchar(50) DEFAULT NULL COMMENT '标识',
  `openType` tinyint(1) DEFAULT '0',
  `meta` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=123 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

10、系统配置及配置分类表

CREATE TABLE `cms_config_type` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL COMMENT '配置分类名称',
  `is_system` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否系统字段，1=是，禁删，2=不是，可以删',
  `create_time` int(11) DEFAULT NULL COMMENT '发布时间',
  `update_time` int(11) DEFAULT NULL,
  `order` int(11) NOT NULL DEFAULT '50' COMMENT '排序',
  `status` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

CREATE TABLE `cms_config` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '配置项id',
  `cname` varchar(60) DEFAULT NULL COMMENT '中文名称',
  `ename` varchar(60) DEFAULT NULL COMMENT '英文名称',
  `value` text COMMENT '默认值',
  `values` text COMMENT '可选值',
  `field_type` tinyint(1) DEFAULT '1' COMMENT '1：输入框 2：文本域  3：单选4:复选   5：下拉菜单 6：编辑器 7：单图',
  `config_type` tinyint(1) DEFAULT '1' COMMENT '1：基本信息',
  `sorting` int(11) DEFAULT '99',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

11、使用数据库管理工具进行数据库表的创建，我这边用的是navicat

12、在config下的knex.js 文件添加数据库配置以及封装了一些增删改查的方法


const knex = require('knex')({
    client: 'mysql2', 
    connection: {
        host: '127.0.0.1', // 地址
        user: 'root', // 账号
        password: '123456', // 密码
        database: 'koacms', // 数据库
        options: {
          port: 3306 // 端口
        }
    }
});
 
  // 分页多表联查并进行排序和统计的函数
const fetchJoinPage = async (data) => {
    const { table, fields, joinTables, where, orderBy, limit, offset } = data
    try {
      const order = orderBy ? order : ['id', 'desc','create_time','desc'];
      const totalCountQuery = knex(table)
        .count('* as total')
        .where(where);
   
      const query = knex(table)
        .select(fields)
        .where(where)
        .orderBy(order)
        .limit(limit)
        .offset(offset);
   
      // 添加联查
      joinTables.forEach(({ joinTable, onCondition, joinType = 'inner' }) => {
        query.join(joinTable, onCondition, joinType);
      });
   
      // 执行查询并获取结果
      const [result, totalCount] = await Promise.all([
        query,
        totalCountQuery
      ]);
   
      return {
        page,
        size,
        total: totalCount[0].total,
        data: result
        
      };
    } catch (error) {
      console.error('查询出错:', error);
      throw error;
    }
  }

// 分页查询多个字段并进行统计
const getPage = async (table,data) => {
    const {  fields='*', page=1, size=10, orderBy, where } = data
    try {
        const offset = (page - 1) * size;
        const order = orderBy ? order : ['id' ,'desc'];
        const countQuery = knex(table)
        .count('* as total')
        .where(where);
    
        const [countResult, rows] = await Promise.all([
        countQuery,
        knex(table)
            .select(fields)
            .where(where)
            .orderBy(order[0],order[1])
            .offset(offset)
            .limit(size)
        ]);
    
        const count = countResult[0].total;
        const pageData = {
            page:Number(page),
            size,
            count,
            list: rows
        };
    
        return pageData;
    } catch (error) {
    console.error('查询出错:', error);
    throw error;
  }
  }
 
// 关联查询
const getRelated = async (parentTable, childTable, parentId) => {
  return knex(childTable)
    .where({ [`${parentTable}_id`]: parentId });
};
 
// 增加
const create = async (table, data) => {
  return knex(table).insert(data, '*');
};

 
// 更新
const update = async (table, id, data) => {
  return knex(table)
    .where({ id })
    .update(data);
};

// 查询
const find = async (table, condition) => {
    return knex(table).where(condition);
  }
 
// 删除
const remove = async (table, id) => {
  return knex(table)
    .where({ id })
    .del();
};
// 批量新增
const batchInsert = async (table, data) => {
  return knex.batchInsert(table, data)
}
// 所有数据列表
const getList = async (table,where={},fields='*') => {
  return knex.select(fields).from(table).where(where).orderBy('id','desc');
}
// 所有数据列表
const getListByIds = async (table,ids,fields='*') => {
  return knex.select(fields).from(table).whereIn('id', ids);
}
// 批量删除
const batchDelete = async (table, ids) => {
  return knex(table).whereIn('id', ids).del()
}
 
// 导出
module.exports = {
  getPage,
  getList,
  getListByIds,
  fetchJoinPage,
  getRelated,
  create,
  update,
  remove,
  find,
  batchInsert,
  batchDelete,
  knex
};

13、在app.js中引入knex.js文件，测试数据库链接

用数据库管理工具，在内容表中，新增一条数据，然后查询后将数据返回到页面上

const Koa = require('koa')
const app = new Koa()
const views = require('koa-art-template');
const path = require('path');
const onerror = require('koa-onerror')
const bodyparser = require('koa-bodyparser')  
const Cors = require('koa2-cors')  
const db = require('./config/knex')
// error handler
onerror(app)

app.use(Cors({
  credentials: true
}))
// middlewares
app.use(bodyparser({
  enableTypes:['json', 'form', 'text']
}))
  
// 配置art-template
views(app, {
  root: path.join(__dirname, 'views'),  // 视图文件夹路径
  extname: '.html',                     // 模板文件扩展名
  debug: process.env.NODE_ENV !== 'production' // 是否开启调试模式
});

// 配置静态文件目录
app.use(require('koa-static')(path.join(__dirname, '/public')));

// 路由
app.use(async (ctx) => {
  const data =  await db.find('cms_content',{id:1})
  console.log('data',data)
  await ctx.render('index', {
      title: 'Hello Koa',
      message: data[0].title
  });
});

// error-handling
app.on('error', (err, ctx) => {
  console.error('server error', err, ctx)
});

module.exports = app

数据库测试结果链接正常