Understand Mysql prepare pre processed statements

MySQL 5.1 supports prefabricated statements from server 1 side. This support can take advantage of the efficient client/server base 2 protocol implemented in MySQL 4.1 if you use the appropriate client-side programming interface. The candidate interfaces include the MySQL C API client library (for C programs), MySQL Connector/J (for Java programs), and MySQL Connector/NET. For example, C API can provide a set of function calls that make up the prefabricated statement API. Other language interfaces can support prefabricated statements that use the base 2 protocol (via links in the C client library). For prefabricated statements, there is also an SQL interface available. This interface is less efficient than using the base 2 protocol for the entire prefabricated statement API, but it does not require programming because at the SQL level, this interface can be directly utilized:

· When you cannot use the programming interface, you can use this interface.

· Some programs allow you to send SQL statements to the server to be executed, such as the mysql client program. You can use this interface from these programs.

· You can use this interface even if the client is using the older version of the client library. The only requirement is that you be able to connect to a server that supports the prefabricated statement SQL syntax.

The SQL syntax for prefabricated statements is used when:

· Before you code, you want to test how well the prefabricated statements work in your application. Or maybe an application has a problem executing prepared statements, and you want to determine what the problem is.

· You want to create a test case that describes a problem that occurred when you used prefabricated statements so that you can compile a program error report.

· You need to use prefabricated statements, but you cannot use programming API that supports prefabricated statements.

The SQL syntax for prefabricated statements is based on three SQL statements:

PREPARE stmt_name FROM preparable_stmt;
 
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
 
{DEALLOCATE | DROP} PREPARE stmt_name;

The PREPARE statement is used to prepare a statement and give it the name stmt_name to refer to it later. Statement names are not sensitive to cases. preparable_stmt can be either a literal string or a user variable that contains the statement text. The text must represent an SQL statement with a single 1, not multiple statements. Use this statement, '? The 'character can be used to make parameters to indicate where the data value is combined with the query from 1 onwards when you execute the query. 'the & # 63; 'characters should not be quoted, even if you want to combine them with string values starting at 1. Parameter makers should only be used where data values should appear, not for SQL keywords, identifiers, etc.

If a prefabricated statement with this name already exists, it is implicitly unallocated until the new language is prepared. This means that if the new statement contains an error and cannot be prepared, an error is returned, and there is no statement with the given name.

The scope of the prefabricated statement is the client session. Within this session, the statement is created. No other client can see it.

After you have prepared a statement, you can execute it using an EXECUTE statement that references the name of the prefabricated statement. If the prefabricated statement contains any parameter manufacturers, you must provide an USING clause that enumerates the user variables, which contain the values to be combined with the parameters. Parameter values can only be provided with user variables. The USING clause must specify the user variables accurately. The number of user variables is as large as the number of parameter manufacturers in the statement.

You can execute a given prefabricated statement multiple times, passing it a different variable before each execution, or setting the variable to a different value.

To unassign a prefabricated statement, use the DEALLOCATE PREPARE statement. An attempt to execute a prefabricated statement after unallocation results in an error.

If you terminate a client session without unallocating previously prefabricated statements, the server will automatically unallocate.

The following SQL statements can be used in prefabricated statements: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE and most SHOW statements. No other statements are currently supported.

The following example shows two ways to prepare 1 statement. This statement is used to calculate the hypotenuse of a three-angle shape given the length of two sides.

The first example shows how to create a prefabricated statement by using a literal string to provide the text of the statement:

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|     5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;

The second example is similar, except that the text of the statement is provided as a user variable:

mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> PREPARE stmt2 FROM @s;
mysql> SET @a = 6;
mysql> SET @b = 8;
mysql> EXECUTE stmt2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|     10 |
+------------+
mysql> DEALLOCATE PREPARE stmt2;

For prepared statements, you can use a position holder. The following statement will return 1 row from tb1 table:

mysql> SET @a=1;

mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";

mysql> EXECUTE STMT USING @a;

The following statement will return lines 2 through 6 from the tb1 table:

mysql> SET @skip=1; SET @numrows=5;

mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";

mysql> EXECUTE STMT USING @skip, @numrows;

The SQL syntax for prefabricated statements cannot be used in nested styles. That is, the statement being passed to PREPARE cannot itself be an PREPARE, EXECUTE, or DEALLOCATE PREPARE statement.

The SQL syntax for a prefabricated statement is different from calling with the prefabricated statement API. For example, you cannot use the mysql_stmt_prepare() C API function to prepare an PREPARE, EXECUTE, or DEALLOCATE PREPARE statement.

The SQL syntax for prefabricated statements can be used in stored procedures, but not in stored functions or triggers.

Above is the entire content of this article, I hope to help you with your study.