Understanding the Mysqlprepare pre-processing statement _ MySQL

This article mainly helps you to learn and understand the Mysqlprepare pre-processing statements. if you are interested in the prepare pre-processing statements, refer to MySQL 5.1 to provide support for premade statements on the server side. If you use a suitable client programming interface, this support can take advantage of the efficient client/server binary protocol implemented in MySQL 4.1. 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 can constitute the API of premade statements. Other language interfaces support premade statements that use the binary protocol (linked in the C client library. An SQL interface can be used for premade statements. Compared with the binary protocol used in the entire premade statement API, this interface is less efficient, but it does not require programming, because at the SQL level, you can Use this interface directly:

· 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 an earlier version of the client library. The only requirement is that you can connect to a server that supports SQL syntax of premade statements.

The SQL syntax of premade statements is used in the following scenarios:

· Before coding, you want to test how premade statements run in your application. Or an application may have problems executing premade statements. you want to determine what the problem is.

· You want to create a test case that describes problems that occur when you use premade statements, so that you can prepare a program error report.

· You must use premade statements, but you cannot use programming APIs that support premade statements.

The SQL syntax of premade 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 assign it the name stmt_name to reference the statement later. The statement name is not sensitive to the case. Preparable_stmt can be a text string or a user variable containing the Statement Text. This text must display a single SQL statement, rather than multiple statements. Use this statement ,'? The 'character can be used to create parameters to indicate where the data value is combined with the query when you perform a query. '? 'Character should not be enclosed by quotation marks, even if you want to combine them with string values, do not enclose them. Parameter specifiers can only be used where data values should appear, rather than SQL keywords and identifiers.

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

The premade statement range is client session. In this session, the statement is created. Other clients cannot see it.

After preparing a statement, you can run it using an EXECUTE statement (which references the name of a premade statement. If a premade statement contains any parameter manufacturer, you must provide a USING clause that lists user variables (including values to be combined with parameters. The parameter values can only be provided by user variables, and the USING clause must accurately specify user variables. The number of user variables is the same as the number of parameter manufacturers in the statement.

You can execute a given premade statement multiple times, pass different variables to it before each execution, or set the variables to different values.

To unallocate a premade statement, use the deallocate prepare statement. An error occurs when you try to execute a premade statement after unallocation.

If you terminate a client session and no pre-fabricated statements are unallocated, the server will automatically unallocate the session.

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

The following example shows two methods to prepare a statement. This statement is used to calculate the oblique edge of a triangle when the length of two edges is given.

The first example shows how to use a text string to create a premade statement to provide the statement text:

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. The difference is that the statement text 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 location toggle. The following statement returns a row from the tb1 table:

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

The following statement returns the second to sixth rows 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 of premade statements cannot be used in a nested style. That is to say, the statement passed to PREPARE cannot be a PREPARE, EXECUTE, or deallocate prepare statement.

The SQL syntax of the premade statement is different from that of the API call of the premade statement. For example, you cannot use the mysql_stmt_prepare () c api function to PREPARE a PREPARE, EXECUTE, or deallocate prepare statement.

SQL syntax of premade statements can be used in stored procedures, but not in stored functions or triggers.

The above is all the content of this article, hoping to help you learn.