998211c483
- 从服务器拉取完整代码 - 按框架规范整理项目结构 - 配置 Drone CI 测试环境部署 - 包含后端(FastAPI)、前端(Vue3)、管理端 技术栈: Vue3 + TypeScript + FastAPI + MySQL
96 lines
5.3 KiB
SQL
96 lines
5.3 KiB
SQL
-- 考培练系统时区数据迁移脚本 - 简化版
|
||
-- 将所有UTC时间戳转换为北京时间（+8小时）
|
||
-- 只更新确定存在的字段
|
||
-- 执行时间：2025-10-17
|
||
|
||
USE kaopeilian;
|
||
SET SQL_SAFE_UPDATES = 0;
|
||
|
||
-- 核心表：users
|
||
UPDATE users SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
UPDATE users SET last_login_at = DATE_ADD(last_login_at, INTERVAL 8 HOUR) WHERE last_login_at IS NOT NULL;
|
||
UPDATE users SET password_changed_at = DATE_ADD(password_changed_at, INTERVAL 8 HOUR) WHERE password_changed_at IS NOT NULL;
|
||
UPDATE users SET deleted_at = DATE_ADD(deleted_at, INTERVAL 8 HOUR) WHERE deleted_at IS NOT NULL;
|
||
|
||
-- teams
|
||
UPDATE teams SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
UPDATE teams SET deleted_at = DATE_ADD(deleted_at, INTERVAL 8 HOUR) WHERE deleted_at IS NOT NULL;
|
||
|
||
-- user_teams
|
||
UPDATE user_teams SET joined_at = DATE_ADD(joined_at, INTERVAL 8 HOUR);
|
||
|
||
-- courses
|
||
UPDATE courses SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
UPDATE courses SET published_at = DATE_ADD(published_at, INTERVAL 8 HOUR) WHERE published_at IS NOT NULL;
|
||
UPDATE courses SET broadcast_generated_at = DATE_ADD(broadcast_generated_at, INTERVAL 8 HOUR) WHERE broadcast_generated_at IS NOT NULL;
|
||
UPDATE courses SET deleted_at = DATE_ADD(deleted_at, INTERVAL 8 HOUR) WHERE deleted_at IS NOT NULL;
|
||
|
||
-- course_materials
|
||
UPDATE course_materials SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
UPDATE course_materials SET deleted_at = DATE_ADD(deleted_at, INTERVAL 8 HOUR) WHERE deleted_at IS NOT NULL;
|
||
|
||
-- knowledge_points
|
||
UPDATE knowledge_points SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
|
||
-- exams
|
||
UPDATE exams SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
UPDATE exams SET start_time = DATE_ADD(start_time, INTERVAL 8 HOUR);
|
||
UPDATE exams SET end_time = DATE_ADD(end_time, INTERVAL 8 HOUR) WHERE end_time IS NOT NULL;
|
||
|
||
-- exam_results
|
||
UPDATE exam_results SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
|
||
-- exam_mistakes
|
||
UPDATE exam_mistakes SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
UPDATE exam_mistakes SET mastered_at = DATE_ADD(mastered_at, INTERVAL 8 HOUR) WHERE mastered_at IS NOT NULL;
|
||
|
||
-- questions
|
||
UPDATE questions SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
|
||
-- positions
|
||
UPDATE positions SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
UPDATE positions SET deleted_at = DATE_ADD(deleted_at, INTERVAL 8 HOUR) WHERE deleted_at IS NOT NULL;
|
||
|
||
-- position_members
|
||
UPDATE position_members SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
UPDATE position_members SET joined_at = DATE_ADD(joined_at, INTERVAL 8 HOUR);
|
||
UPDATE position_members SET deleted_at = DATE_ADD(deleted_at, INTERVAL 8 HOUR) WHERE deleted_at IS NOT NULL;
|
||
|
||
-- system_logs (只有 created_at)
|
||
UPDATE system_logs SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR);
|
||
|
||
-- training相关表
|
||
UPDATE training_scenes SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
UPDATE training_scenes SET deleted_at = DATE_ADD(deleted_at, INTERVAL 8 HOUR) WHERE deleted_at IS NOT NULL;
|
||
|
||
UPDATE training_sessions SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
UPDATE training_sessions SET start_time = DATE_ADD(start_time, INTERVAL 8 HOUR);
|
||
UPDATE training_sessions SET end_time = DATE_ADD(end_time, INTERVAL 8 HOUR) WHERE end_time IS NOT NULL;
|
||
|
||
UPDATE training_messages SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
|
||
UPDATE training_reports SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
|
||
-- practice相关表
|
||
UPDATE practice_scenes SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
|
||
UPDATE practice_sessions SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
UPDATE practice_sessions SET start_time = DATE_ADD(start_time, INTERVAL 8 HOUR) WHERE start_time IS NOT NULL;
|
||
UPDATE practice_sessions SET end_time = DATE_ADD(end_time, INTERVAL 8 HOUR) WHERE end_time IS NOT NULL;
|
||
|
||
UPDATE practice_dialogues SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
|
||
UPDATE practice_reports SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR), updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR);
|
||
|
||
SET SQL_SAFE_UPDATES = 1;
|
||
|
||
-- 验证
|
||
SELECT '时区迁移完成！' as message;
|
||
SELECT '用户示例：' as '';
|
||
SELECT id, username, created_at, updated_at FROM users LIMIT 3;
|
||
SELECT '课程示例：' as '';
|
||
SELECT id, title, created_at, updated_at FROM courses LIMIT 3;
|
||
SELECT '当前时间：' as '';
|
||
SELECT NOW() as beijing_time, UTC_TIMESTAMP() as utc_time;
|
||
|