tencent cloud

All product documents
Data Transfer Service
DocumentationData Transfer ServiceData SyncSync to MySQL seriesMySQL Series Advanced Sync OperationsWHERE Condition Filtering
WHERE Condition Filtering
Download PDF
Last updated: 2024-12-25 15:58:58
WHERE Condition Filtering
Last updated: 2024-12-25 15:58:58
Download PDF

Overview

When you configure synchronization tasks, DTS supports setting WHERE condition filters for a table or multiple tables in batch. Only data that meets the specified rules will be synchronized to the target database, allowing users to flexibly split data.

Application Scope

Batch WHERE condition configuration is currently supported for synchronization linkages between MySQL/MariaDB/Percona/TDSQL-C MySQL/TDSQL TDStore/TDSQL MySQL, as well as for synchronization linkages from MySQL/MariaDB/Percona/TDSQL-C MySQL to Kafka.
For synchronization linkage from TDSQL MySQL to Kafka, only single-table WHERE condition configuration is supported, and batch configuration is not available.

Constraints and Limitations

1. WHERE condition configuration is not supported for entire databases or tables. When multiple tables require WHERE condition filtering, select each table individually and configure the WHERE condition rules.
2. Interaction description between column filtering and WHERE condition filtering:
If column filtering is applied to a table, and a WHERE rule is also configured for the same table, ensure that the column names specified in the WHERE rule are included in the tables to be synchronized. Otherwise, the WHERE rule may be configured successfully, but errors will occur when the task starts later.

Explanation of Where Condition Rules

1. After WHERE filtering conditions are configured, only data that meets the configured rules in a table will be synchronized to the target database.
For INSERT operations, only data that meets the filtering conditions will be synchronized to the target database.
For DELETE operations, only data that meets the filtering conditions will be synchronized to the target database.
For UPDATE operations, users can configure different rules based on their needs.
Both pre-update and post-update data meet the criteria: Indicates that both the pre-update and post-update data should meet the filtering conditions to be synchronized to the target database.
Pre-update meets the criteria: Indicates that the pre-update data meeting the filtering conditions can be synchronized to the target database.
Post-update meets the criteria: Indicates that the post-update data meeting the filtering conditions can be synchronized to the target database.
2. The rules entered for the WHERE condition should be a valid BOOL expression. These rules are stricter compared to MySQL, and certain syntaxes that are supported in MySQL but may generate warnings, such as comparing strings with numbers (c1 + c2 < "abc"), are not supported. Logical, arithmetic, and comparison operations, along with their precedence, follow MySQL conventions. Parentheses can be used to modify operation precedence, and the handling of NULL operands is consistent with MySQL behavior. The DTS system validates the filtering conditions entered and provides alarms for any invalid expressions.
3. The basic operation rules for WHERE conditions are as follows:
Supports referencing column names as variables.
Supports logical operations, including NOT, AND, OR, XOR, &&, and ||.
Supports numeric types (signed/unsigned integers such as TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT; floating-point types such as FLOAT and DOUBLE; and precise types such as DECIMAL) and their arithmetic operations (+, -, *, /, %, DIV, and MOD) and comparison operations (=, !=, >, <, >=, <=, <>, and <=>).
Supports string types (CHAR and VARCHAR) and their comparison operations (binary comparison).
Supports date types (DATE, DATETIME, and TIMESTAMP) and their comparison operations.
Supports time types (TIME) and their comparison operations. Comparisons between date/time variables and strings are also supported, where the string is converted into a date/time constant and evaluated according to date/time comparison rules.
Allows users to specify the time zone for TIMESTAMP constants. For example, if c1 is a TIMESTAMP column and the user selects the time zone +08:00, the filtering rule 'c1 > "2016-10-01 09:00:00"' will be interpreted as 'c1 > "2016-10-01 09:00:00 +08:00"'.

Examples of WHERE Condition Implementation

Below are examples illustrating the operation results in different scenarios, where ID serves as the primary key for all datasets.
Example 1: INSERT operation



Example 2: DELETE operation



Example 3: UPDATE operation (Using both pre-update and post-update data meet the criteria as an example.)




Directions

1. In the Synchronization Task settings, check Synchronization Objects on the Set sync options and objects page.
Note:
You need to select individual tables one by one to enable setting WHERE conditions in subsequent steps. Selecting the entire databases or whole tables does not support setting WHERE conditions.
2. Configure data processing rules and enable WHERE condition filtering.
2.1 Select the objects.
From the synchronization objects selected in the previous step, further select the specific objects that require WHERE condition settings.
2.2 Configure filtering conditions and confirm.
Set UTC time zone: Specify the time zone for comparing TIMESTAMP-type fields. It is recommended to align this with the database time zone.
UPDATE data conditions: Choose from Both pre-update and post-update data meet the criteria, Pre-update meets the criteria, and Post-update meets the criteria.
Condition expression: Enter the WHERE condition expression. For specific rules, see the preceding Explanation of Where Condition Rules section in this chapter. After the WHERE condition is configured, click Check Conditions to validate the input for correctness, and then click Generate Filtering Rule.
3. Confirm the filtering rule, and if it is correct, click Save and Next to proceed with the next steps in the synchronization task process.
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback

Contact Us

Contact our sales team or business advisors to help your business.

Contact Us
Technical Support

Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

Submit a Ticket
7x24 Phone Support
Hong Kong, China
+852 800 906 020 (Toll Free)
United States
+1 844 606 0804 (Toll Free)
United Kingdom
+44 808 196 4551 (Toll Free)
Canada
+1 888 605 7930 (Toll Free)
Australia
+61 1300 986 386 (Toll Free)
EdgeOne hotline
+852 300 80699
More local hotlines coming soon