在Java編程中，應用代碼絕大多數使用了使用了PreparedStatement,無論你是直接使用JDBC還是使用框架。

在Java編程中，絕大多數使用了使用了PreparedStatement的應用代碼沒有啟用預編譯,無論你是直接使用JDBC還是使用框架。

在我所能見到的項目中，幾乎沒有見過啟用MySQL預編譯功能的。網上更有文章說MySQL不支持預編譯，實在是害人不淺。

這篇文章分以下幾個方面：

一、MySQL是支持預編譯的

打開MySQL日志功能，啟動MySQL，然后 tail -f mysql.log.path(默認：/var/log/mysql/mysql.log).

create table axman_test (ID int(4) auto_increment primary key, name varchar(20),age int(4));
　　insert into axman_test (name,age) values ('axman',1000);
　　prepare myPreparedStmt from 'select * from axman_test where name = ? _cke_saved_name = ?';
　　set @name='axman' _cke_saved_name='axman';
　　execute myPreparedStmt using @name;

控制臺可以正確地輸出：

mysql> execute myPreparedStmt using @name;

　　+----+-------+------+

　　| ID | name | age |

　　+----+-------+------+

　　| 1 | axman | 1000 |

　　+----+-------+------+

　　1 row in set (0.00 sec)

而log文件中也忠實地記錄如下：

　　111028 9:25:06 51 Query prepare myPreparedStmt from 'select * from axman_test where name = ? _cke_saved_name = ?'

　　51 Prepare select * from axman_test where name = ?

　　51 Query set @name='axman' _cke_saved_name='axman'

　　111028 9:25:08 51 Query execute myPreparedStmt using @name

　　51 Execute select * from axman_test where name = 'axman' _cke_saved_name = 'axman'

二、通過JDBC本身是可以預編譯的，這個不用多說。相當于我們把控制臺輸入的命令直接通過JDBC語句來執行：

　　Class.forName("org.gjt.mm.mysql.Driver");

　　String url = "jdbc:mysql://localhost:3306/mysql";

　　Connection conn = null;

　　try {

　　conn = DriverManager.getConnection(url, "root", "12345678");

　　Statement stmt = conn.createStatement();

　　/*以下忽略返回值處理*/

　　stmt.executeUpdate("prepare mystmt from 'select * from axman_test where name = ? _cke_saved_name = ?'");

　　stmt.execute("set @name='axman' _cke_saved_name='axman'");

　　stmt.executeQuery("execute mystmt using @name");

　　stmt.close();

　　} finally {

　　if (conn != null) {

　　conn.close();

　　}

　　}

看日志輸出：

　　111028 9:30:19 52 Connect root@localhost on mysql

　　52 Query /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SHOW VARIABLES WHERE Variable_name ='language' _cke_saved_name ='language' OR Variable_name = 'net_write_timeout' _cke_saved_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' _cke_saved_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' _cke_saved_name = 'wait_timeout' OR Variable_name = 'character_set_client' _cke_saved_name = 'character_set_client' OR Variable_name = 'character_set_connection' _cke_saved_name = 'character_set_connection' OR Variable_name = 'character_set' _cke_saved_name = 'character_set' OR Variable_name = 'character_set_server' _cke_saved_name = 'character_set_server' OR Variable_name = 'tx_isolation' _cke_saved_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' _cke_saved_name = 'transaction_isolation' OR Variable_name = 'character_set_results' _cke_saved_name = 'character_set_results' OR Variable_name = 'timezone' _cke_saved_name = 'timezone' OR Variable_name = 'time_zone' _cke_saved_name = 'time_zone' OR Variable_name = 'system_time_zone' _cke_saved_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' _cke_saved_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' _cke_saved_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' _cke_saved_name = 'net_buffer_length' OR Variable_name = 'sql_mode' _cke_saved_name = 'sql_mode' OR Variable_name = 'query_cache_type' _cke_saved_name = 'query_cache_type' OR Variable_name = 'query_cache_size' _cke_saved_name = 'query_cache_size' OR Variable_name = 'init_connect' _cke_saved_name = 'init_connect'

　　52 Query /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SELECT @@session.auto_increment_increment

　　52 Query SHOW COLLATION

　　52 Query SET NAMES latin1

　　52 Query SET character_set_results = NULL

　　52 Query SET autocommit=1

　　52 Query SET sql_mode='STRICT_TRANS_TABLES'

　　52 Query prepare mystmt from 'select * from axman_test where name = ? _cke_saved_name = ?'

　　52 Prepare select * from axman_test where name = ?

　　52 Query set @name='axman' _cke_saved_name='axman'

