What is the use of mysql prepare?

In MySQL, the PREPARE statement can utilize the client/server binary protocol to pass queries containing placeholders "?" to the MySQL server; use the PREPARE statement to execute queries with placeholders to improve query speed and make users’ queries safer.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Introduction to MySQL Prepared Statements

Prior to MySQL version 4.1, queries were sent to the MySQL server in text format. Afterwards, the MySQL server returns the data to the client using text protocol. MySQL must fully parse the query and convert the result set to a string before returning it to the client.

Text protocols have serious performance issues. To solve this problem, MySQL has added a statement called prepare since version 4.1 to implement some new features. The

prepare statement utilizes the client/server binary protocol. It passes a query containing placeholders (?) to the MySQL server as shown in the following example:

SELECT * 
FROM products 
WHERE productCode = ?;

When MySQL executes this query with a different productcode value , the query does not have to be fully parsed. Therefore, this helps MySQL execute queries faster, especially when MySQL executes the query multiple times. Because the prepare statement uses placeholders (?), this helps avoid SQL injection issues, making your application a little more secure.

MySQL prepared statement usage

In order to use MySQL prepared statements, you need to use three other MySQL statements as follows:

• PREPARE - A statement to be executed.
• EXECUTE - Execute the statement defined by the PREPARE statement.
• DEALLOCATE PREPARE - Issues a PREPARE statement.

The following figure illustrates how to use the PREPARE statement:

MySQL PREPARE statement example

Let’s take a look at an example of using the MySQL PREPARE statement.

PREPARE stmt1 FROM 'SELECT productCode, productName
                    FROM products
                    WHERE productCode = ?';

SET @pc = 'S10_1678';
EXECUTE stmt1 USING @pc;

DEALLOCATE PREPARE stmt1;

First, prepare the execution statement using the PREPARE statement. We use the SELECT statement to query product data from the products table based on the specified product code. Then use a question mark (?) as a placeholder for the product code.

Next, a product code variable @pc is declared and its value is set to S10_1678.

Then, use the EXECUTE statement to execute the prepared statement for the product code variable @pc.

Finally, we use DEALLOCATE PREPARE to issue the PREPARE statement.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the use of mysql prepare?. For more information, please follow other related articles on the PHP Chinese website!