zkxw/docs/sql/migrations/2026-10-11-market-payment-indexes.sql

-- 市场移动端催缴/缴费查询索引（MySQL 5.7）。
-- 同名索引定义不一致时重建，重复执行保持幂等。

SET @schema_name = DATABASE();

SET @index_name = 'idx_biz_protocol_payment_schedule_market_scope';
SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR ','),
       MIN(non_unique),
       MAX(non_unique),
       SUM(CASE WHEN sub_part IS NOT NULL THEN 1 ELSE 0 END),
       COUNT(DISTINCT index_type),
       MIN(index_type)
INTO @index_columns,
     @index_non_unique_min,
     @index_non_unique_max,
     @index_prefix_columns,
     @index_type_count,
     @index_type
FROM information_schema.statistics
WHERE table_schema = @schema_name
  AND table_name = 'biz_protocol_payment_schedule'
  AND index_name = @index_name;
SET @ddl = CASE
  WHEN @index_columns = 'tenant_no,sample_payment_id,del_flag'
   AND @index_non_unique_min = 1
   AND @index_non_unique_max = 1
   AND @index_prefix_columns = 0
   AND @index_type_count = 1
   AND @index_type = 'BTREE' THEN 'SELECT 1'
  WHEN @index_columns IS NULL THEN
    'ALTER TABLE biz_protocol_payment_schedule ADD INDEX idx_biz_protocol_payment_schedule_market_scope (tenant_no, sample_payment_id, del_flag)'
  ELSE
    'ALTER TABLE biz_protocol_payment_schedule DROP INDEX idx_biz_protocol_payment_schedule_market_scope, ADD INDEX idx_biz_protocol_payment_schedule_market_scope (tenant_no, sample_payment_id, del_flag)'
END;
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_name = 'idx_biz_protocol_payment_transfer_market_scope';
SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR ','),
       MIN(non_unique),
       MAX(non_unique),
       SUM(CASE WHEN sub_part IS NOT NULL THEN 1 ELSE 0 END),
       COUNT(DISTINCT index_type),
       MIN(index_type)
INTO @index_columns,
     @index_non_unique_min,
     @index_non_unique_max,
     @index_prefix_columns,
     @index_type_count,
     @index_type
FROM information_schema.statistics
WHERE table_schema = @schema_name
  AND table_name = 'biz_protocol_payment_transfer'
  AND index_name = @index_name;
SET @ddl = CASE
  WHEN @index_columns = 'tenant_no,schedule_id,del_flag'
   AND @index_non_unique_min = 1
   AND @index_non_unique_max = 1
   AND @index_prefix_columns = 0
   AND @index_type_count = 1
   AND @index_type = 'BTREE' THEN 'SELECT 1'
  WHEN @index_columns IS NULL THEN
    'ALTER TABLE biz_protocol_payment_transfer ADD INDEX idx_biz_protocol_payment_transfer_market_scope (tenant_no, schedule_id, del_flag)'
  ELSE
    'ALTER TABLE biz_protocol_payment_transfer DROP INDEX idx_biz_protocol_payment_transfer_market_scope, ADD INDEX idx_biz_protocol_payment_transfer_market_scope (tenant_no, schedule_id, del_flag)'
END;
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;