sql:mysq 9.0 paging

 

-- ============================================================
-- 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;
 
 
 
 
 
DELIMITER //
DROP TRIGGER IF EXISTS tr_TimeZoneMappings_BeforeInsert;
CREATE TRIGGER tr_TimeZoneMappings_BeforeInsert
BEFORE INSERT ON TimeZoneMappings
FOR EACH ROW
BEGIN
    IF NEW.TimeZoneId IS NULL THEN
        SET NEW.TimeZoneId = UUID_TO_BIN(UUID(), TRUE);
    END IF;
END //
DELIMITER ;
 
SHOW TRIGGERS LIKE 'tr_TimeZoneMappings_BeforeInsert';
 
 
SELECT VERSION();
SELECT HEX(UUID_TO_BIN(UUID(), TRUE)), BIN_TO_UUID(UUID_TO_BIN(UUID(), TRUE), TRUE);
ALTER TABLE TimeZoneMappings MODIFY COLUMN TimeZoneId VARBINARY(16) NOT NULL PRIMARY KEY;
 
ALTER TABLE TimeZoneMappings MODIFY COLUMN TimeZoneId VARBINARY(16) NOT NULL;
 
TRUNCATE TABLE TimeZoneMappings;
 
-- ============================================================
-- TimeZoneMappings 全量时区数据（去重整理版）
-- 共 432 条唯一 IANA 时区, Sort 已重新编号 1..432
-- MySQL 9.0 / InnoDB / utf8mb4
-- 前置条件: TimeZoneId 必须为 VARBINARY(16)
--   ALTER TABLE TimeZoneMappings MODIFY COLUMN TimeZoneId VARBINARY(16) NOT NULL;
-- 执行前清空旧数据:
TRUNCATE TABLE TimeZoneMappings;
-- ============================================================
 
INSERT INTO TimeZoneMappings
(TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset, Sort, DisplayName, DisplayNameHK, DisplayNameEN, IsActive)
VALUES
(UUID_TO_BIN(UUID(), TRUE), 'Etc/GMT+12', 'Dateline Standard Time', -720, 1, '国际日期变更线西', '國際日期變更線西', 'Dateline Standard Time', 1),
(UUID_TO_BIN(UUID(), TRUE), 'Pacific/Enderbury', 'Line Islands Standard Time', 840, 431, '恩德伯里岛', '恩德伯里島', 'Enderbury', 1),
(UUID_TO_BIN(UUID(), TRUE), 'Pacific/Kiritimati', 'Line Islands Standard Time', 840, 432, '圣诞岛(基里巴斯)', '聖誕島(基里巴斯)', 'Kiritimati', 1)
;
 
 
 
TRUNCATE TABLE TimeZoneMappings;
 
-- ============================================================
-- TimeZoneMappings 亿级分页查询优化方案（MySQL 9.0 / InnoDB / utf8mb4）
-- ============================================================
-- 优化要点：
--   1) 弃用深分页 OFFSET，改用「游标分页」(Keyset Pagination)
--      深 OFFSET 每翻一页都要扫描并丢弃前 N 行；游标分页只扫一页(几十行)，
--      配合 (IsActive, Sort, RowVersion) 复合索引，复杂度 O(页大小)。
--   2) 中文模糊搜索必须用 ngram 全文索引
--      LIKE '%词%' 前导通配符无法走索引；默认 parser 的 FULLTEXT 对中文
--      不分词，必须 WITH PARSER ngram。
--   3) 精确 COUNT(*) 亿级下每次全表扫描，改用「has_more」模式：
--      只判断是否还有下一页，不返回总条数（高频接口推荐）。
--   4) 全部逻辑封装进存储过程 + 绑定参数，避免 SQL 拼接与注入。
--
-- 表结构前置要求（保持你的原表）：
--   TimeZoneId VARBINARY(16) PRIMARY KEY
--   KEY IX_IsActive_Sort (IsActive, Sort, RowVersion)   -- 游标分页核心索引
-- ============================================================
 
-- ============================================================
-- 0) 核心索引检查（你的建表语句已含 IX_IsActive_Sort，无需重复创建）
--    若表被重建过缺少该索引，执行：
-- ALTER TABLE TimeZoneMappings
--   ADD KEY IX_IsActive_Sort (IsActive, Sort, RowVersion);
-- ============================================================
 
-- ============================================================
-- 1) 全文索引改造（中文必须 ngram）
-- ============================================================
-- 1.1 删除旧的默认 parser 全文索引（对中文无效，名称按你原脚本）
SET @drop_ft = (
  SELECT IF(COUNT(*) > 0,
            'ALTER TABLE TimeZoneMappings DROP INDEX ft_idx_display',
            'SELECT 1')
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name   = 'TimeZoneMappings'
    AND index_name   = 'ft_idx_display'
);
PREPARE s_drop_ft FROM @drop_ft;
EXECUTE s_drop_ft;
DEALLOCATE PREPARE s_drop_ft;
 
-- 1.2 重建 ngram 全文索引（按 2 字切分，中文/英文均适用）
ALTER TABLE TimeZoneMappings
  ADD FULLTEXT INDEX ft_idx_display_ngram
    (DisplayName, DisplayNameHK, DisplayNameEN) WITH PARSER ngram;
 
-- 说明：
--   * ngram_token_size 默认 2，可命中 >=2 字的词；
--   * 如需支持单字搜索，在 my.ini 设 ngram_token_size=1 后重启(只读变量)；
--   * 中文 3 字以上词会被切为多个 2 字 token，布尔模式用 +token 组合即可。
 
-- ============================================================
-- 2) 传统页码分页存储过程（管理后台/小表用，亿级高频不推荐）
--    返回：1 个数据结果集 + OUT p_total（精确总数）
-- ============================================================
DROP PROCEDURE IF EXISTS sp_timezone_page;
DROP PROCEDURE IF EXISTS sp_timezone_cursor;
DROP PROCEDURE IF EXISTS sp_timezone_search_ft;
 
DELIMITER //
 
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 '\\');
 
    -- 2) 当前页数据
    SELECT BIN_TO_UUID(TimeZoneId, TRUE) 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 '\\')
    ORDER BY Sort, RowVersion
    LIMIT v_offset, p_page_size;
END //
 
-- ============================================================
-- 3) 游标分页存储过程（亿级主推）
--    入参：上一页最后一条的 (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, TRUE) 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 '\\')
        ORDER BY Sort, RowVersion
        LIMIT p_page_size;
    ELSE
        SELECT BIN_TO_UUID(TimeZoneId, TRUE) 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 '\\')
          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 '\\')
            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 '\\')
              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 //
 
-- ============================================================
-- 4) ngram 全文检索 + 游标分页（中文搜索推荐）
--    入参 p_ft_expr：布尔模式表达式，由应用层构造，如 '+中国' / '+北京'
--    注意：p_ft_expr 会被拼进 SQL，应用层必须白名单校验，勿直接透传用户输入
--    首屏 p_last_sort / p_last_row_version 传 NULL
-- ============================================================
DROP PROCEDURE IF EXISTS sp_timezone_search_ft;
 
DELIMITER //
 
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(2000);
    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);
    SET v_expr = REPLACE(v_expr, '''', '''''');
 
    -- 1) 当前页数据
    SET v_sql = CONCAT(
        'SELECT BIN_TO_UUID(TimeZoneId, TRUE) AS TimeZoneId,',
        ' IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset,',
        ' Sort, RowVersion, DisplayName, DisplayNameHK, DisplayNameEN',
        ' FROM TimeZoneMappings',
        ' WHERE IsActive = 1',
        ' AND MATCH(DisplayName, DisplayNameHK, DisplayNameEN) 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) 是否还有下一页（EXECUTE 不支持 INTO，改用 SELECT ... INTO @变量）
    SET @v_has_more = 0;
    SET v_sql = CONCAT(
        'SELECT EXISTS(',
        ' SELECT 1 FROM TimeZoneMappings',
        ' WHERE IsActive = 1',
        ' AND MATCH(DisplayName, DisplayNameHK, DisplayNameEN) 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);  -- 中文全文
 

　　

# 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
三种分页方式：
  1. sp_timezone_page      传统页码分页（管理后台 / 小表）
  2. sp_timezone_cursor    游标分页，亿级主推（LIKE 模糊，防深分页）
  3. 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()