存储过程，MySQL和PHP

Question

这是一个相当开放的问题。我已经使用MS SQLServer的Stored Procs和classic ASP以及ASP.net有一段时间了，非常喜欢它们。

我正在进行一个小型的业余项目，由于各种原因，我选择了LAMP路线。有没有任何提示/技巧/陷阱或好的起点来开始使用MySQL和PHP5的存储过程？我的MySQL版本支持存储过程。

- Jon P

---

你能解释一下为什么想要使用存储过程吗？传统智慧说“只有在必要时才使用存储过程”，因此我需要了解你的动机才能回答。 - Alex Weinstein

@Alex 我喜欢通过 select 检索多个结果集 (减少数据库调用) 的能力，参数化可以帮助减少 SQL 注入的可能性。此外，使用 MS SQL Server 存储过程执行路径可以被 SQL Server 优化。 同时，我将这个小型项目作为学习工具。 - Jon P

@Alec.continued! 我也可以从存储过程中获得更好的重用性。如果我有一些经常使用的数据库功能，我只需要在一个位置进行更新。我意识到这也可以在 PHP 级别上通过函数/类来处理。但我发现在 MSSQL 和 ASP/.net 中使用存储过程没有任何理由不这样做。 - Jon P

Answer 1

很抱歉，由于PDO使用的MySQL C API存在一个bug，因此在某些MySQL版本中运行上述代码会导致错误：

"Syntax error or access violation: 1414 OUT or INOUT argument $parameter_number for routine $procedure_name is not a variable or NEW pseudo-variable"。

您可以在bugs.mysql.com上查看bug报告。该问题已在版本5.5.3+和6.0.8+中得到修复。

为解决此问题，您需要分离输入和输出参数，并使用用户变量存储结果，如下所示：

$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(:in_string, @out_string)");
$stmt->bindParam(':in_string', 'hello'); 

// call the stored procedure
$stmt->execute();

// fetch the output
$outputArray = $this->dbh->query("select @out_string")->fetch(PDO::FETCH_ASSOC);

print "procedure returned " . $outputArray['@out_string'] . "\n";

- Dan Straw

---

可以这样做。稍作调整。使用PDOStatement::fetchColumn立即获取结果。像这样$result = $this->dbh->query("select @out_string")->fetchColumn()。 - undefined

Answer 2

实际上，在MySQL 5中调用存储过程并不一定需要使用mysqli或PDO。您可以使用旧的mysql_函数来调用它们。唯一无法做到的是返回多个结果集。

我发现返回多个结果集有些容易出错；在某些情况下确实可以工作，但只有当应用程序记得消耗所有结果集时才能正常工作，否则连接将处于断开状态。

- MarkR

Answer 3

忘记 mysqli，它比 PDO 更难使用，应该已经被删除了。它确实在 mysql 上引入了巨大的改进，但有时为了在 mysqli 中实现相同的效果需要付出巨大的努力，例如关联的 fetchAll。

相反，看一下 PDO，特别是准备好的语句和存储过程。

$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

// call the stored procedure
$stmt->execute();

print "procedure returned $value\n";

- michal kralik

---

完全胡说八道。mysqli并不会出现故障，并且不应该被删除。 - Polsonby

Flubba和Michal，你们能详细说明一下你们的立场吗？请用不超过300个字符！ - Jon P

我同意“buggy”并不是一个明智的词汇选择，但我仍然相信在引入PDO时应该将其删除。虽然mysqli确实比mysql引入了巨大的改进，但有时为了在mysqli中实现相同的效果，需要比PDO更大的努力。即关联获取所有。 - mike

我们需要在存储过程中使用预处理语句吗？因为1）存储过程自动避免SQL注入，2）我看不出SQL注入如何通过存储过程实现。 - user11995521

谁谈到了MySQL？楼主没有，这与问题无关。 - undefined

Answer 4

你需要使用MySQLI（MySQL Improved Extension）来调用存储过程。下面是如何调用存储过程的示例：

$mysqli = new MySQLI(user,pass,db);

$result = $mysqli->query("CALL sp_mysp()");

使用存储过程时，需要先关闭第一个结果集，否则会收到错误提示。以下是更多信息：

http://blog.rvdavid.net/using-stored-procedures-mysqli-in-php-5/（链接已失效）。

或者，您可以使用预处理语句，这种方法我认为非常直观易懂：

  $stmt = $mysqli->prepare("SELECT Phone FROM MyTable WHERE Name=?");

  $stmt->bind_param("s", $myName);

  $stmt->execute();

MySQLI文档：http://no.php.net/manual/en/book.mysqli.php

- Gleb Popov

---

以上“答案”中的两个链接要么过时，要么是垃圾邮件。 - ReverseEMF

Answer 5

我一直在使用ADODB，它是一个很好的东西，可以抽象实际命令，使其在不同的SQL服务器之间可移植（例如从mysql到mssql）。然而，存储过程似乎没有直接支持。这意味着，我必须像运行普通查询一样运行SQL查询，但要“调用”存储过程。 以下是一个示例查询：

$query = "Call HeatMatchInsert('$mMatch', '$mOpponent', '$mDate', $mPlayers, $mRound,  '$mMap', '$mServer', '$mPassword', '$mGame', $mSeason, $mMatchType)";

这并没有考虑到返回的数据，这是很重要的。我猜想这可以通过设置一个 @Var 来完成，你可以选择它作为返回的 @Variable。

抽象一点说，虽然基于 php 存储过程的 web 应用程序初次开发很难解决（mssql 很好文档化，但这不是），但完成后非常棒——由于分离，更改非常容易进行。

- SuperRoach