Advertisement: Jobs in UAE, Saudi Arabia, Qatar, Pakistan, Bahrain & other regions. Click here to find one for you.

MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
Powered by MySQL
 
Home / General Questions / Question No: 5

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.

Answer No: 5

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.

Related MySQL FAQs to the Above FAQ

How-can-I-detect-that-MySQL-is-already-installed How can I detect that MySQL is already installed?

Maximum-number-of-columns-in-a-table Maximum number of columns in a table

Can-I-use-FIELD-CONSTRAINTS-on-MySQL Can I use FIELD CONSTRAINTS on MySQL?

What-are-reserved-words-in-MySQL What are reserved words in MySQL?

How-to-manage-Time-Zone-in-MySQL How to manage Time Zone in MySQL?

What-is-History-of-MySQL What is History of MySQL?

Why-values-with-spaces-do-not-come-into-search-result Why values with spaces do not come into search result?

How-does-MySQL-manage-case-sensitivity-of-identifiers How does MySQL manage case sensitivity of identifiers?

What-are-MySQL-connectors-or-client-interfaces What are MySQL connectors or client interfaces?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Sulata iSoft - Limitless Solutions | Limitless Job Opportunities - Careermidway.com

© 2007 - 2014  www.mysqlfaqs.net
All rights reserved. The site is owned and operated by Mr. Javed Bhatti - Pakistan.