aivfo-tl-3.0/sql/精简测试数据.sql

-- =============================================================
-- 时差项目 · 数据库测试数据精简脚本（开发用）
-- 目的：库结构不动，只精简测试数据；按业务主线保留少量可关联的种子数据
-- 默认参数：保留 3 个培养记录 + 关联链；设备流水留近 30 天；日志清空；配置字典全留
-- 作者：依据 项目文档/需求文档/11-数据库测试数据精简方案.md 生成
--
-- ⚠️ 执行前务必：
--   1) 全量备份所有库（mysqldump）
--   2) 仅在【开发库】执行，不要碰生产
--   3) 先整段在测试库演练，确认系统能启动、能查到保留的病例完整链
--   4) 各库名按你的实际库名替换（下方用占位库名，已按模块分段）
-- 说明：很多表无主外键，故用"保留集合临时表 + NOT IN 删除"保证不断链
-- =============================================================

-- ╔══════════════════════════════════════════════════════════╗
-- ║ 第一部分：business 库（病例/胚胎/图片/视频/识别/标记）          ║
-- ║   USE aivfo_business;   -- ← 改成你的实际业务库名             ║
-- ╚══════════════════════════════════════════════════════════╝
USE `aivfo_business`;
SET SQL_SAFE_UPDATES = 0;
START TRANSACTION;

-- 1. 选种子根：保留最近 3 个培养记录（可改 WHERE/LIMIT 或手动指定 id）
DROP TEMPORARY TABLE IF EXISTS keep_record;
CREATE TEMPORARY TABLE keep_record (id BIGINT PRIMARY KEY);
INSERT INTO keep_record(id)
SELECT id FROM embryo_culture_record ORDER BY id DESC LIMIT 3;
-- 如需覆盖不同状态，改用：按 state 各取若干，例如：
--   INSERT INTO keep_record SELECT id FROM (
--     (SELECT id FROM embryo_culture_record WHERE state=1 ORDER BY id DESC LIMIT 1)
--     UNION (SELECT id FROM embryo_culture_record WHERE state=2 ORDER BY id DESC LIMIT 1)
--     UNION (SELECT id FROM embryo_culture_record WHERE state=3 ORDER BY id DESC LIMIT 1)
--   ) t;

-- 2. 衍生保留集合：case_id / tl_sn+house_sn / video_id / picture_uuid
DROP TEMPORARY TABLE IF EXISTS keep_case;
CREATE TEMPORARY TABLE keep_case (case_id VARCHAR(64));
INSERT INTO keep_case SELECT DISTINCT case_id FROM embryo_culture_record WHERE id IN (SELECT id FROM keep_record);

DROP TEMPORARY TABLE IF EXISTS keep_dev;
CREATE TEMPORARY TABLE keep_dev (tl_sn VARCHAR(64), house_sn INT);
INSERT INTO keep_dev SELECT DISTINCT tl_sn, house_sn FROM embryo_culture_record WHERE id IN (SELECT id FROM keep_record);

DROP TEMPORARY TABLE IF EXISTS keep_video;
CREATE TEMPORARY TABLE keep_video (id BIGINT PRIMARY KEY);
INSERT INTO keep_video SELECT id FROM video WHERE embryo_culture_record_id IN (SELECT id FROM keep_record);

-- 3. 删除"跟随主线"的表里 不属于保留根 的数据（先子后父）
DELETE FROM video_update   WHERE video_id NOT IN (SELECT id FROM keep_video);
DELETE FROM video_pictures WHERE video_id NOT IN (SELECT id FROM keep_video);
DELETE FROM video_splice   WHERE video_id NOT IN (SELECT id FROM keep_video);
DELETE FROM video          WHERE embryo_culture_record_id NOT IN (SELECT id FROM keep_record);

DELETE FROM picture_identify_result_cache WHERE embryo_culture_record_id NOT IN (SELECT id FROM keep_record);
DELETE FROM picture_identify_middle        WHERE embryo_culture_record_id NOT IN (SELECT id FROM keep_record);
DELETE FROM picture_identify_result        WHERE embryo_culture_record_id NOT IN (SELECT id FROM keep_record);

DELETE FROM embryo_mark_splice WHERE embryo_culture_record_id NOT IN (SELECT id FROM keep_record);
DELETE FROM embryo_mark         WHERE embryo_culture_record_id NOT IN (SELECT id FROM keep_record);

DELETE FROM picture WHERE embryo_culture_record_id NOT IN (SELECT id FROM keep_record);
DELETE FROM embryo  WHERE embryo_culture_record_id NOT IN (SELECT id FROM keep_record);

-- 4. 删除主线根 与 病例（保留被引用的 case）
DELETE FROM embryo_culture_record WHERE id NOT IN (SELECT id FROM keep_record);
DELETE FROM case_info WHERE case_id NOT IN (SELECT case_id FROM keep_case);
DELETE FROM balance   WHERE (tl_sn, house_sn) NOT IN (SELECT tl_sn, house_sn FROM keep_dev);

-- 5. 流水/日志类：清空（开发查结构不需历史）
TRUNCATE TABLE `log`;
TRUNCATE TABLE `message`;
TRUNCATE TABLE `debug_picture`;

-- 6. 配置/字典表：不动（dictionary / embryo_level / mark / mark_key / mark_weight /
--    identify_config / photo_search_button / software_version）

COMMIT;

-- 7. ⚠️ 图片动态分表 t_picture_*：每个培养记录的图片在 embryo_culture_record.picture_table_name 指定的分表里
--    保留根的分表保留，其余分表整张 DROP。手动执行下面生成的语句：
SELECT CONCAT('DROP TABLE IF EXISTS `', table_name, '`;') AS drop_sql
FROM information_schema.tables
WHERE table_schema = 'aivfo_business'
  AND table_name LIKE 't_picture%'
  AND table_name NOT IN (
      SELECT picture_table_name FROM embryo_culture_record WHERE id IN (SELECT id FROM keep_record)
  );
-- 把查询结果复制出来执行即可（不自动执行，避免误删）。
SET SQL_SAFE_UPDATES = 1;


-- ╔══════════════════════════════════════════════════════════╗
-- ║ 第二部分：tl_setting 库（报警历史/环境采样/指令流水）          ║
-- ║   配置类全留；流水类留近 30 天                                ║
-- ╚══════════════════════════════════════════════════════════╝
USE `aivfo_tl_setting`;   -- ← 改成你的实际库名
SET SQL_SAFE_UPDATES = 0;
START TRANSACTION;

DELETE FROM alarm_data              WHERE create_time  < DATE_SUB(NOW(), INTERVAL 30 DAY);
DELETE FROM alarm_send_info         WHERE create_time  < DATE_SUB(NOW(), INTERVAL 30 DAY);
DELETE FROM environment_temperature WHERE collect_time < DATE_SUB(NOW(), INTERVAL 30 DAY);
DELETE FROM mqtt_message            WHERE create_time  < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 配置/字典/设备表不动：tl_info / tl_setting / house / house_well_setting /
--   house_photograph_setting / house_collect / alarm / alarm_type / alarm_template /
--   alarm_permissions / alarm_personnel / language_configuration / cn_region_info

COMMIT;
SET SQL_SAFE_UPDATES = 1;


-- ╔══════════════════════════════════════════════════════════╗
-- ║ 第三部分：data_transmission 库（视频副本）                    ║
-- ╚══════════════════════════════════════════════════════════╝
USE `aivfo_data_transmission`;   -- ← 改成你的实际库名
SET SQL_SAFE_UPDATES = 0;
START TRANSACTION;
-- 该库 video* 为传输态流水，开发查阅可清空（如需留样改为 WHERE 时间过滤）
TRUNCATE TABLE `video_update`;
TRUNCATE TABLE `video_pictures`;
TRUNCATE TABLE `video_splice`;
TRUNCATE TABLE `video`;
COMMIT;
SET SQL_SAFE_UPDATES = 1;


-- ╔══════════════════════════════════════════════════════════╗
-- ║ 第四部分：services 库 + 框架日志/定时                         ║
-- ╚══════════════════════════════════════════════════════════╝
USE `aivfo_services`;     -- ← 改成你的实际库名
TRUNCATE TABLE `tdi_log`;
-- alarm_contacts 是配置（告警联系人），不动

-- 系统日志库（system_log 表所在库，按实际库名）
-- TRUNCATE TABLE `system_log`;

-- Quartz 定时任务（按实际库名，t_qrtz_* 为运行态，可清触发历史；定义表谨慎）
-- DELETE FROM `t_qrtz_fired_triggers`;
-- DELETE FROM `t_qrtz_scheduler_state`;

-- =============================================================
-- 执行后校验（应能查到保留的病例完整链）：
--   SELECT * FROM embryo_culture_record;                 -- 应剩 3 条
--   SELECT count(*) FROM embryo;                          -- 仅这 3 条记录的胚胎
--   SELECT count(*) FROM picture;                         -- 仅关联图片
--   SELECT count(*) FROM video;                           -- 仅关联视频
-- 配置类表数量应不变。
-- =============================================================