　　52 Query execute mystmt using @name

　　52 Execute select * from axman_test where name = 'axman' _cke_saved_name = 'axman'

　　52 Quit

三、默認的PrearedStatement不能開啟MySQL預編譯功能：

　　Class.forName("org.gjt.mm.mysql.Driver");

　　String url = "jdbc:mysql://localhost:3306/mysql";

　　Connection conn = null;

　　try {

　　conn = DriverManager.getConnection(url, "root", "12345678");

　　PreparedStatement ps = conn.prepareStatement("select * from axman_test where name = ? _cke_saved_name = ?");

　　ps.setString(1, "axman' or 1==1");

　　ResultSet rs = ps.executeQuery();

　　if (rs.next()) {

　　System.out.println(rs.getString(1));

　　}

　　Thread.sleep(1000);

　　rs.close();

　　ps.clearParameters();

　　ps.setString(1, "axman");

　　rs = ps.executeQuery();

　　if (rs.next()) {

　　System.out.println(rs.getString(1));

　　}

　　rs.close();

　　ps.close();

　　} finally {

　　if (conn != null) {

　　conn.close();

　　}

　　}

廢話少說，直接看日志：

　　111028 9:54:03 53 Connect root@localhost on mysql

　　53 Query /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SHOW VARIABLES WHERE Variable_name ='language' _cke_saved_name ='language' OR Variable_name = 'net_write_timeout' _cke_saved_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' _cke_saved_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' _cke_saved_name = 'wait_timeout' OR Variable_name = 'character_set_client' _cke_saved_name = 'character_set_client' OR Variable_name = 'character_set_connection' _cke_saved_name = 'character_set_connection' OR Variable_name = 'character_set' _cke_saved_name = 'character_set' OR Variable_name = 'character_set_server' _cke_saved_name = 'character_set_server' OR Variable_name = 'tx_isolation' _cke_saved_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' _cke_saved_name = 'transaction_isolation' OR Variable_name = 'character_set_results' _cke_saved_name = 'character_set_results' OR Variable_name = 'timezone' _cke_saved_name = 'timezone' OR Variable_name = 'time_zone' _cke_saved_name = 'time_zone' OR Variable_name = 'system_time_zone' _cke_saved_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' _cke_saved_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' _cke_saved_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' _cke_saved_name = 'net_buffer_length' OR Variable_name = 'sql_mode' _cke_saved_name = 'sql_mode' OR Variable_name = 'query_cache_type' _cke_saved_name = 'query_cache_type' OR Variable_name = 'query_cache_size' _cke_saved_name = 'query_cache_size' OR Variable_name = 'init_connect' _cke_saved_name = 'init_connect'

　　53 Query /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SELECT @@session.auto_increment_increment

　　53 Query SHOW COLLATION

　　53 Query SET NAMES latin1

　　53 Query SET character_set_results = NULL

　　53 Query SET autocommit=1

　　53 Query SET sql_mode='STRICT_TRANS_TABLES'

　　53 Query select * from axman_test where name = 'axman' _cke_saved_name = 'axman' or 1==1'

　　111028 9:54:04 53 Query select * from axman_test where name = 'axman' _cke_saved_name = 'axman'

　　53 Quit

兩條語句都是直接執行，而沒有預編譯。注意我的第一條語句select * from axman_test where name = 'axman' _cke_saved_name = 'axman' or 1==1'，下面還會說到它。

接著我們改變一下jdbc.url的選項：

　　String url = "jdbc:mysql://localhost:3306/mysql?cachePrepStmts=true&prepStmtCacheSize=25&prepStmtCacheSqlLimit=256";

執行上面的代碼還是沒有開啟Mysql的預編譯。

四、只有使用了useServerPrepStmts=true才能開啟Mysql的預編譯。

上面的代碼其它不變，只修改String url = "jdbc:mysql://localhost:3306/mysql?useServerPrepStmts=true";

查看日志：

　　111028 10:04:52 54 Connect root@localhost on mysql

