Prepared Statements
MySQL 8.0 provides support for server-side prepared statements. This support takes advantage of the efficient client/server binary protocol. Using prepared statements with placeholders for parameter values has the following benefits:
- Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of almost-identical statements, with only changes to literal or variable values in clauses such as
WHEREfor queries and deletes,SETfor updates, andVALUESfor inserts. - Protection against SQL injection attacks. The parameter values can contain unescaped SQL quote and delimiter characters.
In order to use MySQL prepared statement, you use three following statements:
PREPARE– prepare a statement for execution.EXECUTE– execute a prepared statement prepared by thePREPAREstatement.DEALLOCATE PREPARE– release a prepared statement.
The following diagram illustrates how to use a prepared statement:
PREPARED statement
Statement names are not case-sensitive. preparable_stmt is either a string literal or a user variable that contains the text of the SQL statement. The text must represent a single statement, not multiple statements. Within the statement, ? characters can be used as parameter markers to indicate where data values are to be bound to the query later when you execute it. The ? characters should not be enclosed within quotation marks, even if you intend to bind them to string values. Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.
EXECUTE Statement
EXECUTE stmt_name
[USING @var_name [, @var_name] ...]
After preparing a statement with PREPARE, you execute it with an EXECUTE statement that refers to the prepared statement name. If the prepared statement contains any parameter markers, you must supply a USING clause that lists user variables containing the values to be bound to the parameters. Parameter values can be supplied only by user variables, and the USING clause must name exactly as many variables as the number of parameter markers in the statement.
DEALLOCATE PREPARE Statement
{DEALLOCATE | DROP} PREPARE stmt_name
To deallocate a prepared statement produced with PREPARE, use a DEALLOCATE PREPARE statement that refers to the prepared statement name. Attempting to execute a prepared statement after deallocating it results in an error.
Demo
SQL
# First, prepare a statement
PREPARE stmt1 FROM 'select * from core_channel where name = ?';
set @name = 'name1';
EXECUTE stmt1 USING @name;
set @name = 'name3';
EXECUTE stmt1 USING @name;
# Finally, release the prepared statement
DEALLOCATE PREPARE stmt1;