首页 文章 精选 留言 我的

精选列表

搜索[paging]，共209篇文章
优秀的个人博客,低调大师

sql:postgreSQL 18 paging

1. 引言 本文基于 PostgreSQL 18，完整演示时区映射表 TimeZoneMappings 的建表、数据初始化、三种分页函数（传统页码分页、游标分页、全文检索游标分页）以及 Python 调用示例。内容与 MySQL、Oracle 版本保持一致，方便多数据库迁移对照。 2. 环境准备 本节介绍运行本文示例所需的环境与依赖，包括 PostgreSQL 18 数据库、Python 3.11 以及 psycopg 驱动。 3. 建表与初始化 本节给出 TimeZoneMappings 表的完整建表 SQL，包括 UUID 主键、唯一约束、自动更新时间触发器以及分页所需的核心索引。 4. 分页函数设计 本节讲解三种分页函数的实现思路与适用场景：传统页码分页适合中小数据量，游标分页适合亿级数据，全文检索游标分页则基于 pg_trgm 扩展实现中文模糊搜索。 5. Python 调用示例 本节提供完整的 Python 脚本，演示如何调用上述三个分页函数，并给出运行输出示例。 6. 验证与性能说明 本节介绍如何验证数据量与函数是否就绪，并通过 EXPLAIN 分析索引命中情况，说明不同分页方案在数据量增长时的表现差异。 7. 总结 总结本文要点，并给出后续在多数据库间迁移时的注意事项。 ```sql CREATE DATABASE "geovindu" WITH OWNER = postgres ENCODING = 'UTF8' LC_COLLATE = 'Chinese (Simplified)_China.936' LC_CTYPE = 'Chinese (Simplified)_China.936' TABLESPACE = pg_default CONNECTION LIMIT = -1; COMMENT ON DATABASE "geovindu" --進入資料庫 IS 'geovindu'; -- ============================================================ -- PostgreSQL 18: TimeZoneMappings 建表 + 432 条数据 + 分页函数 -- 共 432 条唯一 IANA 时区, Sort 1..432（与 MySQL/Oracle 版数据完全一致） -- 执行工具: psql / DBeaver / PgAdmin 均可 -- ============================================================ -- 0) 清理旧表（首次执行可注释掉） DROP TABLE IF EXISTS TimeZoneMappings CASCADE; -- 1) 建表（双引号保留驼峰列名，与 MySQL/Oracle 版一致； -- TimeZoneId 用 UUID + gen_random_uuid()（PG13+ 内置，无需扩展） CREATE TABLE "TimeZoneMappings" ( "TimeZoneId" UUID PRIMARY KEY DEFAULT gen_random_uuid(), "IanaTimeZoneId" VARCHAR(100), "WindowsTimeZoneId" VARCHAR(100) NOT NULL, "DailyResetOffset" INTEGER NOT NULL, "Sort" INTEGER NOT NULL DEFAULT 0, "DisplayName" VARCHAR(200), "DisplayNameHK" VARCHAR(200), "DisplayNameEN" VARCHAR(200), "IsActive" SMALLINT NOT NULL DEFAULT 1, "CreatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, "UpdatedAt" TIMESTAMP, "RowVersion" BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE, CONSTRAINT "UQ_IanaTimeZoneId" UNIQUE ("IanaTimeZoneId") ); -- 2) UpdatedAt 自动更新触发器（PostgreSQL 无 ON UPDATE） CREATE OR REPLACE FUNCTION trg_tz_set_updated() RETURNS TRIGGER AS $$ BEGIN NEW."UpdatedAt" := CURRENT_TIMESTAMP; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_tz_updated BEFORE UPDATE ON "TimeZoneMappings" FOR EACH ROW EXECUTE FUNCTION trg_tz_set_updated(); -- 3) 游标分页核心索引 CREATE INDEX "IX_IsActive_Sort" ON "TimeZoneMappings" ("IsActive", "Sort", "RowVersion"); -- 5) 页码分页函数（每行带 total_count，Python 取第一行即可） CREATE OR REPLACE FUNCTION sp_timezone_page( p_keyword TEXT DEFAULT NULL, p_page_number INTEGER DEFAULT 1, p_page_size INTEGER DEFAULT 20 ) RETURNS TABLE ( "total_count" BIGINT, "TimeZoneId" UUID, "IanaTimeZoneId" TEXT, "WindowsTimeZoneId" TEXT, "DailyResetOffset" INTEGER, "Sort" INTEGER, "RowVersion" BIGINT, "DisplayName" TEXT, "DisplayNameHK" TEXT, "DisplayNameEN" TEXT ) LANGUAGE plpgsql AS $$ DECLARE v_kw TEXT := '%'; v_offset INTEGER; BEGIN IF p_page_number IS NULL OR p_page_number 200 THEN p_page_size := 200; END IF; v_offset := (p_page_number - 1) * p_page_size; IF p_keyword IS NOT NULL AND p_keyword <> '' THEN v_kw := '%' || replace(replace(replace(p_keyword, '\', '\\'), '%', '\%'), '_', '\_') || '%'; END IF; RETURN QUERY SELECT (SELECT COUNT(*)::BIGINT FROM "TimeZoneMappings" WHERE "IsActive" = 1 AND (v_kw = '%' OR "DisplayName" LIKE v_kw ESCAPE '\' OR "DisplayNameHK" LIKE v_kw ESCAPE '\' OR "DisplayNameEN" LIKE v_kw ESCAPE '\')), t."TimeZoneId", t."IanaTimeZoneId", t."WindowsTimeZoneId", t."DailyResetOffset", t."Sort", t."RowVersion", t."DisplayName", t."DisplayNameHK", t."DisplayNameEN" FROM "TimeZoneMappings" t WHERE t."IsActive" = 1 AND (v_kw = '%' OR t."DisplayName" LIKE v_kw ESCAPE '\' OR t."DisplayNameHK" LIKE v_kw ESCAPE '\' OR t."DisplayNameEN" LIKE v_kw ESCAPE '\') ORDER BY t."Sort", t."RowVersion" LIMIT p_page_size OFFSET v_offset; END; $$; -- 6) 游标分页函数（亿级主推；每行带 has_more，与 MySQL/Oracle 版同语义） CREATE OR REPLACE FUNCTION sp_timezone_cursor( p_keyword TEXT DEFAULT NULL, p_last_sort INTEGER DEFAULT NULL, p_last_row_version BIGINT DEFAULT NULL, p_page_size INTEGER DEFAULT 20 ) RETURNS TABLE ( "has_more" BOOLEAN, "TimeZoneId" UUID, "IanaTimeZoneId" TEXT, "WindowsTimeZoneId" TEXT, "DailyResetOffset" INTEGER, "Sort" INTEGER, "RowVersion" BIGINT, "DisplayName" TEXT, "DisplayNameHK" TEXT, "DisplayNameEN" TEXT ) LANGUAGE plpgsql AS $$ DECLARE v_first_page BOOLEAN; v_kw TEXT := '%'; BEGIN IF p_page_size IS NULL OR p_page_size 200 THEN p_page_size := 200; END IF; v_first_page := (p_last_sort IS NULL OR p_last_row_version IS NULL); IF p_keyword IS NOT NULL AND p_keyword <> '' THEN v_kw := '%' || replace(replace(replace(p_keyword, '\', '\\'), '%', '\%'), '_', '\_') || '%'; END IF; RETURN QUERY SELECT EXISTS ( SELECT 1 FROM "TimeZoneMappings" WHERE "IsActive" = 1 AND (v_kw = '%' OR "DisplayName" LIKE v_kw ESCAPE '\' OR "DisplayNameHK" LIKE v_kw ESCAPE '\' OR "DisplayNameEN" LIKE v_kw ESCAPE '\') AND (v_first_page OR ("Sort" > p_last_sort OR ("Sort" = p_last_sort AND "RowVersion" > p_last_row_version)))), t."TimeZoneId", t."IanaTimeZoneId", t."WindowsTimeZoneId", t."DailyResetOffset", t."Sort", t."RowVersion", t."DisplayName", t."DisplayNameHK", t."DisplayNameEN" FROM "TimeZoneMappings" t WHERE t."IsActive" = 1 AND (v_kw = '%' OR t."DisplayName" LIKE v_kw ESCAPE '\' OR t."DisplayNameHK" LIKE v_kw ESCAPE '\' OR t."DisplayNameEN" LIKE v_kw ESCAPE '\') AND (v_first_page OR (t."Sort" > p_last_sort OR (t."Sort" = p_last_sort AND t."RowVersion" > p_last_row_version))) ORDER BY t."Sort", t."RowVersion" LIMIT p_page_size; END; $$; -- 7) 全文检索游标分页函数（对应 MySQL/Oracle 的 sp_timezone_search_ft） -- 中文检索索引: pg_trgm（PostgreSQL 官方 contrib 扩展, 3-gram, 无需编译第三方插件） -- 如需中文 2-gram 可改用 pg_bigm 扩展（第三方），函数本体不变 SELECT name, installed_version, available_version FROM pg_available_extensions WHERE name = 'pg_trgm'; -- 确认索引（应看到 2 个：IX_IsActive_Sort、ft_idx_display_trgm） SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'TimeZoneMappings'; CREATE EXTENSION IF NOT EXISTS pg_trgm; -- 三列拼接后的 trgm GIN 索引（表达式须与函数内查询完全一致才能命中索引） CREATE INDEX "ft_idx_display_trgm" ON "TimeZoneMappings" USING GIN (("DisplayName" || ' ' || "DisplayNameHK" || ' ' || "DisplayNameEN") gin_trgm_ops); CREATE OR REPLACE FUNCTION sp_timezone_search_ft( p_keyword TEXT DEFAULT NULL, p_last_sort INTEGER DEFAULT NULL, p_last_row_version BIGINT DEFAULT NULL, p_page_size INTEGER DEFAULT 20 ) RETURNS TABLE ( "has_more" BOOLEAN, "TimeZoneId" UUID, "IanaTimeZoneId" TEXT, "WindowsTimeZoneId" TEXT, "DailyResetOffset" INTEGER, "Sort" INTEGER, "RowVersion" BIGINT, "DisplayName" TEXT, "DisplayNameHK" TEXT, "DisplayNameEN" TEXT ) LANGUAGE plpgsql AS $$ DECLARE v_first_page BOOLEAN; v_kw TEXT := '%'; BEGIN IF p_page_size IS NULL OR p_page_size 200 THEN p_page_size := 200; END IF; v_first_page := (p_last_sort IS NULL OR p_last_row_version IS NULL); IF p_keyword IS NOT NULL AND p_keyword <> '' THEN v_kw := '%' || replace(replace(replace(p_keyword, '\', '\\'), '%', '\%'), '_', '\_') || '%'; END IF; RETURN QUERY SELECT EXISTS ( SELECT 1 FROM "TimeZoneMappings" WHERE "IsActive" = 1 AND (v_kw = '%' OR ("DisplayName" || ' ' || "DisplayNameHK" || ' ' || "DisplayNameEN") LIKE v_kw ESCAPE '\') AND (v_first_page OR ("Sort" > p_last_sort OR ("Sort" = p_last_sort AND "RowVersion" > p_last_row_version)))), t."TimeZoneId", t."IanaTimeZoneId", t."WindowsTimeZoneId", t."DailyResetOffset", t."Sort", t."RowVersion", t."DisplayName", t."DisplayNameHK", t."DisplayNameEN" FROM "TimeZoneMappings" t WHERE t."IsActive" = 1 AND (v_kw = '%' OR (t."DisplayName" || ' ' || t."DisplayNameHK" || ' ' || t."DisplayNameEN") LIKE v_kw ESCAPE '\') AND (v_first_page OR (t."Sort" > p_last_sort OR (t."Sort" = p_last_sort AND t."RowVersion" > p_last_row_version))) ORDER BY t."Sort", t."RowVersion" LIMIT p_page_size; END; $$; -- 1) 数据量 SELECT COUNT(*) AS cnt, COUNT(DISTINCT "IanaTimeZoneId") AS uniq FROM "TimeZoneMappings"; -- 应为 432 / 432 -- 2) 三个函数是否齐 SELECT proname FROM pg_proc WHERE proname IN ('sp_timezone_page','sp_timezone_cursor','sp_timezone_search_ft') ORDER BY proname; -- 应返回 3 行 EXPLAIN (ANALYZE, BUFFERS) SELECT "Sort", "RowVersion", "DisplayName" FROM "TimeZoneMappings" WHERE "IsActive" = 1 AND ("DisplayName" || ' ' || "DisplayNameHK" || ' ' || "DisplayNameEN") LIKE '%岛%' ORDER BY "Sort", "RowVersion" LIMIT 20; -- ============================================================ -- 验证: SELECT COUNT(*) FROM "TimeZoneMappings"; -- 应返回 432 -- SELECT COUNT(DISTINCT "IanaTimeZoneId") FROM "TimeZoneMappings"; -- 432 -- ============================================================ ``` python调用： ```python # encoding: utf-8 # 版权所有 2026 ©涂聚文有限公司™ ® # 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎 # 描述：postgreSQL 18.0 # Author : geovindu,Geovin Du 涂聚文. # IDE : PyCharm 2024.3.6 python 3.11 # os : windows 10 # database : mysql 9.0 sql server 2019, postgreSQL 18.0 Oracle 21c Neo4j # Datetime : 2026/9/12 11:42 # User : geovindu # Product : PyCharm # Project : Pysimple # File : postsqlgrestlpaging.py # -*- coding: utf-8 -*- """ PostgreSQL 18 版 TimeZoneMappings 分页调用示例（对应 MySQL/Oracle 版） 依赖: pip install "psycopg[binary]" 用法: 先执行 timezone_postgresql_18.sql 建表+插数+建函数, 再改下方 PG 连接参数运行本脚本 sp_timezone_page sp_timezone_cursor sp_timezone_search_ft """ import psycopg ============ 连接参数（改成你自己的） ============ PG = { "host": "localhost", "port": 5432, "dbname": "postgres", "user": "postgres", "password": "geovindu", } ================================================ conn = psycopg.connect(**PG) def page(keyword=None, page_number=1, page_size=20): """传统页码分页: 返回 (rows, total_count)""" with conn.cursor() as cur: rows = cur.execute( "SELECT * FROM sp_timezone_page(%s, %s, %s)", (keyword, page_number, page_size), ).fetchall() total = rows[0][0] if rows else 0 return rows, total def cursor_page(keyword=None, last_sort=None, last_row_version=None, page_size=20): """游标分页(亿级主推): 传上次页尾 (Sort, RowVersion), 首页传 None。 返回 (rows, has_more)""" with conn.cursor() as cur: rows = cur.execute( "SELECT * FROM sp_timezone_cursor(%s, %s, %s, %s)", (keyword, last_sort, last_row_version, page_size), ).fetchall() has_more = bool(rows[0][0]) if rows else False return rows, has_more def search_ft(keyword=None, last_sort=None, last_row_version=None, page_size=20): """全文检索游标分页(pg_trgm, 对应 sp_timezone_search_ft): 匹配三列拼接后的全文, 同样基于 (Sort, RowVersion) 游标。 返回 (rows, has_more)""" with conn.cursor() as cur: rows = cur.execute( "SELECT * FROM sp_timezone_search_ft(%s, %s, %s, %s)", (keyword, last_sort, last_row_version, page_size), ).fetchall() has_more = bool(rows[0][0]) if rows else False return rows, has_more def print_rows(rows, first_col_is_meta=True): # 列: [meta] TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset, # Sort, RowVersion, DisplayName, DisplayNameHK, DisplayNameEN off = 1 if first_col_is_meta else 0 for r in rows: print(f" sort={r[off + 4]:>3} rv={r[off + 5]:>4} {r[off + 1]:<28} {r[off + 6]}") def demo_page(): print("=" * 70) print("[1] 传统页码分页: 第 2 页, 每页 5 条, 关键词='岛'") print("=" * 70) rows, total = page(keyword="岛", page_number=2, page_size=5) print(f"总命中: {total} 条") print_rows(rows) def demo_cursor(): print("=" * 70) print("[2] 游标分页(亿级): 关键词='北', 每页 3 条, 从头翻到尾") print("=" * 70) keyword = "北" last_sort, last_rv = None, None page_no = 1 while True: rows, has_more = cursor_page(keyword, last_sort, last_rv, 3) if not rows: break print(f"-- 第 {page_no} 页 --") print_rows(rows) last_sort, last_rv = rows[-1][5], rows[-1][6] # Sort, RowVersion if not has_more: break page_no += 1 print(f"翻页结束, 共 {page_no} 页") def demo_all(): print("=" * 70) print("[3] 游标分页: 无关键词, 每页 20 条, 从头翻到尾") print("=" * 70) last_sort, last_rv = None, None page_no = 1 total_rows = 0 while True: rows, has_more = cursor_page(None, last_sort, last_rv, 20) if not rows: break total_rows += len(rows) print(f"第 {page_no} 页: {len(rows)} 条, 尾游标 sort={rows[-1][5]} rv={rows[-1][6]}, has_more={has_more}") last_sort, last_rv = rows[-1][5], rows[-1][6] if not has_more: break page_no += 1 print(f"全部翻完: {total_rows} 条 / {page_no} 页 (应为 432 条)") def demo_ft(): print("=" * 70) print("[4] 全文检索游标分页(pg_trgm): 关键词='北', 每页 5 条") print("=" * 70) keyword = "北" last_sort, last_rv = None, None page_no = 1 total_rows = 0 while True: rows, has_more = search_ft(keyword, last_sort, last_rv, 5) if not rows: break print(f"-- 第 {page_no} 页 (has_more={has_more}) --") print_rows(rows) total_rows += len(rows) last_sort, last_rv = rows[-1][5], rows[-1][6] if not has_more: break page_no += 1 print(f"全文检索共 {total_rows} 条 / {page_no} 页") if name == "main": try: demo_page() demo_cursor() demo_all() demo_ft() finally: conn.close() ``` 输出： ![](https://developer.qcloudimg.com/http-save/audit-8397743/5e2fd6417951a36ffc38c699aafadbbe.png)
2026-09-13
优秀的个人博客,低调大师

sql: mysql 9.0 paging

一、建表脚本：TimeZoneMappings 表结构设计，含 UUID 主键、时区映射字段、游标键 RowVersion 及索引规划。 二、全文索引改造：中文 ngram 索引与英文默认索引的双索引方案，解决中英文混合检索问题。 三、存储过程定义：传统页码分页、游标分页、全文检索加游标三种分页存储过程。 四、Python 调用示例：PyMySQL 封装三种分页方式的调用代码与翻页演示。 ```sql -- ============================================================ -- MySQL 9.0 - TimeZoneMappings 建表脚本（修正版） -- ============================================================ drop table TimeZoneMappings; CREATE TABLE TimeZoneMappings ( TimeZoneId VARBINARY(16) PRIMARY KEY, -- UUID 存为 BINARY(16) IanaTimeZoneId VARCHAR(100), -- IANA 时区 WindowsTimeZoneId VARCHAR(100) NOT NULL, -- Windows 时区 DailyResetOffset INT NOT NULL, -- 与 UTC 偏移(分钟)，中国=480 Sort INT NOT NULL DEFAULT 0, -- 排序字段 DisplayName VARCHAR(200), -- 简体中文 DisplayNameHK VARCHAR(200), -- 繁体中文 DisplayNameEN VARCHAR(200), -- 英文 IsActive TINYINT NOT NULL DEFAULT 1, -- 0/1 CreatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UpdatedAt TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP, -- 高性能游标键（亿级分页必须） RowVersion BIGINT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE, CONSTRAINT UQ_IanaTimeZoneId UNIQUE (IanaTimeZoneId), KEY IX_IsActive_Sort (IsActive, Sort, RowVersion) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; select * from TimeZoneMappings; -- ============================================================ -- 2) 双全文索引改造（中文 + 英文 分别使用最优解析器） -- ============================================================ -- 2.1 删除旧的 ngram 全文索引（如果存在） SET @drop_ft = ( SELECT IF(COUNT(*) > 0, 'ALTER TABLE TimeZoneMappings DROP INDEX ft_idx_display_ngram', 'SELECT 1') FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'TimeZoneMappings' AND index_name = 'ft_idx_display_ngram' ); PREPARE s_drop_ft FROM @drop_ft; EXECUTE s_drop_ft; DEALLOCATE PREPARE s_drop_ft; -- 2.2 删除旧的默认全文索引（如果存在） SET @drop_ft2 = ( SELECT IF(COUNT(*) > 0, 'ALTER TABLE TimeZoneMappings DROP INDEX ft_idx_display_en', 'SELECT 1') FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'TimeZoneMappings' AND index_name = 'ft_idx_display_en' ); PREPARE s_drop_ft2 FROM @drop_ft2; EXECUTE s_drop_ft2; DEALLOCATE PREPARE s_drop_ft2; -- ============================================================ -- 【英文全文检索修复核心】双索引方案 -- ============================================================ -- 2.3 中文全文索引（ngram 解析器） -- 覆盖 DisplayName + DisplayNameHK -- ngram_token_size=2 对中文2字词准确率接近100% ALTER TABLE TimeZoneMappings ADD FULLTEXT INDEX ft_idx_cn_ngram (DisplayName, DisplayNameHK) WITH PARSER ngram; -- 2.4 英文全文索引（MySQL 默认解析器） -- 覆盖 DisplayNameEN + IanaTimeZoneId -- 默认解析器基于空格/标点分词，对英文文本检索效果优秀 -- IanaTimeZoneId 如 "Pacific/Kanton" 也会被正确分词检索 ALTER TABLE TimeZoneMappings ADD FULLTEXT INDEX ft_idx_display_en (DisplayNameEN, IanaTimeZoneId); -- 说明： -- * ngram 索引仅覆盖中文列，避免英文文本被切分为无意义2字符片段 -- * 默认解析器索引覆盖英文列和时区ID列，英文分词准确 -- * 存储过程用 OR 组合两索引，中文英文搜索均可命中 -- * 如需支持单字搜索，在 my.cnf 设 ngram_token_size=1 后重启(只读变量) -- ============================================================ -- 3) 存储过程定义 -- ============================================================ -- 注意：以下存储过程定义使用 DELIMITER // 改变语句结束符， -- 因为存储过程体内部包含分号 ; ，如果不改变结束符， -- MySQL 会误以为存储过程定义在此处结束而导致语法错误。 -- 先删除已存在的存储过程（避免重复创建报错） DROP PROCEDURE IF EXISTS sp_timezone_page; DROP PROCEDURE IF EXISTS sp_timezone_cursor; DROP PROCEDURE IF EXISTS sp_timezone_search_ft; -- 【关键】改变语句结束符为 //，否则存储过程体内的 ; 会被误识别为语句结束 DELIMITER // -- ============================================================ -- 3.1) 传统页码分页存储过程（管理后台/小表用，亿级高频不推荐） -- 返回：1 个数据结果集 + OUT p_total（精确总数） -- ============================================================ CREATE PROCEDURE sp_timezone_page( IN p_keyword VARCHAR(200), IN p_page_number INT, IN p_page_size INT, OUT p_total BIGINT ) BEGIN DECLARE v_offset INT DEFAULT 0; DECLARE v_kw VARCHAR(220) DEFAULT '%'; -- 参数保护 SET p_page_number = IF(p_page_number IS NULL OR p_page_number < 1, 1, p_page_number); SET p_page_size = IF(p_page_size IS NULL OR p_page_size < 1, 20, IF(p_page_size > 200, 200, p_page_size)); SET v_offset = (p_page_number - 1) * p_page_size; -- 关键词按字面量处理：转义 LIKE 通配符，防用户输入 % / _ 造成语义漂移 IF p_keyword IS NOT NULL AND p_keyword <> '' THEN SET v_kw = CONCAT('%', REPLACE(REPLACE(REPLACE(p_keyword, '\\', '\\\\'), '%', '\\%'), '_', '\\_'), '%'); END IF; -- 1) 精确总数（亿级下成本高，管理后台可接受） SELECT COUNT(*) INTO p_total FROM TimeZoneMappings WHERE IsActive = 1 AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\\' OR DisplayNameHK LIKE v_kw ESCAPE '\\' OR DisplayNameEN LIKE v_kw ESCAPE '\\' OR IanaTimeZoneId LIKE v_kw ESCAPE '\\'); -- 2) 当前页数据 SELECT BIN_TO_UUID(TimeZoneId, 1) AS TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset, Sort, RowVersion, DisplayName, DisplayNameHK, DisplayNameEN FROM TimeZoneMappings WHERE IsActive = 1 AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\\' OR DisplayNameHK LIKE v_kw ESCAPE '\\' OR DisplayNameEN LIKE v_kw ESCAPE '\\' OR IanaTimeZoneId LIKE v_kw ESCAPE '\\') ORDER BY Sort, RowVersion LIMIT v_offset, p_page_size; END // -- ============================================================ -- 3.2) 游标分页存储过程（亿级主推） -- 入参：上一页最后一条的 (Sort, RowVersion)，首页两者传 NULL -- 出参：p_has_more = 1 还有下一页 / 0 没有 -- 数据查询 + has_more 判断都只扫描一页，稳定走 IX_IsActive_Sort -- ============================================================ CREATE PROCEDURE sp_timezone_cursor( IN p_keyword VARCHAR(200), IN p_last_sort INT, IN p_last_row_version BIGINT UNSIGNED, IN p_page_size INT, OUT p_has_more TINYINT ) BEGIN DECLARE v_first_page TINYINT DEFAULT 0; DECLARE v_kw VARCHAR(220) DEFAULT '%'; SET p_page_size = IF(p_page_size IS NULL OR p_page_size < 1, 20, IF(p_page_size > 200, 200, p_page_size)); SET v_first_page = IF(p_last_sort IS NULL OR p_last_row_version IS NULL, 1, 0); IF p_keyword IS NOT NULL AND p_keyword <> '' THEN SET v_kw = CONCAT('%', REPLACE(REPLACE(REPLACE(p_keyword, '\\', '\\\\'), '%', '\\%'), '_', '\\_'), '%'); END IF; -- 1) 当前页数据 IF v_first_page = 1 THEN SELECT BIN_TO_UUID(TimeZoneId, 1) AS TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset, Sort, RowVersion, DisplayName, DisplayNameHK, DisplayNameEN FROM TimeZoneMappings WHERE IsActive = 1 AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\\' OR DisplayNameHK LIKE v_kw ESCAPE '\\' OR DisplayNameEN LIKE v_kw ESCAPE '\\' OR IanaTimeZoneId LIKE v_kw ESCAPE '\\') ORDER BY Sort, RowVersion LIMIT p_page_size; ELSE SELECT BIN_TO_UUID(TimeZoneId, 1) AS TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset, Sort, RowVersion, DisplayName, DisplayNameHK, DisplayNameEN FROM TimeZoneMappings WHERE IsActive = 1 AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\\' OR DisplayNameHK LIKE v_kw ESCAPE '\\' OR DisplayNameEN LIKE v_kw ESCAPE '\\' OR IanaTimeZoneId LIKE v_kw ESCAPE '\\') AND (Sort > p_last_sort OR (Sort = p_last_sort AND RowVersion > p_last_row_version)) ORDER BY Sort, RowVersion LIMIT p_page_size; END IF; -- 2) 是否还有下一页（EXISTS 短路，游标条件走索引，开销极小） IF v_first_page = 1 THEN SELECT EXISTS( SELECT 1 FROM TimeZoneMappings WHERE IsActive = 1 AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\\' OR DisplayNameHK LIKE v_kw ESCAPE '\\' OR DisplayNameEN LIKE v_kw ESCAPE '\\' OR IanaTimeZoneId LIKE v_kw ESCAPE '\\') LIMIT 1 ) INTO p_has_more; ELSE SELECT EXISTS( SELECT 1 FROM TimeZoneMappings WHERE IsActive = 1 AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\\' OR DisplayNameHK LIKE v_kw ESCAPE '\\' OR DisplayNameEN LIKE v_kw ESCAPE '\\' OR IanaTimeZoneId LIKE v_kw ESCAPE '\\') AND (Sort > p_last_sort OR (Sort = p_last_sort AND RowVersion > p_last_row_version)) LIMIT 1 ) INTO p_has_more; END IF; END // -- ============================================================ -- 3.3) 双全文检索 + 游标分页（中文 + 英文 通用） -- 入参 p_ft_expr：布尔模式表达式，由应用层构造，如 '+中国' / '+Pacific' -- 注意：p_ft_expr 会被拼进 SQL，应用层必须白名单校验，勿直接透传用户输入 -- 首屏 p_last_sort / p_last_row_version 传 NULL -- 【英文全文检索修复】 -- 使用双全文索引 OR 组合： -- - 中文列 (DisplayName, DisplayNameHK) → ngram 索引 ft_idx_cn_ngram -- - 英文列 (DisplayNameEN, IanaTimeZoneId) → 默认索引 ft_idx_display_en -- 中文英文搜索均可正确命中 -- ============================================================ CREATE PROCEDURE sp_timezone_search_ft( IN p_ft_expr VARCHAR(255), IN p_last_sort INT, IN p_last_row_version BIGINT UNSIGNED, IN p_page_size INT, OUT p_has_more TINYINT ) BEGIN DECLARE v_first_page TINYINT DEFAULT 0; DECLARE v_sql VARCHAR(3000); DECLARE v_expr VARCHAR(255); SET p_page_size = IF(p_page_size IS NULL OR p_page_size < 1, 20, IF(p_page_size > 200, 200, p_page_size)); SET v_first_page = IF(p_last_sort IS NULL OR p_last_row_version IS NULL, 1, 0); SET v_expr = IF(p_ft_expr IS NULL OR p_ft_expr = '', '+', p_ft_expr); -- 单引号转义，防止破坏外层 SQL SET v_expr = REPLACE(v_expr, '''', ''''''); -- 1) 当前页数据（MATCH...AGAINST 不支持绑定参数，动态拼 SQL；数值字段拼接安全） -- 【英文全文检索修复】双索引 OR 组合： -- MATCH(DisplayName, DisplayNameHK) → 走 ngram 索引（中文优化） -- MATCH(DisplayNameEN, IanaTimeZoneId) → 走默认索引（英文优化） SET v_sql = CONCAT( 'SELECT BIN_TO_UUID(TimeZoneId, 1) AS TimeZoneId,', ' IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset,', ' Sort, RowVersion, DisplayName, DisplayNameHK, DisplayNameEN', ' FROM TimeZoneMappings', ' WHERE IsActive = 1', ' AND (', ' MATCH(DisplayName, DisplayNameHK) AGAINST(''', v_expr, ''' IN BOOLEAN MODE)', ' OR ', ' MATCH(DisplayNameEN, IanaTimeZoneId) AGAINST(''', v_expr, ''' IN BOOLEAN MODE)', ' )', IF(v_first_page = 1, '', CONCAT(' AND (Sort > ', p_last_sort, ' OR (Sort = ', p_last_sort, ' AND RowVersion > ', p_last_row_version, '))')), ' ORDER BY Sort, RowVersion', ' LIMIT ', p_page_size ); SET @s_ft = v_sql; PREPARE s_ft FROM @s_ft; EXECUTE s_ft; DEALLOCATE PREPARE s_ft; -- 2) 是否还有下一页 -- 注意：MySQL 的 EXECUTE 不支持 INTO（EXECUTE ... INTO 是非法语法）， -- 改为在 PREPARE 的 SQL 内写 SELECT ... INTO @用户变量， -- 执行后再赋值给输出参数。 SET @v_has_more = 0; SET v_sql = CONCAT( 'SELECT EXISTS(', ' SELECT 1 FROM TimeZoneMappings', ' WHERE IsActive = 1', ' AND (', ' MATCH(DisplayName, DisplayNameHK) AGAINST(''', v_expr, ''' IN BOOLEAN MODE)', ' OR ', ' MATCH(DisplayNameEN, IanaTimeZoneId) AGAINST(''', v_expr, ''' IN BOOLEAN MODE)', ' )', IF(v_first_page = 1, '', CONCAT(' AND (Sort > ', p_last_sort, ' OR (Sort = ', p_last_sort, ' AND RowVersion > ', p_last_row_version, '))')), ' LIMIT 1) INTO @v_has_more' ); SET @s_ft2 = v_sql; PREPARE s_ft2 FROM @s_ft2; EXECUTE s_ft2; SET p_has_more = @v_has_more; DEALLOCATE PREPARE s_ft2; END // -- 【关键】恢复语句结束符为分号 DELIMITER ; CALL sp_timezone_cursor('岛', NULL, NULL, 20, @has_more); -- 第一页 CALL sp_timezone_cursor('岛', 372, 372, 20, @has_more); -- 用上页最后一条 Sort/RowVersion 翻页 CALL sp_timezone_search_ft('+斐济', NULL, NULL, 20, @has_more); -- 中文全文 ``` python简单调用： ```python # encoding: utf-8 # 版权所有 2026 ©涂聚文有限公司™ ® # 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎 # 描述： # Author : geovindu,Geovin Du 涂聚文. # IDE : PyCharm 2024.3.6 python 3.11 # os : windows 10 # database : mysql 9.0 sql server 2019, postgreSQL 17.0 Oracle 21c Neo4j # Datetime : 2026/9/12 7:21 # User : geovindu # Product : PyCharm # Project : Pysimple # File : mysqlpageing.py # -*- coding: utf-8 -*- """ TimeZoneMappings 亿级分页调用示例（PyMySQL） 配套: timezone_pagination_optimized.sql 三种分页方式： sp_timezone_page 传统页码分页（管理后台 / 小表） sp_timezone_cursor 游标分页，亿级主推（LIKE 模糊，防深分页） sp_timezone_search_ft ngram 全文检索 + 游标（中文搜索推荐） 游标分页翻页规则： 首页 last_sort / last_row_version 传 None； 翻页时把上一页最后一条的 (Sort, RowVersion) 作为下一页入参。 """ import pymysql MYSQL = dict( host="127.0.0.1", port=3306, user="root", password="888888", # 改成实际密码 database="geovindu", # 改成实际库名 charset="utf8mb4", cursorclass=pymysql.cursors.DictCursor, ) def get_conn(): return pymysql.connect(**MYSQL) ---------------- 1) 传统页码分页 ---------------- def page(keyword, page_number, page_size=20): conn = get_conn() try: with conn.cursor() as cur: # 最后一个参数 0 是 OUT 占位符 cur.callproc("sp_timezone_page", (keyword, page_number, page_size, 0)) rows = cur.fetchall() cur.execute("SELECT @_sp_timezone_page_3 AS total") total = cur.fetchone()["total"] return rows, total finally: conn.close() ---------------- 2) 游标分页（亿级推荐） ---------------- def cursor_page(keyword, last_sort=None, last_row_version=None, page_size=20): conn = get_conn() try: with conn.cursor() as cur: cur.callproc( "sp_timezone_cursor", (keyword, last_sort, last_row_version, page_size, 0), ) rows = cur.fetchall() cur.execute("SELECT @_sp_timezone_cursor_4 AS has_more") has_more = bool(cur.fetchone()["has_more"]) return rows, has_more finally: conn.close() ---------------- 3) ngram 全文检索 + 游标 ---------------- def ft_search(ft_expr, last_sort=None, last_row_version=None, page_size=20): conn = get_conn() try: with conn.cursor() as cur: cur.callproc( "sp_timezone_search_ft", (ft_expr, last_sort, last_row_version, page_size, 0), ) rows = cur.fetchall() cur.execute("SELECT @_sp_timezone_search_ft_4 AS has_more") has_more = bool(cur.fetchone()["has_more"]) return rows, has_more finally: conn.close() ---------------- 演示：游标分页翻页循环 ---------------- def demo_cursor(): keyword = "岛" last_sort = None last_rv = None page_no = 1 while True: rows, has_more = cursor_page(keyword, last_sort, last_rv, 20) print(f"--- 第 {page_no} 页 | 本页 {len(rows)} 条 | has_more={has_more} ---") for r in rows: print( f" {r['TimeZoneId']} {r['DisplayName']:<12} " f"Sort={r['Sort']} RowVersion={r['RowVersion']}" ) if not rows or not has_more: break last = rows[-1] last_sort, last_rv = last["Sort"], last["RowVersion"] page_no += 1 ---------------- 演示：全文检索 ---------------- def demo_ft(): rows, has_more = ft_search("+岛") print(f"--- 全文检索 '+岛' | {len(rows)} 条 | has_more={has_more} ---") for r in rows[:5]: print(f" {r['DisplayName']} {r['IanaTimeZoneId']}") if name == "main": # 1) 传统页码分页 rows, total = page("岛", 1, 20) print(f"传统页码: total={total}, 本页 {len(rows)} 条") for r in rows[:3]: print(f" {r['DisplayName']} {r['IanaTimeZoneId']}") # 2) 游标分页翻页循环（亿级推荐） demo_cursor() # 3) ngram 全文检索 demo_ft() ``` 输出： ![](https://developer.qcloudimg.com/http-save/audit-8397743/2c54cbe364426e754cb187004adcebe2.png)
2026-09-12
优秀的个人博客,低调大师

sql: sql server 2019 paging

1. 引言 在跨时区业务系统中，时区映射表是支撑时间转换、调度任务和报表统计的基础数据。本文以 SQL Server 2019 为例，介绍如何创建 TimeZoneMappings 时区映射表，写入 432 条 IANA 时区数据，并配套全文索引与两种分页存储过程，帮助你在实际项目中快速落地。 2. 表结构设计 TimeZoneMappings 表用于保存 IANA 时区与 Windows 时区的对应关系，同时记录每日重置偏移量、排序号、多语言显示名称等字段。主键采用 UNIQUEIDENTIFIER 并由 NEWID() 生成，与 MySQL 的 BINARY(16)、Oracle 的 RAW(16)、PostgreSQL 的 UUID 保持对应。 3. 建表与索引 建表时通过约束保证 IanaTimeZoneId 与 RowVersion 的唯一性，并创建游标分页所需的复合索引。同时提供 UpdatedAt 自动更新触发器，确保数据变更时自动记录更新时间。 4. 全文索引配置 SQL Server 全文索引支持按语言断字符检索。本文针对简体中文、繁体中文和英文分别指定 LANGUAGE 2052、3076、1033，并说明未安装中文语言组件时的降级方案。 5. 分页存储过程 提供三种存储过程：传统页码分页、游标分页（亿级数据主推）以及全文检索游标分页。每个存储过程均返回两个结果集，分别用于总数/续页标记与当前页数据。 6. Python 调用示例 使用 pyodbc 连接 SQL Server，封装页码分页、游标分页与全文检索三个函数，并给出完整的演示脚本，覆盖关键词搜索、翻页遍历与结果打印。 7. 运行结果 展示脚本实际运行输出，验证分页与全文检索功能符合预期。 8. 总结 本文完整演示了 SQL Server 2019 下时区映射表的建表、索引、全文检索与分页实现，并提供了可复用的 Python 调用代码，可作为跨数据库时区方案的参考实现。 ```sql -- ============================================================ -- SQL Server 2019: TimeZoneMappings 建表 + 432 条数据 + 全文索引 + 分页存储过程 -- 共 432 条唯一 IANA 时区, Sort 1..432（与 MySQL/Oracle/PostgreSQL 版数据完全一致） -- 执行工具: SSMS / Azure Data Studio / sqlcmd -- ============================================================ -- 0) 清理（先删全文索引再删表；目录单独清理） IF EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = OBJECT_ID(N'dbo.TimeZoneMappings')) DROP FULLTEXT INDEX ON dbo.TimeZoneMappings; GO IF OBJECT_ID(N'dbo.TimeZoneMappings', N'U') IS NOT NULL DROP TABLE dbo.TimeZoneMappings; GO IF EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = N'ft_tz_catalog') DROP FULLTEXT CATALOG ft_tz_catalog; GO -- 1) 建表（TimeZoneId 用 UNIQUEIDENTIFIER + NEWID()，对应 MySQL BINARY(16)/Oracle RAW(16)/PG UUID） CREATE TABLE dbo.TimeZoneMappings ( [TimeZoneId] UNIQUEIDENTIFIER NOT NULL CONSTRAINT PK_TimeZoneMappings PRIMARY KEY DEFAULT NEWID(), [IanaTimeZoneId] NVARCHAR(100), [WindowsTimeZoneId] NVARCHAR(100) NOT NULL, [DailyResetOffset] INT NOT NULL, [Sort] INT NOT NULL DEFAULT 0, [DisplayName] NVARCHAR(200), [DisplayNameHK] NVARCHAR(200), [DisplayNameEN] NVARCHAR(200), [IsActive] TINYINT NOT NULL DEFAULT 1, [CreatedAt] DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(), [UpdatedAt] DATETIME2(3) NULL, [RowVersion] BIGINT NOT NULL IDENTITY(1,1), CONSTRAINT UQ_IanaTimeZoneId UNIQUE ([IanaTimeZoneId]), CONSTRAINT UQ_RowVersion UNIQUE ([RowVersion]) ); GO -- 2) UpdatedAt 自动更新触发器（SQL Server 无 ON UPDATE） CREATE TRIGGER trg_tz_updated ON dbo.TimeZoneMappings AFTER UPDATE AS BEGIN SET NOCOUNT ON; UPDATE t SET [UpdatedAt] = SYSDATETIME() FROM dbo.TimeZoneMappings t INNER JOIN inserted i ON t.[TimeZoneId] = i.[TimeZoneId]; END; GO -- 3) 游标分页核心索引 CREATE INDEX IX_IsActive_Sort ON dbo.TimeZoneMappings ([IsActive], [Sort], [RowVersion]); GO select * from TimeZoneMappings go -- 5) 全文检索（SQL Server 全文索引） -- 简体中文断字符 LANGUAGE 2052 / 繁体 3076 / 英文 1033 -- !!! 若报 'LANGUAGE 2052 不是有效语言' 说明服务器未安装中文全文语言组件, -- 可改用 LANGUAGE 0（中性）或将中文列降级为 LIKE 检索（sp_timezone_cursor 已覆盖） CREATE FULLTEXT CATALOG ft_tz_catalog AS DEFAULT; GO CREATE FULLTEXT INDEX ON dbo.TimeZoneMappings ( [DisplayName] LANGUAGE 2052, [DisplayNameHK] LANGUAGE 3076, [DisplayNameEN] LANGUAGE 1033 ) KEY INDEX PK_TimeZoneMappings ON ft_tz_catalog WITH STOPLIST = SYSTEM; GO -- 6) 存储过程: 页码分页（返回两个结果集: 1=total_count, 2=数据） CREATE OR ALTER PROCEDURE dbo.sp_timezone_page @p_keyword NVARCHAR(200) = NULL, @p_page_number INT = 1, @p_page_size INT = 20 AS BEGIN SET NOCOUNT ON; IF @p_page_number IS NULL OR @p_page_number 200 SET @p_page_size = 200; DECLARE @v_offset INT = (@p_page_number - 1) * @p_page_size; DECLARE @v_kw NVARCHAR(220) = N'%'; IF @p_keyword IS NOT NULL AND @p_keyword <> N'' SET @v_kw = N'%' + REPLACE(REPLACE(REPLACE(@p_keyword, N'\', N'\\'), N'%', N'\%'), N'_', N'\_') + N'%'; -- 1) 总记录数 SELECT COUNT(*) AS total_count FROM dbo.TimeZoneMappings WHERE [IsActive] = 1 AND (@v_kw = N'%' OR [DisplayName] LIKE @v_kw ESCAPE N'\' OR [DisplayNameHK] LIKE @v_kw ESCAPE N'\' OR [DisplayNameEN] LIKE @v_kw ESCAPE N'\'); -- 2) 分页数据 SELECT [TimeZoneId], [IanaTimeZoneId], [WindowsTimeZoneId], [DailyResetOffset], [Sort], [RowVersion], [DisplayName], [DisplayNameHK], [DisplayNameEN] FROM dbo.TimeZoneMappings WHERE [IsActive] = 1 AND (@v_kw = N'%' OR [DisplayName] LIKE @v_kw ESCAPE N'\' OR [DisplayNameHK] LIKE @v_kw ESCAPE N'\' OR [DisplayNameEN] LIKE @v_kw ESCAPE N'\') ORDER BY [Sort], [RowVersion] OFFSET @v_offset ROWS FETCH NEXT @p_page_size ROWS ONLY; END; GO -- 7) 存储过程: 游标分页（亿级主推; 返回两个结果集: 1=数据, 2=has_more） CREATE OR ALTER PROCEDURE dbo.sp_timezone_cursor @p_keyword NVARCHAR(200) = NULL, @p_last_sort INT = NULL, @p_last_row_version BIGINT = NULL, @p_page_size INT = 20 AS BEGIN SET NOCOUNT ON; IF @p_page_size IS NULL OR @p_page_size 200 SET @p_page_size = 200; DECLARE @v_first_page BIT = 0; IF @p_last_sort IS NULL OR @p_last_row_version IS NULL SET @v_first_page = 1; DECLARE @v_kw NVARCHAR(220) = N'%'; IF @p_keyword IS NOT NULL AND @p_keyword <> N'' SET @v_kw = N'%' + REPLACE(REPLACE(REPLACE(@p_keyword, N'\', N'\\'), N'%', N'\%'), N'_', N'\_') + N'%'; IF @v_first_page = 1 BEGIN SELECT TOP (@p_page_size) [TimeZoneId], [IanaTimeZoneId], [WindowsTimeZoneId], [DailyResetOffset], [Sort], [RowVersion], [DisplayName], [DisplayNameHK], [DisplayNameEN] FROM dbo.TimeZoneMappings WHERE [IsActive] = 1 AND (@v_kw = N'%' OR [DisplayName] LIKE @v_kw ESCAPE N'\' OR [DisplayNameHK] LIKE @v_kw ESCAPE N'\' OR [DisplayNameEN] LIKE @v_kw ESCAPE N'\') ORDER BY [Sort], [RowVersion]; SELECT CASE WHEN EXISTS ( SELECT 1 FROM dbo.TimeZoneMappings WHERE [IsActive] = 1 AND (@v_kw = N'%' OR [DisplayName] LIKE @v_kw ESCAPE N'\' OR [DisplayNameHK] LIKE @v_kw ESCAPE N'\' OR [DisplayNameEN] LIKE @v_kw ESCAPE N'\') ) THEN 1 ELSE 0 END AS has_more; END ELSE BEGIN SELECT TOP (@p_page_size) [TimeZoneId], [IanaTimeZoneId], [WindowsTimeZoneId], [DailyResetOffset], [Sort], [RowVersion], [DisplayName], [DisplayNameHK], [DisplayNameEN] FROM dbo.TimeZoneMappings WHERE [IsActive] = 1 AND (@v_kw = N'%' OR [DisplayName] LIKE @v_kw ESCAPE N'\' OR [DisplayNameHK] LIKE @v_kw ESCAPE N'\' OR [DisplayNameEN] LIKE @v_kw ESCAPE N'\') AND ([Sort] > @p_last_sort OR ([Sort] = @p_last_sort AND [RowVersion] > @p_last_row_version)) ORDER BY [Sort], [RowVersion]; SELECT CASE WHEN EXISTS ( SELECT 1 FROM dbo.TimeZoneMappings WHERE [IsActive] = 1 AND (@v_kw = N'%' OR [DisplayName] LIKE @v_kw ESCAPE N'\' OR [DisplayNameHK] LIKE @v_kw ESCAPE N'\' OR [DisplayNameEN] LIKE @v_kw ESCAPE N'\') AND ([Sort] > @p_last_sort OR ([Sort] = @p_last_sort AND [RowVersion] > @p_last_row_version)) ) THEN 1 ELSE 0 END AS has_more; END END; GO -- 8) 存储过程: 全文检索游标分页（对应 MySQL 的 sp_timezone_search_ft） -- 无关键词时退化为普通游标分页; 有关键词用 CONTAINS 全文检索 CREATE OR ALTER PROCEDURE dbo.sp_timezone_search_ft @p_keyword NVARCHAR(200) = NULL, @p_last_sort INT = NULL, @p_last_row_version BIGINT = NULL, @p_page_size INT = 20 AS BEGIN SET NOCOUNT ON; IF @p_page_size IS NULL OR @p_page_size 200 SET @p_page_size = 200; DECLARE @v_first_page BIT = 0; IF @p_last_sort IS NULL OR @p_last_row_version IS NULL SET @v_first_page = 1; DECLARE @v_ft NVARCHAR(400); IF @p_keyword IS NOT NULL AND @p_keyword <> N'' SET @v_ft = N'"' + REPLACE(@p_keyword, N'"', N'""') + N'"'; IF @p_keyword IS NULL OR @p_keyword = N'' BEGIN -- 无关键词: 等同游标分页 IF @v_first_page = 1 BEGIN SELECT TOP (@p_page_size) [TimeZoneId], [IanaTimeZoneId], [WindowsTimeZoneId], [DailyResetOffset], [Sort], [RowVersion], [DisplayName], [DisplayNameHK], [DisplayNameEN] FROM dbo.TimeZoneMappings WHERE [IsActive] = 1 ORDER BY [Sort], [RowVersion]; SELECT CASE WHEN EXISTS (SELECT 1 FROM dbo.TimeZoneMappings WHERE [IsActive] = 1) THEN 1 ELSE 0 END AS has_more; END ELSE BEGIN SELECT TOP (@p_page_size) [TimeZoneId], [IanaTimeZoneId], [WindowsTimeZoneId], [DailyResetOffset], [Sort], [RowVersion], [DisplayName], [DisplayNameHK], [DisplayNameEN] FROM dbo.TimeZoneMappings WHERE [IsActive] = 1 AND ([Sort] > @p_last_sort OR ([Sort] = @p_last_sort AND [RowVersion] > @p_last_row_version)) ORDER BY [Sort], [RowVersion]; SELECT CASE WHEN EXISTS ( SELECT 1 FROM dbo.TimeZoneMappings WHERE [IsActive] = 1 AND ([Sort] > @p_last_sort OR ([Sort] = @p_last_sort AND [RowVersion] > @p_last_row_version)) ) THEN 1 ELSE 0 END AS has_more; END END ELSE BEGIN -- 全文检索 + 游标分页 IF @v_first_page = 1 BEGIN SELECT TOP (@p_page_size) [TimeZoneId], [IanaTimeZoneId], [WindowsTimeZoneId], [DailyResetOffset], [Sort], [RowVersion], [DisplayName], [DisplayNameHK], [DisplayNameEN] FROM dbo.TimeZoneMappings WHERE [IsActive] = 1 AND (CONTAINS([DisplayName], @v_ft) OR CONTAINS([DisplayNameHK], @v_ft) OR CONTAINS([DisplayNameEN], @v_ft)) ORDER BY [Sort], [RowVersion]; SELECT CASE WHEN EXISTS ( SELECT 1 FROM dbo.TimeZoneMappings WHERE [IsActive] = 1 AND (CONTAINS([DisplayName], @v_ft) OR CONTAINS([DisplayNameHK], @v_ft) OR CONTAINS([DisplayNameEN], @v_ft)) ) THEN 1 ELSE 0 END AS has_more; END ELSE BEGIN SELECT TOP (@p_page_size) [TimeZoneId], [IanaTimeZoneId], [WindowsTimeZoneId], [DailyResetOffset], [Sort], [RowVersion], [DisplayName], [DisplayNameHK], [DisplayNameEN] FROM dbo.TimeZoneMappings WHERE [IsActive] = 1 AND (CONTAINS([DisplayName], @v_ft) OR CONTAINS([DisplayNameHK], @v_ft) OR CONTAINS([DisplayNameEN], @v_ft)) AND ([Sort] > @p_last_sort OR ([Sort] = @p_last_sort AND [RowVersion] > @p_last_row_version)) ORDER BY [Sort], [RowVersion]; SELECT CASE WHEN EXISTS ( SELECT 1 FROM dbo.TimeZoneMappings WHERE [IsActive] = 1 AND (CONTAINS([DisplayName], @v_ft) OR CONTAINS([DisplayNameHK], @v_ft) OR CONTAINS([DisplayNameEN], @v_ft)) AND ([Sort] > @p_last_sort OR ([Sort] = @p_last_sort AND [RowVersion] > @p_last_row_version)) ) THEN 1 ELSE 0 END AS has_more; END END END; GO -- ============================================================ -- 验证: -- SELECT COUNT(*) FROM dbo.TimeZoneMappings; -- 432 -- SELECT COUNT(DISTINCT [IanaTimeZoneId]) FROM dbo.TimeZoneMappings; -- 432 -- -- 全文检索测试(等待全文填充完成后): -- SELECT [Sort], [DisplayName] FROM dbo.TimeZoneMappings -- WHERE CONTAINS([DisplayName], N'"中国"') OR CONTAINS([DisplayNameHK], N'"中国"') OR CONTAINS([DisplayNameEN], N'"中国"'); -- -- 全文填充进度: SELECT * FROM sys.dm_fts_index_population WHERE table_id = OBJECT_ID(N'dbo.TimeZoneMappings'); -- ============================================================ -- -- 全文检索测试(等待全文填充完成后): SELECT [Sort], [DisplayName] FROM dbo.TimeZoneMappings WHERE CONTAINS([DisplayName], N'"中国"') OR CONTAINS([DisplayNameHK], N'"上海"') OR CONTAINS([DisplayNameEN], N'"上海"'); -- -- 全文填充进度: SELECT * FROM sys.dm_fts_index_population WHERE table_id = OBJECT_ID(N'dbo.TimeZoneMappings'); ``` python 调用： ```python # encoding: utf-8 # 版权所有 2026 ©涂聚文有限公司™ ® # 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎 # 描述：sql server # Author : geovindu,Geovin Du 涂聚文. # IDE : PyCharm 2024.3.6 python 3.11 # os : windows 10 # database : mysql 9.0 sql server 2019, postgreSQL 17.0 Oracle 21c Neo4j # Datetime : 2026/9/12 12:51 # User : geovindu # Product : PyCharm # Project : Pysimple # File : sqlserverpaing.py # -*- coding: utf-8 -*- """ SQL Server 2019 版 TimeZoneMappings 分页调用示例（对应 MySQL/Oracle/PostgreSQL 版） 依赖: pip install pyodbc + 安装 Microsoft ODBC Driver 17/18 for SQL Server 用法: 先执行 timezone_sqlserver_2019.sql 建表+插数+建存储过程, 再改下方 CONN_STR 运行本脚本 """ import pyodbc # ============ 连接串（改成你自己的） ============ CONN_STR = ( "DRIVER={ODBC Driver 17 for SQL Server};" "SERVER=DESKTOP-0PH20LJ,1433;" "DATABASE=geovindu;" "UID=sa;" "PWD=geovindu;" "Encrypt=no;" # SQL Server 2019 本地默认可不加密; 云库需 Encrypt=yes "TrustServerCertificate=yes;" ) # =============================================== conn = pyodbc.connect(CONN_STR) conn.autocommit = False def page(keyword=None, page_number=1, page_size=20): """传统页码分页: 返回 (rows, total_count)""" with conn.cursor() as cur: cur.execute( "EXEC dbo.sp_timezone_page @p_keyword=?, @p_page_number=?, @p_page_size=?", (keyword, page_number, page_size), ) row = cur.fetchone() # 结果集1: total_count total = int(row[0]) if row else 0 cur.nextset() # 结果集2: 数据 rows = cur.fetchall() return rows, total def cursor_page(keyword=None, last_sort=None, last_row_version=None, page_size=20): """游标分页(亿级主推): 传上次页尾 (Sort, RowVersion), 首页传 None。 返回 (rows, has_more)""" with conn.cursor() as cur: cur.execute( "EXEC dbo.sp_timezone_cursor @p_keyword=?, @p_last_sort=?, @p_last_row_version=?, @p_page_size=?", (keyword, last_sort, last_row_version, page_size), ) rows = cur.fetchall() # 结果集1: 数据 cur.nextset() # 结果集2: has_more row = cur.fetchone() has_more = bool(row[0]) if row else False return rows, has_more def search_ft(keyword=None, last_sort=None, last_row_version=None, page_size=20): """全文检索游标分页(FULLTEXT CONTAINS): 无关键词退化为普通游标分页。 返回 (rows, has_more)""" with conn.cursor() as cur: cur.execute( "EXEC dbo.sp_timezone_search_ft @p_keyword=?, @p_last_sort=?, @p_last_row_version=?, @p_page_size=?", (keyword, last_sort, last_row_version, page_size), ) rows = cur.fetchall() cur.nextset() row = cur.fetchone() has_more = bool(row[0]) if row else False return rows, has_more def print_rows(rows, meta_first=False): # 列: [meta] TimeZoneId(0), IanaTimeZoneId(1), WindowsTimeZoneId(2), # DailyResetOffset(3), Sort(4), RowVersion(5), DisplayName(6), ... off = 1 if meta_first else 0 for r in rows: print(f" sort={r[off + 4]:>3} rv={r[off + 5]:>4} {r[off + 1]:<28} {r[off + 6]}") def demo_page(): print("=" * 70) print("[1] 传统页码分页: 第 2 页, 每页 5 条, 关键词='北'") print("=" * 70) rows, total = page(keyword="北", page_number=2, page_size=5) print(f"总命中: {total} 条") print_rows(rows) def demo_cursor(): print("=" * 70) print("[2] 游标分页(亿级): 关键词='上海', 每页 3 条, 从头翻到尾") print("=" * 70) keyword = "上海" last_sort, last_rv = None, None page_no = 1 while True: rows, has_more = cursor_page(keyword, last_sort, last_rv, 3) if not rows: break print(f"-- 第 {page_no} 页 --") print_rows(rows) last_sort, last_rv = rows[-1][4], rows[-1][5] if not has_more: break page_no += 1 print(f"翻页结束, 共 {page_no} 页") def demo_all(): print("=" * 70) print("[3] 游标分页: 无关键词, 每页 20 条, 从头翻到尾") print("=" * 70) last_sort, last_rv = None, None page_no = 1 total_rows = 0 while True: rows, has_more = cursor_page(None, last_sort, last_rv, 20) if not rows: break total_rows += len(rows) print(f"第 {page_no} 页: {len(rows)} 条, 尾游标 sort={rows[-1][4]} rv={rows[-1][5]}, has_more={has_more}") last_sort, last_rv = rows[-1][4], rows[-1][5] if not has_more: break page_no += 1 print(f"全部翻完: {total_rows} 条 / {page_no} 页 (应为 432 条)") def demo_ft(): print("=" * 70) print("[4] 全文检索游标分页(FULLTEXT): 关键词='上海', 每页 5 条") print("=" * 70) keyword = "上海" last_sort, last_rv = None, None page_no = 1 total_rows = 0 while True: rows, has_more = search_ft(keyword, last_sort, last_rv, 5) if not rows: break print(f"-- 第 {page_no} 页 (has_more={has_more}) --") print_rows(rows) total_rows += len(rows) last_sort, last_rv = rows[-1][4], rows[-1][5] if not has_more: break page_no += 1 print(f"全文检索共 {total_rows} 条 / {page_no} 页") if __name__ == "__main__": try: demo_page() demo_cursor() demo_all() demo_ft() finally: conn.close() ``` 输出： ![](https://developer.qcloudimg.com/http-save/audit-8397743/ff30e8b986450ac4a122b6bf3fa6a609.png)
2026-09-14
优秀的个人博客,低调大师

sql: Oracle 21c paging

文章纲要 一、建表与基础结构：创建 TimeZoneMappings 表，包含 IANA 时区、Windows 时区、显示名称、排序字段等，并设置主键、唯一约束与自增 RowVersion。 二、触发器与索引：实现 UpdatedAt 自动更新触发器，以及用于分页和全文检索的各类索引。 三、分页存储过程：提供传统页码分页（sp_timezone_page）与游标分页（sp_timezone_cursor）两种方案，适配不同数据量场景。 四、全文检索实现：基于 Oracle Text 创建中英文全文索引，并实现全文检索游标分页存储过程（sp_timezone_search_ft）。 五、Python 调用示例：使用 python-oracledb 连接 Oracle 21c，演示页码分页、游标分页与全文检索分页的完整调用方式。 六、运行结果：展示分页与全文检索的实际输出效果。 ```sql -- ============================================================ -- Oracle 21c: TimeZoneMappings 建表 + 432 条数据 + 分页存储过程 -- 共 432 条唯一 IANA 时区, Sort 1..432（与 MySQL 版数据完全一致） -- 执行工具: SQL*Plus / PL/SQL Developer / DBeaver 均可 -- ============================================================ -- 0) 清理旧表（首次执行可注释掉） BEGIN EXECUTE IMMEDIATE 'DROP TABLE TimeZoneMappings CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END; / -- 1) 建表（VARCHAR2 用 CHAR 语义，兼容中文；RowVersion 用 IDENTITY 自增） CREATE TABLE TimeZoneMappings ( TimeZoneId RAW(16) PRIMARY KEY, IanaTimeZoneId VARCHAR2(100 CHAR), WindowsTimeZoneId VARCHAR2(100 CHAR) NOT NULL, DailyResetOffset NUMBER(10) NOT NULL, Sort NUMBER(10) DEFAULT 0 NOT NULL, -- 修复：DEFAULT 必须在 NOT NULL 之前 DisplayName VARCHAR2(200 CHAR), DisplayNameHK VARCHAR2(200 CHAR), DisplayNameEN VARCHAR2(200 CHAR), IsActive NUMBER DEFAULT 1 NOT NULL, -- 修复：DEFAULT 必须在 NOT NULL 之前 CreatedAt TIMESTAMP DEFAULT SYSTIMESTAMP, UpdatedAt TIMESTAMP, RowVersion NUMBER(20) GENERATED BY DEFAULT AS IDENTITY NOT NULL, CONSTRAINT UQ_IanaTimeZoneId UNIQUE (IanaTimeZoneId) ); / select * from TimeZoneMappings; -- 2) UpdatedAt 自动更新触发器（Oracle 无 ON UPDATE） CREATE OR REPLACE TRIGGER trg_tz_updated BEFORE UPDATE ON TimeZoneMappings FOR EACH ROW BEGIN :NEW.UpdatedAt := SYSTIMESTAMP; END; / -- 3) 游标分页核心索引 CREATE INDEX IX_IsActive_Sort ON TimeZoneMappings (IsActive, Sort, RowVersion); / -- 5) 存储过程: 传统页码分页（管理后台/小表） CREATE OR REPLACE PROCEDURE sp_timezone_page( p_keyword IN VARCHAR2, p_page_number IN NUMBER, p_page_size IN NUMBER, p_total OUT NUMBER, p_cur OUT SYS_REFCURSOR ) AS v_offset NUMBER; v_kw VARCHAR2(220 CHAR) := '%'; BEGIN IF p_page_number IS NULL OR p_page_number 200 THEN p_page_size := 200; END IF; v_offset := (p_page_number - 1) * p_page_size; IF p_keyword IS NOT NULL AND p_keyword <> '' THEN v_kw := '%' || REPLACE(REPLACE(REPLACE(p_keyword, '\', '\\'), '%', '\%'), '_', '\_') || '%'; END IF; SELECT COUNT(*) INTO p_total FROM TimeZoneMappings WHERE IsActive = 1 AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\' OR DisplayNameHK LIKE v_kw ESCAPE '\' OR DisplayNameEN LIKE v_kw ESCAPE '\'); OPEN p_cur FOR SELECT TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset, Sort, RowVersion, DisplayName, DisplayNameHK, DisplayNameEN FROM TimeZoneMappings WHERE IsActive = 1 AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\' OR DisplayNameHK LIKE v_kw ESCAPE '\' OR DisplayNameEN LIKE v_kw ESCAPE '\') ORDER BY Sort, RowVersion OFFSET v_offset ROWS FETCH NEXT p_page_size ROWS ONLY; END; / -- 6) 存储过程: 游标分页（亿级主推，与 MySQL 版同语义） CREATE OR REPLACE PROCEDURE sp_timezone_cursor( p_keyword IN VARCHAR2, p_last_sort IN NUMBER, p_last_row_version IN NUMBER, p_page_size IN NUMBER, p_has_more OUT NUMBER, p_cur OUT SYS_REFCURSOR ) AS v_first_page NUMBER := 0; v_kw VARCHAR2(220 CHAR) := '%'; BEGIN IF p_page_size IS NULL OR p_page_size 200 THEN p_page_size := 200; END IF; IF p_last_sort IS NULL OR p_last_row_version IS NULL THEN v_first_page := 1; END IF; IF p_keyword IS NOT NULL AND p_keyword <> '' THEN v_kw := '%' || REPLACE(REPLACE(REPLACE(p_keyword, '\', '\\'), '%', '\%'), '_', '\_') || '%'; END IF; IF v_first_page = 1 THEN OPEN p_cur FOR SELECT TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset, Sort, RowVersion, DisplayName, DisplayNameHK, DisplayNameEN FROM TimeZoneMappings WHERE IsActive = 1 AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\' OR DisplayNameHK LIKE v_kw ESCAPE '\' OR DisplayNameEN LIKE v_kw ESCAPE '\') ORDER BY Sort, RowVersion FETCH FIRST p_page_size ROWS ONLY; ELSE OPEN p_cur FOR SELECT TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset, Sort, RowVersion, DisplayName, DisplayNameHK, DisplayNameEN FROM TimeZoneMappings WHERE IsActive = 1 AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\' OR DisplayNameHK LIKE v_kw ESCAPE '\' OR DisplayNameEN LIKE v_kw ESCAPE '\') AND (Sort > p_last_sort OR (Sort = p_last_sort AND RowVersion > p_last_row_version)) ORDER BY Sort, RowVersion FETCH FIRST p_page_size ROWS ONLY; END IF; -- 是否还有下一页（EXISTS 短路） IF v_first_page = 1 THEN SELECT CASE WHEN EXISTS ( SELECT 1 FROM TimeZoneMappings WHERE IsActive = 1 AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\' OR DisplayNameHK LIKE v_kw ESCAPE '\' OR DisplayNameEN LIKE v_kw ESCAPE '\') ) THEN 1 ELSE 0 END INTO p_has_more FROM DUAL; ELSE SELECT CASE WHEN EXISTS ( SELECT 1 FROM TimeZoneMappings WHERE IsActive = 1 AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\' OR DisplayNameHK LIKE v_kw ESCAPE '\' OR DisplayNameEN LIKE v_kw ESCAPE '\') AND (Sort > p_last_sort OR (Sort = p_last_sort AND RowVersion > p_last_row_version)) ) THEN 1 ELSE 0 END INTO p_has_more FROM DUAL; END IF; END; / -- ============================================================ -- 验证: SELECT COUNT(*) FROM TimeZoneMappings; -- 应返回 432 -- SELECT COUNT(DISTINCT IanaTimeZoneId) FROM TimeZoneMappings; -- 432 -- ============================================================ CREATE INDEX idx_tz_en_text ON TimeZoneMappings(DisplayNameEN) INDEXTYPE IS CTXSYS.CONTEXT; CREATE INDEX idx_tz_iana_text ON TimeZoneMappings(IanaTimeZoneId) INDEXTYPE IS CTXSYS.CONTEXT; -- 如果报错，执行以下 -- 查看是否存在 CTXSYS 用户 SELECT * FROM all_users WHERE username = 'CTXSYS'; -- 解锁 CTXSYS 用户（如果存在但被锁定） ALTER USER CTXSYS ACCOUNT UNLOCK; GRANT ctxapp TO C##GEOVINDU; GRANT EXECUTE ON ctxsys.ctx_ddl TO C##GEOVINDU; -- 如果执行 GRANT ctxapp TO YOUR_USER; 时提示角色不存在，说明你的 Oracle 21c 数据库在安装时没有勾选 Oracle Text 组件。此时需要以 SYS 身份运行初始化脚本来安装： -- 以 SYS 登录 -- @$ORACLE_HOME/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP NOLOCK; BEGIN -- 创建一个中文分词器偏好 ctxsys.ctx_ddl.create_preference('chinese_lexer', 'chinese_vgram_lexer'); END; / -- 使用指定的中文分词器创建全文索引 CREATE INDEX idx_tz_iana_text ON TimeZoneMappings(IanaTimeZoneId) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('lexer chinese_lexer'); CREATE OR REPLACE PROCEDURE sp_timezone_page( p_page_num IN NUMBER, -- 页码（从1开始） p_page_size IN NUMBER, -- 每页大小 p_order_by IN VARCHAR2, -- 排序字段，如 'Sort ASC, RowVersion ASC' p_total_count OUT NUMBER, -- 输出：总记录数 p_cursor OUT SYS_REFCURSOR -- 输出：当前页数据游标 ) AS BEGIN -- 1. 获取总记录数 SELECT COUNT(*) INTO p_total_count FROM TimeZoneMappings; -- 2. 获取当前页数据 (使用 12c+ 的 OFFSET ... FETCH 语法) OPEN p_cursor FOR SELECT TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset, Sort, DisplayName, DisplayNameHK, DisplayNameEN, IsActive, CreatedAt, UpdatedAt, RowVersion FROM TimeZoneMappings ORDER BY Sort ASC, RowVersion ASC -- 默认按 Sort 和 RowVersion 排序 OFFSET (p_page_num - 1) * p_page_size ROWS FETCH NEXT p_page_size ROWS ONLY; END; / CREATE OR REPLACE PROCEDURE sp_timezone_cursor( p_last_sort IN NUMBER, -- 上一页最后一条记录的 Sort 值 p_last_row_version IN NUMBER, -- 上一页最后一条记录的 RowVersion 值 p_page_size IN NUMBER, -- 每页大小 p_has_more OUT NUMBER, -- 输出：是否还有下一页 (1=有, 0=无) p_cursor OUT SYS_REFCURSOR -- 输出：当前页数据游标 ) AS -- 1. 变量声明必须放在 AS 和 BEGIN 之间 v_temp_cursor SYS_REFCURSOR; v_row_count NUMBER := 0; v_rec TimeZoneMappings%ROWTYPE; BEGIN -- 2. 打开临时游标，多取 1 条用于判断是否还有下一页 OPEN v_temp_cursor FOR SELECT TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset, Sort, DisplayName, DisplayNameHK, DisplayNameEN, IsActive, CreatedAt, UpdatedAt, RowVersion FROM TimeZoneMappings WHERE (p_last_sort IS NULL AND p_last_row_version IS NULL) -- 首页查询 OR (Sort > p_last_sort) -- 展开的元组比较 OR (Sort = p_last_sort AND RowVersion > p_last_row_version) ORDER BY Sort ASC, RowVersion ASC FETCH NEXT p_page_size + 1 ROWS ONLY; -- 关键：多取一条 -- 3. 循环临时游标，计算实际返回的行数 LOOP FETCH v_temp_cursor INTO v_rec; EXIT WHEN v_temp_cursor%NOTFOUND; v_row_count := v_row_count + 1; END LOOP; CLOSE v_temp_cursor; -- 4. 如果取出的行数 > 页面大小，说明还有下一页 p_has_more := CASE WHEN v_row_count > p_page_size THEN 1 ELSE 0 END; -- 5. 打开正式游标返回给应用层（只取刚好一页的数据） OPEN p_cursor FOR SELECT TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset, Sort, DisplayName, DisplayNameHK, DisplayNameEN, IsActive, CreatedAt, UpdatedAt, RowVersion FROM TimeZoneMappings WHERE (p_last_sort IS NULL AND p_last_row_version IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_version) ORDER BY Sort ASC, RowVersion ASC FETCH NEXT p_page_size ROWS ONLY; END; / CREATE OR REPLACE PROCEDURE sp_timezone_search_ft( p_search_expr IN VARCHAR2, p_last_sort IN NUMBER, p_last_row_ver IN NUMBER, p_page_size IN NUMBER, p_has_more OUT NUMBER, p_cursor OUT SYS_REFCURSOR ) AS v_row_count NUMBER := 0; v_rec TimeZoneMappings%ROWTYPE; v_temp_cur SYS_REFCURSOR; BEGIN -- 1. 打开临时游标，多取 1 条用于判断是否还有下一页 OPEN v_temp_cur FOR SELECT * FROM ( SELECT * FROM ( -- 英文索引 1 SELECT * FROM TimeZoneMappings WHERE CONTAINS(DisplayNameEN, p_search_expr, 1) > 0 AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver)) UNION -- IANA 索引 2 SELECT * FROM TimeZoneMappings WHERE CONTAINS(IanaTimeZoneId, p_search_expr, 2) > 0 AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver)) UNION -- 简体中文索引 3 SELECT * FROM TimeZoneMappings WHERE CONTAINS(DisplayName, p_search_expr, 3) > 0 AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver)) UNION -- 繁体中文索引 4 SELECT * FROM TimeZoneMappings WHERE CONTAINS(DisplayNameHK, p_search_expr, 4) > 0 AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver)) ) ORDER BY Sort ASC, RowVersion ASC ) FETCH NEXT p_page_size + 1 ROWS ONLY; -- 2. 循环临时游标，计算实际返回的行数 LOOP FETCH v_temp_cur INTO v_rec; EXIT WHEN v_temp_cur%NOTFOUND; v_row_count := v_row_count + 1; END LOOP; CLOSE v_temp_cur; -- 3. 判断是否还有下一页 p_has_more := CASE WHEN v_row_count > p_page_size THEN 1 ELSE 0 END; -- 4. 打开正式游标返回给应用层 OPEN p_cursor FOR SELECT * FROM ( SELECT * FROM ( SELECT * FROM TimeZoneMappings WHERE CONTAINS(DisplayNameEN, p_search_expr, 1) > 0 AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver)) UNION SELECT * FROM TimeZoneMappings WHERE CONTAINS(IanaTimeZoneId, p_search_expr, 2) > 0 AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver)) UNION SELECT * FROM TimeZoneMappings WHERE CONTAINS(DisplayName, p_search_expr, 3) > 0 AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver)) UNION SELECT * FROM TimeZoneMappings WHERE CONTAINS(DisplayNameHK, p_search_expr, 4) > 0 AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver)) ) ORDER BY Sort ASC, RowVersion ASC ) FETCH NEXT p_page_size ROWS ONLY; END; / -- 将现有的 RowVersion 全部替换为基于 Sort 排序的唯一递增数字 MERGE INTO TimeZoneMappings t USING ( SELECT TimeZoneId, ROW_NUMBER() OVER (ORDER BY Sort ASC) AS new_rv FROM TimeZoneMappings ) src ON (t.TimeZoneId = src.TimeZoneId) WHEN MATCHED THEN UPDATE SET t.RowVersion = src.new_rv; SELECT Sort, RowVersion, IanaTimeZoneId, DisplayNameEN FROM TimeZoneMappings ORDER BY Sort ASC; -- 如果索引不存在，这两句会报错，忽略即可 DROP INDEX idx_tz_en_text; DROP INDEX idx_tz_iana_text; -- 为英文名称创建全文索引 CREATE INDEX idx_tz_en_text ON TimeZoneMappings(DisplayNameEN) INDEXTYPE IS CTXSYS.CONTEXT; -- 为 IANA 时区ID创建全文索引 CREATE INDEX idx_tz_iana_text ON TimeZoneMappings(IanaTimeZoneId) INDEXTYPE IS CTXSYS.CONTEXT; -- 同步两个索引 EXEC CTX_DDL.SYNC_INDEX('idx_tz_en_text'); EXEC CTX_DDL.SYNC_INDEX('idx_tz_iana_text'); -- 测试是否能搜到 Pacific SELECT IanaTimeZoneId, DisplayNameEN FROM TimeZoneMappings WHERE CONTAINS(IanaTimeZoneId, 'Pacific', 1) > 0; DROP INDEX idx_tz_en_text; DROP INDEX idx_tz_iana_text; BEGIN -- 如果偏好已存在，会报错，这里直接忽略即可 CTXSYS.CTX_DDL.CREATE_PREFERENCE('chinese_lexer', 'chinese_vgram_lexer'); EXCEPTION WHEN OTHERS THEN NULL; END; / -- 为 DisplayName 创建中文全文索引 CREATE INDEX idx_tz_en_text ON TimeZoneMappings(DisplayNameEN) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('lexer chinese_lexer'); -- 为 IanaTimeZoneId 创建中文全文索引（虽然主要是英文，但加上中文分词器也能兼容） CREATE INDEX idx_tz_iana_text ON TimeZoneMappings(IanaTimeZoneId) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('lexer chinese_lexer'); -- 手动同步索引 EXEC CTX_DDL.SYNC_INDEX('idx_tz_en_text'); EXEC CTX_DDL.SYNC_INDEX('idx_tz_iana_text'); -- 1. 删除可能存在的旧中文索引（如果报错不存在，忽略即可） DROP INDEX idx_tz_cn_text; DROP INDEX idx_tz_hk_text; -- 2. 创建中文分词器偏好（防止重名报错） BEGIN CTXSYS.CTX_DDL.DROP_PREFERENCE('my_chinese_vgram'); EXCEPTION WHEN OTHERS THEN NULL; END; / BEGIN CTXSYS.CTX_DDL.CREATE_PREFERENCE('my_chinese_vgram', 'chinese_vgram_lexer'); END; / -- 3. 创建中文全文索引 CREATE INDEX idx_tz_cn_text ON TimeZoneMappings(DisplayName) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('lexer my_chinese_vgram'); CREATE INDEX idx_tz_hk_text ON TimeZoneMappings(DisplayNameHK) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('lexer my_chinese_vgram'); -- 4. 【关键】手动同步索引 EXEC CTXSYS.CTX_DDL.SYNC_INDEX('idx_tz_cn_text'); EXEC CTXSYS.CTX_DDL.SYNC_INDEX('idx_tz_hk_text'); SELECT token_text FROM DR$idx_tz_cn_text$I WHERE token_text LIKE '%北%' OR token_text LIKE '%岛%'; SELECT token_text FROM DR$idx_tz_hk_text$I WHERE token_text LIKE '%島%' OR token_text LIKE '%北%'; SELECT IanaTimeZoneId, DisplayName FROM TimeZoneMappings WHERE CONTAINS(IanaTimeZoneId, '留尼汪', 1) > 0; -- 测试中文搜索（注意：chinese_vgram_lexer 支持部分匹配，搜“标准”或“中国”应该都能命中） SELECT IanaTimeZoneId, DisplayName FROM TimeZoneMappings WHERE CONTAINS(DisplayName, '标准', 1) > 0; SELECT DisplayName, LENGTH(DisplayName), LENGTHB(DisplayName) FROM TimeZoneMappings WHERE DisplayName LIKE '%岛%'; SELECT DisplayNameHK, LENGTH(DisplayNameHK), LENGTHB(DisplayNameHK) FROM TimeZoneMappings WHERE DisplayNameHK LIKE '%島%'; DROP INDEX idx_tz_en_text; DROP INDEX idx_tz_iana_text; BEGIN CTXSYS.CTX_DDL.DROP_PREFERENCE('my_chinese_vgram'); EXCEPTION WHEN OTHERS THEN NULL; -- 如果不存在则忽略 END; / BEGIN CTXSYS.CTX_DDL.CREATE_PREFERENCE('my_chinese_vgram', 'chinese_vgram_lexer'); END; / CREATE INDEX idx_tz_en_text ON TimeZoneMappings(DisplayNameEN) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('lexer my_chinese_vgram'); CREATE INDEX idx_tz_iana_text ON TimeZoneMappings(IanaTimeZoneId) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('lexer my_chinese_vgram'); EXEC CTXSYS.CTX_DDL.SYNC_INDEX('idx_tz_en_text'); EXEC CTXSYS.CTX_DDL.SYNC_INDEX('idx_tz_iana_text'); SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET'; -- 补救现有数据的 RowVersion (使用 Sort 值作为初始版本号，保证唯一递增) CREATE OR REPLACE TRIGGER trg_tz_rowversion BEFORE INSERT ON TimeZoneMappings FOR EACH ROW BEGIN -- 仅在未提供 RowVersion 时，自动分配下一个序列值 -- 假设你的表名是 TimeZoneMappings，Oracle 自动生成的序列通常命名为 ISEQ$$_XXXXX -- 如果不确定序列名，可以使用以下通用方式： IF :NEW.RowVersion IS NULL THEN -- 注意：如果你的建表语句使用了 IDENTITY，这里可以直接不写触发器， -- 或者使用如下方式安全赋值： SELECT TimeZoneMappings_SEQ.NEXTVAL INTO :NEW.RowVersion FROM DUAL; -- (注：如果不知道自动生成的序列名，建议在建表时显式指定序列) END IF; END; / -- 1. 删除旧索引 DROP INDEX idx_tz_en_text; DROP INDEX idx_tz_iana_text; -- 2. 重新创建索引 (使用中文分词器兼容英文) BEGIN ctxsys.ctx_ddl.create_preference('my_lexer', 'chinese_vgram_lexer'); EXCEPTION WHEN OTHERS THEN NULL; -- 偏好已存在则忽略 END; / CREATE INDEX idx_tz_en_text ON TimeZoneMappings(DisplayNameEN) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('lexer my_lexer'); CREATE INDEX idx_tz_iana_text ON TimeZoneMappings(IanaTimeZoneId) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('lexer my_lexer'); -- 3. 【关键】手动同步索引，否则刚插入的数据搜不到 EXEC CTX_DDL.SYNC_INDEX('idx_tz_en_text'); EXEC CTX_DDL.SYNC_INDEX('idx_tz_iana_text'); DECLARE v_cursor SYS_REFCURSOR; v_total_count NUMBER; v_rec TimeZoneMappings%ROWTYPE; BEGIN sp_timezone_page( p_page_num => 1, p_page_size => 5, p_order_by => 'Sort ASC', p_total_count => v_total_count, p_cursor => v_cursor ); DBMS_OUTPUT.PUT_LINE('=== sp_timezone_page 测试结果 ==='); DBMS_OUTPUT.PUT_LINE('总记录数: ' || v_total_count); LOOP FETCH v_cursor INTO v_rec; EXIT WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Sort: ' || v_rec.Sort || ', RV: ' || v_rec.RowVersion || ', IANA: ' || v_rec.IanaTimeZoneId); END LOOP; CLOSE v_cursor; END; / DECLARE v_cursor SYS_REFCURSOR; v_has_more NUMBER; v_rec TimeZoneMappings%ROWTYPE; BEGIN sp_timezone_cursor( p_last_sort => NULL, p_last_row_version => NULL, p_page_size => 5, p_has_more => v_has_more, p_cursor => v_cursor ); DBMS_OUTPUT.PUT_LINE('=== sp_timezone_cursor 测试结果 ==='); DBMS_OUTPUT.PUT_LINE('是否还有下一页 (1=是, 0=否): ' || v_has_more); LOOP FETCH v_cursor INTO v_rec; EXIT WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Sort: ' || v_rec.Sort || ', RV: ' || v_rec.RowVersion || ', IANA: ' || v_rec.IanaTimeZoneId); END LOOP; CLOSE v_cursor; END; / SET SERVEROUTPUT ON; DECLARE v_cursor SYS_REFCURSOR; v_has_more NUMBER; v_rec TimeZoneMappings%ROWTYPE; BEGIN sp_timezone_search_ft( p_search_expr => '北', p_last_sort => NULL, p_last_row_ver => NULL, p_page_size => 5, p_has_more => v_has_more, p_cursor => v_cursor ); DBMS_OUTPUT.PUT_LINE('=== 测试 1：搜索简体中文 北 ==='); DBMS_OUTPUT.PUT_LINE('是否还有下一页 (1=是, 0=否): ' || v_has_more); LOOP FETCH v_cursor INTO v_rec; EXIT WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Sort: ' || v_rec.Sort || ', RV: ' || v_rec.RowVersion || ', IANA: ' || v_rec.IanaTimeZoneId || ', CN: ' || v_rec.DisplayName); END LOOP; CLOSE v_cursor; END; / DECLARE v_cursor SYS_REFCURSOR; v_has_more NUMBER; v_rec TimeZoneMappings%ROWTYPE; BEGIN sp_timezone_search_ft( p_search_expr => '島', p_last_sort => NULL, p_last_row_ver => NULL, p_page_size => 5, p_has_more => v_has_more, p_cursor => v_cursor ); DBMS_OUTPUT.PUT_LINE('=== 测试 2：搜索繁体中文 島 ==='); DBMS_OUTPUT.PUT_LINE('是否还有下一页 (1=是, 0=否): ' || v_has_more); LOOP FETCH v_cursor INTO v_rec; EXIT WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Sort: ' || v_rec.Sort || ', RV: ' || v_rec.RowVersion || ', IANA: ' || v_rec.IanaTimeZoneId || ', HK: ' || v_rec.DisplayNameHK); END LOOP; CLOSE v_cursor; END; / DECLARE v_cursor SYS_REFCURSOR; v_has_more NUMBER; v_rec TimeZoneMappings%ROWTYPE; BEGIN sp_timezone_search_ft( p_search_expr => 'Pacific', p_last_sort => NULL, p_last_row_ver => NULL, p_page_size => 5, p_has_more => v_has_more, p_cursor => v_cursor ); DBMS_OUTPUT.PUT_LINE('=== 测试 3：搜索英文 Pacific ==='); DBMS_OUTPUT.PUT_LINE('是否还有下一页 (1=是, 0=否): ' || v_has_more); LOOP FETCH v_cursor INTO v_rec; EXIT WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Sort: ' || v_rec.Sort || ', RV: ' || v_rec.RowVersion || ', IANA: ' || v_rec.IanaTimeZoneId || ', EN: ' || v_rec.DisplayNameEN); END LOOP; CLOSE v_cursor; END; / ``` python调用： ```javascript 文章纲要 一、建表与基础结构：创建 TimeZoneMappings 表，包含 IANA 时区、Windows 时区、显示名称、排序字段等，并设置主键、唯一约束与自增 RowVersion。 二、触发器与索引：实现 UpdatedAt 自动更新触发器，以及用于分页和全文检索的各类索引。 三、分页存储过程：提供传统页码分页（sp_timezone_page）与游标分页（sp_timezone_cursor）两种方案，适配不同数据量场景。 四、全文检索实现：基于 Oracle Text 创建中英文全文索引，并实现全文检索游标分页存储过程（sp_timezone_search_ft）。 五、Python 调用示例：使用 python-oracledb 连接 Oracle 21c，演示页码分页、游标分页与全文检索分页的完整调用方式。 六、运行结果：展示分页与全文检索的实际输出效果。 ———————————————— 版权声明：本文为CSDN博主「geovindu」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/geovindu/article/details/165090068 ``` 输出： ![](https://developer.qcloudimg.com/http-save/audit-8397743/f690890e4dde58ae846ec44160d76835.png)
2026-09-13

资源下载

更多资源
Mario

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

时间: 2026-10-18 大小: 211.28KB 下载: 56次
腾讯云软件源

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

时间: 2026-08-21 大小: 1MB 下载: 10次
Nacos

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

时间: 2026-09-24 大小: 189MB 下载: 9次
Rocky Linux

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

时间: 2026-08-21 大小: 1.42GB 下载: 6次
热门标签
DeepSeek Jdk25 OpenAi HarmonyOS6 Nacos3 SpringBoot4 SpringCloud Docker Kubernetes Service Mesh Redis Gemini3 Rocky

欢迎您来访！

登录后，您将获得更多功能！
热门文章
联系我们

有任何问题或合作意向欢迎联系我们

Email: 99873273@qq.com

QQ: 99873273

用户登录
用户注册