mysql - MySQL存储例程-常见陷阱？

Question

这些就是我遇到的：
不能用ALTER PROCEDURE更改存储过程的主体应该使用DROP procedure和CREATE procedure。
PREPARE不接受局部变量。这行不通：
DECLARE sql VARCHAR(32) DEFAULT 'SELECT 1';

PREPARE stmt FROM sql;
FETCH[光标名称]进入。。不接受全局变量。这行不通：
FETCH mycursor INTO @a;
还有什么例子吗？

Answer 1

首先，很抱歉在这个帖子里有这么多无耻的插件。我之所以这么做是因为我一直在努力解决这些问题，而详细地解释每一个问题将花费太多时间。好的方面是，我链接到的所有文章都应该通过查看第一段左右的内容，让您清楚地知道它们是否值得阅读。
将[光标名称]提取到。。不接受全局变量。
不是真的。局部变量的作用很好。
准备
至于PREPARE，与您所提到的相同，为占位符指定参数值也需要使用用户定义的变量，而不是局部变量：

PREPARE stmt FROM 'INSERT INTO tab VALUES (?,?)';
EXECUTE stmt USING @val1, @val2;
DEALLOCATE PREPARE stmt;

欲了解更多信息准备，见我无耻的插头： http://rpbouman.blogspot.com/2005/11/mysql-5-prepared-statement-syntax-and.html
PREPARE的另一个限制是不能在存储的函数或触发器中使用它
触发器
目前，MySQL只实现 FOR EACH ROW触发器。这意味着您只能对行级别的INSERT、UPDATE或DELETE事件执行操作。有一个技巧可以用来模拟 BEFORE STATEMENT触发器，请参见： http://rpbouman.blogspot.com/2006/04/mysql-hack-emulates-before-statement_30.html。不过，这并不是真正有用的——更有用的是一个 AFTER STATEMENT触发器，但我还没有找到任何方法来触发这个触发器。
触发器的另一个限制是，每个表只能有一种类型。触发器的“类型”由四个部分组成：触发器时间（前/后）、触发器语句（插入、更新、删除）、触发器级别（语句/行，仅实现行）。因此，例如在mysql中，在任何给定的表上只能有一个 BEFORE INSERT FOR EACH ROW触发器。
对触发器的另一个非常重要的限制是，它们不会为级联外键的结果触发操作。因此，innodb表上的外键级联操作所导致的更新和删除不会在受级联操作影响的表上触发任何更新/删除触发器。
分隔符
我认为最常见的问题是，存储例程中的语句分隔符与用于分隔普通sql语句的语句分隔符（分号）相同。这意味着，要定义存储例程，必须首先将分隔符设置为其他值，以便使用分号分隔存储例程语句：

DELIMITER $$

CREATE PROCEDURE p(p_name)
BEGIN
    INSERT INTO tab VALUES (p_name);  -- these statements are terminated with semi-colon
    SELECT last_insert_id();
END; -- after this we do the custom delimiter to create the procedure
$$ 

-- now let's reset the delimiter again:
DELIMITER ; 

CALL p('Boe'); -- we can use the semi-colon again now.

有关MySQL分隔符的更多信息，请参见： http://rpbouman.blogspot.com/2008/02/most-misunderstood-character-in-mysqls.html
提出错误
一个非常重要的问题是，在MySQL 5.0…5.1中不能引发用户定义的错误。有关问题的说明，请参见 http://rpbouman.blogspot.com/2006/02/dont-you-need-proper-error-handling.html。这个问题在MySQL 5.5中得到了解决，您可以使用标准的SQL SIGNAL语句来解决这个问题。见 http://rpbouman.blogspot.com/2009/12/validating-mysql-data-entry-with_15.html。要解决MySQL v<5.5中缺少的特性，请看这里： http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html。也有其他方法，但它们都依赖于引发一些其他已知但不是用户定义的错误。
性能
我想另一个重要的问题是MySQL存储过程不快。普通SQL中的表达式的执行速度比将它们放在存储函数或存储过程中要快得多。存储例程中的SQL与SQL-plain的速度差不多，但您无法从预编译中获益，因为MySQL没有实现这一点。几个简单的基准说明了这一点：

mysql> SELECT BENCHMARK(10000000, 1+1);
+--------------------------+
| benchmark(10000000, 1+1) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.30 sec)

现在一个函数中的等价项：

mysql> CREATE FUNCTION f_one_plus_one() RETURNS INT RETURN 1+1;
mysql> SELECT BENCHMARK(10000000, f_one_plus_one());
+---------------------------------------+
| benchmark(10000000, f_one_plus_one()) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (28.73 sec)

现在，您可以很快地将函数的性能包括28.73／0.30，这大约是100倍慢，因为还有其他一些因素需要考虑。我的建议是对您的特定代码进行基准测试。
光标
另一个问题是诅咒。它们很慢，因为当您打开它们时，它们会在临时表中具体化。要了解游标性能与纯SQL的比较，请参见 http://rpbouman.blogspot.com/2006/09/refactoring-mysql-cursors.html
当在游标中循环时，不能以内联方式更新游标-它是只读的，并且是向前的。另一个“gotcha”并不是真正的gotcha，但值得一提的是：您只能使用标准的ansi sql来控制游标循环。这种语法往往比MS-SQL和Oracle（可能还有其他产品）中的等效语法要详细得多。有关MySQL中的游标循环，请参见 http://rpbouman.blogspot.com/2005/09/want-to-write-cursor-loop-with-mysql.html
参数
不能为存储的例程参数指定默认值。
无模块/包
存储例程就是这样-容器就是数据库。不能在一个包或模块中打包多个相关例程。
这些是我此刻想到的最重要的事情。不过，我相信还有更多的事情你可以考虑。