234 lines
13 KiB
SQL
234 lines
13 KiB
SQL
-- MariaDB dump 10.19 Distrib 10.4.32-MariaDB, for Win64 (AMD64)
|
|
--
|
|
-- Host: localhost Database: myblog
|
|
-- ------------------------------------------------------
|
|
-- Server version 10.4.32-MariaDB
|
|
|
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
|
/*!40101 SET NAMES utf8mb4 */;
|
|
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
|
|
/*!40103 SET TIME_ZONE='+00:00' */;
|
|
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
|
|
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
|
|
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
|
|
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
|
|
|
|
--
|
|
-- Table structure for table `article_tags`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `article_tags`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `article_tags` (
|
|
`article_id` int(10) unsigned NOT NULL,
|
|
`tag_id` int(10) unsigned NOT NULL,
|
|
PRIMARY KEY (`article_id`,`tag_id`),
|
|
KEY `tag_id` (`tag_id`),
|
|
CONSTRAINT `article_tags_ibfk_1` FOREIGN KEY (`article_id`) REFERENCES `articles` (`id`) ON DELETE CASCADE,
|
|
CONSTRAINT `article_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `article_tags`
|
|
--
|
|
|
|
LOCK TABLES `article_tags` WRITE;
|
|
/*!40000 ALTER TABLE `article_tags` DISABLE KEYS */;
|
|
INSERT INTO `article_tags` VALUES (2,2),(3,3);
|
|
/*!40000 ALTER TABLE `article_tags` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `articles`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `articles`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `articles` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`user_id` int(10) unsigned NOT NULL,
|
|
`category_id` int(10) unsigned DEFAULT NULL,
|
|
`title` varchar(200) NOT NULL,
|
|
`slug` varchar(220) NOT NULL,
|
|
`content` mediumtext NOT NULL,
|
|
`excerpt` varchar(500) DEFAULT NULL,
|
|
`status` enum('published','draft') NOT NULL DEFAULT 'draft',
|
|
`views` int(10) unsigned NOT NULL DEFAULT 0,
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `slug` (`slug`),
|
|
KEY `user_id` (`user_id`),
|
|
KEY `category_id` (`category_id`),
|
|
KEY `idx_status_created` (`status`,`created_at`),
|
|
CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
|
|
CONSTRAINT `articles_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `articles`
|
|
--
|
|
|
|
LOCK TABLES `articles` WRITE;
|
|
/*!40000 ALTER TABLE `articles` DISABLE KEYS */;
|
|
INSERT INTO `articles` VALUES (1,1,4,'TCP 三次握手','tcp','## 什么是 TCP 三次握手\n\nTCP 是面向连接的可靠传输协议。在正式传输数据之前，通信双方需要通过三次握手建立连接，确认彼此的收发能力都正常。\n\n## 三次握手的流程\n\n第一次握手：客户端发送一个带有 SYN 标志的报文，并带上一个初始序列号，请求建立连接。\n\n第二次握手：服务端收到 SYN 后，回复一个带有 SYN 和 ACK 标志的报文，确认收到客户端的请求，同时通知自己的初始序列号。\n\n第三次握手：客户端收到服务端的 SYN+ACK 后，再发送一个 ACK 报文进行确认。此时连接建立，双方可以开始传输数据。\n\n## 为什么要三次握手\n\n两次握手是不够的。如果服务端发出的应答报文丢失，客户端会重发 SYN，可能导致服务端建立多个无效连接，浪费资源。\n\n三次握手可以确保双方都知道\"我能发、你能收\"以及\"你能发、我能收\"，从而可靠地建立连接。\n\n## 简单总结\n\n连接建立的过程可以概括为：\n\nSYN -> SYN+ACK -> ACK\n\n三次握手是 TCP 可靠性的基础，它保证了连接的双方在传输数据前都已准备好，让后续的数据传输更加安全可靠。','TCP 通过三次握手建立可靠连接，确保双方就绪后才开始传输数据。','published',72,'2026-09-09 10:13:59','2026-09-09 11:12:19'),(2,1,4,'一杯清茶的时间','post-6aa131674c754','## 一杯清茶的时间\n\n下午三点，泡一杯茶。看茶叶在水中慢慢舒展，热气袅袅升起。\n\n生活的节奏太快，需要这样一杯茶的时间，让自己慢下来，也静下来。\n\n## 茶里的小事\n\n烧水、温杯、投茶、注水，每一个步骤都不急不缓。看着茶叶在热水中一点点舒展开来，仿佛整个下午也跟着慢了下来。茶汤的颜色由浅转深，香气在鼻尖萦绕，这一刻，烦恼似乎都变得遥远。\n\n## 慢一点的智慧\n\n我们习惯了赶时间，却常常忘了，有些事急不得，也催不来。就像这杯茶，火候到了，自然就香。人也一样，给自己一点时间和空间，很多事会自然明朗。\n\n一杯茶，不只是一杯茶，它是一段与自己相处的时光，是喧嚣生活里，为自己留出的一方清净。喝完这一杯，再从容地回到忙碌中去。','生活太快，需要一杯茶让自己慢下来。','published',54,'2026-09-08 10:13:59','2026-09-09 11:06:19'),(3,1,4,'写在周末','post-6aa131674cf54','## 写在周末\n\n周末适合整理，整理房间，也整理心情。\n\n把一周的疲惫卸下，把下周的清单列好，然后从容地迎接新的一周。日子虽平凡，却也值得认真对待。\n\n## 整理生活\n\n周末的早晨，拉开窗帘，让阳光照进来。把散落的物品归位，把积攒了一周的衣服洗好，把桌面的杂物清理干净。当房间变得整洁，心情也会跟着轻盈起来。\n\n## 给自己一点时间\n\n除了整理房间，也别忘了整理心情。翻一翻这周的相册，回想几件开心的小事；或者约上好友，去吃一顿喜欢的饭。生活的意义，往往就藏在这些看似平常的片段里。\n\n周末不是工作的暂停，而是生活的重启。休息好、整理好，才能以更好的状态，去迎接下一个平凡又值得期待的日子。','周末适合整理，整理房间，也整理心情。','published',71,'2026-09-07 10:13:59','2026-09-09 11:06:19');
|
|
/*!40000 ALTER TABLE `articles` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `categories`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `categories`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `categories` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(60) NOT NULL,
|
|
`slug` varchar(80) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `slug` (`slug`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `categories`
|
|
--
|
|
|
|
LOCK TABLES `categories` WRITE;
|
|
/*!40000 ALTER TABLE `categories` DISABLE KEYS */;
|
|
INSERT INTO `categories` VALUES (1,'设计','design'),(2,'前端','frontend'),(3,'后端','backend'),(4,'随笔','notes');
|
|
/*!40000 ALTER TABLE `categories` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `comments`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `comments`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `comments` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`article_id` int(10) unsigned NOT NULL,
|
|
`parent_id` int(10) unsigned DEFAULT NULL,
|
|
`author` varchar(60) NOT NULL,
|
|
`email` varchar(120) DEFAULT NULL,
|
|
`content` text NOT NULL,
|
|
`status` enum('pending','approved') NOT NULL DEFAULT 'pending',
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
PRIMARY KEY (`id`),
|
|
KEY `article_id` (`article_id`),
|
|
CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`article_id`) REFERENCES `articles` (`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `comments`
|
|
--
|
|
|
|
LOCK TABLES `comments` WRITE;
|
|
/*!40000 ALTER TABLE `comments` DISABLE KEYS */;
|
|
/*!40000 ALTER TABLE `comments` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `tags`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `tags`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `tags` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(60) NOT NULL,
|
|
`slug` varchar(80) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `slug` (`slug`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `tags`
|
|
--
|
|
|
|
LOCK TABLES `tags` WRITE;
|
|
/*!40000 ALTER TABLE `tags` DISABLE KEYS */;
|
|
INSERT INTO `tags` VALUES (1,'随笔','notes'),(2,'生活','life'),(3,'分享','share');
|
|
/*!40000 ALTER TABLE `tags` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `users`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `users`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `users` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`username` varchar(50) NOT NULL,
|
|
`password_hash` varchar(255) NOT NULL,
|
|
`email` varchar(120) DEFAULT NULL,
|
|
`role` enum('admin','author') NOT NULL DEFAULT 'author',
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `username` (`username`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `users`
|
|
--
|
|
|
|
LOCK TABLES `users` WRITE;
|
|
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
|
|
INSERT INTO `users` VALUES (1,'admin','$2y$10$4bocGkDIxtmTyK.S7H5AQ.lm/ZzONQpeuZXHhiuRLERFsZ2iaD0qK',NULL,'admin','2026-09-09 08:07:24');
|
|
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `visitor_stats`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `visitor_stats`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `visitor_stats` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`visit_date` date NOT NULL,
|
|
`ip` varchar(45) DEFAULT NULL,
|
|
`page` varchar(200) DEFAULT NULL,
|
|
`ua` varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uniq_visit` (`visit_date`,`ip`,`page`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `visitor_stats`
|
|
--
|
|
|
|
LOCK TABLES `visitor_stats` WRITE;
|
|
/*!40000 ALTER TABLE `visitor_stats` DISABLE KEYS */;
|
|
INSERT INTO `visitor_stats` VALUES (1,'2026-09-09','::1','article/1','Mozilla/5.0 (Windows NT; Windows NT 10.0; zh-CN) WindowsPowerShell/5.1.26100.8972'),(5,'2026-09-09','::1','article/2','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/152.0.0.0 Safari/537.36 Edg/152.0.0.0'),(8,'2026-09-09','::1','article/3','Mozilla/5.0 (Windows NT; Windows NT 10.0; zh-CN) WindowsPowerShell/5.1.26100.8972'),(14,'2026-09-09','::1','search','Mozilla/5.0 (Windows NT; Windows NT 10.0; zh-CN) WindowsPowerShell/5.1.26100.8972'),(16,'2026-09-09','::1','archive','Mozilla/5.0 (Windows NT; Windows NT 10.0; zh-CN) WindowsPowerShell/5.1.26100.8972'),(17,'2026-09-09','::1','category/4','Mozilla/5.0 (Windows NT; Windows NT 10.0; zh-CN) WindowsPowerShell/5.1.26100.8972'),(18,'2026-09-09','::1','tag/1','Mozilla/5.0 (Windows NT; Windows NT 10.0; zh-CN) WindowsPowerShell/5.1.26100.8972'),(19,'2026-09-09','::1','about','Mozilla/5.0 (Windows NT; Windows NT 10.0; zh-CN) WindowsPowerShell/5.1.26100.8972'),(37,'2026-09-09','::1','category/1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/152.0.0.0 Safari/537.36 Edg/152.0.0.0'),(38,'2026-09-09','::1','category/2','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/152.0.0.0 Safari/537.36 Edg/152.0.0.0'),(39,'2026-09-09','::1','category/3','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/152.0.0.0 Safari/537.36 Edg/152.0.0.0');
|
|
/*!40000 ALTER TABLE `visitor_stats` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
|
|
|
|
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
|
|
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
|
|
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
|
|
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|
|
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
|
|
|
|
-- Dump completed on 2026-09-09 19:16:36
|