Data Synchronization with DM: Mapping Upstream Field Data to a Different Value in Downstream Data

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 数据同步dm,上游字段数据映射到下游数据时,改成另一个值

| username: TiDBer_QHSxuEa1

Are there any methods to achieve data mapping during data synchronization with DM, where the upstream field data is changed to another value in the downstream data?
For example, if the upstream data is “男” (male), it becomes “male” in the downstream.

| username: 昵称想不起来了 | Original post link

I haven’t seen this feature configuration before, it seems like additional tools are needed for real-time modification.

| username: tidb菜鸟一只 | Original post link

Currently, DM does not support this feature. If it is TiCDC, the downstream can modify the field content through Kafka before writing to the downstream database.

| username: Clover | Original post link

Version 7.3 is said to be supported.

| username: redgame | Original post link

Not really. This kind.

| username: 有猫万事足 | Original post link

Your TiDB version is outdated. There is no good solution for version 6.5.1.

In version 7.1, you can use generated columns to work around the issue.

use test;
CREATE TABLE `t` (
  `f1` varchar(30) NOT NULL,
  `f1_en` VARCHAR(64) AS (case f1 when '男' then 'male' end) STORED -- Stored generated column, when the value of f1 is '男', the value here will be 'male'
) ENGINE=InnoDB;

insert into t(f1) values('男');

select * from t;

You can see that when you insert t1=‘男’, the expression set in the generated column will automatically generate a column with ‘male’.
In the absence of column permissions, you can use a view to replace f1 with f1_en.

DM keeps the inserted values unchanged.

Documentation on generated columns:

Some of my own practices:

| username: TiDB_C罗 | Original post link

You can use SQL expressions to filter DML, but what you have here is a rewrite.