|
|
-- 禁用外键检查
|
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
|
|
-- 如果数据库存在，删除它
|
|
|
DROP DATABASE IF EXISTS glucowisebase;
|
|
|
|
|
|
-- 创建数据库
|
|
|
CREATE DATABASE glucowisebase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
|
|
-- 使用数据库
|
|
|
USE glucowisebase;
|
|
|
|
|
|
-- ==================== 创建表（按依赖顺序） ====================
|
|
|
|
|
|
-- 1. 首先创建没有外键依赖的表
|
|
|
CREATE TABLE `users` (
|
|
|
`id` bigint NOT NULL,
|
|
|
`username` varchar(50) DEFAULT NULL,
|
|
|
`wx_id` varchar(20) DEFAULT NULL,
|
|
|
`avatar_url` varchar(255) DEFAULT NULL,
|
|
|
`gender` enum('male','female') DEFAULT NULL,
|
|
|
`birthday` date DEFAULT NULL,
|
|
|
`status` enum('active','inactive','banned') DEFAULT NULL,
|
|
|
`created_at` timestamp NULL DEFAULT NULL,
|
|
|
`updated_at` timestamp NULL DEFAULT NULL,
|
|
|
`banned_until` timestamp NULL DEFAULT NULL,
|
|
|
`background_url` varchar(255) DEFAULT NULL,
|
|
|
PRIMARY KEY (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `roles` (
|
|
|
`id` bigint NOT NULL,
|
|
|
`role_name` varchar(50) DEFAULT NULL,
|
|
|
`description` text,
|
|
|
`permissions` json DEFAULT NULL,
|
|
|
`created_at` timestamp NULL DEFAULT NULL,
|
|
|
PRIMARY KEY (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `knowledge_base` (
|
|
|
`id` bigint NOT NULL AUTO_INCREMENT,
|
|
|
`name` varchar(200) DEFAULT NULL,
|
|
|
`url` varchar(255) DEFAULT NULL,
|
|
|
`updated_at` timestamp NULL DEFAULT NULL,
|
|
|
PRIMARY KEY (`id`)
|
|
|
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
-- 2. 创建依赖 users 和 roles 的表
|
|
|
CREATE TABLE `admins` (
|
|
|
`id` bigint NOT NULL,
|
|
|
`password` varchar(255) DEFAULT NULL,
|
|
|
`real_name` varchar(255) DEFAULT NULL,
|
|
|
`phone` varchar(20) DEFAULT NULL,
|
|
|
`last_login` timestamp NULL DEFAULT NULL,
|
|
|
`created_at` timestamp NULL DEFAULT NULL,
|
|
|
`role_id` bigint DEFAULT NULL,
|
|
|
PRIMARY KEY (`id`),
|
|
|
KEY `FK_admin_role` (`role_id`),
|
|
|
CONSTRAINT `FK_admin_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
-- 3. 创建依赖 users 的表
|
|
|
CREATE TABLE `ai_conversations` (
|
|
|
`id` bigint NOT NULL,
|
|
|
`question` text,
|
|
|
`answer` text,
|
|
|
`created_at` timestamp NULL DEFAULT NULL,
|
|
|
`user_id` bigint DEFAULT NULL,
|
|
|
PRIMARY KEY (`id`),
|
|
|
KEY `FK_ai_user` (`user_id`),
|
|
|
CONSTRAINT `FK_ai_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `devices` (
|
|
|
`id` bigint NOT NULL AUTO_INCREMENT,
|
|
|
`device_name` varchar(100) DEFAULT NULL,
|
|
|
`device_type` varchar(50) DEFAULT NULL,
|
|
|
`device_model` varchar(100) DEFAULT NULL,
|
|
|
`mac_address` varchar(20) DEFAULT NULL,
|
|
|
`connection_type` enum('bluetooth','wifi') DEFAULT NULL,
|
|
|
`status` enum('active','inactive','error') DEFAULT NULL,
|
|
|
`last_sync_time` timestamp NULL DEFAULT NULL,
|
|
|
`bought_date` date DEFAULT NULL,
|
|
|
`user_id` bigint DEFAULT NULL,
|
|
|
PRIMARY KEY (`id`),
|
|
|
KEY `FK_device_user` (`user_id`),
|
|
|
CONSTRAINT `FK_device_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `families` (
|
|
|
`id` bigint NOT NULL AUTO_INCREMENT,
|
|
|
`family_name` varchar(100) DEFAULT NULL,
|
|
|
`description` text,
|
|
|
`creator_user_id` bigint DEFAULT NULL,
|
|
|
PRIMARY KEY (`id`),
|
|
|
KEY `FK_family_creator` (`creator_user_id`),
|
|
|
CONSTRAINT `FK_family_creator` FOREIGN KEY (`creator_user_id`) REFERENCES `users` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `follows` (
|
|
|
`follower_id` bigint NOT NULL,
|
|
|
`followed_id` bigint NOT NULL,
|
|
|
PRIMARY KEY (`follower_id`,`followed_id`),
|
|
|
KEY `FK_follow_followed` (`followed_id`),
|
|
|
CONSTRAINT `FK_follow_followed` FOREIGN KEY (`followed_id`) REFERENCES `users` (`id`),
|
|
|
CONSTRAINT `FK_follow_follower` FOREIGN KEY (`follower_id`) REFERENCES `users` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `messages` (
|
|
|
`message_type` enum('system','reminder','alert') DEFAULT NULL,
|
|
|
`content` text,
|
|
|
`user_id` bigint NOT NULL,
|
|
|
`created_at` timestamp NOT NULL,
|
|
|
PRIMARY KEY (`user_id`,`created_at`),
|
|
|
CONSTRAINT `FK_message_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `monitoring_plans` (
|
|
|
`id` bigint NOT NULL AUTO_INCREMENT,
|
|
|
`frequency` json DEFAULT NULL,
|
|
|
`reminder_settings` json DEFAULT NULL,
|
|
|
`user_id` bigint DEFAULT NULL,
|
|
|
PRIMARY KEY (`id`),
|
|
|
KEY `FK_plan_user` (`user_id`),
|
|
|
CONSTRAINT `FK_plan_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `posts` (
|
|
|
`id` bigint NOT NULL,
|
|
|
`content` text,
|
|
|
`images` json DEFAULT NULL,
|
|
|
`visibility` enum('PUBLIC','PRIVATE') DEFAULT NULL,
|
|
|
`status` enum('pending','approved','rejected') DEFAULT NULL,
|
|
|
`author_id` bigint DEFAULT NULL,
|
|
|
`created_at` timestamp NULL DEFAULT NULL,
|
|
|
`approved_at` timestamp NULL DEFAULT NULL,
|
|
|
`reviewed_by` bigint DEFAULT NULL,
|
|
|
PRIMARY KEY (`id`),
|
|
|
KEY `FK_post_author` (`author_id`),
|
|
|
KEY `FK_post_reviewer` (`reviewed_by`),
|
|
|
CONSTRAINT `FK_post_author` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`),
|
|
|
CONSTRAINT `FK_post_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `admins` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `private_messages` (
|
|
|
`url` varchar(255) DEFAULT NULL,
|
|
|
`content` text,
|
|
|
`message_type` enum('text','image','audio') DEFAULT NULL,
|
|
|
`sender_id` bigint NOT NULL,
|
|
|
`receiver_id` bigint NOT NULL,
|
|
|
`created_at` timestamp NOT NULL,
|
|
|
PRIMARY KEY (`sender_id`,`receiver_id`,`created_at`),
|
|
|
KEY `FK_private_message_receiver` (`receiver_id`),
|
|
|
CONSTRAINT `FK_private_message_receiver` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`),
|
|
|
CONSTRAINT `FK_private_message_sender` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `reports_users` (
|
|
|
`reporter_id` bigint NOT NULL,
|
|
|
`reported_user_id` bigint NOT NULL,
|
|
|
`report_type` varchar(50) DEFAULT NULL,
|
|
|
`description` text,
|
|
|
`status` enum('pending','approved','rejected') DEFAULT NULL,
|
|
|
`images_url` json DEFAULT NULL,
|
|
|
`reviewed_by` bigint DEFAULT NULL,
|
|
|
`processed_at` timestamp NULL DEFAULT NULL,
|
|
|
`result` text,
|
|
|
`created_at` timestamp NOT NULL,
|
|
|
PRIMARY KEY (`reporter_id`,`reported_user_id`,`created_at`),
|
|
|
KEY `FK_report_user_reported` (`reported_user_id`),
|
|
|
KEY `FK_report_user_admin` (`reviewed_by`),
|
|
|
CONSTRAINT `FK_report_user_admin` FOREIGN KEY (`reviewed_by`) REFERENCES `admins` (`id`),
|
|
|
CONSTRAINT `FK_report_user_reported` FOREIGN KEY (`reported_user_id`) REFERENCES `users` (`id`),
|
|
|
CONSTRAINT `FK_report_user_reporter` FOREIGN KEY (`reporter_id`) REFERENCES `users` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
-- 4. 创建依赖其他表的表
|
|
|
CREATE TABLE `family_members` (
|
|
|
`family_id` bigint NOT NULL,
|
|
|
`user_id` bigint NOT NULL,
|
|
|
PRIMARY KEY (`family_id`,`user_id`),
|
|
|
KEY `FK_family_member_user` (`user_id`),
|
|
|
CONSTRAINT `FK_family_member_family` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`),
|
|
|
CONSTRAINT `FK_family_member_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `health_data` (
|
|
|
`id` bigint NOT NULL AUTO_INCREMENT,
|
|
|
`data_type` enum('glucose','heart_rate','oxygen_saturation') DEFAULT NULL,
|
|
|
`value` decimal(8,2) DEFAULT NULL,
|
|
|
`unit` varchar(20) DEFAULT NULL,
|
|
|
`measurement_time` timestamp NULL DEFAULT NULL,
|
|
|
`data_source` enum('manual','auto') DEFAULT NULL,
|
|
|
`user_id` bigint DEFAULT NULL,
|
|
|
`device_id` bigint DEFAULT NULL,
|
|
|
`created_at` timestamp NULL DEFAULT NULL,
|
|
|
PRIMARY KEY (`id`),
|
|
|
KEY `FK_health_user` (`user_id`),
|
|
|
KEY `FK_health_device` (`device_id`),
|
|
|
CONSTRAINT `FK_health_device` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`),
|
|
|
CONSTRAINT `FK_health_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `home_banners` (
|
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
|
`image_url` varchar(255) DEFAULT NULL,
|
|
|
`link_url` varchar(255) DEFAULT NULL,
|
|
|
`interval_time` int DEFAULT NULL,
|
|
|
`created_by` bigint DEFAULT NULL,
|
|
|
PRIMARY KEY (`id`),
|
|
|
KEY `FK_banner_admin` (`created_by`),
|
|
|
CONSTRAINT `FK_banner_admin` FOREIGN KEY (`created_by`) REFERENCES `admins` (`id`)
|
|
|
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `interactions` (
|
|
|
`content` text,
|
|
|
`commenter_id` bigint NOT NULL,
|
|
|
`target_user_id` bigint DEFAULT NULL,
|
|
|
`post_id` bigint NOT NULL,
|
|
|
`created_at` timestamp NOT NULL,
|
|
|
PRIMARY KEY (`commenter_id`,`post_id`,`created_at`),
|
|
|
KEY `FK_interaction_target_user` (`target_user_id`),
|
|
|
KEY `FK_interaction_post` (`post_id`),
|
|
|
CONSTRAINT `FK_interaction_commenter` FOREIGN KEY (`commenter_id`) REFERENCES `users` (`id`),
|
|
|
CONSTRAINT `FK_interaction_post` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
|
|
|
CONSTRAINT `FK_interaction_target_user` FOREIGN KEY (`target_user_id`) REFERENCES `users` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `likes` (
|
|
|
`user_id` bigint NOT NULL,
|
|
|
`target_id` bigint NOT NULL,
|
|
|
PRIMARY KEY (`user_id`,`target_id`),
|
|
|
KEY `FK_like_post` (`target_id`),
|
|
|
CONSTRAINT `FK_like_post` FOREIGN KEY (`target_id`) REFERENCES `posts` (`id`),
|
|
|
CONSTRAINT `FK_like_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `reports_posts` (
|
|
|
`reporter_id` bigint NOT NULL,
|
|
|
`post_id` bigint NOT NULL,
|
|
|
`report_type` varchar(50) DEFAULT NULL,
|
|
|
`description` text,
|
|
|
`status` enum('pending','reviewed','dismissed') DEFAULT NULL,
|
|
|
`reviewed_by` bigint DEFAULT NULL,
|
|
|
`processed_at` timestamp NULL DEFAULT NULL,
|
|
|
`created_at` timestamp NOT NULL,
|
|
|
`result` text,
|
|
|
PRIMARY KEY (`reporter_id`,`post_id`,`created_at`),
|
|
|
KEY `FK_report_post_post` (`post_id`),
|
|
|
KEY `FK_report_post_admin` (`reviewed_by`),
|
|
|
CONSTRAINT `FK_report_post_admin` FOREIGN KEY (`reviewed_by`) REFERENCES `admins` (`id`),
|
|
|
CONSTRAINT `FK_report_post_post` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
|
|
|
CONSTRAINT `FK_report_post_reporter` FOREIGN KEY (`reporter_id`) REFERENCES `users` (`id`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
CREATE TABLE `tweets` (
|
|
|
`id` bigint NOT NULL AUTO_INCREMENT,
|
|
|
`title` varchar(200) DEFAULT NULL,
|
|
|
`url` varchar(500) NOT NULL,
|
|
|
`content` text,
|
|
|
`tags` json DEFAULT NULL,
|
|
|
`view_count` int DEFAULT NULL,
|
|
|
`create_at` timestamp NULL DEFAULT NULL,
|
|
|
`base_id` bigint DEFAULT NULL,
|
|
|
PRIMARY KEY (`id`),
|
|
|
KEY `idx_tweets_base_id` (`base_id`),
|
|
|
KEY `idx_tweets_create_at` (`create_at`),
|
|
|
CONSTRAINT `FK_tweet_knowledge_base` FOREIGN KEY (`base_id`) REFERENCES `knowledge_base` (`id`)
|
|
|
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
|
|
-- ==================== 插入数据（按依赖顺序） ====================
|
|
|
|
|
|
-- 1. 先插入 roles
|
|
|
INSERT INTO `roles` VALUES
|
|
|
(1,'超级管理员','系统最高权限管理员','[\"user:manage\", \"content:manage\", \"system:config\"]','2024-01-01 08:00:00'),
|
|
|
(2,'内容管理员','负责内容审核和管理','[\"content:review\", \"content:publish\"]','2024-01-01 08:00:00'),
|
|
|
(3,'客服管理员','处理用户反馈和举报','[\"report:handle\", \"user:support\"]','2024-01-01 08:00:00');
|
|
|
|
|
|
-- 2. 插入 users
|
|
|
INSERT INTO `users` VALUES
|
|
|
(1,'张三','wx_zhangsan','https://example.com/avatar1.jpg','male','1985-03-15','active','2024-01-15 18:00:00','2024-01-15 18:00:00',NULL,NULL),
|
|
|
(2,'李四','wx_lisi','https://example.com/avatar2.jpg','female','1990-07-22','active','2024-01-16 19:30:00','2024-01-16 19:30:00',NULL,NULL),
|
|
|
(3,'张三','wx_wangwu','/images/avatar1.png','female','1988-12-07','active','2024-01-17 17:15:00','2025-12-09 07:19:23',NULL,'/images/covers/profile-cover.jpg'),
|
|
|
(4,'赵六','wx_zhaoliu','https://example.com/avatar4.jpg','female','1992-05-30','inactive','2024-01-18 22:20:00','2024-01-18 22:20:00',NULL,NULL),
|
|
|
(5,'孙七','wx_sunqi','https://example.com/avatar5.jpg','male','1987-09-12','active','2024-01-20 00:45:00','2024-01-20 00:45:00',NULL,NULL),
|
|
|
(176,'美滋滋','demo_user_1','/images/avatar1.png','male',NULL,'active','2025-12-09 05:20:11','2025-12-09 05:20:11',NULL,NULL);
|
|
|
|
|
|
-- 3. 插入 admins
|
|
|
INSERT INTO `admins` VALUES
|
|
|
(1,'11111','黄思慧','15382829127','2025-12-09 07:07:43','2024-01-01 16:00:00',1),
|
|
|
(2,'hashed_password_2','李审核员','13800138002','2024-11-05 18:30:00','2024-01-01 16:00:00',2),
|
|
|
(3,'hashed_password_3','王客服','13800138003','2024-11-05 19:15:00','2024-01-01 16:00:00',3);
|
|
|
|
|
|
-- 4. 插入其他表的数据（按照你的原始数据）
|
|
|
-- 这里按照你的原始INSERT语句顺序，但我会调整以确保外键依赖
|
|
|
|
|
|
-- 插入 knowledge_base
|
|
|
INSERT INTO `knowledge_base` VALUES
|
|
|
(4,'新华网健康','http://www.xinhuanet.com/health/','2025-12-09 07:11:07'),
|
|
|
(5,'人民健康频道','http://health.people.com.cn/','2025-12-09 05:18:04');
|
|
|
|
|
|
-- 插入 ai_conversations
|
|
|
INSERT INTO `ai_conversations` VALUES
|
|
|
(1,'血糖正常范围是多少？','正常空腹血糖范围为3.9-6.1mmol/L，餐后2小时血糖应小于7.8mmol/L。','2024-11-05 17:00:00',1),
|
|
|
(2,'如何控制餐后血糖？','建议控制碳水摄入、适量运动、按时用药，并定期监测血糖。','2024-11-05 18:00:00',2),
|
|
|
(3,'糖尿病可以吃什么水果？','建议选择低GI水果如苹果、梨、草莓等，注意控制分量。','2024-11-05 19:00:00',3);
|
|
|
|
|
|
-- 插入 devices
|
|
|
INSERT INTO `devices` VALUES
|
|
|
(1,'血糖仪001','血糖仪','GT-100','00:1B:44:11:3A:B7','bluetooth','active','2024-11-05 16:30:00','2024-01-10',1),
|
|
|
(2,'智能手环','手环','Band-Pro','00:1B:44:11:3A:B8','bluetooth','active','2024-11-05 17:15:00','2024-02-15',2),
|
|
|
(3,'血氧仪','血氧监测','OX-200','00:1B:44:11:3A:B9','bluetooth','inactive','2024-10-20 21:00:00','2024-03-20',3);
|
|
|
|
|
|
-- 插入 families
|
|
|
INSERT INTO `families` VALUES
|
|
|
(1,'张家','张三的家庭组',1),
|
|
|
(2,'李家','李四的家庭组',2);
|
|
|
|
|
|
-- 插入 family_members
|
|
|
INSERT INTO `family_members` VALUES
|
|
|
(1,1),(2,2),(1,3),(2,4);
|
|
|
|
|
|
-- 插入 follows
|
|
|
INSERT INTO `follows` VALUES
|
|
|
(2,1),(3,1),(1,2),(3,2),(1,3);
|
|
|
|
|
|
-- 插入 health_data
|
|
|
INSERT INTO `health_data` VALUES
|
|
|
(1,'glucose',5.60,'mmol/L','2024-11-05 16:00:00','auto',1,1,'2024-11-05 16:00:00'),
|
|
|
(2,'glucose',6.20,'mmol/L','2024-11-05 20:30:00','manual',1,NULL,'2024-11-05 20:30:00'),
|
|
|
(3,'heart_rate',72.00,'bpm','2024-11-05 17:00:00','auto',2,2,'2024-11-05 17:00:00'),
|
|
|
(4,'oxygen_saturation',98.00,'%','2024-11-05 18:00:00','auto',3,3,'2024-11-05 18:00:00'),
|
|
|
(5,'glucose',5.80,'mmol/L','2024-11-05 22:00:00','manual',2,NULL,'2024-11-05 22:00:00');
|
|
|
|
|
|
-- 插入 home_banners
|
|
|
INSERT INTO `home_banners` VALUES
|
|
|
(7,'http://localhost:8080/uploads/banner-images/f4513832-8f7d-47d7-89ff-8c53b0a27cf1.jpg','24',5000,1),
|
|
|
(8,'http://localhost:8080/uploads/banner-images/0cce189f-537d-4f17-9570-664d2586cc34.jpg','6',5000,1),
|
|
|
(9,'http://localhost:8080/uploads/banner-images/f8dc20f0-c746-4951-b147-da1e8ed2762b.jpg','8',5000,1),
|
|
|
(10,'http://localhost:8080/uploads/banner-images/0a15e587-da0c-49be-8d3e-f13fccabbcc7.jpg','22',5000,1);
|
|
|
|
|
|
-- 插入 interactions
|
|
|
INSERT INTO `interactions` VALUES
|
|
|
('@李四 你的运动计划是怎样的？',1,2,2,'2024-11-05 19:30:00'),
|
|
|
('很好的分享，学到了！',2,NULL,1,'2024-11-05 18:45:00'),
|
|
|
('@张三 我主要是晨跑和力量训练',2,1,2,'2024-11-05 19:35:00'),
|
|
|
('谢谢你的经验分享！',3,NULL,1,'2024-11-05 19:00:00');
|
|
|
|
|
|
-- 插入 likes
|
|
|
INSERT INTO `likes` VALUES
|
|
|
(1,1),(2,1),(3,1),(1,2),(2,3);
|
|
|
|
|
|
-- 插入 messages
|
|
|
INSERT INTO `messages` VALUES
|
|
|
('system','系统维护通知：本周六凌晨2-4点进行系统升级',1,'2024-11-05 16:00:00'),
|
|
|
('reminder','提醒：您今天还有1次血糖未测量',1,'2024-11-06 02:00:00'),
|
|
|
('alert','警告：您的血糖值异常，请及时处理',2,'2024-11-05 20:30:00');
|
|
|
|
|
|
-- 插入 monitoring_plans
|
|
|
INSERT INTO `monitoring_plans` (`id`, `frequency`, `reminder_settings`, `user_id`) VALUES
|
|
|
(4, '[
|
|
|
{
|
|
|
"time": "空腹",
|
|
|
"displayTime": "07:00",
|
|
|
"alarm": true,
|
|
|
"alarmTime": "07:00",
|
|
|
"days": [
|
|
|
{"day": "一", "status": "checked", "text": "✓", "completed": true},
|
|
|
{"day": "二", "status": "checked", "text": "✓", "completed": true},
|
|
|
{"day": "三", "status": "empty", "text": "", "completed": false},
|
|
|
{"day": "四", "status": "empty", "text": "", "completed": false},
|
|
|
{"day": "五", "status": "empty", "text": "", "completed": false},
|
|
|
{"day": "六", "status": "empty", "text": "", "completed": false},
|
|
|
{"day": "日", "status": "empty", "text": "", "completed": false}
|
|
|
]
|
|
|
},
|
|
|
{
|
|
|
"time": "早餐后",
|
|
|
"displayTime": "09:00",
|
|
|
"alarm": true,
|
|
|
"alarmTime": "09:00",
|
|
|
"days": [
|
|
|
{"day": "一", "status": "checked", "text": "✓", "completed": true},
|
|
|
{"day": "二", "status": "empty", "text": "", "completed": false},
|
|
|
{"day": "三", "status": "empty", "text": "", "completed": false},
|
|
|
{"day": "四", "status": "empty", "text": "", "completed": false},
|
|
|
{"day": "五", "status": "empty", "text": "", "completed": false},
|
|
|
{"day": "六", "status": "empty", "text": "", "completed": false},
|
|
|
{"day": "日", "status": "empty", "text": "", "completed": false}
|
|
|
]
|
|
|
},
|
|
|
{
|
|
|
"time": "午餐前",
|
|
|
"displayTime": "11:30",
|
|
|
"alarm": false,
|
|
|
"alarmTime": "",
|
|
|
"days": [
|
|
|
{"day": "一", "status": "disabled", "text": "", "completed": false},
|
|
|
{"day": "二", "status": "disabled", "text": "", "completed": false},
|
|
|
{"day": "三", "status": "disabled", "text": "", "completed": false},
|
|
|
{"day": "四", "status": "disabled", "text": "", "completed": false},
|
|
|
{"day": "五", "status": "disabled", "text": "", "completed": false},
|
|
|
{"day": "六", "status": "empty", "text": "", "completed": false},
|
|
|
{"day": "日", "status": "empty", "text": "", "completed": false}
|
|
|
]
|
|
|
}
|
|
|
]',
|
|
|
'[
|
|
|
{
|
|
|
"id": 1,
|
|
|
"time": "07:00",
|
|
|
"label": "空腹测量",
|
|
|
"repeat": "daily",
|
|
|
"enabled": true,
|
|
|
"days": [true, true, true, true, true, true, true]
|
|
|
},
|
|
|
{
|
|
|
"id": 2,
|
|
|
"time": "09:00",
|
|
|
"label": "早餐后测量",
|
|
|
"repeat": "weekdays",
|
|
|
"enabled": true,
|
|
|
"days": [true, true, true, true, true, false, false]
|
|
|
},
|
|
|
{
|
|
|
"id": 3,
|
|
|
"time": "17:30",
|
|
|
"label": "晚餐前",
|
|
|
"repeat": "daily",
|
|
|
"enabled": true,
|
|
|
"days": [true, true, true, true, true, true, true]
|
|
|
}
|
|
|
]', 1);
|
|
|
|
|
|
-- 插入 posts
|
|
|
INSERT INTO `posts` VALUES
|
|
|
(1,'今天血糖控制得很好，分享一下我的饮食经验！','[\"img1.jpg\", \"img2.jpg\"]','PUBLIC','approved',1,'2024-11-05 18:00:00','2024-11-05 18:30:00',2),
|
|
|
(2,'运动对血糖控制真的很重要，大家要坚持锻炼！','[\"exercise.jpg\"]','PUBLIC','approved',2,'2024-11-05 19:00:00','2024-11-05 19:15:00',2),
|
|
|
(3,'求助：最近血糖波动比较大，有什么建议吗？',NULL,'PUBLIC','pending',3,'2024-11-05 20:00:00',NULL,NULL),
|
|
|
(4,'私人记录：今天的用药情况',NULL,'PRIVATE','approved',1,'2024-11-05 21:00:00','2024-11-05 21:05:00',2);
|
|
|
|
|
|
-- 插入 private_messages
|
|
|
INSERT INTO `private_messages` VALUES
|
|
|
(NULL,'你好，想请教一下血糖监测的问题','text',2,1,'2024-11-05 22:00:00'),
|
|
|
(NULL,'当然可以，有什么具体问题？','text',1,2,'2024-11-05 22:05:00'),
|
|
|
('audio_message.mp3',NULL,'audio',3,2,'2024-11-05 23:00:00');
|
|
|
|
|
|
-- 插入 reports_posts
|
|
|
INSERT INTO `reports_posts` VALUES
|
|
|
(2,3,'违规内容','帖子包含不当内容','reviewed',3,'2024-11-06 01:00:00','2024-11-06 00:30:00','内容已审核，无违规'),
|
|
|
(3,1,'广告嫌疑','疑似推广产品','pending',NULL,NULL,'2024-11-06 02:00:00',NULL);
|
|
|
|
|
|
-- 插入 reports_users
|
|
|
INSERT INTO `reports_users` VALUES
|
|
|
(2,3,'虚假信息','发布不实医疗信息','pending',NULL,NULL,NULL,NULL,'2024-11-06 00:00:00');
|
|
|
|
|
|
-- 插入 tweets（这里只插入前几条作为示例）
|
|
|
INSERT INTO `tweets` VALUES
|
|
|
(7,'权威发布','http://www.xinhuanet.com/health/spq/index.html','减重不再难...','[\"健康控糖\", \"健康运动\"]',0,'2025-12-09 05:15:34',4),
|
|
|
(8,'权威发布','http://www.xinhuanet.com/health/qwfb/index.html','国家卫生健康委发布新版三级医院评审标准...','[\"健康饮食\"]',8,'2025-12-09 05:15:35',4);
|
|
|
|
|
|
-- 重新启用外键检查
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
|
|
-- 显示成功消息
|
|
|
SELECT '数据库 glucowisebase 创建并初始化成功！' AS Message;
|
|
|
|
|
|
-- 用户活动记录表
|
|
|
CREATE TABLE IF NOT EXISTS user_activity (
|
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
|
user_id BIGINT NOT NULL COMMENT '用户ID',
|
|
|
activity_date DATE NOT NULL COMMENT '活动日期',
|
|
|
login_count INT DEFAULT 1 COMMENT '当日登录次数',
|
|
|
first_login_time DATETIME COMMENT '首次登录时间',
|
|
|
last_login_time DATETIME COMMENT '最后登录时间',
|
|
|
is_active BOOLEAN DEFAULT TRUE COMMENT '是否活跃',
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
|
|
-- 索引
|
|
|
UNIQUE KEY uk_user_date (user_id, activity_date),
|
|
|
INDEX idx_activity_date (activity_date),
|
|
|
INDEX idx_user_id (user_id),
|
|
|
INDEX idx_is_active (is_active),
|
|
|
|
|
|
-- 外键约束
|
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户活动记录表';
|
|
|
|
|
|
-- 每日活跃用户统计表（预聚合数据，提高查询性能）
|
|
|
CREATE TABLE IF NOT EXISTS daily_active_stats (
|
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
|
stat_date DATE NOT NULL COMMENT '统计日期',
|
|
|
active_user_count INT DEFAULT 0 COMMENT '活跃用户数',
|
|
|
new_user_count INT DEFAULT 0 COMMENT '新用户数',
|
|
|
total_login_count INT DEFAULT 0 COMMENT '总登录次数',
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
|
|
-- 索引
|
|
|
UNIQUE KEY uk_stat_date (stat_date),
|
|
|
INDEX idx_stat_date (stat_date)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='每日活跃用户统计表';
|
|
|
|
|
|
-- 插入一些示例数据（最近30天）
|
|
|
INSERT INTO daily_active_stats (stat_date, active_user_count, new_user_count, total_login_count) VALUES
|
|
|
('2024-11-12', 120, 5, 180),
|
|
|
('2024-11-13', 135, 8, 210),
|
|
|
('2024-11-14', 128, 3, 195),
|
|
|
('2024-11-15', 142, 12, 225),
|
|
|
('2024-11-16', 156, 6, 240),
|
|
|
('2024-11-17', 148, 4, 220),
|
|
|
('2024-11-18', 132, 7, 200),
|
|
|
('2024-11-19', 145, 9, 215),
|
|
|
('2024-11-20', 158, 11, 245),
|
|
|
('2024-11-21', 162, 5, 250),
|
|
|
('2024-11-22', 155, 8, 235),
|
|
|
('2024-11-23', 149, 6, 225),
|
|
|
('2024-11-24', 138, 4, 205),
|
|
|
('2024-11-25', 125, 3, 185),
|
|
|
('2024-11-26', 140, 7, 210),
|
|
|
('2024-11-27', 152, 10, 230),
|
|
|
('2024-11-28', 168, 12, 260),
|
|
|
('2024-11-29', 175, 8, 270),
|
|
|
('2024-11-30', 182, 15, 285),
|
|
|
('2024-12-01', 178, 6, 275),
|
|
|
('2024-12-02', 165, 9, 255),
|
|
|
('2024-12-03', 172, 11, 265),
|
|
|
('2024-12-04', 185, 13, 290),
|
|
|
('2024-12-05', 192, 7, 300),
|
|
|
('2024-12-06', 188, 10, 295),
|
|
|
('2024-12-07', 195, 14, 310),
|
|
|
('2024-12-08', 201, 8, 320),
|
|
|
('2024-12-09', 198, 12, 315),
|
|
|
('2024-12-10', 205, 9, 325),
|
|
|
('2024-12-11', 210, 11, 335);
|
|
|
|
|
|
-- 禁用外键检查（如果有外键依赖）
|
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
|
|
-- 1. 备份当前自增值
|
|
|
SELECT MAX(id) INTO @max_id FROM users;
|
|
|
|
|
|
-- 2. 删除主键并修改为自增
|
|
|
ALTER TABLE users
|
|
|
DROP PRIMARY KEY,
|
|
|
MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT FIRST,
|
|
|
ADD PRIMARY KEY (id);
|
|
|
|
|
|
-- 3. 设置自增起始值为当前最大值+1
|
|
|
SET @new_start = IFNULL(@max_id, 0) + 1;
|
|
|
SET @sql = CONCAT('ALTER TABLE users AUTO_INCREMENT = ', @new_start);
|
|
|
PREPARE stmt FROM @sql;
|
|
|
EXECUTE stmt;
|
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
|
|
-- 4. 添加其他索引（可选）
|
|
|
ALTER TABLE users
|
|
|
ADD UNIQUE INDEX idx_wx_id (wx_id),
|
|
|
ADD INDEX idx_status (status),
|
|
|
ADD INDEX idx_created_at (created_at);
|
|
|
|
|
|
-- 重新启用外键检查
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
