一、建表脚本：TimeZoneMappings 表结构设计，含 UUID 主键、时区映射字段、游标键 RowVersion 及索引规划。

二、全文索引改造：中文 ngram 索引与英文默认索引的双索引方案，解决中英文混合检索问题。

三、存储过程定义：传统页码分页、游标分页、全文检索加游标三种分页存储过程。

四、Python 调用示例：PyMySQL 封装三种分页方式的调用代码与翻页演示。

-- ============================================================
-- 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 &lt; 1, 1, p_page_number);
SET p_page_size   = IF(p_page_size   IS NULL OR p_page_size   &lt; 1, 20,
                       IF(p_page_size &gt; 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 &lt;&gt; '' 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 &lt; 1, 20,
                     IF(p_page_size &gt; 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 &lt;&gt; '' 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 &gt; p_last_sort
           OR (Sort = p_last_sort AND RowVersion &gt; 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 &gt; p_last_sort
               OR (Sort = p_last_sort AND RowVersion &gt; 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 &lt; 1, 20,
                     IF(p_page_size &gt; 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 &gt; ', p_last_sort,
              ' OR (Sort = ', p_last_sort,
              ' AND RowVersion &gt; ', 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 &gt; ', p_last_sort,
              ' OR (Sort = ', p_last_sort,
              ' AND RowVersion &gt; ', 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);  -- 中文全文

# 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()

sql: mysql 9.0 paging

建表脚本：TimeZoneMappings 表结构设计，含 UUID 主键、时区映射字段、游标键 RowVersion 及索引规划。

人工智能

本文介绍MySQL 9.0时区映射表亿级数据分页优化方案，涵盖双全文索引（中文ngram+英文默认）解决中英混合检索，三种存储过程实现传统页码、游标及全文检索分页，并附PyMySQL调用示例，重点推荐游标分页以规避深分页性能瓶颈。

Windows

Python

MySQL

腾讯云数据库特惠，新用户首单0.5折起


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

sql: mysql 9.0 paging-腾讯云开发者社区-腾讯云

sql: mysql 9.0 paging

sql: mysql 9.0 paging

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