版本:2.0.5
场景:本来是打算通过跨 mysql catalog 关联查询数据,但是发现 mysql 的 catalog 建了以后,过几天就会出现查询异常,表现就是一直查看不出来一直卡着,最后客户端超时断开链接,详见:查询Mysql Catalog异常
然后把我 mysql 的表同步到 doris,发现更奇怪的事情,一样的语句在跨 catalog 的时候可以正常,表迁过来就不行了
建表语句
CREATE TABLE `xxxx` (
`id` int(11) NULL,
`config` json NULL COMMENT '配置json'
) ENGINE=OLAP
UNIQUE KEY(`id`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"is_being_synced" = "false",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);
查询语句
select config
from xxx
where is_deleted = 0
and json_contains(config, 'TC', '$.s1')
and json_contains(config, 1, '$.c1')
and json_contains(config, 56104, '$.c2')
limit 1;
demo数据
{
"tips": "<p>测试</p><p><strong style=\"color: rgb(255, 0, 0);\">xxx:</strong></p><p>yyyy<strong style=\"color: rgb(255, 0, 0);\">zzzz。</p>",
"c1": [
1,
2
],
"c2": [
64398
],
"s1": [
"GSTC",
"QSCFW"
]
}
补充:mysql 的类型也是 json