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

-- 市场移动端运费批次查询与并发关联约束（MySQL 5.7，幂等）。
-- 若存量数据冲突或同名对象定义错误则明确中止，不静默移动/删除数据。

SET NAMES utf8mb4;

DELIMITER $$

DROP PROCEDURE IF EXISTS `ensure_market_freight_indexes`$$
CREATE PROCEDURE `ensure_market_freight_indexes`()
BEGIN
  DECLARE duplicate_rows INT DEFAULT 0;
  DECLARE object_count INT DEFAULT 0;
  DECLARE index_non_unique INT DEFAULT 1;
  DECLARE index_columns VARCHAR(512) DEFAULT '';
  DECLARE index_prefix_count INT DEFAULT 0;
  DECLARE index_type_count INT DEFAULT 0;
  DECLARE index_type_name VARCHAR(32) DEFAULT '';
  DECLARE generated_extra VARCHAR(255) DEFAULT '';
  DECLARE generated_expression TEXT;
  DECLARE normalized_generation_expression TEXT;
  DECLARE generated_data_type VARCHAR(64) DEFAULT '';
  DECLARE generated_length BIGINT DEFAULT 0;
  DECLARE generated_nullable VARCHAR(3) DEFAULT '';
  DECLARE exact_freight_unique_indexes INT DEFAULT 0;

  SELECT COUNT(*) INTO duplicate_rows
  FROM (
    SELECT tenant_no, freight_no
    FROM biz_sample_freight
    GROUP BY tenant_no, freight_no
    HAVING COUNT(*) > 1
  ) duplicate_freights;
  IF duplicate_rows > 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'duplicate freight numbers found; reconcile them before rerunning migration';
  END IF;

  SELECT COUNT(*) INTO duplicate_rows
  FROM (
    SELECT tenant_no, sample_source, sample_id
    FROM biz_sample_freight_item
    WHERE del_flag = 0
    GROUP BY tenant_no, sample_source, sample_id
    HAVING COUNT(*) > 1
  ) duplicate_active_links;
  IF duplicate_rows > 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'duplicate active sample freight links found; reconcile them before rerunning migration';
  END IF;

  SELECT COUNT(*) INTO object_count
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'biz_sample_freight_item'
    AND COLUMN_NAME = 'active_sample_key';
  IF object_count = 0 THEN
    ALTER TABLE biz_sample_freight_item
      ADD COLUMN active_sample_key VARCHAR(170)
      GENERATED ALWAYS AS (
        IF(del_flag = 0, CONCAT(tenant_no, '#', sample_source, '#', sample_id), NULL)
      ) STORED;
  ELSE
    SELECT EXTRA, GENERATION_EXPRESSION, DATA_TYPE,
           CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
    INTO generated_extra, generated_expression, generated_data_type,
         generated_length, generated_nullable
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'biz_sample_freight_item'
      AND COLUMN_NAME = 'active_sample_key';
    SET normalized_generation_expression = LOWER(
      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        COALESCE(generated_expression, ''),
        '`', ''), ' ', ''), CHAR(9), ''), CHAR(10), ''), '(', ''), ')', '')
    );
    IF UPPER(COALESCE(generated_extra, '')) NOT LIKE '%STORED GENERATED%'
       OR LOWER(COALESCE(generated_data_type, '')) <> 'varchar'
       OR COALESCE(generated_length, 0) <> 170
       OR UPPER(COALESCE(generated_nullable, '')) <> 'YES'
       OR normalized_generation_expression <>
          'ifdel_flag=0,concattenant_no,''#'',sample_source,''#'',sample_id,null' THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'wrong active_sample_key type/expression; drop column and dependent index, then rerun migration';
    END IF;
  END IF;

  SELECT COUNT(*), COALESCE(MIN(NON_UNIQUE), 1),
         COALESCE(GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ','), ''),
         SUM(CASE WHEN SUB_PART IS NOT NULL THEN 1 ELSE 0 END),
         COUNT(DISTINCT INDEX_TYPE), COALESCE(MIN(INDEX_TYPE), '')
  INTO object_count, index_non_unique, index_columns,
       index_prefix_count, index_type_count, index_type_name
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'biz_sample_freight_item'
    AND INDEX_NAME = 'uk_bsfi_active_sample';
  IF object_count = 0 THEN
    ALTER TABLE biz_sample_freight_item
      ADD UNIQUE INDEX uk_bsfi_active_sample (active_sample_key);
  ELSEIF object_count <> 1 OR index_non_unique <> 0
      OR index_columns <> 'active_sample_key' OR index_prefix_count <> 0
      OR index_type_count <> 1 OR index_type_name <> 'BTREE' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'wrong index uk_bsfi_active_sample; drop it and rerun migration';
  END IF;

  -- 旧迁移已创建 uk_sample_freight_tenant_no；任何名称的等价精确唯一索引均复用。
  -- 但若本迁移预留名称已存在且定义错误，必须先修复，不能静默绕过。
  SELECT COUNT(*), COALESCE(MIN(NON_UNIQUE), 1),
         COALESCE(GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ','), ''),
         SUM(CASE WHEN SUB_PART IS NOT NULL THEN 1 ELSE 0 END),
         COUNT(DISTINCT INDEX_TYPE), COALESCE(MIN(INDEX_TYPE), '')
  INTO object_count, index_non_unique, index_columns,
       index_prefix_count, index_type_count, index_type_name
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'biz_sample_freight'
    AND INDEX_NAME = 'uk_bsf_tenant_freight_no';
  IF object_count > 0 AND (object_count <> 2 OR index_non_unique <> 0
      OR index_columns <> 'tenant_no,freight_no' OR index_prefix_count <> 0
      OR index_type_count <> 1 OR index_type_name <> 'BTREE') THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'wrong index uk_bsf_tenant_freight_no; drop it and rerun migration';
  END IF;

  SELECT COUNT(*) INTO exact_freight_unique_indexes
  FROM (
    SELECT INDEX_NAME
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'biz_sample_freight'
    GROUP BY INDEX_NAME
    HAVING MIN(NON_UNIQUE) = 0
       AND MAX(NON_UNIQUE) = 0
       AND GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ',') =
           'tenant_no,freight_no'
       AND SUM(CASE WHEN SUB_PART IS NOT NULL THEN 1 ELSE 0 END) = 0
       AND COUNT(DISTINCT INDEX_TYPE) = 1
       AND MIN(INDEX_TYPE) = 'BTREE'
  ) exact_freight_unique_index_definitions;
  IF exact_freight_unique_indexes = 0 THEN
    ALTER TABLE biz_sample_freight
      ADD UNIQUE INDEX uk_bsf_tenant_freight_no (tenant_no, freight_no);
  END IF;

  SELECT COUNT(*), COALESCE(MIN(NON_UNIQUE), 1),
         COALESCE(GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ','), ''),
         SUM(CASE WHEN SUB_PART IS NOT NULL THEN 1 ELSE 0 END),
         COUNT(DISTINCT INDEX_TYPE), COALESCE(MIN(INDEX_TYPE), '')
  INTO object_count, index_non_unique, index_columns,
       index_prefix_count, index_type_count, index_type_name
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'biz_sample_freight'
    AND INDEX_NAME = 'idx_bsf_market_list';
  IF object_count = 0 THEN
    ALTER TABLE biz_sample_freight
      ADD INDEX idx_bsf_market_list (tenant_no, del_flag, create_time, id);
  ELSEIF object_count <> 4 OR index_non_unique <> 1
      OR index_columns <> 'tenant_no,del_flag,create_time,id'
      OR index_prefix_count <> 0 OR index_type_count <> 1
      OR index_type_name <> 'BTREE' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'wrong index idx_bsf_market_list; drop it and rerun migration';
  END IF;

  SELECT COUNT(*), COALESCE(MIN(NON_UNIQUE), 1),
         COALESCE(GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ','), ''),
         SUM(CASE WHEN SUB_PART IS NOT NULL THEN 1 ELSE 0 END),
         COUNT(DISTINCT INDEX_TYPE), COALESCE(MIN(INDEX_TYPE), '')
  INTO object_count, index_non_unique, index_columns,
       index_prefix_count, index_type_count, index_type_name
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'biz_sample_freight_item'
    AND INDEX_NAME = 'idx_bsfi_market_batch';
  IF object_count = 0 THEN
    ALTER TABLE biz_sample_freight_item
      ADD INDEX idx_bsfi_market_batch (tenant_no, freight_id, del_flag, create_time, id);
  ELSEIF object_count <> 5 OR index_non_unique <> 1
      OR index_columns <> 'tenant_no,freight_id,del_flag,create_time,id'
      OR index_prefix_count <> 0 OR index_type_count <> 1
      OR index_type_name <> 'BTREE' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'wrong index idx_bsfi_market_batch; drop it and rerun migration';
  END IF;
END$$

CALL `ensure_market_freight_indexes`()$$
DROP PROCEDURE `ensure_market_freight_indexes`$$

DELIMITER ;