MySQL Database learning



Home / General Questions / What is prepared statement in MySQL?
Share this job:


What is prepared statement in MySQL?

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.





General Questions related other questions

How can I detect that MySQL is already installed?

By the following ways, you can detect that MySQL is already installed or not.Firstly, If there is no firewall in between, you may check to see if port 3306 is responding as the MySQL Server uses port 3306 by ...

What is prepared statement in MySQL?

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 ...

Maximum number of columns in a table

As of our understanding, The maximum number of columns for any storage engine depends upon the option \"AVG_ROW_LENGTH\". If your length of the column name is large, then number of columns will be less and obvi ...

Can I use FIELD CONSTRAINTS on MySQL?

MySQL doesn\'t support declarative CHECK CONSTRAINTs, you could use triggers as a workaround. However, you can do those checks with triggers. If a BEFORE trigger fails, the operation on the corresponding row is ...

What are reserved words in MySQL?

Complete reserved word list for MySQL 5:http://dev.mysql.com/doc/mysqld ...

How to manage Time Zone in MySQL?

MySQL Server allows to set the current time zone on a per-connection basis. Time zone settings are determined by the time_zone system variable. To view current Time Zone run the following query:SHOW ...

What is History of MySQL?

MySQL is a powerful and the most popular Open Source Software relational database management system (RDBMS) that uses SQL (Structured Query Language). MySQL is officially pronounced \"My esquel\", not \"My se ...

Why values with spaces do not come into search result?

To trim spaces from the value of its both sides, MySQL provides TRIM() function.  You can use TRIM to fix spaces  problem in your select queries.  Take an example:SELECT * FROM tbl_nam ...

How does MySQL manage case sensitivity of identifiers?

A property that affects how you use identifiers is whether they're case sensitive; some identifiers are case sensitive and others are not. You should understand which is which and use them accordingly. ...

What are MySQL connectors or client interfaces?

MySQL AB provides several application programming interfaces (APIs) for accessing the MySQL server. The interface included with distributions of MySQL itself is libmysqlclient, the C client library. This API ...