tencent cloud

TDSQL Boundless

Release Notes
Product Introduction
Overview
Scenarios
Product Architecture
Instance Types
Compatibility Notes
Usage specification recommendations
Kernel Features
Kernel Overview
Kernel Version Release Notes
Functionality Features
Performance Features
Billing
Billing Overview
Purchase Method
Pricing Details
Renewal
Overdue Payments
Refund
Getting Started
Creating an Instance
Connect to Instances
User Guide
Data Migration
Data Subscription
Instance Management
Parameter Configuration
Account Management
Security Group
Backup and Restoration
Database Auditing
Tag Management
Use Cases
Technical Evolution and Usage Practices of Online DDL
Lock Mechanism Analysis and Troubleshooting Practices
Data Intelligent Scheduling and Related Practices for Performance Optimization
TDSQL Boundless Selection Guide and Practical Tutorial
Developer Guide
Developer Guide (MySQL Compatibility Mode)
Developer Guide (HBase Compatibility Mode)
Performance Tuning
Performance Tuning Overview
SQL Tuning
DDL Tuning
Performance White Paper
Performance Overview
TPC-C Test
Sysbench Test
API Documentation
History
Introduction
API Category
Making API Requests
Instance APIs
Security Group APIs
Task APIs
Backup APIs
Rollback APIs
Parameter APIs
Database APIs
Data Types
Error Codes
General Reference
System Architecture
SQL Reference
Database Parameter Description
TPC-H benchmark data model reference
Error Code Information
Security and Compliance
FAQs
Agreements
Service Level Agreement
Terms of Service
Privacy Policy
Data Processing And Security Agreement
Contact Us
Glossary
DocumentaçãoTDSQL BoundlessDeveloper GuideDeveloper Guide (MySQL Compatibility Mode)Data WritingPrepared Statement

Prepared Statement

PDF
Modo Foco
Tamanho da Fonte
Última atualização: 2026-03-30 09:37:14
This article introduces the concept, SQL syntax, and usage of prepared statements in TDSQL Boundless. By templating SQL statements, prepared statements implement separation between statements and parameters, which can effectively improve query performance and guard against SQL injection attacks.

Overview of Prepared Statements

Prepared statements are a technique that templates multiple SQL statements differing only in parameters. They separate the structure of SQL statements from actual parameters, allowing the database server to parse and compile the SQL template first. During subsequent executions, only parameters need to be supplied.
TDSQL Boundless is compatible with MySQL 8.0 protocol and fully supports the prepared statements feature. Prepared statements offer the following benefits:
Enhanced Security: Separation of parameters from SQL statements during transmission fundamentally prevents the risk of SQL injection attacks.
Enhanced Performance: SQL statements only need to be parsed and compiled once during the initial execution. When the same statement is executed subsequently, only different parameter values need to be passed, saving the overhead of repeated parsing.
Reduced Network Overhead: During execution, only parameter values are transmitted instead of the complete SQL text repeatedly, decreasing network traffic between the client and server.
In application development, using prepared statements to execute SQL operations is a recommended best practice.

Prepared statements workflow

The use of prepared statements is divided into three stages:
1. Prepare: The client sends an SQL statement template containing placeholders to the server-side. The server parses and compiles the statement, generates an execution plan and caches it, then returns a statement identifier.
2. Execute: Reference the prepared statement using the statement identifier and pass the actual parameter values. The server binds the parameter values to the corresponding placeholders, executes the statement, and returns the result.
3. Release (Deallocate): When a prepared statement is no longer needed, the client sends a deallocation command, and the server reclaims the resources occupied by that statement.

SQL Syntax

TDSQL Boundless supports managing the lifecycle of prepared statements—including creation, execution, and deallocation—directly through SQL statements.

Create a prepared statement

Use the PREPARE statement to create a prepared statement. In the SQL template, the English half-width question mark ? is used as a parameter placeholder.
Syntax Format
PREPARE stmt_name FROM preparable_stmt;
Parameter description
Parameter
Description
stmt_name
The name of the prepared statement, used for subsequent references to this statement. The name must be unique within the current session and is case-insensitive.
preparable_stmt
The SQL statement template, which can be a string literal or a user variable. Use ? as a parameter placeholder.
Example
The following example creates a prepared statement that queries employee information by id:
PREPARE stmt_query FROM 'SELECT * FROM employees WHERE id = ?';
SQL templates can also be passed via user variables:
SET @sql = 'SELECT * FROM employees WHERE id = ?';
PREPARE stmt_query FROM @sql;
Note:
Prepared statements are scoped to the current session. When the session ends, all prepared statements defined within the session are automatically released.

