MySQL分页的2种常用方式，附代码

以下是MySQL分页的2种常用方式，本文以python为例，为大家演示：

1、使用LIMIT和OFFSET进行分页

我们首先连接到MySQL数据库，并创建一个游标对象。然后，我们执行一个COUNT查询来获取结果集的总行数。我们还指定了每页显示的行数(page_size)，并使用它计算了总页数(total_pages)。

接下来，我们使用LIMIT和OFFSET子句来进行分页查询。LIMIT子句指定每页返回的最大行数，OFFSET子句指定从哪一行开始返回结果。我们使用一个循环来遍历所有页码，并将每页的结果集输出到控制台。

import mysql.connector

# 连接到数据库
cnx = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='test')

# 创建游标对象
cursor = cnx.cursor()

# 执行查询，获取结果集总行数
query = 'SELECT COUNT(*) FROM my_table'
cursor.execute(query)
total_rows = cursor.fetchone()[0]

# 每页显示的行数
page_size = 10

# 计算总页数并输出
total_pages = (total_rows + page_size - 1) // page_size
print('Total pages:', total_pages)

# 分页查询并输出结果
for page in range(total_pages):
    query = 'SELECT * FROM my_table LIMIT %s OFFSET %s'
    cursor.execute(query, (page_size, page_size * page))
    rows = cursor.fetchall()
    for row in rows:
        print(row)

# 关闭游标和连接
cursor.close()
cnx.close()

请注意，在确保分页查询时，需要根据实际情况调整LIMIT和OFFSET的值，并进行适当的错误处理。

2、使用ROW_NUMBER()进行分页

使用了MySQL窗口函数ROW_NUMBER()来进行分页查询。我们首先执行一个COUNT查询来获取结果集的总行数，并计算出总页数(total_pages)。然后，我们使用一个循环遍历所有页码，并使用ROW_NUMBER()函数对结果集进行编号。

在每次查询中，我们首先指定一个OFFSET参数，计算出要跳过的行数。然后，我们使用一个子查询来将结果集中的每一行都分配一个唯一的行号(row_num)。最后，我们使用WHERE子句和LIMIT子句来返回指定页码的数据，并将其输出到控制台。

import mysql.connector

# 连接到数据库
cnx = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='test')

# 创建游标对象
cursor = cnx.cursor()

# 每页显示的行数
page_size = 10

# 计算总页数并输出
query = 'SELECT COUNT(*) FROM my_table'
cursor.execute(query)
total_rows = cursor.fetchone()[0]
total_pages = (total_rows + page_size - 1) // page_size
print('Total pages:', total_pages)

# 分页查询并输出结果
for page in range(total_pages):
    offset = page_size * page
    query = '''
        SELECT * FROM (
            SELECT *, ROW_NUMBER() OVER () as row_num FROM my_table
        ) t WHERE t.row_num > %s LIMIT %s
    '''
    cursor.execute(query, (offset, page_size))
    rows = cursor.fetchall()
    for row in rows:
        print(row)

# 关闭游标和连接
cursor.close()
cnx.close()

注意，在使用ROW_NUMBER()函数进行分页时，需要注意以下几点：

1. 语法：ROW_NUMBER() OVER (ORDER BY column_name) AS row_num

• ROW_NUMBER是一个窗口函数，OVER子句用于指定排序方式。
• ORDER BY子句是必需的，以确保结果集是按正确的顺序排列的。

2. 分页查询：SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY column_name) AS row_num FROM table_name) AS a WHERE a.row_num BETWEEN start_row AND end_row

• 在子查询中使用ROW_NUMBER函数，并将其作为新列别名row_num添加到结果集中。
• 将子查询作为临时表（也称为内部表或派生表）进行处理，并使用WHERE子句和BETWEEN运算符来筛选所需的行数范围。

3. 性能问题：

• ROW_NUMBER函数可能会导致性能问题，因为它需要对整个结果集进行排序和编号。对于大型数据集来说，这可能会非常耗时。
• 可以通过创建适当的索引、使用更好的算法或缩小结果集大小等方式改善性能问题。

4. 数据一致性：

• ROW_NUMBER函数仅对当前查询结果起作用，如果基础表的数据发生变化，则结果可能会发生不一致。
• 如果需要实现数据一致性，请考虑使用其他分页技术，例如OFFSET FETCH或LIMIT OFFSET等。