SQL例

create table if not exists os_attachment
(
    aid      int unsigned auto_increment comment '资源文件表'
        primary key,
    author   int unsigned default 1  not null comment '作者UID',
    sortid   int(10)      default 0  not null comment '分类ID',
    blogid   int unsigned default 0  not null comment '文章ID（已废弃）',
    filename varchar(255) default '' not null comment '文件名',
    filesize int(10)      default 0  not null comment '文件大小',
    filepath varchar(255) default '' not null comment '文件路径',
    addtime  bigint       default 0  not null comment '创建时间',
    width    int(10)      default 0  not null comment '图片宽度',
    height   int(10)      default 0  not null comment '图片高度',
    mimetype varchar(40)  default '' not null comment '文件mime类型',
    thumfor  int(10)      default 0  not null comment '缩略图的原资源ID（已废弃）'
)
    collate = utf8mb4_unicode_ci;

create index thum_uid
    on os_attachment (thumfor, author);

create table if not exists os_blog
(
    gid          int unsigned auto_increment comment '文章表'
        primary key,
    title        varchar(255)    default ''     not null comment '文章标题',
    date         bigint                         not null comment '发布时间',
    content      longtext                       not null comment '文章内容',
    excerpt      longtext                       not null comment '文章摘要',
    cover        varchar(255)    default ''     not null comment '封面图',
    alias        varchar(200)    default ''     not null comment '文章别名',
    author       int(10)         default 1      not null comment '作者UID',
    sortid       int(10)         default -1     not null comment '分类ID',
    type         varchar(20)     default 'blog' not null comment '文章OR页面',
    views        int unsigned    default 0      not null comment '阅读量',
    comnum       int unsigned    default 0      not null comment '评论数量',
    attnum       int unsigned    default 0      not null comment '附件数量（已废弃）',
    top          enum ('n', 'y') default 'n'    not null comment '置顶',
    sortop       enum ('n', 'y') default 'n'    not null comment '分类置顶',
    hide         enum ('n', 'y') default 'n'    not null comment '草稿y',
    checked      enum ('n', 'y') default 'y'    not null comment '文章是否审核',
    allow_remark enum ('n', 'y') default 'y'    not null comment '允许评论y',
    password     varchar(255)    default ''     not null comment '访问密码',
    template     varchar(255)    default ''     not null comment '模板',
    tags         text                           null comment '标签'
)
    collate = utf8mb4_unicode_ci;

create index author
    on os_blog (author);

create index comnum
    on os_blog (comnum);

create index date
    on os_blog (date);

create index sortid
    on os_blog (sortid);

create index top
    on os_blog (top, date);

create index views
    on os_blog (views);

create table if not exists os_comment
(
    cid     int unsigned auto_increment comment '评论表'
        primary key,
    gid     int unsigned    default 0   not null comment '文章ID',
    pid     int unsigned    default 0   not null comment '父级评论ID',
    top     enum ('n', 'y') default 'n' not null comment '置顶',
    poster  varchar(20)     default ''  not null comment '发布人昵称',
    uid     int(10)         default 0   not null comment '发布人UID',
    comment text                        not null comment '评论内容',
    mail    varchar(60)     default ''  not null comment 'email',
    url     varchar(75)     default ''  not null comment 'homepage',
    ip      varchar(128)    default ''  not null comment 'ip address',
    agent   varchar(512)    default ''  not null comment 'user agent',
    hide    enum ('n', 'y') default 'n' not null comment '是否审核',
    date    bigint                      not null comment '创建时间'
)
    collate = utf8mb4_unicode_ci;

create index date
    on os_comment (date);

create index gid
    on os_comment (gid);

create index hide
    on os_comment (hide);

create table if not exists os_link
(
    id          int unsigned auto_increment comment '链接表'
        primary key,
    sitename    varchar(255)    default ''  not null comment '名称',
    siteurl     varchar(255)    default ''  not null comment '地址',
    description varchar(512)    default ''  not null comment '备注信息',
    hide        enum ('n', 'y') default 'n' not null comment '是否隐藏',
    taxis       int unsigned    default 0   not null comment '排序序号'
)
    collate = utf8mb4_unicode_ci;

create table if not exists os_media_sort
(
    id       int unsigned auto_increment comment '资源分类表'
        primary key,
    sortname varchar(255) default '' not null comment '分类名'
)
    collate = utf8mb4_unicode_ci;

