-- ============================================================ -- R2 升级脚本：事项库两表（事项库与办事大厅详细设计 §2.5，幂等） -- 目标库：存量 bsch_cms（生产/预发）；干净环境直接用 cms_init.sql 无需本脚本 -- 顺序：加表（CREATE IF NOT EXISTS 幂等）→ 索引守卫补建（幂等）→ 校验 -- 说明：MySQL 无 ADD INDEX IF NOT EXISTS，建表内联索引；存量库若只建了表缺索引 -- （含 r2 首版之后新增的 idx_svc_item_portal），由第 3 段守卫块自动补建，可反复重跑。 -- ============================================================ -- 1) 事项主表 CREATE TABLE IF NOT EXISTS `cms_service_item` ( `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键', `site_id` INT NOT NULL COMMENT '所属站点', `item_code` VARCHAR(64) NOT NULL COMMENT '事项编码（站内唯一，跨服务键的一半）', `title` VARCHAR(200) NOT NULL COMMENT '事项名称', `summary` VARCHAR(500) NULL COMMENT '一句话简介（列表副标题/快照摘要）', `object_key` VARCHAR(64) NULL COMMENT '服务对象（→ cms_service_dict.dict_key, dict_type=object）', `category_key` VARCHAR(64) NULL COMMENT '事项类别（→ cms_service_dict.dict_key, dict_type=category）', `dept_group_id` INT NULL COMMENT '服务部门（UserBase 组树节点 id 快照）', `dept_name` VARCHAR(128) NULL COMMENT '部门名快照（改名不自动同步）', `handle_mode` TINYINT NOT NULL DEFAULT 1 COMMENT '办理方式：1=线上 2=线下 3=线上线下', `online_flag` TINYINT NOT NULL DEFAULT 1 COMMENT '是否可在线办理：1=是 0=否（"在线办理"筛选维度）', `handle_place` VARCHAR(200) NULL COMMENT '办理地点', `handle_time_limit` VARCHAR(64) NULL COMMENT '办理时限（文本，如"3 个工作日"）', `promise_time_limit` VARCHAR(64) NULL COMMENT '承诺时限（文本）', `contact_phone` VARCHAR(64) NULL COMMENT '咨询电话', `supervise_phone` VARCHAR(64) NULL COMMENT '监督投诉电话', `process_html` LONGTEXT NULL COMMENT '办理流程 HTML（保存时 jsoup 清洗）', `material_html` LONGTEXT NULL COMMENT '所需材料 HTML（同上）', `notice_html` LONGTEXT NULL COMMENT '注意事项 HTML（同上）', `faq_category_id` INT NULL COMMENT '关联栏目：常见问题（逻辑外键 → cms_category.id）', `notice_category_id` INT NULL COMMENT '关联栏目：公告信息（同上）', `rule_category_id` INT NULL COMMENT '关联栏目：文件规定（同上）', `cover_image` VARCHAR(500) NULL COMMENT '事项图标/封面（上传相对路径）', `sort_no` INT NOT NULL DEFAULT 0 COMMENT '管理端自定义排序（升序，列表第一优先级）', `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0=草稿 1=已发布 2=已下架', `publish_time` DATETIME NULL COMMENT '发布时间', `offline_time` DATETIME NULL COMMENT '下架时间', `view_count` INT NOT NULL DEFAULT 0 COMMENT '指南页浏览量（异步累加，不承诺精确）', `ext_json` JSON NULL COMMENT '扩展（一期仅 apply_url："我要办理"外链）', `create_by` VARCHAR(64) NULL, `create_time` DATETIME NULL, `update_by` VARCHAR(64) NULL, `update_time` DATETIME NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_svc_item_code` (`site_id`, `item_code`), KEY `idx_svc_item_list` (`site_id`, `status`, `online_flag`, `sort_no`), KEY `idx_svc_item_obj` (`site_id`, `status`, `object_key`), KEY `idx_svc_item_cat` (`site_id`, `status`, `category_key`), KEY `idx_svc_item_dept` (`site_id`, `status`, `dept_group_id`), KEY `idx_svc_item_portal` (`site_id`, `status`, `sort_no` ASC, `publish_time` DESC, `id` DESC) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'CMS 事项（L1 事项库主表）'; -- 2) 事项字典表 CREATE TABLE IF NOT EXISTS `cms_service_dict` ( `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键', `site_id` INT NOT NULL COMMENT '所属站点', `dict_type` VARCHAR(32) NOT NULL COMMENT '类型：object=服务对象 category=事项类别', `dict_key` VARCHAR(64) NOT NULL COMMENT '稳定键（站内同类型唯一；被事项引用后不可改）', `dict_name` VARCHAR(128) NOT NULL COMMENT '展示名', `sort_no` INT NOT NULL DEFAULT 0 COMMENT '排序（升序）', `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0=停用 1=启用（停用不出现在筛选，存量引用仍可渲染）', `ext_json` JSON NULL COMMENT '预留扩展（颜色/图标等）', `create_by` VARCHAR(64) NULL, `create_time` DATETIME NULL, `update_by` VARCHAR(64) NULL, `update_time` DATETIME NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_svc_dict` (`site_id`, `dict_type`, `dict_key`), KEY `idx_svc_dict_list` (`site_id`, `dict_type`, `status`, `sort_no`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'CMS 事项字典（服务对象/事项类别）'; -- 3) 索引守卫补建（仅当表已存在但缺索引时生效；幂等，可反复重跑） -- idx_svc_item_portal：门户服务中心列表 ORDER BY sort_no ASC, publish_time DESC, id DESC 的对症索引 -- （原 idx_svc_item_list 前导列含门户未过滤的 online_flag，无法用于该排序 → 必然 filesort） SET @db := DATABASE(); SET @sql := IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = 'cms_service_item' AND index_name = 'uk_svc_item_code') = 0, 'ALTER TABLE cms_service_item ADD UNIQUE KEY uk_svc_item_code (site_id, item_code)', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = 'cms_service_item' AND index_name = 'idx_svc_item_list') = 0, 'ALTER TABLE cms_service_item ADD KEY idx_svc_item_list (site_id, status, online_flag, sort_no)', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = 'cms_service_item' AND index_name = 'idx_svc_item_obj') = 0, 'ALTER TABLE cms_service_item ADD KEY idx_svc_item_obj (site_id, status, object_key)', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = 'cms_service_item' AND index_name = 'idx_svc_item_cat') = 0, 'ALTER TABLE cms_service_item ADD KEY idx_svc_item_cat (site_id, status, category_key)', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = 'cms_service_item' AND index_name = 'idx_svc_item_dept') = 0, 'ALTER TABLE cms_service_item ADD KEY idx_svc_item_dept (site_id, status, dept_group_id)', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = 'cms_service_item' AND index_name = 'idx_svc_item_portal') = 0, 'ALTER TABLE cms_service_item ADD KEY idx_svc_item_portal (site_id, status, sort_no ASC, publish_time DESC, id DESC)', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = 'cms_service_dict' AND index_name = 'uk_svc_dict') = 0, 'ALTER TABLE cms_service_dict ADD UNIQUE KEY uk_svc_dict (site_id, dict_type, dict_key)', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = 'cms_service_dict' AND index_name = 'idx_svc_dict_list') = 0, 'ALTER TABLE cms_service_dict ADD KEY idx_svc_dict_list (site_id, dict_type, status, sort_no)', 'DO 0'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 4) 校验 -- 4.1) 应返回 0 行（两表存在且注释非空） SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('cms_service_item', 'cms_service_dict') AND TABLE_COMMENT = ''; -- 4.2) 应返回 10 行（两表全部索引就位：主键 2 + 唯一键 2 + 普通索引 6） SELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name IN ('cms_service_item', 'cms_service_dict') GROUP BY table_name, index_name ORDER BY table_name, index_name;