预处理语句

预处理语句是一种将多个仅有参数不同的 SQL 语句进行模板化的语句,它让 SQL 语句与参数进行了分离。可以用它提升 SQL 语句的:

  • 安全性:因为参数和语句已经分离,所以避免了 SQL 注入攻击的风险。
  • 性能:因为语句在 TiDB 端被预先解析,后续执行只需要传递参数,节省了完整 SQL 解析、拼接 SQL 语句字符串以及网络传输的代价。

在大部分的应用程序中,SQL 语句是可以被枚举的,可以使用有限个 SQL 语句来完成整个应用程序的数据查询,所以使用预处理语句是最佳实践之一。

SQL 语法

本节将介绍创建、使用及删除预处理语句的 SQL 语法。

创建预处理语句

  1. PREPARE {prepared_statement_name} FROM '{prepared_statement_sql}';
参数描述
{prepared_statement_name}预处理语句名称
{prepared_statement_sql}预处理语句 SQL,以英文半角问号做占位符

你可查看 PREPARE 语句 获得更多信息。

使用预处理语句

预处理语句仅可使用用户变量作为参数,因此,需先使用 SET 语句 设置变量后,供 EXECUTE 语句 调用预处理语句。

  1. SET @{parameter_name} = {parameter_value};
  2. EXECUTE {prepared_statement_name} USING @{parameter_name};
参数描述
{parameter_name}用户参数名
{parameter_value}用户参数值
{prepared_statement_name}预处理语句名称,需和创建预处理语句中定义的名称一致

你可查看 EXECUTE 语句 获得更多信息。

删除预处理语句

  1. DEALLOCATE PREPARE {prepared_statement_name};
参数描述
{prepared_statement_name}预处理语句名称,需和创建预处理语句中定义的名称一致

你可查看 DEALLOCATE 语句 获得更多信息。

例子

本节以使用预处理语句,完成查询数据和插入数据两个场景的示例。

查询示例

例如,需要查询 Bookshop 应用 中,id 为 1 的书籍信息。

  • SQL
  • Java

使用 SQL 查询示例:

  1. PREPARE `books_query` FROM 'SELECT * FROM `books` WHERE `id` = ?';

运行结果为:

  1. Query OK, 0 rows affected (0.01 sec)
  1. SET @id = 1;

运行结果为:

  1. Query OK, 0 rows affected (0.04 sec)
  1. EXECUTE `books_query` USING @id;

运行结果为:

  1. +---------+---------------------------------+--------+---------------------+-------+--------+
  2. | id | title | type | published_at | stock | price |
  3. +---------+---------------------------------+--------+---------------------+-------+--------+
  4. | 1 | The Adventures of Pierce Wehner | Comics | 1904-06-06 20:46:25 | 586 | 411.66 |
  5. +---------+---------------------------------+--------+---------------------+-------+--------+
  6. 1 row in set (0.05 sec)

使用 Java 查询示例:

  1. // ds is an entity of com.mysql.cj.jdbc.MysqlDataSource
  2. try (Connection connection = ds.getConnection()) {
  3. PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM `books` WHERE `id` = ?");
  4. preparedStatement.setLong(1, 1);
  5. ResultSet res = preparedStatement.executeQuery();
  6. if(!res.next()) {
  7. System.out.println("No books in the table with id 1");
  8. } else {
  9. // got book's info, which id is 1
  10. System.out.println(res.getLong("id"));
  11. System.out.println(res.getString("title"));
  12. System.out.println(res.getString("type"));
  13. }
  14. } catch (SQLException e) {
  15. e.printStackTrace();
  16. }

插入示例

还是使用 books 表 为例,需要插入一个 titleTiDB Developer Guide, typeScience & Technology, stock100, price0.0, published_at插入的当前时间 的书籍信息。需要注意的是,books 表的主键包含 AUTO_RANDOM 属性,无需指定它。如果你对插入数据还不了解,可以在插入数据一节了解更多数据插入的相关信息。

  • SQL
  • Java

使用 SQL 插入数据示例如下:

  1. PREPARE `books_insert` FROM 'INSERT INTO `books` (`title`, `type`, `stock`, `price`, `published_at`) VALUES (?, ?, ?, ?, ?);';

运行结果为:

  1. Query OK, 0 rows affected (0.03 sec)
  1. SET @title = 'TiDB Developer Guide';
  2. SET @type = 'Science & Technology';
  3. SET @stock = 100;
  4. SET @price = 0.0;
  5. SET @published_at = NOW();

运行结果为:

  1. Query OK, 0 rows affected (0.04 sec)
  1. EXECUTE `books_insert` USING @title, @type, @stock, @price, @published_at;

运行结果为:

  1. Query OK, 1 row affected (0.03 sec)

使用 Java 插入数据示例如下:

  1. try (Connection connection = ds.getConnection()) {
  2. String sql = "INSERT INTO `books` (`title`, `type`, `stock`, `price`, `published_at`) VALUES (?, ?, ?, ?, ?);";
  3. PreparedStatement preparedStatement = connection.prepareStatement(sql);
  4. preparedStatement.setString(1, "TiDB Developer Guide");
  5. preparedStatement.setString(2, "Science & Technology");
  6. preparedStatement.setInt(3, 100);
  7. preparedStatement.setBigDecimal(4, new BigDecimal("0.0"));
  8. preparedStatement.setTimestamp(5, new Timestamp(Calendar.getInstance().getTimeInMillis()));
  9. preparedStatement.executeUpdate();
  10. } catch (SQLException e) {
  11. e.printStackTrace();
  12. }

可以看到,JDBC 帮你管控了预处理语句的生命周期,而无需你在应用程序里手动使用预处理语句的创建、使用、删除等。但值得注意的是,因为 TiDB 兼容 MySQL 协议,在客户端使用 MySQL JDBC Driver 的过程中,其默认配置并非开启 服务端 的预处理语句选项,而是使用客户端的预处理语句。你需要关注以下配置项,来获得在 JDBC 下 TiDB 服务端预处理语句的支持,及在你的使用场景下的最佳配置:

参数作用推荐场景推荐配置
useServerPrepStmts是否使用服务端开启预处理语句支持在需要多次使用预处理语句时true
cachePrepStmts客户端是否缓存预处理语句useServerPrepStmts=truetrue
prepStmtCacheSqlLimit预处理语句最大大小(默认 256 字符)预处理语句大于 256 字符时按实际预处理语句大小配置
prepStmtCacheSize预处理语句最大缓存数量 (默认 25 条)预处理语句数量大于 25 条时按实际预处理语句数量配置

在此处给出一个较为的通用场景的 JDBC 连接字符串配置,以 Host: 127.0.0.1,Port: 4000,用户: root,密码: 空,默认数据库: test为例:

  1. jdbc:mysql://127.0.0.1:4000/test?user=root&useConfigs=maxPerformance&useServerPrepStmts=true&prepStmtCacheSqlLimit=2048&prepStmtCacheSize=256&rewriteBatchedStatements=true&allowMultiQueries=true

你也可以查看插入行一章,来查看是否需要在插入数据场景下更改其他 JDBC 的参数。

有关 Java 的完整示例,可参阅: