内容表
CREATE TABLE `article_content` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID tags:{"primary_key":"true"}',
`content` text COMMENT 'content 内容',
`category` text COMMENT '分类列表',
`tags` text COMMENT '标签列表',
`genre` int NOT NULL DEFAULT 0 COMMENT '题材',
`title` varchar(256) NOT NULL DEFAULT '' COMMENT '文章标题',
`abstract` varchar(256) NOT NULL DEFAULT '' COMMENT '摘要',
`visbility` bigint NOT NULL DEFAULT 0 COMMENT '可见度',
`creator_id` bigint NOT NULL DEFAULT 0 COMMENT '创建人id',
`creator` varchar(32) NOT NULL DEFAULT '' COMMENT '创建人',
`deleted_at` timestamp NULL COMMENT '删除时间 options:{"null_as_pointer":true}',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET = utf8mb4 COMMENT '文章内容';
新增字段
ALTER TABLE article_content ADD COLUMN editor int COMMENT "编辑器类型"; COMMENT "编辑器";
ALTER TABLE article_content ADD COLUMN reship_url text COMMENT "原文链接";
ALTER TABLE article_content ADD COLUMN logo varchar(256) COMMENT "logo";
ALTER TABLE article_content ADD COLUMN source int COMMENT "来源";
标签表
CREATE TABLE `article_tag` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID tags:{"primary_key":"true"}',
`tag_id` bigint NOT NULL DEFAULT 0 COMMENT '标签id',
`tag` varchar(256) NOT NULL DEFAULT '' COMMENT '标签名',
`visbility` bigint NOT NULL DEFAULT 0 COMMENT '可见度',
`creator` varchar(32) NOT NULL DEFAULT '' COMMENT '创建人',
`creator_id` bigint NOT NULL DEFAULT 0 COMMENT '创建人id',
`deleted_at` timestamp NULL COMMENT '删除时间 options:{"null_as_pointer":true}',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET = utf8mb4 COMMENT '标签基础表';
分类映射表
CREATE TABLE `article_tag_map` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID tags:{"primary_key":"true"}',
`tag_id` bigint NOT NULL DEFAULT 0 COMMENT '标签id',
`article_id` bigint NOT NULL DEFAULT 0 COMMENT '文章id',
`deleted_at` timestamp NULL COMMENT '删除时间 options:{"null_as_pointer":true}',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
INDEX article_tag_id(article_id, tag_id)
) ENGINE = InnoDB CHARSET = utf8mb4 COMMENT '文章标签映射表';
分类表
CREATE TABLE `article_category` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID tags:{"primary_key":"true"}',
`name` varchar(256) NOT NULL DEFAULT '' COMMENT '分类名字',
`visibility` bigint NOT NULL DEFAULT 0 COMMENT '可见度',
`creator` varchar(32) NOT NULL DEFAULT '' COMMENT '创建人',
`parent_id` bigint NOT NULL DEFAULT 0 COMMENT '父节点',
`creator_id` bigint NOT NULL DEFAULT 0 COMMENT '创建人id',
`deleted_at` timestamp NULL COMMENT '删除时间 options:{"null_as_pointer":true}', `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='标签基础表';
分类映射表
CREATE TABLE `article_category_map` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID tags:{"primary_key":"true"}',
`category_id` int NOT NULL DEFAULT 0 COMMENT '标签id',
`article_id` bigint NOT NULL DEFAULT 0 COMMENT '文章id',
`deleted_at` timestamp NULL COMMENT '删除时间 options:{"null_as_pointer":true}',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET = utf8mb4 COMMENT '文章标签映射表';
合集
CREATE TABLE `article_collection` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID tags:{"primary_key":"true"}',
`name` varchar(256) NOT NULL DEFAULT '' COMMENT '合集名字',
`author_id` bigint NOT NULL DEFAULT 0 COMMENT '所属人',
`parent_id` bigint NOT NULL DEFAULT 0 COMMENT '父节点',
`deleted_at` timestamp NULL COMMENT '删除时间 options:{"null_as_pointer":true}',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET = utf8mb4 COMMENT '合集id';
合集映射表
CREATE TABLE `article_collection_map` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID tags:{"primary_key":"true"}',
`name` varchar(256) NOT NULL DEFAULT '' COMMENT '合集名字',
`collection_id` bigint NOT NULL DEFAULT 0 COMMENT '合集映射的名字',
`article_id` bigint NOT NULL DEFAULT 0 COMMENT '合集映射的文章',
`deleted_at` timestamp NULL COMMENT '删除时间 options:{"null_as_pointer":true}',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET = utf8mb4 COMMENT '合集映射';
用户表
CREATE TABLE `user` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID tags:{"primary_key":"true"}',
`email` varchar(256) NOT NULL DEFAULT '' COMMENT '邮箱',
`username` varchar(256) NOT NULL DEFAULT '' COMMENT '用户名',
`password` varchar(32) NOT NULL DEFAULT '' COMMENT '用户密码',
`show_name` varchar(256) NOT NULL DEFAULT '' COMMENT '昵称',
`phone` varchar(256) NOT NULL DEFAULT '' COMMENT '手机',
`level` varchar(256) NOT NULL DEFAULT '' COMMENT '用户等级',
`deleted_at` timestamp NULL COMMENT '删除时间 options:{"null_as_pointer":true}',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
UNIQUE INDEX ` username` (username),
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET = utf8mb4 COMMENT '用户';
ALTER TABLE user ADD COLUMN avatar text COMMENT "avatar";
ALTER TABLE user ADD COLUMN tags varchar(256) COMMENT "tags";
文件路径表
CREATE TABLE `blog_file` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID tags:{"primary_key":"true"}',
`filename` varchar(256) NOT NULL DEFAULT '' COMMENT '文件名字',
`content_type` varchar(256) NOT NULL DEFAULT '' COMMENT '内容类型',
`owner` varchar(256) NOT NULL DEFAULT '' COMMENT '用户名',
`url` varchar(256) NOT NULL DEFAULT '' COMMENT 'url',
`href` varchar(256) NOT NULL DEFAULT '' COMMENT 'href',
`path` varchar(256) NOT NULL DEFAULT '' COMMENT '路径',
`bucket` varchar(256) NOT NULL DEFAULT '' COMMENT 'Bucket',
`alt` varchar(256) NOT NULL DEFAULT '' COMMENT 'alt',
`md5` varchar(256) NOT NULL DEFAULT '' COMMENT 'md5',
`prefix` varchar(256) NOT NULL DEFAULT '' COMMENT '前缀',
`minio_key` varchar(256) NOT NULL DEFAULT '' COMMENT 'minio_key的key',
`save_type` varchar(256) NOT NULL DEFAULT '' COMMENT '存储类型',
`deleted_at` timestamp NULL COMMENT '删除时间 options:{"null_as_pointer":true}',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
UNIQUE INDEX ` url` (url),
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET = utf8mb4 COMMENT '文件存储表';
CREATE TABLE `article_content_spider` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID tags:{"primary_key":"true"}',
`content` longtext,
`category` text COMMENT '分类列表',
`tags` text COMMENT '标签列表',
`title` varchar(1024) NOT NULL DEFAULT '' COMMENT '文章标题',
`abstract` text COMMENT '摘要',
`keywords` text COMMENT '关键字',
`content_html` longtext COMMENT 'html文本',
`image_url` text COMMENT '图片',
`image_url1` text COMMENT '图片',
`image_url2` text COMMENT '图片',
`image_url3` text COMMENT '图片',
`origin_url` varchar(256) NOT NULL DEFAULT '' COMMENT '原始链接',
`origin_source` varchar(256) NOT NULL DEFAULT '' COMMENT '原始类型',
`read_count` varchar(32) COMMENT '阅读人数',
`like` varchar(32) COMMENT '点赞人数',
`comment_count` varchar(32) NOT NULL DEFAULT '' COMMENT '评论人数人数',
`comment1` text COMMENT '评论人1',
`comment2` text COMMENT '评论人2',
`comment3` text COMMENT '评论人3',
`comment1_count` varchar(32) NOT NULL DEFAULT '' COMMENT '评论人1 count',
`comment2_count` varchar(32) NOT NULL DEFAULT '' COMMENT '评论人2 count',
`comment3_count` varchar(32) NOT NULL DEFAULT '' COMMENT '评论人3 count',
`author` varchar(256) NOT NULL DEFAULT '' COMMENT '作者',
`timestamp` varchar(256) NOT NULL DEFAULT '' COMMENT 'timestamp',
`timestamp_out` varchar(256) NOT NULL DEFAULT '' COMMENT 'timestamp_out',
`author_url` text COMMENT '作者地址',
`visibility` bigint NOT NULL DEFAULT '0' COMMENT '可见度',
`creator_id` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
`creator` varchar(32) NOT NULL DEFAULT '' COMMENT '创建人',
`deleted_at` timestamp NULL DEFAULT NULL COMMENT '删除时间 options:{"null_as_pointer":true}',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`logo` varchar(256) DEFAULT NULL COMMENT 'logo',
`genre` int DEFAULT NULL COMMENT '内容类型',
`editor` int DEFAULT NULL COMMENT '编辑器类型',
`source` int DEFAULT NULL COMMENT '类型',
`reship_url` text COMMENT '转载链接',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=173 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='文章内容';
alter table article_content_spider AUTO_INCREMENT 10000000;
ALTER TABLE article_content_spider ADD UNIQUE (origin_url);