zkxw/docs/sql/migrate-estimated-duration-min-to-h.sql

-- 预估耗时单位：分钟 → 小时
-- 适用表：biz_process、biz_process_route_step、biz_production_task_step
-- 执行前请先备份，并用下方预检语句确认数据格式。
--
-- 生产预检样例（2026-06）：biz_production_task_step 存在 '6h'（5 条），
-- 属「已是小时、仅去单位后缀」，不可 ÷60。
--
-- 迁移规则：
--   1) 纯数字（如 30、480）        → ÷60，视为原分钟值
--   2) 带 h/小时（如 6h、2.5 小时） → 去掉单位，数值不变
--   3) 带 min/分钟（如 30min）      → 去掉单位后 ÷60
--   4) 其它格式                     → 不自动处理，需人工核对

-- ---------- 预检：迁移前查看当前值（只读，可直接执行）----------
-- 注意：biz_production_task_step 与其它两表 collation 可能不一致，UNION 时需显式 COLLATE

-- A. 三表汇总：各值、条数、迁移动作、迁移后预览（小时）
SELECT
  src_table,
  estimated_duration,
  row_cnt,
  CASE
    WHEN estimated_duration REGEXP '^[0-9]+(\\.[0-9]+)?$' THEN '会换算(÷60)' COLLATE utf8mb4_unicode_ci
    WHEN LOWER(TRIM(estimated_duration)) REGEXP '^[0-9]+(\\.[0-9]+)?\\s*(h|小时)$' THEN '规范化(去h)' COLLATE utf8mb4_unicode_ci
    WHEN LOWER(TRIM(estimated_duration)) REGEXP '^[0-9]+(\\.[0-9]+)?\\s*(min|分钟)$' THEN '会换算(去min÷60)' COLLATE utf8mb4_unicode_ci
    ELSE '跳过(需人工)' COLLATE utf8mb4_unicode_ci
  END AS migrate_action,
  CASE
    WHEN estimated_duration REGEXP '^[0-9]+(\\.[0-9]+)?$' THEN TRIM(
      TRAILING '.'
      FROM TRIM(
        TRAILING '0'
        FROM CAST(CAST(estimated_duration AS DECIMAL(12, 4)) / 60 AS CHAR)
      )
    )
    WHEN LOWER(TRIM(estimated_duration)) REGEXP '^[0-9]+(\\.[0-9]+)?\\s*(h|小时)$' THEN TRIM(
      TRAILING '.'
      FROM TRIM(
        TRAILING '0'
        FROM CAST(
          CAST(
            TRIM(
              REPLACE(REPLACE(LOWER(TRIM(estimated_duration)), '小时', ''), 'h', '')
            ) AS DECIMAL(12, 4)
          ) AS CHAR
        )
      )
    )
    WHEN LOWER(TRIM(estimated_duration)) REGEXP '^[0-9]+(\\.[0-9]+)?\\s*(min|分钟)$' THEN TRIM(
      TRAILING '.'
      FROM TRIM(
        TRAILING '0'
        FROM CAST(
          CAST(
            TRIM(
              REPLACE(REPLACE(LOWER(TRIM(estimated_duration)), '分钟', ''), 'min', '')
            ) AS DECIMAL(12, 4)
          ) / 60 AS CHAR
        )
      )
    )
    ELSE NULL
  END AS after_migration_h
FROM (
  SELECT 'biz_process' COLLATE utf8mb4_unicode_ci AS src_table,
         estimated_duration COLLATE utf8mb4_unicode_ci AS estimated_duration,
         COUNT(*) AS row_cnt
  FROM biz_process
  WHERE del_flag = 0 AND estimated_duration IS NOT NULL AND estimated_duration != ''
  GROUP BY estimated_duration
  UNION ALL
  SELECT 'biz_process_route_step' COLLATE utf8mb4_unicode_ci,
         estimated_duration COLLATE utf8mb4_unicode_ci,
         COUNT(*)
  FROM biz_process_route_step
  WHERE del_flag = 0 AND estimated_duration IS NOT NULL AND estimated_duration != ''
  GROUP BY estimated_duration
  UNION ALL
  SELECT 'biz_production_task_step' COLLATE utf8mb4_unicode_ci,
         estimated_duration COLLATE utf8mb4_unicode_ci,
         COUNT(*)
  FROM biz_production_task_step
  WHERE del_flag = 0 AND estimated_duration IS NOT NULL AND estimated_duration != ''
  GROUP BY estimated_duration
) t
ORDER BY src_table, row_cnt DESC, estimated_duration;

