MySQL Prepared Statement
Before the MySQL version 4.1, each query was sent to the MySQL server in the textual format and using the textual protocol, it returned the data to the client. Before returning the result to the client, MySQL has fully parsed the query and transforms the result set into a string. Here, parsing means the submitted query is syntactically and semantically verified, and privileges are also verified.
The textual protocol that returns the data to the client has serious performance issues. To overcome this issue, MySQL provides a new feature called prepared statement since version 4.1.
The prepared statement or parameterized statement is used to execute the same statements repeatedly with high efficiency. It takes advantage of the client/server binary protocol. Prepared statement passes the query that contains placeholders (?) to the MySQL Server. See the below example:
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.
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:
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:
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.
The following are the syntax of the prepare 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.
The following are the syntax to execute the prepared statement:
It is the last and optional stage, which is used to release the prepared statement.
The following are the syntax to deallocate the prepared statement:
Key Points Related To Prepared Statement
MySQL Prepared Statement Example
Let us understand how to use the MySQL Prepared Statement by taking some examples.
Here, we are going to create a statement directly with the help of the PREPARE statement as follows:
Next, we will assign the two values into two variables that can be used for placeholder (?):
Now, we can execute the query with the help of an EXECUTE statement:
After execution, we will get the result as a sum. See the below image to understand the result:
The below examples will use the employee table from the sample database that contains the following data.
First, we will prepare a statement that returns the employee name and designation specified by employee id:
Next, we need to declare a variable named id and set its value to '1':
Now, we can execute the prepared statement with the help of an EXECUTE statement:
After execution, we will get the result that contains the name and designation of an employee. See the below image to understand the query execution:
Again, we will assign another value for variable id:
Now, execute the prepared statement with the new employee id. We will see the output as follows:
Finally, we can release the prepared statement manually. However, they will be removed automatically when the session is closed.
If we try to execute the prepared statement after executing the above query, we will get an error as follows:
How can we use prepared statements in a stored procedure?
We can use the prepared statements in a stored procedure by writing it inside the BEGIN and END block. We can understand it by creating an example that returns all records from a table by passing the table's name as a parameter of the stored procedure.
Create the stored procedure as follows:
See the below image to execute the stored procedure:
After successful creation, we can invoke this procedure by specifying the table name as its parameter.
It will show all records of the table. See the below image:
Statement vs. Prepared Statement
The following are the main differences between the statement and prepared statement in MySQL:
Stored Procedure vs. Prepared Statement
The following are the main differences between the stored procedure and prepared statement in MySQL: