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

-- 市场移动端样本统一列表、进度与轻量办理索引（MySQL 5.7）。
-- 同名索引的列顺序、唯一性、前缀或类型不一致时重建；重复执行保持幂等。

SET @schema_name = DATABASE();

SET @table_name = 'biz_protocol_sample';
SET @index_name = 'idx_bps_market_sample_code';
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 = @table_name AND index_name = @index_name;
SET @ddl = CASE
  WHEN @index_columns = 'tenant_no,del_flag,sample_code' 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_sample ADD INDEX idx_bps_market_sample_code (tenant_no, del_flag, sample_code)'
  ELSE
    'ALTER TABLE biz_protocol_sample DROP INDEX idx_bps_market_sample_code, ADD INDEX idx_bps_market_sample_code (tenant_no, del_flag, sample_code)'
END;
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @table_name = 'biz_protocol_sample';
SET @index_name = 'idx_bps_market_parent';
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 = @table_name AND index_name = @index_name;
SET @ddl = CASE
  WHEN @index_columns = 'tenant_no,del_flag,collection_protocol_id' 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_sample ADD INDEX idx_bps_market_parent (tenant_no, del_flag, collection_protocol_id)'
  ELSE
    'ALTER TABLE biz_protocol_sample DROP INDEX idx_bps_market_parent, ADD INDEX idx_bps_market_parent (tenant_no, del_flag, collection_protocol_id)'
END;
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @table_name = 'biz_research_donation_sample';
SET @index_name = 'idx_brds_market_sample_code';
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 = @table_name AND index_name = @index_name;
SET @ddl = CASE
  WHEN @index_columns = 'tenant_no,del_flag,sample_code' 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_research_donation_sample ADD INDEX idx_brds_market_sample_code (tenant_no, del_flag, sample_code)'
  ELSE
    'ALTER TABLE biz_research_donation_sample DROP INDEX idx_brds_market_sample_code, ADD INDEX idx_brds_market_sample_code (tenant_no, del_flag, sample_code)'
END;
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @table_name = 'biz_research_donation_sample';
SET @index_name = 'idx_brds_market_parent';
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 = @table_name AND index_name = @index_name;
SET @ddl = CASE
  WHEN @index_columns = 'tenant_no,del_flag,research_donation_id' 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_research_donation_sample ADD INDEX idx_brds_market_parent (tenant_no, del_flag, research_donation_id)'
  ELSE
    'ALTER TABLE biz_research_donation_sample DROP INDEX idx_brds_market_parent, ADD INDEX idx_brds_market_parent (tenant_no, del_flag, research_donation_id)'
END;
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @table_name = 'biz_sample_reception';
SET @index_name = 'idx_bsr_market_sample_progress';
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 = @table_name AND index_name = @index_name;
SET @ddl = CASE
  WHEN @index_columns = 'tenant_no,reception_source,sample_code,del_flag,reception_time' 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_sample_reception ADD INDEX idx_bsr_market_sample_progress (tenant_no, reception_source, sample_code, del_flag, reception_time)'
  ELSE
    'ALTER TABLE biz_sample_reception DROP INDEX idx_bsr_market_sample_progress, ADD INDEX idx_bsr_market_sample_progress (tenant_no, reception_source, sample_code, del_flag, reception_time)'
END;
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @table_name = 'biz_qc_work_order';
SET @index_name = 'idx_bqwo_market_sample_progress';
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 = @table_name AND index_name = @index_name;
SET @ddl = CASE
  WHEN @index_columns = 'tenant_no,reception_source,trigger_source,sample_code,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_qc_work_order ADD INDEX idx_bqwo_market_sample_progress (tenant_no, reception_source, trigger_source, sample_code, del_flag)'
  ELSE
    'ALTER TABLE biz_qc_work_order DROP INDEX idx_bqwo_market_sample_progress, ADD INDEX idx_bqwo_market_sample_progress (tenant_no, reception_source, trigger_source, sample_code, del_flag)'
END;
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @table_name = 'biz_storage_release_review';
SET @index_name = 'idx_bsrr_market_sample_progress';
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 = @table_name AND index_name = @index_name;
SET @ddl = CASE
  WHEN @index_columns = 'tenant_no,reception_source,sample_code,del_flag,storage_time' 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_storage_release_review ADD INDEX idx_bsrr_market_sample_progress (tenant_no, reception_source, sample_code, del_flag, storage_time)'
  ELSE
    'ALTER TABLE biz_storage_release_review DROP INDEX idx_bsrr_market_sample_progress, ADD INDEX idx_bsrr_market_sample_progress (tenant_no, reception_source, sample_code, del_flag, storage_time)'
END;
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @table_name = 'biz_protocol_storage_cert';
SET @index_name = 'idx_bpsc_market_parent';
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 = @table_name AND index_name = @index_name;
SET @ddl = CASE
  WHEN @index_columns = 'tenant_no,collection_protocol_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_storage_cert ADD INDEX idx_bpsc_market_parent (tenant_no, collection_protocol_id, del_flag)'
  ELSE
    'ALTER TABLE biz_protocol_storage_cert DROP INDEX idx_bpsc_market_parent, ADD INDEX idx_bpsc_market_parent (tenant_no, collection_protocol_id, del_flag)'
END;
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @table_name = 'biz_protocol_fullmoon_todo';
SET @index_name = 'idx_bpft_market_parent';
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 = @table_name AND index_name = @index_name;
SET @ddl = CASE
  WHEN @index_columns = 'tenant_no,collection_protocol_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_fullmoon_todo ADD INDEX idx_bpft_market_parent (tenant_no, collection_protocol_id, del_flag)'
  ELSE
    'ALTER TABLE biz_protocol_fullmoon_todo DROP INDEX idx_bpft_market_parent, ADD INDEX idx_bpft_market_parent (tenant_no, collection_protocol_id, del_flag)'
END;
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;