-- B. 明细：带工序代码/名称，便于逐条核对
SELECT 'biz_process' COLLATE utf8mb4_unicode_ci AS src_table,
       id COLLATE utf8mb4_unicode_ci AS id,
       process_code COLLATE utf8mb4_unicode_ci AS process_code,
       process_name COLLATE utf8mb4_unicode_ci AS process_name,
       estimated_duration COLLATE utf8mb4_unicode_ci AS estimated_duration
FROM biz_process
WHERE del_flag = 0 AND estimated_duration IS NOT NULL AND estimated_duration != ''
UNION ALL
SELECT 'biz_process_route_step' COLLATE utf8mb4_unicode_ci,
       id COLLATE utf8mb4_unicode_ci,
       process_code COLLATE utf8mb4_unicode_ci,
       process_name COLLATE utf8mb4_unicode_ci,
       estimated_duration COLLATE utf8mb4_unicode_ci
FROM biz_process_route_step
WHERE del_flag = 0 AND estimated_duration IS NOT NULL AND estimated_duration != ''
UNION ALL
SELECT 'biz_production_task_step' COLLATE utf8mb4_unicode_ci,
       id COLLATE utf8mb4_unicode_ci,
       process_code COLLATE utf8mb4_unicode_ci,
       process_name COLLATE utf8mb4_unicode_ci,
       estimated_duration COLLATE utf8mb4_unicode_ci
FROM biz_production_task_step
WHERE del_flag = 0 AND estimated_duration IS NOT NULL AND estimated_duration != ''
ORDER BY src_table, process_code, id;

-- ---------- 迁移（确认预检结果后再执行下方 UPDATE）----------
-- 每张表按 ①纯数字÷60 → ②去min÷60 → ③去h 顺序执行

-- biz_process
UPDATE `biz_process`
SET `estimated_duration` = TRIM(
  TRAILING '.'
  FROM TRIM(
    TRAILING '0'
    FROM CAST(CAST(`estimated_duration` AS DECIMAL(12, 4)) / 60 AS CHAR)
  )
)
WHERE `del_flag` = 0
  AND `estimated_duration` IS NOT NULL
  AND `estimated_duration` != ''
  AND `estimated_duration` REGEXP '^[0-9]+(\\.[0-9]+)?$';

UPDATE `biz_process`
SET `estimated_duration` = TRIM(
  TRAILING '.'
  FROM TRIM(
    TRAILING '0'
    FROM CAST(
      CAST(
        TRIM(
          REPLACE(REPLACE(LOWER(TRIM(`estimated_duration`)), '分钟', ''), 'min', '')
        ) AS DECIMAL(12, 4)
      ) / 60 AS CHAR
    )
  )
)
WHERE `del_flag` = 0
  AND `estimated_duration` IS NOT NULL
  AND `estimated_duration` != ''
  AND LOWER(TRIM(`estimated_duration`)) REGEXP '^[0-9]+(\\.[0-9]+)?\\s*(min|分钟)$';

UPDATE `biz_process`
SET `estimated_duration` = TRIM(
  TRAILING '.'
  FROM TRIM(
    TRAILING '0'
    FROM CAST(
      CAST(
        TRIM(
          REPLACE(REPLACE(LOWER(TRIM(`estimated_duration`)), '小时', ''), 'h', '')
        ) AS DECIMAL(12, 4)
      ) AS CHAR
    )
  )
)
WHERE `del_flag` = 0
  AND `estimated_duration` IS NOT NULL
  AND `estimated_duration` != ''
  AND LOWER(TRIM(`estimated_duration`)) REGEXP '^[0-9]+(\\.[0-9]+)?\\s*(h|小时)$';

-- biz_process_route_step
UPDATE `biz_process_route_step`
SET `estimated_duration` = TRIM(
  TRAILING '.'
  FROM TRIM(
    TRAILING '0'
    FROM CAST(CAST(`estimated_duration` AS DECIMAL(12, 4)) / 60 AS CHAR)
  )
)
WHERE `del_flag` = 0
  AND `estimated_duration` IS NOT NULL
  AND `estimated_duration` != ''
  AND `estimated_duration` REGEXP '^[0-9]+(\\.[0-9]+)?$';

