Shulou(Shulou.com)06/02 Report--

What this article shares with you is about the grammar examples and precautions of MySQL SQL preprocessing Prepared. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it.

1. Execution processing of SQL statements 1. Instant SQL

A SQL returns when the final execution is received in DB. The general process is as follows:

1. Lexical and semantic analysis

two。 Optimize SQL statements and make execution plan

3. Execute and return the result

As mentioned above, a SQL is directly processed by the flow, compiled once, and run once. This kind of common statement is called Immediate Statements (instant SQL).

2. Preprocess SQL

However, in the vast majority of cases, a certain SQL statement may be called repeatedly for execution, or only individual values will be different each time (for example, different values of select's where clause, different values of update's set clause, and different values of insert). If you need to go through the above lexical and semantic parsing, sentence optimization, making an execution plan, etc., then the efficiency is obviously not good.

The so-called precompiled sentence is to replace the value in this kind of SQL statement with placeholder, which can be regarded as template or parameterization of SQL sentence, which is generally called Prepared Statements.

The advantages of precompiled sentences are summarized as follows: compiling once, running many times, eliminating the process of parsing and optimization; in addition, precompiled sentences can prevent SQL injection.

Note:

Although the efficiency may be improved to a certain extent by preprocessing SQL, but for optimization, the optimal execution plan is not achieved only by the template of SQL statements, but often needs to estimate the cost through specific values.

II. Prepared SQL Statement Syntax

MySQL officially refers to prepare, execute and deallocate as PREPARE STATEMENT. The translator is used to calling it a preprocessing statement.

The supported version of MySQL preprocessing statement is earlier, so the commonly used version of MySQL supports this syntax.

Syntax:

# define preprocessing statement PREPARE stmt_name FROM preparable_stmt;# execute preprocessing statement EXECUTE stmt_name [USING @ var_name [, @ var_name]...]; # delete (release) definition {DEALLOCATE | DROP} PREPARE stmt_name

1. Use string definition to preprocess SQL (right triangle calculation)

Mysql > PREPARE stmt1 FROM 'SELECT SQRT (POW, 2) + POW (2) AS hypotenuse';Query OK, 0 rows affected (0.00 sec) Statement preparedmysql > SET @ a = 3 SET query OK, 0 rows affected (0.00 sec) mysql > SET @ b = 4; Query OK, 0 rows affected (0.00 sec) mysql > EXECUTE stmt1 USING @ a, @ b +-+ | hypotenuse | +-+ | 5 | +-+ 1 row in set (0.00 sec) mysql > DEALLOCATE PREPARE stmt1; Query OK, 0 rows affected (0.00 sec)

2. Use variable definition to preprocess SQL (right triangle calculation)

Mysql > SET @ s = 'SELECT SQRT (POW (?, 2) + POW (?, 2) AS hypotenuse';Query OK, 0 rows affected (0.00 sec) mysql > PREPARE stmt2 FROM @ s OK, 0 rows affected (0.00 sec) Statement preparedmysql > SET @ c = 6 rows affected query OK, 0 rows affected (0.00 sec) mysql > SET @ d = 8 position query OK, 0 rows affected (0.00 sec) mysql > EXECUTE stmt2 USING @ c, @ d +-+ | hypotenuse | +-+ | 10 | +-+ 1 row in set (0.00 sec) mysql > DEALLOCATE PREPARE stmt2;Query OK, 0 rows affected (0.00 sec)

3. Solve the problem of unable to transfer parameters.

We know that for the value in the LIMIT clause, it must be constant and variables must not be used, that is, you can't use: SELECT * FROM TABLE LIMIT @ skip, @ numrows;, so you can use the PREPARE statement to solve this problem.

Mysql > SET @ skip = 100; SET @ numrows = 3persQuery OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql > SELECT * FROM T1 LIMIT @ skip, @ numrows;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near'@ skip, @ numrows' at line 1mysql > PREPARE stmt3 FROM "SELECT * FROM T1 LIMIT?,?"; Query OK, 0 rows affected (0.00 sec) Statement preparedmysql > EXECUTE stmt3 USING @ skip, @ numrows +-+-+ | a | filler | +-+-+ | 100 | filler | 101 | filler | 102 | filler | +-+-+ 3 rows in set (0.00 sec) mysql > DEALLOCATE PREPARE stmt3;Query OK, 0 rows affected (0.00 sec)

In this way, the problem of syntax error reporting when passing parameters is basically solved by using variable definition preprocessing SQL introduced in 2. Similarly: when a variable is passed as a table name, MySQL will regard the variable name as a table name, which is neither original intention nor syntax error. The solution in SQL Server is to pass parameters by string concatenation and interspersed variables, and then use the whole SQL statement as a variable. Finally, the splicing SQL execution is called with sp_executesql, and Prepared SQL Statement is similar.

Mysql > SET @ table ='T2 query OK, 0 rows affected (0.00 sec) mysql > SET @ s = CONCAT ('SELECT * FROM', @ table); Query OK, 0 rows affected (0.00 sec) mysql > PREPARE stmt4 FROM @ s politics query OK, 0 rows affected (0.00 sec) Statement preparedmysql > EXECUTE stmt4 +-+ | id | score | grade | +-+ | 1 | 99 | A | 2 | 81 | B | 3 | 55 | D | 4 | 69 | C | +-+ 4 rows in set (0.00 sec) mysql > DROP PREPARE stmt4 Query OK, 0 rows affected (0 sec) 3. Points for attention in the use of preprocessing SQL

1. Stmt_name, as the recipient of preparable_stmt, is uniquely identified and is not case-sensitive.

2.? in the preparable_stmt statement? Is a placeholder, represents a string, does not need to be? Enclose it in quotation marks.

3. Define an existing stmt_name, and the original one will be released immediately, similar to the reassignment of variables.

4. The scope of PREPARE stmt_name is session.

The global maximum stored preprocessing statement can be controlled by the max_prepared_stmt_count variable.

Mysql > show variables like 'max_prepared%' +-+-+ | Variable_name | Value | +-+-+ | max_prepared_stmt_count | 16382 | + -+ 1 row in set (0.00 sec)

Preprocessing and compiling SQL takes up resources, so it is a good habit to use DEALLOCATE PREPARE to release resources in time after use.

IV. Advantages of Prepared Statements

1. Safety

Prepared Statements increases security through the separation of sql logic and data, and the separation of sql logic and data can prevent common types of sql injection attacks (SQL injection attack).

two。 Performance

Prepared Statements parses the syntax only once. When you start Prepared Statements, mysql will check the syntax and prepare the statement to run. When you execute query many times, there will be no additional burden. If you run query many times (such as insert), this preprocessing will greatly improve performance.

He uses the binary protocol protocol, which is more efficient.

These are the syntax examples and precautions of MySQL SQL preprocessing Prepared. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.