Execute the prepared statement

Use the EXECUTE statement to execute the created prepared statement, and pass parameter values via the USING clause. Parameter values must be passed through user variables.
Syntax Format
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
Parameter description
Parameter
Description
stmt_name
The name of the prepared statement to be executed, which must be a statement already created via PREPARE
@var_name
User variables used to pass parameter values to the prepared statement. The number of variables must match the number of ? placeholders in the SQL template.
Example
Execute the prepared statement created above to query employee information with id being 1:
SET @id = 1;
EXECUTE stmt_query USING @id;
The execution result example is as follows:
+----+----------+------------+--------+
| id | name     | department | salary |
+----+----------+------------+--------+
|  1 | Zhang San| R&D        |  15000 |
+----+----------+------------+--------+
1 row in set (0.00 sec)
By passing different parameter values, you can repeatedly execute the same prepared statement:
SET @id = 2;
EXECUTE stmt_query USING @id;

Delete prepared statement

Use the DEALLOCATE PREPARE statement to release created prepared statements and reclaim server resources.
Syntax Format
{DEALLOCATE | DROP} PREPARE stmt_name;
Parameter description
Parameter
Description
stmt_name
The name of the prepared statement to be released
Example
DEALLOCATE PREPARE stmt_query;
Note:
If a session connection is terminated without releasing prepared statements, the server will automatically release all prepared statements in that session. However, in scenarios involving persistent connections or connection pools, it is recommended to proactively call DEALLOCATE PREPARE to release resources after using prepared statements, avoiding occupying server memory.

Usage Examples

The following complete example demonstrates the usage of prepared statements in query and data operation scenarios.

Query Example

The following example demonstrates how to use prepared statements to query book information with the specified id in the books table.
1. Creating a prepared statement:
PREPARE stmt_select_book FROM 'SELECT * FROM books WHERE id = ?';
2. Setting parameters and executing the query:
SET @book_id = 1;
EXECUTE stmt_select_book USING @book_id;
3. Executing again with different parameters:
SET @book_id = 5;
EXECUTE stmt_select_book USING @book_id;
4. Release prepared statement:
DEALLOCATE PREPARE stmt_select_book;

Insert example

The following example demonstrates how to use prepared statements to insert a piece of data into the books table.
1. Creating a prepared statement:
PREPARE stmt_insert_book FROM 'INSERT INTO books (title, type, stock, price, published_at) VALUES (?, ?, ?, ?, ?)';
2. Setting parameters and executing the insertion:
SET @title = 'TDSQL Boundless Practical Guide';
SET @type = 'Technology';
SET @stock = 100;
SET @price = 89.00;
SET @published_at = '2026-01-01';
EXECUTE stmt_insert_book USING @title, @type, @stock, @price, @published_at;
3. Release prepared statement:
DEALLOCATE PREPARE stmt_insert_book;

Update example

The following example demonstrates how to use prepared statements to update the inventory for a specified book in the books table.
1. Creating a prepared statement:
PREPARE stmt_update_stock FROM 'UPDATE books SET stock = ? WHERE id = ?';
2. Setting parameters and executing the update:
SET @new_stock = 200;
SET @book_id = 1;
EXECUTE stmt_update_stock USING @new_stock, @book_id;
3. Release prepared statement:
DEALLOCATE PREPARE stmt_update_stock;

Delete example

The following example demonstrates how to use prepared statements to delete the book record with the specified id in the books table.
1. Creating a prepared statement:
PREPARE stmt_delete_book FROM 'DELETE FROM books WHERE id = ?';
2. Setting parameters and executing the deletion:
SET @book_id = 10;
EXECUTE stmt_delete_book USING @book_id;
3. Release prepared statement:
DEALLOCATE PREPARE stmt_delete_book;

Dynamic SQL Example

Prepared statements support passing dynamically constructed SQL strings through user variables, making them suitable for scenarios where SQL content needs to be determined at runtime.
The following example demonstrates how to dynamically check whether the current database supports a specific storage engine variable:
-- Dynamically construct a query statement
SET @query_str = 'SELECT COUNT(*) INTO @result FROM performance_schema.session_variables WHERE VARIABLE_NAME = ?';
PREPARE stmt_check FROM @query_str;

-- Set parameters and execute
SET @var_name = 'rocksdb_bulk_load';
EXECUTE stmt_check USING @var_name;

-- View the results
SELECT @result;

