124 lines
12 KiB
SQL
124 lines
12 KiB
SQL
-- 退货近期增量汇总（2026-10-12），MySQL 8.0.16+，可重复执行。
|
|
-- 连接到需要升级的 MySQL 实例后，整份执行；目标仅 baiyue_lastleg。
|
|
-- USE 指定数据库，不指定服务器；本脚本不创建数据库，不写测试单据，不修改库存/清点事实。
|
|
-- 前提：已有尾程和退货合箱/出库基线表（不是新库初始化脚本）。
|
|
-- 汇总10月10日/12日直接退货增量，并补齐清点、事实数量及多次换标申请结构。
|
|
-- 排车车队/IAM权限为独立业务基线，不纳入本退货脚本。
|
|
-- PARCEL三类型换标、RETURN_PACK/RETURN_COMPLETION_TRANSFER历史以及清点续作均复用现有结构。
|
|
-- DDL隐式提交；执行客户端遇错须停止，不使用 --force。可重复执行不等于整个脚本事务回滚。
|
|
USE `baiyue_lastleg`;
|
|
SELECT DATABASE() AS target_database, @@hostname AS mysql_host, @@port AS mysql_port, VERSION() AS mysql_version;
|
|
|
|
-- 一、件数出库快照、部分扣件守恒、同箱分批出库索引。
|
|
-- 退货单 SKU 按件数出库快照。目标库：baiyue_lastleg。可重复执行。
|
|
USE baiyue_lastleg;
|
|
SET @return_sku_quantity_ddl = IF(
|
|
EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE()
|
|
AND table_name='ll_outbound_pallet_item' AND column_name='return_sku_quantity'),
|
|
'SELECT 1',
|
|
'ALTER TABLE ll_outbound_pallet_item ADD COLUMN return_sku_quantity BIGINT NULL COMMENT ''退货单SKU本来源计划出库件数快照，NULL为历史整箱出库'' AFTER sku_code'
|
|
);
|
|
PREPARE return_sku_quantity_stmt FROM @return_sku_quantity_ddl;
|
|
EXECUTE return_sku_quantity_stmt;
|
|
DEALLOCATE PREPARE return_sku_quantity_stmt;
|
|
|
|
-- 部分出库仅扣件数；整箱扣一箱，保留守恒和非负约束。
|
|
SET @return_sku_check_ddl = IF(EXISTS(SELECT 1 FROM information_schema.table_constraints WHERE constraint_schema=DATABASE() AND table_name='ll_return_inventory_outbound_movement' AND constraint_name='chk_ll_return_outbound_movement_delta'), 'ALTER TABLE ll_return_inventory_outbound_movement DROP CHECK chk_ll_return_outbound_movement_delta, ADD CONSTRAINT chk_ll_return_outbound_movement_delta CHECK(quantity>0 AND total_after>=0 AND available_after>=0 AND total_before-total_after=quantity AND available_before-available_after=quantity AND cartons_after>=0 AND cartons_before-cartons_after IN (0,1) AND total_after>=available_after)', 'ALTER TABLE ll_return_inventory_outbound_movement ADD CONSTRAINT chk_ll_return_outbound_movement_delta CHECK(quantity>0 AND total_after>=0 AND available_after>=0 AND total_before-total_after=quantity AND available_before-available_after=quantity AND cartons_after>=0 AND cartons_before-cartons_after IN (0,1) AND total_after>=available_after)');
|
|
PREPARE return_sku_check_stmt FROM @return_sku_check_ddl;
|
|
EXECUTE return_sku_check_stmt;
|
|
DEALLOCATE PREPARE return_sku_check_stmt;
|
|
|
|
-- 二、标签状态（旧记录保留NULL，应用按既有兼容规则处理）。
|
|
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND column_name='label_status'), 'SELECT 1', 'ALTER TABLE ll_return_inventory_carton ADD COLUMN label_status VARCHAR(32) NULL COMMENT ''WAIT_LABEL待贴标 CONFIRMED已扫码确认标签''');
|
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- 三、清点结构；不将历史已签收箱自动回退为待清点。
|
|
-- 退货托盘签收箱内清点；MySQL 8；仅在目标尾程库执行，可重复运行。
|
|
USE baiyue_lastleg;
|
|
SET @rc_count_ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND column_name='count_status'),'SELECT 1','ALTER TABLE ll_return_inventory_carton ADD COLUMN count_status VARCHAR(16) NOT NULL DEFAULT ''CONFIRMED'' COMMENT ''箱内清点状态 PENDING待清点 CONFIRMED已确认''');
|
|
PREPARE rc_count_stmt FROM @rc_count_ddl; EXECUTE rc_count_stmt; DEALLOCATE PREPARE rc_count_stmt;
|
|
SET @rc_count_ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND column_name='counted_at'),'SELECT 1','ALTER TABLE ll_return_inventory_carton ADD COLUMN counted_at DATETIME(3) NULL COMMENT ''箱内清点确认时间''');
|
|
PREPARE rc_count_stmt FROM @rc_count_ddl; EXECUTE rc_count_stmt; DEALLOCATE PREPARE rc_count_stmt;
|
|
SET @rc_count_ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND column_name='counted_by'),'SELECT 1','ALTER TABLE ll_return_inventory_carton ADD COLUMN counted_by VARCHAR(128) NULL COMMENT ''箱内清点确认操作人''');
|
|
PREPARE rc_count_stmt FROM @rc_count_ddl; EXECUTE rc_count_stmt; DEALLOCATE PREPARE rc_count_stmt;
|
|
SET @rc_count_ddl = IF(EXISTS(SELECT 1 FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND index_name='idx_ll_return_count'),'SELECT 1','CREATE INDEX idx_ll_return_count ON ll_return_inventory_carton(overseas_warehouse_id,count_status,status,source_receipt_id)');
|
|
PREPARE rc_count_stmt FROM @rc_count_ddl; EXECUTE rc_count_stmt; DEALLOCATE PREPARE rc_count_stmt;
|
|
|
|
-- MySQL 8，本地尾程库，可重复执行；不修改现有清点数量或自动释放未完成任务。
|
|
USE baiyue_lastleg;
|
|
-- 退货箱内清点作业会话：每位操作员只能认领一个批次，每个批次只能被一人认领。
|
|
CREATE TABLE IF NOT EXISTS ll_return_carton_count_session (
|
|
operator_id VARCHAR(128) NOT NULL COMMENT '平台PDA操作员标识',
|
|
warehouse_id VARCHAR(128) NOT NULL COMMENT '认领时绑定海外仓标识',
|
|
receipt_id BIGINT NULL COMMENT '正在清点的签收批次，完成后释放为NULL',
|
|
started_at DATETIME(3) NULL COMMENT '本次开始清点时间',
|
|
created_at DATETIME(3) NOT NULL COMMENT '会话创建时间',
|
|
updated_at DATETIME(3) NOT NULL COMMENT '会话更新时间',
|
|
PRIMARY KEY(operator_id),
|
|
UNIQUE KEY uk_ll_return_count_session_receipt(receipt_id)
|
|
) ENGINE=InnoDB COMMENT='退货箱内清点单人单任务持久会话';
|
|
|
|
-- 四、换标事实数量与一单多申请；保留幂等唯一键。
|
|
SET @ddl = (
|
|
SELECT IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.columns
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'll_relabel_pda_unit_fact'
|
|
AND column_name = 'quantity'
|
|
),
|
|
'SELECT 1',
|
|
'ALTER TABLE ll_relabel_pda_unit_fact ADD COLUMN quantity INT NOT NULL DEFAULT 1 COMMENT ''本次事实处理件数，箱级作业固定为1'' AFTER reason'
|
|
)
|
|
);
|
|
PREPARE ll_return_bulk_relabel_quantity_stmt FROM @ddl;
|
|
EXECUTE ll_return_bulk_relabel_quantity_stmt;
|
|
DEALLOCATE PREPARE ll_return_bulk_relabel_quantity_stmt;
|
|
|
|
|
|
SET @relabel_has_unique = (SELECT COUNT(*) FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE() AND table_name = 'll_relabel_application'
|
|
AND index_name = 'uk_ll_relabel_application_order');
|
|
SET @relabel_has_index = (SELECT COUNT(*) FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE() AND table_name = 'll_relabel_application'
|
|
AND index_name = 'idx_ll_relabel_application_order');
|
|
SET @relabel_index_sql = CASE
|
|
WHEN @relabel_has_unique > 0 AND @relabel_has_index = 0 THEN
|
|
'ALTER TABLE ll_relabel_application DROP INDEX uk_ll_relabel_application_order, ADD INDEX idx_ll_relabel_application_order (lastleg_order_id)'
|
|
WHEN @relabel_has_unique > 0 THEN
|
|
'ALTER TABLE ll_relabel_application DROP INDEX uk_ll_relabel_application_order'
|
|
WHEN @relabel_has_index = 0 THEN
|
|
'ALTER TABLE ll_relabel_application ADD INDEX idx_ll_relabel_application_order (lastleg_order_id)'
|
|
ELSE 'SELECT 1' END;
|
|
PREPARE relabel_index_migration FROM @relabel_index_sql;
|
|
EXECUTE relabel_index_migration;
|
|
DEALLOCATE PREPARE relabel_index_migration;
|
|
|
|
-- 同箱分批索引：同时已有旧/新索引或旧索引已删除时也可执行。
|
|
SET @return_index_ddl = IF(EXISTS(SELECT 1 FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_outbound_movement' AND index_name='uk_ll_return_outbound_carton_sku'), 'ALTER TABLE ll_return_inventory_outbound_movement DROP INDEX uk_ll_return_outbound_carton_sku', 'SELECT 1');
|
|
PREPARE return_index_stmt FROM @return_index_ddl; EXECUTE return_index_stmt; DEALLOCATE PREPARE return_index_stmt;
|
|
SET @return_index_ddl = IF(EXISTS(SELECT 1 FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_outbound_movement' AND index_name='idx_ll_return_outbound_carton_sku'), 'SELECT 1', 'ALTER TABLE ll_return_inventory_outbound_movement ADD INDEX idx_ll_return_outbound_carton_sku(customer_id,carton_id,sku_code)');
|
|
PREPARE return_index_stmt FROM @return_index_ddl; EXECUTE return_index_stmt; DEALLOCATE PREPARE return_index_stmt;
|
|
|
|
-- 五、原包装及尺寸重量来源。逐列检查，支持部分执行后重跑。
|
|
-- 托盘重量/体积由当前绑定箱的实测尺寸重量汇总，无需新增托盘字段。
|
|
SET @return_measurement_ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_return_receipt' AND column_name='original_packaging'), 'SELECT 1', 'ALTER TABLE ll_return_receipt ADD COLUMN original_packaging TINYINT(1) NULL COMMENT ''是否使用原箱原包装'' AFTER weight');
|
|
PREPARE return_measurement_stmt FROM @return_measurement_ddl; EXECUTE return_measurement_stmt; DEALLOCATE PREPARE return_measurement_stmt;
|
|
SET @return_measurement_ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_return_receipt' AND column_name='measurement_source'), 'SELECT 1', 'ALTER TABLE ll_return_receipt ADD COLUMN measurement_source VARCHAR(32) NULL COMMENT ''尺寸重量来源 PALLET_MEASURED托盘实测 UNIT_SUM子箱汇总'' AFTER original_packaging');
|
|
PREPARE return_measurement_stmt FROM @return_measurement_ddl; EXECUTE return_measurement_stmt; DEALLOCATE PREPARE return_measurement_stmt;
|
|
SET @return_measurement_ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND column_name='original_packaging'), 'SELECT 1', 'ALTER TABLE ll_return_inventory_carton ADD COLUMN original_packaging TINYINT(1) NULL COMMENT ''是否使用原箱原包装'' AFTER weight');
|
|
PREPARE return_measurement_stmt FROM @return_measurement_ddl; EXECUTE return_measurement_stmt; DEALLOCATE PREPARE return_measurement_stmt;
|
|
|
|
-- 校验输出：核对新增字段、索引和守恒约束。
|
|
SELECT table_name,column_name,column_type,is_nullable,column_default FROM information_schema.columns
|
|
WHERE table_schema=DATABASE() AND (
|
|
(table_name='ll_outbound_pallet_item' AND column_name='return_sku_quantity') OR
|
|
(table_name='ll_return_inventory_carton' AND column_name IN('label_status','count_status','counted_at','counted_by','original_packaging')) OR
|
|
(table_name='ll_return_receipt' AND column_name IN('original_packaging','measurement_source')) OR
|
|
(table_name='ll_relabel_pda_unit_fact' AND column_name='quantity')) ORDER BY table_name,column_name;
|
|
SELECT table_name,index_name,non_unique,GROUP_CONCAT(column_name ORDER BY seq_in_index) AS indexed_columns
|
|
FROM information_schema.statistics WHERE table_schema=DATABASE() AND index_name IN
|
|
('idx_ll_return_outbound_carton_sku','uk_ll_return_outbound_pick_sku','idx_ll_relabel_application_order','uk_ll_return_count_session_receipt','idx_ll_return_count') GROUP BY table_name,index_name,non_unique;
|
|
SELECT constraint_name,check_clause FROM information_schema.check_constraints WHERE constraint_schema=DATABASE() AND constraint_name='chk_ll_return_outbound_movement_delta';
|