MySQL中的游标和绑定变量是什么

PHPz

发布时间：2023-05-27 13:30:19

1265人浏览过

来源于亿速云

转载

一、MySQL游标简介

mysql在服务器端提供只读的、单向的游标，而且只能在存储过程或者更底层的客户端api中使用。

MySQL游标是只读的，因为它指向的对象存储在临时表中而非实际查询到的数据。它可以逐行指向查询结果，然后让程序做进一步的处理。在一个存储过程中，游标可以被多次使用并且可以被“嵌套”在循环结构中。

MySQL的游标设计也为粗心的人“准备”了陷阱。因为是使用临时表实现的，所以它在效率上给开发人员一个错觉。打开游标时需要执行整个查询是最重要的要注意的事项。

考虑下面的存储过程：

CREATE PROCEDURE bad_cursor()
BEGIN
DECLARE film_id INT;
DECLARE f CURSOR FOR SELECT film_id FROM sakila.film;
OPEN f;
FETCH f INTO film_id;
CLOSE f;
END

这个例子表明，游标在处理未完成的数据时可以立即关闭。使用Oracle或者SQL Server的用户不会认为这个存储过程有什么问题，但是在MySQL中，这会带来很多的不必要的额外操作。使用SHOW STATUS来诊断这个存储过程，可以看到它需要做1000个索引页的读取，做1000个写入。在打开游标动作的第五行发生了1000次读写操作，原因在于表sakila.film中有1000条记录。

这个案例告诉我们，如果在关闭游标的时候你只是扫描一个大结果集的一小部分，那么存储过程可能不仅没有减少开销，相反带来了大量的额外开销。这时，你需要考虑使用LIMIT来限制返回的结果集。

使用游标可能导致MySQL执行一些额外的低效I/O操作。因为临时内存表不支持BLOB和TEXT类型，如果游标返回的结果包含这样的列的话，MySQL就必须创建临时磁盘表来存放，这样性能可能会很糟。即便没有该列，当临时表超过tmp_table_size大小时，MySQL仍会在磁盘上创建临时表。

虽然MySQL不支持客户端游标，但可以通过客户端API缓存所有查询结果来模拟游标。这和直接将结果放在一个内存数组中来维护并没有什么不同。

二、绑定变量

从MySQL 4.1版本开始，就支持服务器端的绑定变量（prepared statement），这大大提高了客户端和服务器端数据传输的效率。如果你使用支持新协议的客户端，例如MySQL CAPI，就可以使用绑定变量功能。另外，Java和.NET的也都可以使用各自的客户端Connector/J和Connector/NET来使用绑定变量。

最后，还有一个SQL接口用于支持绑定变量，后面我们将讨论这个（这里容易引起困扰）。

客户端向服务器发送一份 SQL 语句的模板，以创建一个绑定变量的 SQL。服务器端收到这个SQL语句框架后，解析并存储这个SQL语句的部分执行计划，返回给客户端一个SQL语句处理句柄。以后每次执行这类查询，客户端都指定使用这个句柄。

绑定变量的SQL，使用问号标记可以接收参数的位置，当真正需要执行具体查询的时候，则使用具体值代替这些问号。例如，下面是一个绑定变量的SQL语句：

INSERT INTO tbl(col1, col2, col3) VALUES (?, ?, ?);

发送该SQL句柄和各个问号参数取值到服务器端，就能执行具体的查询。反复使用这样的方式执行具体的查询，这正是绑定变量的优势所在。具体如何发送取值参数和SQL句柄，则和各个客户端的编程语言有关。使用Java和.NET的MySQL连接器就是一种办法。很多使用MySQL C语言链接库的客户端可以提供类似的接口，需要根据使用的编程语言的文档来了解如何使用绑定变量。

因为如下的原因，MySQL在使用绑定变量的时候可以更高效地执行大量的重复语句：

1.在服务器端只需要解析一次SQL语句。

2.在服务器端某些优化器的工作只需要执行一次，因为它会缓存一部分的执行计划。

以二进制的方式只发送参数和句柄，比起每次都发送ASCII码文本效率更高，一个二进制的日期字段只需要三个字节，但如果是ASCII码则需要十个字节。通过使用绑定变量的形式，BLOB和TEXT字段可以分块传输，从而达到最大的节省。这样就不需要一次性传输了。二进制协议在客户端也可能节省很多内存，减少了网络开销，另外，还节省了将数据从存储原始格式转换成文本格式的开销。

4.仅仅是参数——而不是整个查询语句——需要发送到服务器端，所以网络开销会更小。