　　54 Query /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SHOW VARIABLES WHERE Variable_name ='language' _cke_saved_name ='language' OR Variable_name = 'net_write_timeout' _cke_saved_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' _cke_saved_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' _cke_saved_name = 'wait_timeout' OR Variable_name = 'character_set_client' _cke_saved_name = 'character_set_client' OR Variable_name = 'character_set_connection' _cke_saved_name = 'character_set_connection' OR Variable_name = 'character_set' _cke_saved_name = 'character_set' OR Variable_name = 'character_set_server' _cke_saved_name = 'character_set_server' OR Variable_name = 'tx_isolation' _cke_saved_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' _cke_saved_name = 'transaction_isolation' OR Variable_name = 'character_set_results' _cke_saved_name = 'character_set_results' OR Variable_name = 'timezone' _cke_saved_name = 'timezone' OR Variable_name = 'time_zone' _cke_saved_name = 'time_zone' OR Variable_name = 'system_time_zone' _cke_saved_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' _cke_saved_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' _cke_saved_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' _cke_saved_name = 'net_buffer_length' OR Variable_name = 'sql_mode' _cke_saved_name = 'sql_mode' OR Variable_name = 'query_cache_type' _cke_saved_name = 'query_cache_type' OR Variable_name = 'query_cache_size' _cke_saved_name = 'query_cache_size' OR Variable_name = 'init_connect' _cke_saved_name = 'init_connect'

　　54 Query /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SELECT @@session.auto_increment_increment

　　54 Query SHOW COLLATION

　　54 Query SET NAMES latin1

　　54 Query SET character_set_results = NULL

　　54 Query SET autocommit=1

　　54 Query SET sql_mode='STRICT_TRANS_TABLES'

　　54 Prepare select * from axman_test where name = ?

　　54 Execute select * from axman_test where name = 'axman' _cke_saved_name = 'axman' or 1==1'

　　111028 10:04:53 54 Execute select * from axman_test where name = 'axman' _cke_saved_name = 'axman'

　　54 Close stmt

　　54 Quit

如果useServerPrepStmts=true，ConneciontImpl在prepareStatement時會產生一個ServerPreparedStatement.在這個ServerPreparedStatement對象構造時首先會把當前SQL語句發送給MySQL進行預編譯，然后將返回的結果緩存起來，其中包含預編譯的名稱(我們可以看成是當前SQL語句編譯后的函數名)，簽名(參數列表)，然后執行的時候就會直接把參數傳給這個函數請求MySQL執行這個函數。否則返回的是客戶端預編譯語句，它僅做參數化工作，見第五節。

注意上面的代碼中，兩次執行使用的是同一個PreparedStatement句柄.如果使用個不同的PreparedStatement句柄，把代碼改成：

　　Class.forName("org.gjt.mm.mysql.Driver");

　　String url = "jdbc:mysql://localhost:3306/mysql?useServerPrepStmts=true";

　　Connection conn = null;

　　try {

　　conn = DriverManager.getConnection(url, "root", "12345678");

　　PreparedStatement ps = conn.prepareStatement("select * from axman_test where name = ? _cke_saved_name = ?");

　　ps.setString(1, "axman' or 1==1");

　　ResultSet rs = ps.executeQuery();

　　if (rs.next()) {

　　System.out.println(rs.getString(1));

　　}

　　Thread.sleep(1000);

　　rs.close();

　　ps.close();

　　ps = conn.prepareStatement("select * from axman_test where name = ? _cke_saved_name = ?");

　　ps.setString(1, "axman");

　　rs = ps.executeQuery();

　　if (rs.next()) {

　　System.out.println(rs.getString(1));

　　}

　　rs.close();

　　ps.close();

　　} finally {

　　if (conn != null) {

　　conn.close();

　　}

　　}

再看日志輸出：

　　Connect root@localhost on mysql

　　55 Query /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SHOW VARIABLES WHERE Variable_name ='language' _cke_saved_name ='language' OR Variable_name = 'net_write_timeout' _cke_saved_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' _cke_saved_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' _cke_saved_name = 'wait_timeout' OR Variable_name = 'character_set_client' _cke_saved_name = 'character_set_client' OR Variable_name = 'character_set_connection' _cke_saved_name = 'character_set_connection' OR Variable_name = 'character_set' _cke_saved_name = 'character_set' OR Variable_name = 'character_set_server' _cke_saved_name = 'character_set_server' OR Variable_name = 'tx_isolation' _cke_saved_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' _cke_saved_name = 'transaction_isolation' OR Variable_name = 'character_set_results' _cke_saved_name = 'character_set_results' OR Variable_name = 'timezone' _cke_saved_name = 'timezone' OR Variable_name = 'time_zone' _cke_saved_name = 'time_zone' OR Variable_name = 'system_time_zone' _cke_saved_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' _cke_saved_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' _cke_saved_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' _cke_saved_name = 'net_buffer_length' OR Variable_name = 'sql_mode' _cke_saved_name = 'sql_mode' OR Variable_name = 'query_cache_type' _cke_saved_name = 'query_cache_type' OR Variable_name = 'query_cache_size' _cke_saved_name = 'query_cache_size' OR Variable_name = 'init_connect' _cke_saved_name = 'init_connect'

