MySQL Prepared Statements: PREPARE, EXECUTE, and Parameters

A prepared statement separates an SQL statement from the values supplied when it runs. In MySQL’s SQL interface, use PREPARE to define the statement, EXECUTE to run it with values, and DEALLOCATE PREPARE to release it. Prepared statements are especially useful when the same query runs repeatedly with different values.

For application code, use your MySQL connector’s parameter-binding API rather than building SQL by concatenating input. Bound values are treated as data, not SQL syntax. For basic query syntax, see MySQL SELECT.

MySQL PREPARE statement syntax

PREPARE statement_name FROM 'SELECT ... WHERE column_name = ?';
SET @value = ...;
EXECUTE statement_name USING @value;
DEALLOCATE PREPARE statement_name;

The ? is a parameter marker. Do not put it inside quotes. Each marker is bound to a user variable in the matching position in the USING list.

Reuse a prepared query with different values

Create a small products table for this example:

CREATE TABLE prepared_demo_products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(8, 2) NOT NULL
);

INSERT INTO prepared_demo_products (product_id, product_name, price) VALUES
    (1, 'Notebook', 4.50),
    (2, 'Pen', 1.25),
    (3, 'Backpack', 32.00),
    (4, 'Desk lamp', 24.00);

Prepare a query that finds products above a minimum price, then execute it with a bound value:

SET @min_price = 10.00;

PREPARE products_above_price FROM
    'SELECT product_id, product_name, price
     FROM prepared_demo_products
     WHERE price > ?
     ORDER BY price DESC';

EXECUTE products_above_price USING @min_price;

The first execution returns the Backpack and Desk lamp. Set a different value and execute the same prepared statement again:

SET @min_price = 25.00;
EXECUTE products_above_price USING @min_price;

DEALLOCATE PREPARE products_above_price;

The second execution returns only the Backpack. DEALLOCATE PREPARE releases the statement when you no longer need it. Prepared statements created with SQL syntax belong to the current session, so prepare and execute them on the same connection.

What parameter markers can and cannot replace

A parameter marker can stand in for a data value, such as a price, name, or date:

SELECT product_id
FROM prepared_demo_products
WHERE price > ?;

It cannot stand in for an identifier or SQL keyword. For example, SELECT * FROM ? cannot bind a table name. If an application must choose a table or column dynamically, select the identifier from a fixed allowlist and construct that part of the SQL separately; continue to bind the values.

The SQL statement passed to PREPARE must be one statement. When reusing a prepared statement, bind values with consistent data types. Prepared statements reduce repeated parsing overhead and help keep data separate from SQL syntax, but they do not guarantee every query will run faster; query design and workload still matter.

Summary

• Use PREPARE, EXECUTE ... USING, and DEALLOCATE PREPARE in the same MySQL session.
• Bind user-supplied values instead of concatenating them into SQL text.
• Parameter markers stand for data values, not table names, column names, or SQL keywords.
• Reusing a statement can reduce parsing overhead, but measure performance for the actual workload.

For full syntax and behavior, see the MySQL 8.4 Prepared Statements manual and PREPARE statement reference.