5.MySQL在存储参数的时候，直接将其存放到缓存中，不再需要在内存中多次复制。

绑定变量相对也更安全。不需要在应用程序中处理转义，这样可以变得更加简单，同时也能大幅降低SQL注入和攻击的风险。（任何时候都不要信任用户输入，即使是使用绑定变量的时候。）

可以只在使用绑定变量的时候才使用二进制传输协议。如果使用常规的mysql_query()接口，则无法使用二进制传输协议。还有一些客户端让你使用绑定变量，先发送带参数的绑定SQL，然后发送变量值，但是实际上，这些客户端只是模拟了绑定变量的接口，最后还是会直接用具体值代替参数后，再使用mysql_query()发送整个查询语句。

2.1 绑定变量的优化

对使用绑定变量的SQL，MySQL能够缓存其部分执行计划，如果某些执行计划需要根据传入的参数来计算时，MySQL就无法缓存这部分的执行计划。根据优化器什么时候工作，可以将优化分为三类。

在本书编写的时候，下面的三点是适用的。

1.在准备阶段

服务器解析SQL语句，移除不可能的条件，并且重写子查询。

2.在第一次执行的时候

如果可能的话，服务器先简化嵌套循环的关联，并将外关联转化成内关联。

3.在每次SQL语句执行时

服务器做如下事情：

1)过滤分区。

2)如果可能的话，尽量移除COUNT()、MIN()和MAX()。

3)移除常数表达式。

4)检测常量表。

Copy Leaks

AI内容检测和分级，帮助创建和保护原创内容

下载

5)做必要的等值传播。

6)分析和优化ref、range和索引优化等访问数据的方法。

7)优化关联顺序。

2.2 SQL接口的绑定变量

MySQL支持了SQL接口的绑定变量。不使用二进制传输协议也可以直接以SQL的方式使用绑定变量。下面案例展示了如何使用SQL接口的绑定变量：

MySQL中的游标和绑定变量是什么

当服务器收到这些SQL语句后，先会像一般客户端的链接库一样将其翻译成对应的操作。

这意味着你无须使用二进制协议也可以使用绑定变量。

正如你看到的，比起直接编写的SQL语句，这里的语法看起来有一些怪怪的。

那么，这种写法实现的绑定变量到底有什么优势呢？

最主要的用途就是在存储过程中使用。在MySQL 5.0版本中，就可以在存储过程中使用绑定变量，其语法和前面介绍的SQL接口的绑定变量类似。意思是在存储过程中可以创建和运行基于动态SQL语句的代码

“动态”是指可以通过灵活地拼接字符串等参数构建SQL语句。举个例子，下面这个存储过程可以在特定的数据库中执行OPTIMIZE TABLE操作：

DROP PROCEDURE IF EXISTS optimize_tables;
DELIMITER //
CREATE PROCEDURE optimize_tables(db_name VARCHAR(64))
BEGIN
DECLARE t VARCHAR(64);
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = db_name AND TABLE_TYPE = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
tables_loop: LOOP
FETCH c INTO t;
IF done THEN
LEAVE tables_loop;
END IF;
SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name, ".", t);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE c;
END//
DELIMITER ;

可以这样调用这个存储过程：

mysql> CALL optimize_tables('sakila')

另一种实现存储过程中循环的办法是：

REPEAT
FETCH c INTO t;
IF NOT done THEN
SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name, ".", t);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;

REPEAT和其他循环结构最大的不同是，它在每次循环中都会检查两次循环条件。在这个例子中，因为循环条件检查的是一个整数判断，并不会有什么性能问题，如果循环的判断条件非常复杂的话，则需要注意这两者的区别。

像这样使用SQL接口的绑定变量拼接表名和库名是很常见的，这样的好处是无须使用任何参数就能完成SQL语句。由于库名和表名都是关键字，因此在绑定变量的二进制协议中无法将这两个参数化。LIMIT子句是另一个经常需要动态设置的，因为在二进制协议中无法将其参数化。

另外，编写存储过程时，SQL接口的绑定变量通常可以很大程度地帮助我们调试绑定变量，如果不是在存储过程中，SQL接口的绑定变量就不是那么有用了。因为SQL接口的绑定变量，它既没有使用二进制传输协议，也没有能够节省带宽，相反还总是需要增加至少一次额外网络传输才能完成一次查询。所有只有在某些特殊的场景下SQL接口的绑定变量才有用，比如当SQL语句非常非常长，并且需要多次执行的时候。