74 lines
5.1 KiB
SQL
74 lines
5.1 KiB
SQL
-- 固定目标本机 baiyue_lastleg，只修复自动免费仓租结构，不更新业务数据。
|
|
-- 使用独立连接、遇错停止；先核对既有表结构，不能重放空库初始化。
|
|
USE baiyue_lastleg;
|
|
SELECT COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_finance_fee_source'
|
|
AND COLUMN_NAME IN ('amount','original_amount','exchange_rate','created_by','fee_item_code','direction');
|
|
|
|
-- 上次 CALL 前置检查失败可能遗留本专用过程，仅清理本脚本自己的过程后重建。
|
|
DROP PROCEDURE IF EXISTS storage_rent_zero_fee_repair_20261023;
|
|
DELIMITER $$
|
|
CREATE PROCEDURE storage_rent_zero_fee_repair_20261023()
|
|
BEGIN
|
|
DECLARE checked_columns INT DEFAULT 0;
|
|
DECLARE checked_constraints INT DEFAULT 0;
|
|
DECLARE rate_type VARCHAR(64);
|
|
DECLARE rate_comment TEXT;
|
|
DECLARE rate_default TEXT;
|
|
DECLARE rate_default_sql TEXT;
|
|
IF DATABASE()<>'baiyue_lastleg' THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='目标必须为 baiyue_lastleg';
|
|
END IF;
|
|
SELECT COUNT(*) INTO checked_columns FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_finance_fee_source'
|
|
AND ((COLUMN_NAME IN ('amount','original_amount') AND DATA_TYPE='decimal' AND IS_NULLABLE='NO')
|
|
OR (COLUMN_NAME='exchange_rate' AND DATA_TYPE='decimal' AND EXTRA='')
|
|
OR (COLUMN_NAME IN ('created_by','fee_item_code','direction') AND DATA_TYPE='varchar' AND IS_NULLABLE='NO'));
|
|
SELECT COUNT(*) INTO checked_constraints FROM information_schema.TABLE_CONSTRAINTS
|
|
WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='ll_finance_fee_source'
|
|
AND CONSTRAINT_NAME='chk_ll_finance_fee_amount' AND CONSTRAINT_TYPE='CHECK';
|
|
IF checked_columns<>6 OR checked_constraints<>1 THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='费用源表字段或金额约束异常，停止修复';
|
|
END IF;
|
|
-- 增加新约束前只读检查；人工费用仍须正额且有效汇率，防止意外放宽。
|
|
IF EXISTS (SELECT 1 FROM ll_finance_fee_source
|
|
WHERE NOT ((amount>0 OR (amount=0 AND original_amount=0 AND created_by='STORAGE_RENT' AND fee_item_code='FEE_TYPE_077' AND direction='RECEIVABLE'))
|
|
AND ((exchange_rate IS NOT NULL AND exchange_rate>0) OR (exchange_rate IS NULL AND amount=0 AND original_amount=0 AND created_by='STORAGE_RENT' AND fee_item_code='FEE_TYPE_077' AND direction='RECEIVABLE')))) THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='存在不满足新约束的费用，停止且不改数据';
|
|
END IF;
|
|
SELECT COUNT(*) INTO checked_constraints FROM information_schema.TABLE_CONSTRAINTS
|
|
WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='ll_finance_fee_source'
|
|
AND CONSTRAINT_NAME='chk_ll_finance_fee_exchange_rate' AND CONSTRAINT_TYPE='CHECK';
|
|
IF checked_constraints=0 THEN
|
|
-- 保留真实已有列的精度和备注，不把存量 DECIMAL(18,8) 收窄到基线精度。
|
|
SELECT COLUMN_TYPE,COLUMN_COMMENT,COLUMN_DEFAULT INTO rate_type,rate_comment,rate_default
|
|
FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_finance_fee_source' AND COLUMN_NAME='exchange_rate';
|
|
IF rate_type NOT REGEXP '^decimal[(][0-9]+,[0-9]+[)]$' THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='汇率列不是预期定点数类型，停止修复';
|
|
END IF;
|
|
IF LOCATE('自动零元仓租可空',rate_comment)=0 THEN
|
|
SET rate_comment=CONCAT(rate_comment,'，自动零元仓租可空');
|
|
END IF;
|
|
-- 保留既有数值默认值（例如1.00000000）；拒绝表达式或非数值默认值。
|
|
IF rate_default IS NOT NULL AND rate_default NOT REGEXP '^-?[0-9]+([.][0-9]+)?$' THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='汇率默认值不是预期数值，停止修复';
|
|
END IF;
|
|
SET rate_default_sql=IF(rate_default IS NULL,' DEFAULT NULL',CONCAT(' DEFAULT ',rate_default));
|
|
-- 单次原子 ALTER 同步调整可空性与两项约束，原币及结算列完全不改。
|
|
SET @storage_rent_zero_fee_ddl=CONCAT(
|
|
'ALTER TABLE baiyue_lastleg.ll_finance_fee_source DROP CHECK chk_ll_finance_fee_amount, ',
|
|
'MODIFY COLUMN exchange_rate ',rate_type,' NULL',rate_default_sql,' COMMENT ',QUOTE(rate_comment),', ',
|
|
'ADD CONSTRAINT chk_ll_finance_fee_amount CHECK(amount>0 OR (amount=0 AND original_amount=0 AND created_by=''STORAGE_RENT'' AND fee_item_code=''FEE_TYPE_077'' AND direction=''RECEIVABLE'')), ',
|
|
'ADD CONSTRAINT chk_ll_finance_fee_exchange_rate CHECK((exchange_rate IS NOT NULL AND exchange_rate>0) OR (exchange_rate IS NULL AND amount=0 AND original_amount=0 AND created_by=''STORAGE_RENT'' AND fee_item_code=''FEE_TYPE_077'' AND direction=''RECEIVABLE''))');
|
|
PREPARE storage_rent_zero_fee_stmt FROM @storage_rent_zero_fee_ddl;
|
|
EXECUTE storage_rent_zero_fee_stmt;
|
|
DEALLOCATE PREPARE storage_rent_zero_fee_stmt;
|
|
SET @storage_rent_zero_fee_ddl=NULL;
|
|
END IF;
|
|
END$$
|
|
DELIMITER ;
|
|
CALL storage_rent_zero_fee_repair_20261023();
|
|
DROP PROCEDURE storage_rent_zero_fee_repair_20261023;
|
|
SHOW CREATE TABLE ll_finance_fee_source;
|