How to use stored procedures in php+mysql

1. MySQL stored procedures

A stored procedure is a set of precompiled SQL statements used to implement specific business logic. Compared with writing similar business logic at the application layer, using stored procedures can move these logic to the database level, saving network transmission time and improving performance. The following is a simple MySQL stored procedure:

CREATE PROCEDURE `my_proc`(IN `p_id` INT, OUT `p_name` VARCHAR(20))
BEGIN
    SELECT `name` INTO `p_name` FROM `users` WHERE `id` = `p_id`;
END

This stored procedure accepts an integer parameter p_id, and assigns the corresponding user's name to the p_name output parameter . When calling this stored procedure at the application layer, you only need to pass the parameter p_id to get the name of the corresponding user. The advantage of using a stored procedure is that the next time the stored procedure is called, the MySQL engine has already compiled the procedure into machine code, and there is no need to parse the SQL statement again, so the execution efficiency is higher.

2. PHP calls MySQL stored procedures

In PHP development, using stored procedures can greatly reduce the time of database operations. The following is the PHP code that calls the above MySQL stored procedure:

<?php
// 连接MySQL数据库
$db = new mysqli(&#39;localhost&#39;, &#39;user&#39;, &#39;password&#39;, &#39;test&#39;);
if ($db->connect_errno) {
    die(&#39;Connect Error:&#39; . $db->connect_error);
}

// 准备存储过程的参数和调用语句
$id = 1;
$p_name = &#39;&#39;;
$sql = "CALL my_proc($id, @p_name)";

// 执行调用语句
if ($db->multi_query($sql)) {
    // 获取输出参数
    $db->next_result();
    $result = $db->query("SELECT @p_name AS `name`");
    $row = $result->fetch_assoc();
    $p_name = $row[&#39;name&#39;];

    // 输出查询结果
    echo "User $id&#39;s name is $p_name";
} else {
    echo "Call Procedure Error:" . $db->error;
}

// 关闭数据库连接
$db->close();
?>

In this PHP code, first connect to the MySQL database, and then prepare the input parameters and calling statements of the stored procedure. Since the query results generated by calling the stored procedure are output through the OUT parameter, multiple SQL statements need to be executed to obtain the query results: first execute the calling statement, then execute the SELECT statement, and finally obtain the output parameter p_name. After the call is completed, just close the database connection.

The above is the detailed content of How to use stored procedures in php+mysql. For more information, please follow other related articles on the PHP Chinese website!