mysql表一对多同步到es，只有c表的status字段无法同步

canal版本为1.1.5 release

数据库关系

CREATE TABLE c ( cid int(11) NOT NULL AUTO_INCREMENT, aid int(11) NOT NULL, status tinyint(1) NOT NULL, ctime timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0), PRIMARY KEY (cid), INDEX aid(aid) );

CREATE TABLE a ( id int(11) NOT NULL AUTO_INCREMENT, code varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, stats tinyint(1) NOT NULL, ctime timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0), PRIMARY KEY (id) USING BTREE );

mapping

PUT /test { "mappings" : { "properties" : { "a" : { "properties" : { "code" : { "type" : "keyword" }, "ctime" : { "type" : "date", "format" : "yyyy-MM-dd HH:mm:ss || strict_date_optional_time || epoch_millis" }, "stats" : { "type" : "byte" } } }, "aid" : { "type" : "long" }, "cid" : { "type" : "long" }, "ctime" : { "type" : "date", "format" : "yyyy-MM-dd HH:mm:ss || strict_date_optional_time || epoch_millis" }, "id" : { "type" : "long" }, "status" : { "type" : "byte" } } } }

sync.yml

dataSourceKey: im-order destination: order groupId: ord esMapping: _index: test _id: _id upsert: false sql: "select c.cid _id, ai.id aid, c.cid id, c.status status, c.ctime ctime, ai.ainfo a from c c left join (select id, json_object('code', code, 'ctime', DATE_FORMAT(ctime, '%Y-%m-%dT%T+08:00'), 'stats', stats) ainfo from a) ai on c.aid = ai.id" objFields: a: object commitBatch: 3000

原提问者GitHub用户caiyt-byte