主内容表

CREATE TABLE IF NOT EXISTS `diary`
(
  `diary_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id值',
  `user_name` VARCHAR(255) NOT NULL COMMENT '加密用户名',
  `classify` VARCHAR(255) NOT NULL DEFAULT '默认分类' COMMENT '分类',
  `title` VARCHAR(255) NOT NULL DEFAULT '无标题内容' COMMENT '标题',
  `contents` TEXT NOT NULL DEFAULT '' COMMENT '内容',
  `create_time` INT UNSIGNED DEFAULT 0 COMMENT '创建时间',
  `update_time` INT UNSIGNED DEFAULT 0 COMMENT '更新时间',
  PRIMARY KEY ( `diary_id` ),
  #创建普通索引
  INDEX `index_user_name` (user_name(50)),
  INDEX `index_classify` (classify(50))
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

用户表

CREATE TABLE IF NOT EXISTS `diary_users`
(
  `user_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id值',
  `user_name` varchar(50) NOT NULL UNIQUE COMMENT '用户名',
  `user_password` varchar(255) NOT NULL COMMENT '加密密码',
  `create_time` INT UNSIGNED DEFAULT 0 COMMENT '创建时间',
  `update_time` INT UNSIGNED DEFAULT 0 COMMENT '更新时间',
  PRIMARY KEY ( `user_id` ),
  #创建普通索引
  INDEX `index_user_name` (user_name(50))
)ENGINE=InnoDB DEFAULT CHARSET=utf8;