How to use stored procedures in php+mysql

MySQL and PHP are two technologies commonly used in website development. Among them, MySQL is a database management system, responsible for data storage and management; PHP is a server-side scripting language, used to generate web content. In some complex business scenarios, stored procedures need to be used to complete some specific data operations. This article will introduce the use of stored procedures from both MySQL and PHP.

1. MySQL stored procedures

MySQL stored procedures are a collection of precompiled SQL statements that can be used to execute some 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('Connect Error:' . $db->connect_error);
}

// 准备存储过程的参数和调用语句
$id = 1;
$p_name = '';
$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['name'];

    // 输出查询结果
    echo "User $id'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.

Summary

In complex business logic, using MySQL stored procedures can move some logic to the database level, improving the operating efficiency and response speed of the system. When PHP calls a MySQL stored procedure, you need to pay attention to the input parameters and output parameters of the stored procedure, and execute multiple SQL statements to obtain query results. In this way, in practice, combining MySQL stored procedures and PHP development can handle database operations more conveniently and improve efficiency.

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!