Prepared or Parameterized Statement in MySQL

I already shared a theory note on Prepared or Parameterized statements of Database System.

In this post, I am sharing a practical about prepared statements of MySQL.

You can write or create a prepared statement in MySQL.
But this is not an efficient way because the binary protocol through a prepared statement API is better.

But still, you can write, and even it doesn’t require any other programming, you can directly write in SQL.

You can use a prepared statement for MySQL Client program.
You can also use a prepared statement in a stored procedure for the dynamic SQL approach.

Below is a small practical demonstration on this:

Let’s first create sample table and data.

1 2 3 4 5 6 7 8 9

CREATE TABLE Test (    TestNumber INTEGER    ,TestName VARCHAR(50) );   INSERT INTO Test VALUES (1,'ABC'),(8,'EFG') ,(16,'PQR'),(26,'XYZ'),(41,'XVK');

Create prepared statement:

1 2 3

PREPARE TestStmt FROM 'SELECT * FROM Test WHERE TestNumber=?';

You can see ‘?’ in WHERE clause. This ‘? ‘ means a required parameter.
You can pass the parameter during execution of Prepared Statement.

Execute Prepared Statement:

1 2	SET @a = 8; EXECUTE TestStmt USING @a;

Result is:

1 2 3

--------- 8 | 'EFG' ---------

De-allocate prepared statement:

1	DEALLOCATE PREPARE TestStmt;

You can also set max_prepared_stmt_count this is a system variable to guard creation of too many prepared statements.