-- Release the statement
DEALLOCATE PREPARE stmt_check;
Note:
mysqldump tool uses a similar PREPARE...FROM @variable approach to dynamically construct SQL statements during data export, which is used to detect whether the target server supports a specific storage engine feature.

Supported SQL statement types

In TDSQL Boundless, prepared statements support the following types of SQL statements:
Data Manipulation Statements
SELECT
INSERT
UPDATE
DELETE
REPLACE
CALL
DO
Data Definition Statements
CREATE DATABASE,CREATE TABLE,CREATE TEMPORARY TABLE,CREATE INDEX,CREATE USER
DROP DATABASE,DROP TABLE,DROP TEMPORARY TABLE,DROP INDEX,DROP USER,DROP VIEW
ALTER USER
RENAME TABLE,RENAME USER
TRUNCATE TABLE
Database Management Statements
ANALYZE TABLE,CHECKSUM TABLE,OPTIMIZE TABLE,REPAIR TABLE
GRANT,REVOKE ALL PRIVILEGES
SHOW CREATE TABLE,SHOW CREATE PROCEDURE,SHOW CREATE FUNCTION,SHOW CREATE VIEW,SHOW CREATE EVENT
SHOW BINLOG EVENTS,SHOW MASTER STATUS,SHOW BINARY LOGS,SHOW SLAVE STATUS
INSTALL PLUGIN,UNINSTALL PLUGIN
KILL
COMMIT
SET OPTION
RESET MASTER,RESET SLAVE
SLAVE START,SLAVE STOP
Note:
SQL statements not listed above (such as LOAD DATA INFILE, USE, BEGIN, and so on) are not supported for execution via prepared statements.

Client-side APIs

In addition to SQL syntax, applications can also utilize the prepared statements feature through the MySQL client programming interface (C API). The C API communicates with the server using the MySQL binary protocol, which offers higher transmission efficiency than the text-based protocol.

C API Calling Workflow

The following is the typical invocation process for prepared statements in the C API:
1. Use mysql_stmt_init() to initialize the prepared statement handle.
2. Use mysql_stmt_prepare() to send the SQL template to the server for preprocessing.
3. Use mysql_stmt_bind_param() to bind parameter values.
4. Use mysql_stmt_execute() to execute the prepared statement.
5. Use mysql_stmt_bind_result() and mysql_stmt_fetch() to obtain query results (applicable only to query statements).
6. Use mysql_stmt_close() to close the statement handle and release resources.
The following are descriptions of the main API functions:
Function
Description
mysql_stmt_init()
Initialize a MYSQL_STMT handle for subsequent prepared statement operations.
mysql_stmt_prepare()
Send the SQL statement template to the server for parsing and compilation.
mysql_stmt_bind_param()
Bind variables in the application to the ? placeholder in the SQL template.
mysql_stmt_execute()
Execute the prepared statement.
mysql_stmt_store_result()
Fetch the complete result set from the server to the client cache.
mysql_stmt_result_metadata()
Obtain metadata information (column names, types, and so on) for the results returned by prepared statements.
mysql_stmt_fetch()
Fetch result set data row by row.
mysql_stmt_close()
Close the prepared statement handle to release client-side and server-side resources.
mysql_stmt_errno()
Return the error code for the most recent operation performed on the prepared statement.
mysql_stmt_error()
Return the error message for the most recent operation performed on the prepared statement.
mysql_stmt_attr_set()
Set statement attributes, such as cursor type CURSOR_TYPE_READ_ONLY.
mysql_stmt_next_result()
Used to handle multiple result set scenarios; obtain the next result set.

Cursor mode

The C API supports the use of server-side cursors in prepared statements. By setting the statement attribute STMT_ATTR_CURSOR_TYPE to CURSOR_TYPE_READ_ONLY, the read-only cursor mode can be enabled. In this mode, the result set will not be loaded in full into client memory but fetched row by row on demand, making it suitable for handling queries with large datasets.

Programming language driver

MySQL drivers for all major programming languages encapsulate the prepared statements feature, allowing developers to utilize it without directly invoking the C API. Below are the common APIs for prepared statements in each language:
Programming languages
Driver or framework
Prepared Statements API
Java
JDBC
java.sql.PreparedStatement
Python
MySQL Connector/Python
cursor.execute(sql, params)
Go
go-sql-driver/mysql
db.Prepare() + stmt.Exec()
PHP
PDO
PDO::prepare() + PDOStatement::execute()
Node.js
mysql2
connection.execute(sql, values)
C/C++
MySQL C API
mysql_stmt_prepare() + mysql_stmt_execute()
Note:
TDSQL Boundless is compatible with the MySQL 8.0 protocol, and standard MySQL client drivers can be used directly without modification.

