Pang-Blog开发-1：数据库设计

概要

数据库设计没有太严格的需求分析，也没有标准的E-R图，个人参考了一些大佬们的博客上的东西，然后自己总结了一个数据库。有关数据库我也没有过多的介绍的，下面还是直接贴注解和代码吧：

数据库搭建

用户表（唯一管理员）

登陆账号
登陆密码（加密）
密码盐值
昵称
性别
个人邮箱
个人QQ
个人电话
个人头像（储存路径）
个人生日

访客表（记录访客的一些基本信息）

访客IP（访客IP为主键）
第一次访问时间
上次访问时间
访问次数
黑名单（如果是黑名单则限制访问）

访客操作表（记录访客的操作）

操作ID
访客IP（外键）
操作时间
操作内容

菜单表（储存树状菜单）

菜单ID
菜单名
父级菜单
菜单顺序

文章表（储存文章信息和文章内容）

文章ID
文章标题
文章图标（路径）
文章内容
文章发表时间
点击量
修改时间
文章类别（外键）

分类表（文章分类）

分类ID
分类名

用户操作表（记录管理员操作）

操作ID
操作内容
操作时间

博客设置表（储存一些博客的基本设置）

博客名
博客背景色
博客图标（路径）

代码

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for admin_log
-- ----------------------------
DROP TABLE IF EXISTS `admin_log`;
CREATE TABLE `admin_log`  (
  `rl_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户操作id',
  `rl_time` datetime NOT NULL COMMENT '操作时间',
  `rl_detail` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '操作内容',
  PRIMARY KEY (`rl_id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for article
-- ----------------------------
DROP TABLE IF EXISTS `article`;
CREATE TABLE `article`  (
  `article_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '文章id',
  `article_title` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '文章标题',
  `article_icon` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文章图标',
  `article_time` datetime NOT NULL COMMENT '发表时间',
  `article_num` int(11) NOT NULL COMMENT '点击量',
  `article_update` datetime NOT NULL COMMENT '上次修改时间',
  `article_detail` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '文章内容',
  `article_classify` int(11) NOT NULL COMMENT '文章分类',
  PRIMARY KEY (`article_id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for caller
-- ----------------------------
DROP TABLE IF EXISTS `caller`;
CREATE TABLE `caller`  (
  `ip` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'ip',
  `first_call` datetime NOT NULL COMMENT '第一次访问时间',
  `last_call` datetime NOT NULL COMMENT '上次访问时间',
  `call_num` int(11) NOT NULL COMMENT '访问次数',
  `blacklist` enum('0','1') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '黑名单'
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for caller_log
-- ----------------------------
DROP TABLE IF EXISTS `caller_log`;
CREATE TABLE `caller_log`  (
  `cl_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '操作id',
  `cl_ip` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '访客ip',
  `cl_time` datetime NOT NULL COMMENT '操作时间',
  `cl_detail` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '操作内容',
  PRIMARY KEY (`cl_id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for classify
-- ----------------------------
DROP TABLE IF EXISTS `classify`;
CREATE TABLE `classify`  (
  `classify_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '分类id',
  `classify_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '分类名',
  PRIMARY KEY (`classify_id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for config
-- ----------------------------
DROP TABLE IF EXISTS `config`;
CREATE TABLE `config`  (
  `key` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '键',
  `value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '值',
  `note` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '注释',
  PRIMARY KEY (`key`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of config
-- ----------------------------
INSERT INTO `config` VALUES ('blog_namearticle', NULL, '博客名');
INSERT INTO `config` VALUES ('blog_back_color', NULL, '博客背景色');
INSERT INTO `config` VALUES ('blog_icon', NULL, '博客图标路径');

-- ----------------------------
-- Table structure for menu
-- ----------------------------
DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu`  (
  `menu_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'tinmghl ',
  `menu_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '菜单名',
  `menu_father` int(11) UNSIGNED ZEROFILL NOT NULL COMMENT '父菜单，为0则是顶级菜单',
  `menu_sort` tinyint(11) NOT NULL COMMENT '菜单顺序',
  PRIMARY KEY (`menu_id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `login_num` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '登陆账号',
  `login_password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '加密密码',
  `login_salt` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '盐值',
  `username` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
  `gender` enum('man','woman') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'man' COMMENT '性别',
  `mail` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `qq` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'QQ',
  `tel` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
  `icon` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户头像（存储路径）',
  `birthday` datetime NULL DEFAULT NULL COMMENT '用户生日',
  PRIMARY KEY (`login_num`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;