UPDATE `biz_process_route_step`
SET `estimated_duration` = TRIM(
  TRAILING '.'
  FROM TRIM(
    TRAILING '0'
    FROM CAST(
      CAST(
        TRIM(
          REPLACE(REPLACE(LOWER(TRIM(`estimated_duration`)), '分钟', ''), 'min', '')
        ) AS DECIMAL(12, 4)
      ) / 60 AS CHAR
    )
  )
)
WHERE `del_flag` = 0
  AND `estimated_duration` IS NOT NULL
  AND `estimated_duration` != ''
  AND LOWER(TRIM(`estimated_duration`)) REGEXP '^[0-9]+(\\.[0-9]+)?\\s*(min|分钟)$';

UPDATE `biz_process_route_step`
SET `estimated_duration` = TRIM(
  TRAILING '.'
  FROM TRIM(
    TRAILING '0'
    FROM CAST(
      CAST(
        TRIM(
          REPLACE(REPLACE(LOWER(TRIM(`estimated_duration`)), '小时', ''), 'h', '')
        ) AS DECIMAL(12, 4)
      ) AS CHAR
    )
  )
)
WHERE `del_flag` = 0
  AND `estimated_duration` IS NOT NULL
  AND `estimated_duration` != ''
  AND LOWER(TRIM(`estimated_duration`)) REGEXP '^[0-9]+(\\.[0-9]+)?\\s*(h|小时)$';

-- biz_production_task_step（生产环境存在 6h 等值，走第 3 步「去h」）
UPDATE `biz_production_task_step`
SET `estimated_duration` = TRIM(
  TRAILING '.'
  FROM TRIM(
    TRAILING '0'
    FROM CAST(CAST(`estimated_duration` AS DECIMAL(12, 4)) / 60 AS CHAR)
  )
)
WHERE `del_flag` = 0
  AND `estimated_duration` IS NOT NULL
  AND `estimated_duration` != ''
  AND `estimated_duration` REGEXP '^[0-9]+(\\.[0-9]+)?$';

UPDATE `biz_production_task_step`
SET `estimated_duration` = TRIM(
  TRAILING '.'
  FROM TRIM(
    TRAILING '0'
    FROM CAST(
      CAST(
        TRIM(
          REPLACE(REPLACE(LOWER(TRIM(`estimated_duration`)), '分钟', ''), 'min', '')
        ) AS DECIMAL(12, 4)
      ) / 60 AS CHAR
    )
  )
)
WHERE `del_flag` = 0
  AND `estimated_duration` IS NOT NULL
  AND `estimated_duration` != ''
  AND LOWER(TRIM(`estimated_duration`)) REGEXP '^[0-9]+(\\.[0-9]+)?\\s*(min|分钟)$';

UPDATE `biz_production_task_step`
SET `estimated_duration` = TRIM(
  TRAILING '.'
  FROM TRIM(
    TRAILING '0'
    FROM CAST(
      CAST(
        TRIM(
          REPLACE(REPLACE(LOWER(TRIM(`estimated_duration`)), '小时', ''), 'h', '')
        ) AS DECIMAL(12, 4)
      ) AS CHAR
    )
  )
)
WHERE `del_flag` = 0
  AND `estimated_duration` IS NOT NULL
  AND `estimated_duration` != ''
  AND LOWER(TRIM(`estimated_duration`)) REGEXP '^[0-9]+(\\.[0-9]+)?\\s*(h|小时)$';

-- 列注释同步（可选，与语义一致）
ALTER TABLE `biz_process`
  MODIFY COLUMN `estimated_duration` varchar(100) DEFAULT NULL COMMENT '预估耗时(小时)';
ALTER TABLE `biz_process_route_step`
  MODIFY COLUMN `estimated_duration` varchar(100) DEFAULT NULL COMMENT '预估耗时(小时)';
ALTER TABLE `biz_production_task_step`
  MODIFY COLUMN `estimated_duration` varchar(100) DEFAULT NULL COMMENT '预估耗时(小时)';