Home » Learn – Prepared Statement

Learn – Prepared Statement

  • by

Learn – Prepared Statement

Before MySQL version 4.1, each query was transmitted to the MySQL server in textual format, and the data was returned to the client via the textual protocol. MySQL thoroughly parses the query and converts the result set into a string before providing the result to the client. Parsing means that the provided query is checked for syntactic and semantic errors, as well as privileges.
The textual protocol used to return data to the client has significant performance difficulties. Since MySQL 4.1, a new feature called prepared statement has been available to address this issue.
The prepared statement, also known as a parameterized statement, is used to run the same statements over and over again.

mysql > SELECT * FROM student WHERE studentId = ?;

When MySQL executes the above statement using different values of studentId, it cannot parse the statement fully. As a result, MySQL will execute the statement faster, especially when it executes the same query multiple times.

mysql > SELECT * FROM student WHERE studentId = ?;

The prepared statement contains placeholders (?), which helps to avoid many SQL injection variants and makes our application more secure.

Advantages of Prepared Statement

The following are the advantages of the prepared statement in MySQL:
We can execute a prepared statement multiple times repeatedly.
Upon every execution, the current value of the bound variable is evaluated and sent to the server. The statement is not parsed again. The statement template is not transferred to the server again.

Basic Workflow of Prepared Statement

The basic workflow of the prepared statement mainly consists of two stages. However, it has one optional stage that is summarized below:
PREPARE
EXECUTE
DEALLOCATE (OPTIONAL)

PREPARE Stage

At the prepare stage, a statement template is sent to the database server. The server performs a syntax check and initializes internal server resources for later use. In short, it prepares a statement for execution.

PREPARE stmt_name FROM preparable_stmt;

EXECUTE Stage

At the execution stage, the client binds parameter values and sends them to the server. The server creates a statement from the statement template and the bound values to execute it using the previously created internal resources. In short, once the prepared statement prepares the query, we are ready to execute that query.SyntaxThe following are the syntax to execute the prepared statement:

EXECUTE stmt_name [USING @var_name [, @var_name]....]

DEALLOCATE/DROP Stage

It is the last and optional stage, which is used to release the prepared statement.

{DEALLOCATE | DROP} PREPARE stmt_name;

A prepared statement created in one session is not available to other sessions. It means prepared statements are session-specific.
When a session ends, whether normally or abnormally, its prepared statements no longer exists in the memory.

mysql > PREPARE stmtA FROM
'SELECT Name, Designation FROM employeetable
WHERE Emp_id = ?';

DELIMITER $$
CREATE PROCEDURE table_detail(table_name Varchar(40))
BEGIN
SET @A:= CONCAT('Select * from',' ',table_name);
Prepare stmtA FROM @A;
EXECUTE stmtA;
END$$
DELIMITER ;

 

mysql > CALL table_detail('employee');

It will show all records of the table.