PHP 存储过程参数

Question

我创建了一个测试数据库，如下所示:

USE test;
DROP TABLE IF EXISTS test;
DROP PROCEDURE IF EXISTS testparams;
CREATE TABLE test
(
  inparm INT,
  inoutparam INT,
  outparm INT
);

INSERT INTO test VALUES (0, 0, 0);

DELIMITER $$
CREATE PROCEDURE testparams(IN i INT, INOUT io INT, OUT o INT)
BEGIN
   UPDATE test SET inoutparm = io + 1, outparm = FLOOR(RAND() * 1000)
               WHERE inparm = i; 

   SELECT outparm INTO o FROM test WHERE inparm = i;
   SELECT inoutparam INTO io FROM test WHERE inparm = i;

END $$

我还有一个 PHP 脚本:

<?php

$c = new mysqli('localhost', 'test', 'test', 'test') or die('Cannot connect');
echo "Connected\n";

$in = 0;
$out = -1;
$inout = 3;

echo "In: $in Out: $out: Inout: $inout\n";

$s = $c->prepare('CALL testparams(?, ?, ?)') or die('Unable to prepare: ' . $c->error);
$s->bind_param('iii', $in, $inout, $out);
#$s->bind_result($out, $inout);
$s->execute();

echo "After execute SP\nIn: $in Out: $out: Inout: $inout\n";

echo "End of SP\n";
$s = $c->prepare('SELECT inparm, inoutparam, outparm FROM test');

# $s->bind_result($in, $inout, $out); - Get a error here

$s->execute();
while ($s->fetch())
{
    echo "In: $in Out: $out: Inout: $inout\n";
}

?>

我的问题是存储过程似乎没有被调用。这是由于使用 OUT 和 INOUT 参数造成的吗？如果是这样，您如何在 PHP 中使用这些参数？

生成以下输出的脚本演示了这一点

Connected
In: 0 Out: -1: Inout: 3
End of SP
In: 0 Out: 0: Inout: 0

Answer 1

CALL Syntax

For programs written in a language that provides a MySQL interface, there is no native method for directly retrieving the results of OUT or INOUT parameters from CALL statements. To get the parameter values, pass user-defined variables to the procedure in the CALL statement and then execute a SELECT statement to produce a result set containing the variable values. To handle an INOUT parameter, execute a statement prior to the CALL that sets the corresponding user variable to the value to be passed to the procedure.

这似乎在 MySQL 5.6 中发生了变化

In MySQL 5.6, C programs can use the prepared-statement interface to execute CALL statements and access OUT and INOUT parameters. ... Languages that provide a MySQL interface can use prepared CALL statements to directly retrieve OUT and INOUT procedure parameters.

这样就可以了

<?php

$c = new mysqli('localhost', 'test', 'test', 'test') or die('Cannot connect');
echo "<pre>Connected<br>";

$in = 0;
$out = -1;
$inout = 3;

echo "Before executing SP<br>In: $in Inout: $inout Out: $out<br>";

$s = $c->prepare('SET @i = ?, @io = ?') or die('Unable to prepare: ' . $c->error);
$s->bind_param('ii', $in, $inout);
$s->execute();

$s = $c->prepare("CALL testparams(@i, @io, @o)") or die('Unable to prepare: ' . $c->error);
$s->execute();

$s = $c->prepare('SELECT @io, @o');
$s->execute();
$s->bind_result($inout, $out);
$s->fetch();

echo "After execute SP<br>In: $in Inout: $inout Out: $out<br>";

echo "End of SP<br></pre>";

?>

输出

Connected
Before executing SP
In: 0 Inout: 3 Out: -1
After execute SP
In: 0 Inout: 4 Out: 851
End of SP

顺便说一句，SP 有一个拼写错误。这一行

UPDATE test SET inoutparm = io + 1, outparm = FLOOR(RAND() * 1000)

应该是

UPDATE test SET inoutparam = io + 1, outparm = FLOOR(RAND() * 1000)
                        ^