　　55 Query /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SELECT @@session.auto_increment_increment

　　55 Query SHOW COLLATION

　　55 Query SET NAMES latin1

　　55 Query SET character_set_results = NULL

　　55 Query SET autocommit=1

　　55 Query SET sql_mode='STRICT_TRANS_TABLES'

　　55 Prepare select * from axman_test where name = ?

　　55 Execute select * from axman_test where name = 'axman' _cke_saved_name = 'axman' or 1==1'

　　111028 10:10:24 55 Close stmt

　　55 Prepare select * from axman_test where name = ?

　　55 Execute select * from axman_test where name = 'axman' _cke_saved_name = 'axman'

　　55 Close stmt

　　55 Quit

　　55 Quit

同一個SQL語句發生了兩次預編譯。這不是我們想要的效果，要想對同一SQL語句多次執行不是每次都預編譯，就要使用cachePrepStmts=true，這個選項可以讓JVM端緩存每個SQL語句的預編譯結果，說白了就是以SQL語句為key, 將預編譯結果緩存起來，下次遇到相同的SQL語句時作為key去get一下看看有沒有這個SQL語句的預編譯結果，有就直接合出來用。我們還是以事實來說明：

上面的代碼只修改String url = "jdbc:mysql://localhost:3306/mysql?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=25&prepStmtCacheSqlLimit=256";

這行代碼中有其它參數自己去讀文檔，我不多啰嗦，執行的結果：

　　111028 10:27:23 58 Connect root@localhost on mysql

　　58 Query /* mysql-connector-java-5.1.18 ( Revision: [email protected] ) */SHOW VARIABLES WHERE Variable_name ='language' _cke_saved_name ='language' OR Variable_name = 'net_write_timeout' _cke_saved_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' _cke_saved_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' _cke_saved_name = 'wait_timeout' OR Variable_name = 'character_set_client' _cke_saved_name = 'character_set_client' OR Variable_name = 'character_set_connection' _cke_saved_name = 'character_set_connection' OR Variable_name = 'character_set' _cke_saved_name = 'character_set' OR Variable_name = 'character_set_server' _cke_saved_name = 'character_set_server' OR Variable_name = 'tx_isolation' _cke_saved_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' _cke_saved_name = 'transaction_isolation' OR Variable_name = 'character_set_results' _cke_saved_name = 'character_set_results' OR Variable_name = 'timezone' _cke_saved_name = 'timezone' OR Variable_name = 'time_zone' _cke_saved_name = 'time_zone' OR Variable_name = 'system_time_zone' _cke_saved_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' _cke_saved_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' _cke_saved_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' _cke_saved_name = 'net_buffer_length' OR Variable_name = 'sql_mode' _cke_saved_name = 'sql_mode' OR Variable_name = 'query_cache_type' _cke_saved_name = 'query_cache_type' OR Variable_name = 'query_cache_size' _cke_saved_name = 'query_cache_size' OR Variable_name = 'init_connect' _cke_saved_name = 'init_connect'

　　58 Query /* mysql-connector-java-5.1.18 ( Revision: [email protected] ) */SELECT @@session.auto_increment_increment

　　58 Query SHOW COLLATION

　　58 Query SET NAMES latin1

　　58 Query SET character_set_results = NULL

　　58 Query SET autocommit=1

　　58 Query SET sql_mode='STRICT_TRANS_TABLES'

　　58 Prepare select * from axman_test where name = ?

　　58 Execute select * from axman_test where name = 'axman' _cke_saved_name = 'axman' or 1==1'

　　111028 10:27:24 58 Execute select * from axman_test where name = 'axman' _cke_saved_name = 'axman'

　　58 Quit

注意僅發生一次預編譯，盡管代碼本身在第一次執行后關閉了ps.close();但因為使用了cachePrepStmts=true,底層并沒有真實關閉。

五、即使沒有開啟MySQL的預編譯，堅持使用PreparedStatement仍然非常必要。

在第三節的最后我說到"注意我的第一條語句select * from axman_test where name = 'axman' _cke_saved_name = 'axman' or 1==1'，下面還會說到它。"，現在我們回過頭來看，即使沒有開啟MySQL端的預編譯，我們仍然要堅持使用PreparedStatement，因為JVM端對PreparedStatement的SQL語句進行了參數化，即用占位符替換參數，以后任何內容輸入都是字符串或其它類型的值，而不會和原始的SQL語句拚接產生SQL注入，對字符串中的任何字符都會做檢查，如果可能是SQL語句使用的標識符，會進行轉義。然后發送一個合法的安全的SQL語句給數據庫執行。