Python中如何解决MySQL后端分页问题

在Python里用MySQL做分页，直接用LIMIT和OFFSET就行，这是最直接的方法。不过数据量大了性能会变差，特别是OFFSET值很大的时候。给你个完整的例子：

import mysql.connector
from mysql.connector import Error

def get_paginated_data(page_number=1, page_size=10):
    """获取分页数据的基础实现"""
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='your_database',
            user='your_user',
            password='your_password'
        )
        
        cursor = connection.cursor(dictionary=True)
        
        # 计算偏移量
        offset = (page_number - 1) * page_size
        
        # 基础分页查询
        query = """
        SELECT id, name, email, created_at 
        FROM users 
        ORDER BY id 
        LIMIT %s OFFSET %s
        """
        
        cursor.execute(query, (page_size, offset))
        results = cursor.fetchall()
        
        # 获取总记录数（用于计算总页数）
        count_query = "SELECT COUNT(*) as total FROM users"
        cursor.execute(count_query)
        total_records = cursor.fetchone()['total']
        total_pages = (total_records + page_size - 1) // page_size
        
        return {
            'data': results,
            'current_page': page_number,
            'page_size': page_size,
            'total_records': total_records,
            'total_pages': total_pages
        }
        
    except Error as e:
        print(f"数据库错误: {e}")
        return None
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

# 使用示例
if __name__ == "__main__":
    # 获取第2页，每页10条
    page_data = get_paginated_data(page_number=2, page_size=10)
    
    if page_data:
        print(f"第{page_data['current_page']}页数据:")
        for row in page_data['data']:
            print(row)
        print(f"总记录数: {page_data['total_records']}")
        print(f"总页数: {page_data['total_pages']}")

如果数据量特别大，考虑用基于游标的分页，用WHERE子句代替OFFSET：

def get_paginated_data_cursor(last_id=None, page_size=10):
    """基于游标的分页（性能更好）"""
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='your_database',
            user='your_user',
            password='your_password'
        )
        
        cursor = connection.cursor(dictionary=True)
        
        if last_id:
            # 不是第一页，使用WHERE过滤
            query = """
            SELECT id, name, email, created_at 
            FROM users 
            WHERE id > %s 
            ORDER BY id 
            LIMIT %s
            """
            cursor.execute(query, (last_id, page_size))
        else:
            # 第一页
            query = """
            SELECT id, name, email, created_at 
            FROM users 
            ORDER BY id 
            LIMIT %s
            """
            cursor.execute(query, (page_size,))
        
        results = cursor.fetchall()
        
        # 获取下一页的起始ID
        next_last_id = results[-1]['id'] if results else None
        
        return {
            'data': results,
            'next_last_id': next_last_id,
            'has_more': len(results) == page_size
        }
        
    except Error as e:
        print(f"数据库错误: {e}")
        return None
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

简单说就是小数据用LIMIT/OFFSET，大数据用游标分页。