Home / General Questions / Question No: 5

While I am studying the MySQL documentation I found these lines

SET @a=1; 
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?'; 
EXECUTE STMT USING @a; 

Please explain me what are these parts mean and also PREPARE STMT.

MySQL Server supports prepared statements, which are useful when you want to run several queries that differ only in very small details. For example, you can prepare a statement, and then execute it multiple times, each time with different data values.

Besides offering a convenience, prepared statements also provide enhanced performance because the complete statement is parsed only once by the server. When the parse is complete, the server and client may make use of a new protocol that requires fewer data conversions (and usually makes for less traffic between the server and client) than when sending each statement individually.

In most circumstances, statements are prepared and executed using the programming interface that you normally use for writing applications that use MySQL like PHP (In PHP mysqli extension supports prepared statement functionality. The mysqli extension is recommended for safety from sql injetions and good performance more than mysql classic). However, to aid in testing and debugging, it is possible to define and use prepared statements from within the mysql command-line client.

Here is a short example that illustrates the use of a prepared statement. It prepares a statement that returns us first name of user against the given id, executes it multiple times, and disposes of it:

PREPARE MySTMT FROM 'SELECT first_name FROM user WHERE id=?'; 
SET @id=1; 
EXECUTE MySTMT USING @id; 

/*results*/


+--------------+

| first_name |

+--------------+

| James      |

+--------------+


 Again execute to get name of another 

SET @id= 2; EXECUTE MySTMT USING @code; 

/*results*/


+------------+

| first_name |

+------------+

| Keith      |

+------------+

The PREPARE statement is used to define an SQL statement that will be executed later. PREPARE takes two arguments: a name to assign to the statement once it has been prepared, and the text of an SQL statement. Prepared statement names are not case sensitive. The text of the statement can be given either as a literal string or as a user variable containing the statement.

The statement may not be complete, because data values that are unknown at preparation time are represented by question mark ('?') characters that serve as parameter markers. At the time the statement is executed, you provide specific data values, one for each parameter in the statement. The server replaces the markers with the data values to complete the statement. Different values can be used each time the statement is executed.