How do we use prepared statements in MySQL?

#MySQL server supports prepared statements, which is very useful when we want to run many queries that differ only in small details. We can prepare a statement and then execute it multiple times, each time with different data values. Basically, prepared statements in MySQL utilize the client/server binary protocol. Prepared statements provide enhanced performance because the server only parses a complete statement.

The following are the steps to use prepared statements in MySQL -

Prepared Statements

This is our first step in preparing statements using PREPARE statement step. For example, the following is a report written using data from the "Tender" table -

Example

PREPARE stmt FROM ‘Select tender_value from Tender Where Companyname = ?;’

Execute PREPARED statement

This is what we will execute The second step uses the "PREPARE" statement to prepare the statement. For example, we will execute a prepared statement using the following syntax stmt -

Example

EXECUTE stmt USING @variable_name;

Here @variable_name will have the value we want to pass at ? in a PREPARE statement. Before executing the prepared statement, we need to set the value of @variable_name using the SET statement.

DEALLOCATE PREPARED statement

This is the last step where we will release the prepared statement with the help of DEALLOCATE statement. For example, we will release the prepared statement stmt with the help of the following syntax -

Example

DEALLOCATE PREPARE stmt;

Following is the query where we run the prepared statement -

mysql> PREPARE stmt FROM 'SELECT tender_value from Tender WHERE
Companyname = ?';
Query OK, 0 rows affected (0.09 sec)
Statement prepared

mysql> SET @A = 'Singla Group.';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt using @A;
+--------------+
| tender_value |
+--------------+
|   220.255997 |
+--------------+
1 row in set (0.07 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

The above is the detailed content of How do we use prepared statements in MySQL?. For more information, please follow other related articles on the PHP Chinese website!