MySQL pre-compilation function details, MySQL compilation function details

MySQL pre-compilation function details, MySQL compilation function details

This article shares with you the MySQL pre-compilation function for your reference. The specific content is as follows:

1. Benefits of pre-Compilation

You have used the PreparedStatement interface in JDBC, which has the pre-compilation function. What is the pre-compilation function? What are its advantages?
After a customer sends an SQL statement to the server, the server always needs to check whether the syntax format of the SQL statement is correct, compile the SQL statement into an executable function, and finally execute the SQL statement. The validation syntax and compilation may take more time than SQL statement execution.
If we need to execute multiple insert statements, but the values for each insert are different, the MySQL server also needs to verify the syntax format and compile of the SQL statement every time, this wastes too much time. If the Pre-compilation function is used, only one syntax checksum and compilation is performed for the SQL statement, so the efficiency is high.

2. Pre-compile MySQL

MySQL pre-compilation is divided into three steps:
Execute the pre-compiled statement, for example, prepare myfun from 'select * from t_book where bid =? '
Set variables, such as: set @ str = 'b1'
Execute the statement, for example: execute myfun using @ str
If you need to execute myfun again, you no longer need the first step, that is, you do not need to compile the statement again:
Set variables, such as: set @ str = 'b2'
Execute the statement, for example: execute myfun using @ str
You can view the MySQL Log to see the execution process:

3. Use Statement for pre-Compilation

Using Statement to execute pre-compilation is to execute the preceding SQL Statement once.

Connection con = JdbcUtils.getConnection();Statement stmt = con.createStatement();stmt.executeUpdate("prepare myfun from 'select * from t_book where bid=?'");stmt.executeUpdate("set @str='b1'");ResultSet rs = stmt.executeQuery("execute myfun using @str");while(rs.next()) { System.out.print(rs.getString(1) + ", "); System.out.print(rs.getString(2) + ", "); System.out.print(rs.getString(3) + ", "); System.out.println(rs.getString(4));}stmt.executeUpdate("set @str='b2'");rs = stmt.executeQuery("execute myfun using @str");while(rs.next()) { System.out.print(rs.getString(1) + ", "); System.out.print(rs.getString(2) + ", "); System.out.print(rs.getString(3) + ", "); System.out.println(rs.getString(4));}rs.close();stmt.close();con.close();

4. useserverprep0000ts Parameters

By default, PreparedStatement cannot be used for pre-compilation. In this case, the useserverprep;ts = true parameter must be provided in the url (MySQL Server

Pre-compilation is not supported in versions earlier than 4.1, whereas in Versions later than 5.0.5, Connector/J does not enable the pre-compilation function by default ).

Example: jdbc: mysql: // localhost: 3306/test? Useserverprepsponts = true
In this way, the mysql driver will first send the SQL statement to the server for pre-compilation, and then only send the parameter to the server when executing executeQuery.

Connection con = JdbcUtils.getConnection();String sql = "select * from t_book where bid=?";PreparedStatement pstmt = con.prepareStatement(sql);pstmt.setString(1, "b1");ResultSet rs = pstmt.executeQuery();while(rs.next()) { System.out.print(rs.getString(1) + ", "); System.out.print(rs.getString(2) + ", "); System.out.print(rs.getString(3) + ", "); System.out.println(rs.getString(4));}pstmt.setString(1, "b2");rs = pstmt.executeQuery();while(rs.next()) { System.out.print(rs.getString(1) + ", "); System.out.print(rs.getString(2) + ", "); System.out.print(rs.getString(3) + ", "); System.out.println(rs.getString(4));}rs.close();pstmt.close();con.close();

5. cacheprep0000ts Parameters

When different PreparedStatement objects are used to execute the same SQL statement, compilation occurs twice because the driver does not cache the compiled function key, resulting in secondary compilation. If you want to cache the key of the compiled function, you must set the cacheprepcomputs parameter to true. For example:
Jdbc: mysql: /// localhost: 3306/test? Useserverprepancts = true & cacheprepancts = true

Connection con = JdbcUtils.getConnection();String sql = "select * from t_book where bid=?";PreparedStatement pstmt = con.prepareStatement(sql);pstmt.setString(1, "b1");ResultSet rs = pstmt.executeQuery();while(rs.next()) { System.out.print(rs.getString(1) + ", "); System.out.print(rs.getString(2) + ", "); System.out.print(rs.getString(3) + ", "); System.out.println(rs.getString(4));}pstmt = con.prepareStatement(sql);pstmt.setString(1, "b2");rs = pstmt.executeQuery();while(rs.next()) { System.out.print(rs.getString(1) + ", "); System.out.print(rs.getString(2) + ", "); System.out.print(rs.getString(3) + ", "); System.out.println(rs.getString(4));}rs.close();pstmt.close();con.close();

6. Open Batch Processing

MySQL batch processing also needs to be enabled through parameters: rewriteBatchedStatements = true

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.