mysql - 什么是 mysql 缓冲游标 w.r.t python mysql 连接器

Question

谁能举个例子来理解这一点？

After executing a query, a MySQLCursorBuffered cursor fetches the entire result set from the server and buffers the rows. For queries executed using a buffered cursor, row-fetching methods such as fetchone() return rows from the set of buffered rows. For nonbuffered cursors, rows are not fetched from the server until a row-fetching method is called. In this case, you must be sure to fetch all rows of the result set before executing any other statements on the same connection, or an InternalError (Unread result found) exception will be raised.

谢谢

Answer 1

我可以想到这两种类型的 Cursor 有两种不同之处。

第一种方法是，如果您使用缓冲游标执行查询，您可以通过检查 MySQLCursorBuffered.rowcount 获得返回的行数。但是，未缓冲游标的 rowcount 属性会在调用 execute 方法后立即返回 -1。这基本上意味着尚未从服务器获取整个结果集。此外，无缓冲游标的 rowcount 属性随着您从中获取行而增加，而缓冲游标的 rowcount 属性在您从中获取行时保持不变。

以下代码片段试图说明上述观点:

import mysql.connector


conn = mysql.connector.connect(database='db',
                               user='username',
                               password='pass',
                               host='localhost',
                               port=3306)

buffered_cursor = conn.cursor(buffered=True)
unbuffered_cursor = conn.cursor(buffered=False)

create_query = """
drop table if exists people;
create table if not exists people (
    personid int(10) unsigned auto_increment,
    firstname varchar(255),
    lastname varchar(255),
    primary key (personid)
);
insert into people (firstname, lastname)
values ('Jon', 'Bon Jovi'),
('David', 'Bryan'),
('Tico', 'Torres'),
('Phil', 'Xenidis'),
('Hugh', 'McDonald')
"""

# Create and populate a table
results = buffered_cursor.execute(create_query, multi=True)
conn.commit()

buffered_cursor.execute("select * from people")
print("Row count from a buffer cursor:", buffered_cursor.rowcount)
unbuffered_cursor.execute("select * from people")
print("Row count from an unbuffered cursor:", unbuffered_cursor.rowcount)

print()
print("Fetching rows from a buffered cursor: ")

while True:
    try:
        row = next(buffered_cursor)
        print("Row:", row)
        print("Row count:", buffered_cursor.rowcount)
    except StopIteration:
        break

print()
print("Fetching rows from an unbuffered cursor: ")

while True:
    try:
        row = next(unbuffered_cursor)
        print("Row:", row)
        print("Row count:", unbuffered_cursor.rowcount)
    except StopIteration:
        break

上面的代码片段应该返回如下内容:

Row count from a buffered reader:  5
Row count from an unbuffered reader:  -1

Fetching rows from a buffered cursor:
Row: (1, 'Jon', 'Bon Jovi')
Row count: 5
Row: (2, 'David', 'Bryan')
Row count: 5
Row: (3, 'Tico', 'Torres')
Row count: 5
Row: (4, 'Phil', 'Xenidis')
Row count: 5
Row: (5, 'Hugh', 'McDonald')
Row: 5

Fetching rows from an unbuffered cursor:
Row: (1, 'Jon', 'Bon Jovi')
Row count: 1
Row: (2, 'David', 'Bryan')
Row count: 2
Row: (3, 'Tico', 'Torres')
Row count: 3
Row: (4, 'Phil', 'Xenidis')
Row count: 4
Row: (5, 'Hugh', 'McDonald')
Row count: 5

如您所见，无缓冲游标的 rowcount 属性从 -1 开始，并随着我们循环遍历它生成的结果而增加。缓冲游标不是这种情况。

第二种区分方法是注意两者中的哪一个(在同一连接下)先执行 execute。如果您开始执行未完全获取行的无缓冲游标，然后尝试使用缓冲游标执行查询，则会引发 InternalError 异常，并要求您使用或放弃无缓冲游标返回的内容。下面是一个例子:

import mysql.connector


conn = mysql.connector.connect(database='db',
                               user='username',
                               password='pass',
                               host='localhost',
                               port=3306)

buffered_cursor = conn.cursor(buffered=True)
unbuffered_cursor = conn.cursor(buffered=False)

create_query = """
drop table if exists people;
create table if not exists people (
    personid int(10) unsigned auto_increment,
    firstname varchar(255),
    lastname varchar(255),
    primary key (personid)
);
insert into people (firstname, lastname)
values ('Jon', 'Bon Jovi'),
('David', 'Bryan'),
('Tico', 'Torres'),
('Phil', 'Xenidis'),
('Hugh', 'McDonald')
"""

# Create and populate a table
results = buffered_cursor.execute(create_query, multi=True)
conn.commit()

unbuffered_cursor.execute("select * from people")
unbuffered_cursor.fetchone()
buffered_cursor.execute("select * from people")

上面的代码片段将引发一个 InternalError 异常，并显示一条消息表明有一些未读的结果。它的基本意思是，在您可以使用同一连接下的任何游标执行另一个查询之前，需要完全使用无缓冲游标返回的结果。如果将 unbuffered_cursor.fetchone() 更改为 unbuffered_cursor.fetchall()，错误将消失。

还有其他不太明显的差异，例如内存消耗。缓冲游标可能会消耗更多内存，因为它们可能从服务器获取结果集并缓冲行。

我希望这证明是有用的。