create table if not exists os_navi
(
    id        int unsigned auto_increment comment '导航表'
        primary key,
    naviname  varchar(30)      default ''  not null comment '导航名称',
    url       varchar(512)     default ''  not null comment '导航地址',
    newtab    enum ('n', 'y')  default 'n' not null comment '在新窗口打开',
    hide      enum ('n', 'y')  default 'n' not null comment '是否隐藏',
    taxis     int unsigned     default 0   not null comment '排序序号',
    pid       int unsigned     default 0   not null comment '父级ID',
    isdefault enum ('n', 'y')  default 'n' not null comment '是否系统默认导航，如首页',
    type      tinyint unsigned default 0   not null comment '导航类型 0自定义 1首页 2笔记 3后台管理 4分类 5页面',
    type_id   int unsigned     default 0   not null comment '导航类型对应ID'
)
    collate = utf8mb4_unicode_ci;

create table if not exists os_options
(
    option_id    int(11) unsigned auto_increment comment '站点配置信息表'
        primary key,
    option_name  varchar(75) not null comment '配置项',
    option_value longtext    not null comment '配置项值',
    constraint option_name_uindex
        unique (option_name)
)
    collate = utf8mb4_unicode_ci;

create table if not exists os_sort
(
    sid         int unsigned auto_increment comment '分类表'
        primary key,
    sortname    varchar(255) default '' not null comment '分类名',
    alias       varchar(200) default '' not null comment '别名',
    taxis       int unsigned default 0  not null comment '排序序号',
    pid         int unsigned default 0  not null comment '父分类ID',
    description text                    not null comment '备注',
    template    varchar(255) default '' not null comment '分类模板'
)
    collate = utf8mb4_unicode_ci;

create table if not exists os_storage
(
    sid        int(8) auto_increment comment '对象存储表'
        primary key,
    plugin     varchar(32) not null comment '插件名',
    name       varchar(32) not null comment '对象名',
    type       varchar(8)  not null comment '对象数据类型',
    value      text        not null comment '对象值',
    createdate int         not null comment '创建时间',
    lastupdate int         not null comment '更新时间',
    constraint plugin
        unique (plugin, name)
)
    collate = utf8mb4_unicode_ci;

create table if not exists os_tag
(
    tid     int unsigned auto_increment comment '标签表'
        primary key,
    tagname varchar(60) default '' not null comment '标签名',
    gid     text                   not null comment '文章ID'
)
    collate = utf8mb4_unicode_ci;

create index tagname
    on os_tag (tagname);

create table if not exists os_tpl_options_data
(
    id       int(11) unsigned auto_increment
        primary key,
    template char(64)            not null,
    name     char(64)            not null,
    depend   char(64) default '' not null,
    data     longtext            not null,
    constraint template
        unique (template, name)
)
    engine = InnoDB;

create table if not exists os_twitter
(
    id       int auto_increment comment '笔记表'
        primary key,
    content  text                   not null comment '笔记内容',
    img      varchar(200)           null comment '图片',
    author   int(10)      default 1 not null comment '作者UID',
    date     bigint                 not null comment '创建时间',
    replynum int unsigned default 0 not null comment '回复数量'
)
    collate = utf8mb4_unicode_ci;

create index author
    on os_twitter (author);

create table if not exists os_user
(
    uid         int unsigned auto_increment comment '用户表'
        primary key,
    username    varchar(32)     default ''  not null comment '用户名',
    password    varchar(64)     default ''  not null comment '用户密码',
    nickname    varchar(20)     default ''  not null comment '昵称',
    role        varchar(60)     default ''  not null comment '用户组',
    ischeck     enum ('n', 'y') default 'n' not null comment '内容是否需要管理员审核',
    photo       varchar(255)    default ''  not null comment '头像',
    email       varchar(60)     default ''  not null comment '邮箱',
    description varchar(255)    default ''  not null comment '备注',
    ip          varchar(128)    default ''  not null comment 'ip地址',
    state       tinyint         default 0   not null comment '用户状态 0正常 1禁用',
    create_time int                         not null comment '创建时间',
    update_time int                         not null comment '更新时间'
)
    collate = utf8mb4_unicode_ci;

create index email
    on os_user (email);

create index username
    on os_user (username);