MySQL（prepare开启）
在Java编程中，应用代码绝大多数使用了PreparedStatement,无论你是直接使用JDBC还是使用框架。
在Java编程中，绝大多数使用了使用了PreparedStatement连接MySQL的应用代码没有启用预编译,无论你是直接使用JDBC还是使用框架。在我所能见到的项目中，几乎没有见过启用MySQL预编译功能的。网上更有文章说MySQL不支持预编译，实在是害人不浅。
要想知道你的应用是否真正的使用了预编译，请执行：show global status like '%prepare%';看看曾经编译过几条，当前Prepared_stmt_count 是多少。大多数是0吧？
这篇文章分以下几个方面：一.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 = ?';
set @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 = ?'
51 Prepare select * from axman_test where name = ?
51 Query set @name='axman'
111028 9:25:08 51 Query execute myPreparedStmt using @name
51 Execute select * from axman_test where 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 = ?'");
stmt.execute("set @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' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_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 = ?'
52 Prepare select * from axman_test where name = ?
52 Query set @name='axman'
52 Query execute mystmt using @name
52 Execute select * from axman_test where name = 'axman'
52 Quit
三.默认的PrearedStatement不能开启MySQL预编译功能：
虽然第二节中我们通过JDBC手工指定MySQL进行预编译，但是PrearedStatement却并不自动帮我们做这件事。
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 = ?");
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' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_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\' or 1==1'
111028 9:54:04 53 Query select * from axman_test where name = 'axman'
53 Quit
两条语句都是直接执行，而没有预编译。注意我的第一条语句select * from axman_test where 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' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_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\' or 1==1'
111028 10:04:53 54 Execute select * from axman_test where name = 'axman'
54 Close stmt
54 Quit
如果useServerPrepStmts=true，ConneciontImpl在prepareStatement时会产生一个ServerPreparedStatement.在这个ServerPreparedStatement对象构造时首先会把当前SQL语句发送给MySQL进行预编译，然后将返回的结果缓存起来，其中包含预编译的名称（我们可以看成是当前SQL语句编译后的函数名），签名（参数列表），然后执行的时候就会直接把参数传给这个函数请求MySQL执行这个函数。否则返回的是客户端预编译语句，它仅做参数化工作，见第五节。
ServerPreparedStatement在请求预编译和执行预编译后的SQL 函数时，虽然和我们上面手工预编译工作相同，但它与MySQL交互使用的是压缩格式，如prepare指令码是22，这样可以减少交互时传输的数据量。
注意上面的代码中，两次执行使用的是同一个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 = ?");
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 = ?");
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' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_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\' 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'
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: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
58 Query /* mysql-connector-java-5.1.18 ( Revision: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq ) */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\' or 1==1'
111028 10:27:24 58 Execute select * from axman_test where name = 'axman'
58 Quit
注意仅发生一次预编译，尽管代码本身在第一次执行后关闭了ps.close();但因为使用了cachePrepStmts=true,底层并没有真实关闭。
千万注意，同一条SQL语句尽量在一个全局的地方定义，然后在不同地方引用，这样做一是为了DBA方便地对SQL做统一检查和优化，就象IBatis把SQL语句定义在XML文件中一样。二是同一语句不同写法，即使空格不同，大小写不同也会重新预编译，因为JVM端缓存是直接以SQL本身为key而不会对SQL格式化以后再做为key。
我们来看下面的输出：
35 Prepare select * from axman_test where name = ?
35 Execute select * from axman_test where name = 'axman\' or 1==1'
111029 9:54:31 35 Prepare select * FROM axman_test where name = ?
35 Execute select * FROM axman_test where name = 'axman'
第一条语句和第二条语句的差别是FROM在第二条语句中被大写了，这样还是发生了两次预编译。
37 Prepare select * from axman_test where name = ?
37 Execute select * from axman_test where name = 'axman\' or 1==1'
111029 9:59:00 37 Prepare select * from axman_test where name = ?
37 Execute select * from axman_test where name = 'axman'
这里两条语句只是第二条的from后面多了个空格，因为你现在看到是HTML格式，如果不加转义符，两个空格也显示一个空格，所以你能可看不到区别，但你可以在自己的机器上试一下。
五.即使没有开启MySQL的预编译，坚持使用PreparedStatement仍然非常必要。
在第三节的最后我说到"注意我的第一条语句select * from axman_test where name = 'axman\' or 1==1'，下面还会说到它。"，现在我们回过头来看，即使没有开启MySQL端的预编译，我们仍然要坚持使用PreparedStatement，因为JVM端对PreparedStatement的SQL语句进行了参数化，即用占位符替换参数，以后任何内容输入都是字符串或其它类型的值，而不会和原始的SQL语句拚接产生SQL注入，对字符串中的任何字符都会做检查，如果可能是SQL语句使用的标识符，会进行转义。然后发送一个合法的安全的SQL语句给数据库执行。
对应prepare 在服务端的配置 参考mysql官方：
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_prepared_stmt_count
-
Command-Line Format --max_prepared_stmt_count=# System Variable Name max_prepared_stmt_count Variable Scope Global Dynamic Variable Yes Permitted Values Type integer Default 16382 Min Value 0 Max Value 1048576 This variable limits the total number of prepared statements in the server. It can be used in environments where there is the potential for denial-of-service attacks based on running the server out of memory by preparing huge numbers of statements. If the value is set lower than the current number of prepared statements, existing statements are not affected and can be used, but no new statements can be prepared until the current number drops below the limit. The default value is 16,382. The permissible range of values is from 0 to 1 million. Setting the value to 0 disables prepared statements.
