Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we use prepared statements in MySQL?
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)
342 Views
