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 −
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 −
PREPARE stmt FROM ‘Select tender_value from Tender Where Companyname = ?;’
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 −
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.
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 −
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)