@@ -0,0 +1,119 @@
-- =============================================================================
-- 协议操作日志：兼容生产旧表同名冲突，重建为 Issue #24 模型
-- MySQL 5.7；可重复执行。上线前须备份，并在目标副本验证。
--
-- 背景：
-- 生产曾有「采集协议单操作日志」同名表（collection_protocol_id / operation_*），
-- 与当前代码（biz_type / before_json / after_json）不兼容。
-- 旧脚本 2026-11-05-protocol-operation-log.sql 使用 CREATE IF NOT EXISTS，
-- 遇到旧表会直接跳过，无法纠偏。本脚本用于生产纠偏与新环境兜底。
--
-- 行为：
-- 1) 已是新模型（有 biz_type）→ 跳过结构变更
-- 2) 旧模型且 0 行 → DROP 后按新模型 CREATE
-- 3) 旧模型且有数据 → RENAME 为 biz_protocol_operation_log_legacy_20261109 再 CREATE
-- 4) 表不存在 → 直接 CREATE
-- =============================================================================
SET @ db : = DATABASE ( ) ;
SET @ table_exists : = (
SELECT COUNT ( * ) FROM information_schema . tables
WHERE table_schema = @ db AND table_name = ' biz_protocol_operation_log '
) ;
SET @ has_legacy_col : = (
SELECT COUNT ( * ) FROM information_schema . columns
WHERE table_schema = @ db
AND table_name = ' biz_protocol_operation_log '
AND column_name = ' collection_protocol_id '
) ;
SET @ has_new_col : = (
SELECT COUNT ( * ) FROM information_schema . columns
WHERE table_schema = @ db
AND table_name = ' biz_protocol_operation_log '
AND column_name = ' biz_type '
) ;
SET @ is_legacy : = IF ( @ table_exists > 0 AND @ has_legacy_col > 0 AND @ has_new_col = 0 , 1 , 0 ) ;
SET @ legacy_rows : = 0 ;
SET @ cnt_sql : = IF (
@ is_legacy = 1 ,
' SET @legacy_rows = (SELECT COUNT(*) FROM `biz_protocol_operation_log`) ' ,
' SET @legacy_rows = 0 '
) ;
PREPARE pol_stmt FROM @ cnt_sql ;
EXECUTE pol_stmt ;
DEALLOCATE PREPARE pol_stmt ;
-- 空旧表：直接删除
SET @ drop_sql : = IF (
@ is_legacy = 1 AND @ legacy_rows = 0 ,
' DROP TABLE `biz_protocol_operation_log` ' ,
' SELECT '' skip drop empty legacy biz_protocol_operation_log '' AS migration_status '
) ;
PREPARE pol_stmt FROM @ drop_sql ;
EXECUTE pol_stmt ;
DEALLOCATE PREPARE pol_stmt ;
-- 有数据的旧表：改名保留，避免丢历史（当前生产为 0 行，通常不会走到这里）
SET @ legacy_backup_exists : = (
SELECT COUNT ( * ) FROM information_schema . tables
WHERE table_schema = @ db AND table_name = ' biz_protocol_operation_log_legacy_20261109 '
) ;
SET @ rename_sql : = IF (
@ is_legacy = 1 AND @ legacy_rows > 0 AND @ legacy_backup_exists = 0 ,
' RENAME TABLE `biz_protocol_operation_log` TO `biz_protocol_operation_log_legacy_20261109` ' ,
IF (
@ is_legacy = 1 AND @ legacy_rows > 0 AND @ legacy_backup_exists > 0 ,
' SELECT '' ERROR: legacy backup biz_protocol_operation_log_legacy_20261109 already exists; resolve manually '' AS migration_status ' ,
' SELECT '' skip rename legacy biz_protocol_operation_log '' AS migration_status '
)
) ;
PREPARE pol_stmt FROM @ rename_sql ;
EXECUTE pol_stmt ;
DEALLOCATE PREPARE pol_stmt ;
-- 新模型建表（纠偏后或全新环境）
CREATE TABLE IF NOT EXISTS ` biz_protocol_operation_log ` (
` id ` VARCHAR ( 64 ) NOT NULL COMMENT ' 主键ID ' ,
` biz_type ` VARCHAR ( 32 ) NOT NULL COMMENT ' 业务类型 cell_storage/research_donation ' ,
` biz_id ` VARCHAR ( 64 ) NOT NULL COMMENT ' 协议单或捐赠单ID ' ,
` protocol_no ` VARCHAR ( 64 ) NULL COMMENT ' 协议/捐赠编号快照 ' ,
` action ` VARCHAR ( 32 ) NOT NULL COMMENT ' 动作 create/update/delete/void/activate/expire/to_research/to_storage/audit ' ,
` client_channel ` VARCHAR ( 16 ) NOT NULL DEFAULT ' web ' COMMENT ' 来源端 web/mini ' ,
` before_json ` MEDIUMTEXT NULL COMMENT ' 操作前协议信息JSON ' ,
` after_json ` MEDIUMTEXT NULL COMMENT ' 操作后协议信息JSON ' ,
` operator_user_id ` VARCHAR ( 64 ) NULL COMMENT ' 操作人用户ID ' ,
` operator_user_name ` VARCHAR ( 64 ) NULL COMMENT ' 操作人姓名快照 ' ,
` operate_time ` DATETIME NOT NULL COMMENT ' 业务操作时间 ' ,
` tenant_no ` VARCHAR ( 64 ) NOT NULL COMMENT ' 租户编号 ' ,
` order_num ` INT NULL COMMENT ' 排序号 ' ,
` remark ` VARCHAR ( 512 ) NULL COMMENT ' 备注 ' ,
` create_by ` VARCHAR ( 64 ) NULL COMMENT ' 创建人id ' ,
` create_dept_id ` VARCHAR ( 64 ) NOT NULL DEFAULT ' ' COMMENT ' 创建人部门ID ' ,
` create_time ` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ' 创建时间 ' ,
` update_by ` VARCHAR ( 64 ) NULL COMMENT ' 更新人id ' ,
` update_time ` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ' 更新时间 ' ,
` del_flag ` SMALLINT NOT NULL DEFAULT 0 COMMENT ' 删除标记 ' ,
PRIMARY KEY ( ` id ` ) ,
KEY ` ix_pol_biz ` ( ` tenant_no ` , ` biz_type ` , ` biz_id ` , ` del_flag ` ) ,
KEY ` ix_pol_time ` ( ` tenant_no ` , ` operate_time ` )
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = ' 协议单操作过程日志 ' ;
-- 验收：新模型关键列应存在
SELECT
COLUMN_NAME ,
COLUMN_TYPE ,
IS_NULLABLE ,
COLUMN_COMMENT
FROM information_schema . columns
WHERE table_schema = DATABASE ( )
AND table_name = ' biz_protocol_operation_log '
AND COLUMN_NAME IN (
' biz_type ' , ' biz_id ' , ' action ' , ' before_json ' , ' after_json ' ,
' client_channel ' , ' operate_time ' , ' collection_protocol_id ' , ' operation_code '
)
ORDER BY ORDINAL_POSITION ;
zlwu
cykj