版本3.3.6(从3.2.9升上来的)
create table t1 (c1 varchar(200),c2 varchar(200));
insert into t1 values ("江西中心(赣)","调拨");
insert into t1 values ("江西中心(赣)","大区");
insert into t1 values ("江西中心(赣)","点点点");
select * from t1;
select distinct c1 from tmp.t1 where c1 not LIKE '%江西中心%' and c2 NOT LIKE '%调拨%' and c2 NOT LIKE '%大区%';
结果:
mysql> select distinct c1 from tmp.t1 where c1 not LIKE '%江西中心%' and c2 NOT LIKE '%调拨%' and c2 NOT LIKE '%大区%';
+-------------------+
| c1 |
+-------------------+
| 江西中心(赣) |
+-------------------+
1 row in set (1.24 sec)
执行计划:
mysql> explain select distinct c1 from tmp.t1 where c1 not LIKE '%江西中心%' and c2 NOT LIKE '%调拨%' and c2 NOT LIKE '%大区%';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:1: c1 |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 6:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 3: c1 |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 06 |
| UNPARTITIONED |
| |
| 5:Decode |
| | <dict id 3> : <string id 1> |
| | |
| 4:AGGREGATE (merge finalize) |
| | group by: 3: c1 |
| | |
| 3:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| colocate exec groups: ExecGroup{groupId=1, nodeIds=[0, 1, 2]} |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 03 |
| HASH_PARTITIONED: 3: c1 |
| |
| 2:AGGREGATE (update serialize) |
| | STREAMING |
| | group by: 3: c1 |
| | |
| 1:Project |
| | <slot 3> : 3: c1 |
| | |
| 0:OlapScanNode |
| TABLE: t1 |
| PREAGGREGATION: ON |
| PREDICATES: DictDecode(3: c1, [<place-holder> LIKE '%江西中心%']), DictDecode(4: c2, [NOT (<place-holder> REGEXP '^((.*调拨.*)|(.*大区.*))$')]) |
| partitions=1/1 |
| rollup: t1 |
| tabletRatio=6/6 |
| tabletList=202620583,202620587,202620591,202620595,202620599,202620603 |
| cardinality=1 |
| avgRowSize=9.0 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
50 rows in set (0.01 sec)