JDBC Connection Best Practices

When Java JDBC is used to connect to TDSQL Boundless, it is recommended to configure the following parameters to achieve optimal prepared statement performance.
Parameter
Recommended Value
Description
useServerPrepStmts
true
Enable server-side prepared statements. By default, JDBC uses client-emulated prepared statements; setting this to true switches to server-side prepared statements, thereby improving performance for repeated execution of identical statements.
cachePrepStmts
true
Enable client-side prepared statement caching to avoid redundant recreation of prepared statements upon each use.
prepStmtCacheSqlLimit
2048
The maximum SQL length for a single prepared statement (unit: bytes). The default value is 256, which may be insufficient for complex SQL statements; it is recommended to increase this value.
prepStmtCacheSize
256
The maximum number of client-side cached prepared statements. The default value is 25; it is recommended to adjust this based on the actual number of SQL statements used in the application.
Recommended JDBC Connection String Example
jdbc:mysql://<host>:<port>/<database>?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSqlLimit=2048&prepStmtCacheSize=256&rewriteBatchedStatements=true&allowMultiQueries=true
Replace <host>, <port>, and <database> with the actual connection information of your TDSQL Boundless instance.

Audit and Monitoring

TDSQL Boundless's audit log plugin records execution events of prepared statements. In the audit logs, operations on prepared statements generate event records of the following types:
Event type
Description
Prepare
The audit log records the creation operation of prepared statements, including the SQL template text.
Execute
The audit log records the execution operation of prepared statements, including the actual parameter values.
Query
The audit log records PREPARE, EXECUTE, and DEALLOCATE PREPARE statements executed via the text protocol.
Each event record in the audit log contains fields such as COMMAND_CLASS, CONNECTION_ID, STATUS, and SQLTEXT, which can be used for security auditing and execution analysis.

Prepared Statements Best Practices

The following are recommended practices for using prepared statements:
Always Use Parameterized Queries: Pass all external inputs through the parameter placeholder ? to avoid directly concatenating user input into SQL strings, effectively preventing SQL injection attacks.
Reuse Prepared Statements Properly: For SQL statements frequently executed in applications, create prepared statements once and execute them multiple times to fully utilize the performance benefits.
Release Unused Statements Promptly: In scenarios involving persistent connections or connection pools, promptly call DEALLOCATE PREPARE to release prepared statements after use, avoiding occupying server memory resources.
Enable Server-Side Prepared Statements: In the JDBC connection, set useServerPrepStmts to true to use server-side prepared statements instead of client emulation, thereby achieving higher execution efficiency.
Configure Client-Side Caching: Enable cachePrepStmts and properly set prepStmtCacheSize and prepStmtCacheSqlLimit to reduce the overhead of repeatedly creating prepared statements.

System Variable

The system variable max_prepared_stmt_count controls the total number of prepared statements that can concurrently exist on the server. If the upper limit is reached, new PREPARE operations will return an error. The current number of created prepared statements can be viewed via SHOW GLOBAL STATUS LIKE 'Prepared_stmt_count'.
Variable
Scope
Default Value
Scope
Description
max_prepared_stmt_count
GLOBAL
16382
0 ~ 4194304
The global total number of prepared statements allowed to exist concurrently on the server (across all sessions). When the limit is reached, new PREPARE operations will return ERROR 1461 (42000): Can't create more than max_prepared_stmt_count statements

Limitations

When using prepared statements, note the following considerations:
Prepared statements are scoped to the current session. They are isolated from each other across different sessions and cannot be used across sessions.
The number of parameter placeholders ? in a prepared statement has no fixed upper limit, but is limited by the max_allowed_packet system variable.
When a client connection is disconnected and reconnected, previously created prepared statements become invalid and must be recreated by re-executing PREPARE.
Prepared statements do not support SQL templates containing multiple statements (that is, multi-statement execution separated by semicolons ; is not supported).
When the PREPARE ... FROM @variable method is used to pass SQL text, the statement undergoes full syntax parsing during preparation. Therefore, ensure that the SQL syntax in the user variable is correct.
In distributed transaction scenarios, prepared statements and regular statements are subject to the same transaction management mechanism and can be mixed within a transaction.
Anterior: Data DeletionPróximo: Single-Table Queries

Ajuda e Suporte

Esta página foi útil?

comentários