How can we use prepared statements in MySQL?

MySQLMySQLi Database

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

Followings are the steps for using prepared statements in MySQL −

Prepare the statement 

It is the first step in which we will prepare a statement by using PREPARE statement. For example, following is a statement prepared by using the data from ‘Tender’ table −

Example

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

EXECUTE the PREPARED statement

 It is the second step in which we will execute the statement which has been prepared by using ‘PREPARE’ statement. For example, we will execute the prepared statement stmt with the following syntax −

Example

EXECUTE stmt USING @variable_name;

Here @variable_name will have the value which we want tp pass at the place of ? in the PREPARE statement. We need to set the value of @variable_name by using SET statement before executing the prepared statement.

DEALLOCATE the PREPARED statement

It is the last step in which we will release the prepared statement with the help of DEALLOCATE statement. For example, we will deallocate the prepared statement stmt with the help of following syntax −

Example

DEALLOCATE PREPARE stmt;

Followings are the queries with the help of which 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)
raja
Published on 20-Feb-2018 15:44:58
Advertisements