1. Benefits of precompilation
Everyone has usually used the PreparedStatement interface in JDBC, which has a precompilation function. What is the precompilation function? What are its benefits?
When the client sends a SQL statement to the server, the server always needs to verify whether the syntax format of the SQL statement is correct, then compile the SQL statement into an executable function, and finally execute the SQL statement. The time spent on syntax verification and compilation may be more than the time spent executing SQL statements.
If we need to execute multiple insert statements, but only the values inserted are different each time, the MySQL server also needs to verify the syntax format of the SQL statement and compile it each time, which wastes too much time. time. If you use the precompilation function, the SQL statement is only syntax checked and compiled once, so the efficiency is high.
2. MySQL performs precompilation
MySQL performs precompilation in three steps:
Execute precompiled statements, for example: prepare myfun from 'select * from t_book where bid=?'
Set variables, for example: set @str='b1'
Execute statements, for example :execute myfun using @str
If you need to execute myfun again, then the first step is no longer needed, that is, there is no need to compile the statement:
Set variables, for example: set @str='b2'
Execute statements, for example: execute myfun using @str
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();
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();
##
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();
MySQL’s batch processing also needs to be turned on through parameters: rewriteBatchedStatements=true
The above is the detailed content of MySQL precompilation function. For more information, please follow other related articles on the PHP Chinese website!