gbase数据、南大通用产品文档：GBase8aexpr LIKE pat [ESCAPE 'escapechar']

函数说明
expr LIKE pat [ESCAPE 'escape-char']。
使用SQL 的简单的正则表达式进行比较的
模式匹配。如果表达式expr 匹配pat，返回1（TRUE），否则返回0（FALSE）。
模式未必就是文字字符串，例如，它可以使用字符串表达式或表列。可以在模式
中使用下面所示的两个通配符与LIKE 配合使用。
表5- 13 通配符说明
字符
含义
%
匹配任意多个字符，或零个字符。
_
严格地匹配一个字符。
示例
示例1：expr 与pat 相匹配，通配符为“_”，返回1。
gbase> SELECT 'David!' LIKE 'David_' FROM dual;
+------------------------+
| 'David!' LIKE 'David_' |
+------------------------+

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
729
|
1 |
+------------------------+
1 row in set
示例2：expr 与pat 相匹配，通配符为“%”，返回1。
gbase> SELECT 'David!' LIKE '%D%v%' FROM dual;
+-----------------------+
| 'David!' LIKE '%D%v%' |
+-----------------------+
|
1 |
+-----------------------+
1 row in set
说明
substr 在str 中的位置，以1 开始计数。
返回的位置是按全字符串正向位置计数的，与从哪个位置开始无关。
表5- 14 字符串说明
字符串
含义
\%
匹配一个%字符。
\_
匹配一个_字符。
示例3：expr 与pat 不匹配，返回0。
gbase> SELECT 'David!' LIKE 'David\_' FROM dual;
+-------------------------+
| 'David!' LIKE 'David\_' |
+-------------------------+
|
0 |
+-------------------------+
1 row in set
示例4：转义字符“\_”匹配“_”。
gbase> SELECT 'David_' LIKE 'David\_' FROM dual;
+-------------------------+
| 'David_' LIKE 'David\_' |
+-------------------------+
|
1 |
+-------------------------+
1 row in set

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
730
示例5：为了指定一个不同的转义字符，可以使用ESCAPE 子句。
gbase> SELECT 'David_' LIKE 'David|_' ESCAPE '|' FROM dual;
+------------------------------------+
| 'David_' LIKE 'David|_' ESCAPE '|' |
+------------------------------------+
|
1 |
+------------------------------------+
1 row in set
示例6
gbase> SELECT 'abc' LIKE 'ABC' FROM dual;
+------------------+
| 'abc' LIKE 'ABC' |
+------------------+
|
1 |
+------------------+
1 row in set
示例7
gbase> SELECT 'abc' LIKE BINARY 'ABC' FROM dual;
+-------------------------+
| 'abc' LIKE BINARY 'ABC' |
+-------------------------+
|
0 |
+-------------------------+
1 row in set
说明
以上示例6 和示例7 表明，字符串比较是忽略大小写的，除非任一
操作数是一个二进制字符串。
示例8：LIKE 允许用在一个数字表达式上。
gbase> SELECT 10 LIKE '1%' FROM dual;
+--------------+
| 10 LIKE '1%' |
+--------------+
|
1 |
+--------------+
1 row in set

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
731
注意

由于GBase 8a MPP Cluster 在字符串中使用C 转义语法(例如，用
“\n”代表一个换行字符)，
在LIKE 字符串中，
必须将用到的“\”双写；

例如，若要查找“\n”，必须将其写成“\\n”。而若要查找“\”，
则必须将其写成‘\\\\’。原因是反斜线符号会被语法分析程序剥离一
次，在进行模式匹配时，又会被剥离一次，最后会剩下一个反斜线
符号接受匹配。

说明
操作符优先级在下面列出，从最高到最低。
同一行的操作符具有同样的优先级。
BINARY，COLLATE
!
-(unary minus),～(unary bit inversion)
^
*,/,DIV,%,MOD
-,+
<<,>>
&
|
=,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
BETWEEN,CASE,WHEN,THEN,ELSE
NOT
&&,AND
OR,XOR
:=

EXPLAIN 后边有EXTENDED 时，显示查询计划的扩展输出格式。

示例1：
EXPLAIN EXTENDED SELECT x1.id2 FROM x1, x2 WHERE x1.id2 = x2.id3
AND x1.id3 IN (SELECT id2 FROM x2 WHERE x2.id4 > 10);
查询计划：
QueryPlan:
Uncorrelated SUB plan
==============================================

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
1153
==============================================
QueryPlan:
++++++++++++++++++++++++++++++++++++++++++++++
Leaf type: REGULAR STEP
【一般步骤，与汇总步骤对应】
Need combiner: false【不需要汇总】
Target temp table: _tmp_2030479552_19_t160_1_1496193667_s 【目标临时
表名】
Temp
table
definition:
CREATE
TABLE
`gctmpdb`._tmp_2030479552_19_t160_1_1496193667_s
AS
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/ /*+ TID('131280') */ DISTINCT
`regress_db_link.x2`.`id2`
AS
`id2`
FROM
`regress_db_link`.`x2`
`regress_db_link.x2` WHERE (`regress_db_link.x2`.`id4` > 10) LIMIT 0【创建目
标表的SQL】
Optimization:
Query
String:
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/ /*+ TID('131280') */ DISTINCT
`regress_db_link.x2`.`id2`
AS
`id2`
FROM
`regress_db_link`.`x2`
`regress_db_link.x2` WHERE (`regress_db_link.x2`.`id4` > 10)【查询语句】
【索引内容】May use index: `regress_db_link`.`x2`.`id4`{Smart Index}
【代价内容】
CostInfo: Start(0), Run(0.11), Selectivity(0.578947), Width(8),
Rows(11)
==============================================
end SUB plan
++++++++++++++++++++++++++++++++++++++++++++++
Leaf type: REGULAR STEP
Need combiner: false
Target temp table: _tmp_2030479552_19_t160_2_1496193667_s
Temp
table
definition:
CREATE
TABLE
`gctmpdb`._tmp_2030479552_19_t160_2_1496193667_s
AS
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280')
*/
`regress_db_link.x1`.`id2`
AS
`id2`
FROM
`regress_db_link`.`x1`
`regress_db_link.x1`
WHERE
`regress_db_link.x1`.`id3`
IN
(SELECT
`id2`
FROM `gctmpdb`.`_tmp_2030479552_19_t160_1_1496193667_s`) LIMIT 0
Optimization:
Query
String:
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280')
*/

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
1154
`regress_db_link.x1`.`id2`
AS
`id2`
FROM
`regress_db_link`.`x1`
`regress_db_link.x1`
WHERE
`regress_db_link.x1`.`id3`
IN
(SELECT
`id2`
FROM `gctmpdb`.`_tmp_2030479552_19_t160_1_1496193667_s`)
【代价内容】
CostInfo: Start(0), Run(0.07), Selectivity(0.291667), Width(8),
Rows(7)
++++++++++++++++++++++++++++++++++++++++++++++
Leaf type: REGULAR STEP
Need combiner: false
Target temp table: _tmp_2030479552_19_t160_3_1496193667_s
Temp
table
definition:
CREATE
TABLE
`gctmpdb`._tmp_2030479552_19_t160_3_1496193667_s
AS
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280')
*/
`_tmp_2030479552_19_t160_2_1496193667_s`.`id2`
AS
`id2`
FROM
`gctmpdb`._tmp_2030479552_19_t160_2_1496193667_s
INNER
JOIN
`regress_db_link`.`x2`
`regress_db_link.x2`
ON
(`_tmp_2030479552_19_t160_2_1496193667_s`.`id2`
=
`regress_db_link.x2`.`id3`) LIMIT 0
Optimization:
Query
String:
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280')
*/
`_tmp_2030479552_19_t160_2_1496193667_s`.`id2`
AS
`id2`
FROM
`gctmpdb`._tmp_2030479552_19_t160_2_1496193667_s
INNER
JOIN
`regress_db_link`.`x2`
`regress_db_link.x2`
ON
(`_tmp_2030479552_19_t160_2_1496193667_s`.`id2`
=
`regress_db_link.x2`.`id3`)
【代价内容】
CostInfo:
Start(0.526),
Run(0.52),
Selectivity(0.0789474),
Width(12), Rows(10)
==============================================
Uncorrelated Subplan:
CExecStep
--------------------------
isQueryFinalStep = 0
【不是查询的最后一步】

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
1155
DestType = 1
【结果发送到所有节点】
aStepDetail
【8a 所有节点属性】
---------------
isProducer = 1
【是生产者】
isConsumer = 1
【是消费者】
producerDistID = 1
【生产者distribution id 为1】
consumerDistID = 1
【消费者distribution id 为1】
isSingleHashNode = 0
【非单节点hash 优化】
isHashRedist = 0
【不是hash 重分布】
isGroupHashRedist = 0
isAllTableAreHashTmpDist = 0
【非所有源表都是HASH 临时表】
isExistsHashReditTable = 0
【源表不存在HASH 临时表】
queryString = SELECT /*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280')
*/
DISTINCT
`regress_db_link.x2`.`id2`
AS
`id2`
FROM
`regress_db_link`.`x2` `regress_db_link.x2` WHERE (`regress_db_link.x2`.`id4` >
10)
targetTable = `gctmpdb`._tmp_2030479552_19_t160_1_1496193667_s
targetSchema
=
CREATE
TABLE
`gctmpdb`._tmp_2030479552_19_t160_1_1496193667_s
AS
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/ /*+ TID('131280') */ DISTINCT
`regress_db_link.x2`.`id2`
AS
`id2`
FROM
`regress_db_link`.`x2`
`regress_db_link.x2` WHERE (`regress_db_link.x2`.`id4` > 10) LIMIT 0
CExecStep
--------------------------
isQueryFinalStep = 0
DestType = 1
aStepDetail
---------------
isProducer = 1
isConsumer = 1
producerDistID = 1
consumerDistID = 1
isSingleHashNode = 0
isHashRedist = 0

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
1156
isGroupHashRedist = 0
isAllTableAreHashTmpDist = 0
isExistsHashReditTable = 0
queryString = SELECT /*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280') */ `regress_db_link.x1`.`id2` AS `id2` FROM `regress_db_link`.`x1`
`regress_db_link.x1`
WHERE
`regress_db_link.x1`.`id3`
IN
(SELECT
`id2`
FROM `gctmpdb`.`_tmp_2030479552_19_t160_1_1496193667_s`)
targetTable = `gctmpdb`._tmp_2030479552_19_t160_2_1496193667_s
targetSchema
=
CREATE
TABLE
`gctmpdb`._tmp_2030479552_19_t160_2_1496193667_s
AS
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280')
*/
`regress_db_link.x1`.`id2`
AS
`id2`
FROM
`regress_db_link`.`x1`
`regress_db_link.x1`
WHERE
`regress_db_link.x1`.`id3`
IN
(SELECT
`id2`
FROM `gctmpdb`.`_tmp_2030479552_19_t160_1_1496193667_s`) LIMIT 0
Step Drop List = `gctmpdb`._tmp_2030479552_19_t160_1_1496193667_s
CExecStep
--------------------------
isQueryFinalStep = 0
DestType = 0
aStepDetail
---------------
isProducer = 1
isConsumer = 0
producerDistID = 1
consumerDistID = 1
isSingleHashNode = 0
isHashRedist = 0
isGroupHashRedist = 0
isAllTableAreHashTmpDist = 0
isExistsHashReditTable = 0
queryString = SELECT /*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280') */ `_tmp_2030479552_19_t160_2_1496193667_s`.`id2` AS `id2`
FROM `gctmpdb`._tmp_2030479552_19_t160_2_1496193667_s INNER JOIN
`regress_db_link`.`x2`
`regress_db_link.x2`
ON
(`_tmp_2030479552_19_t160_2_1496193667_s`.`id2`
=

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
1157
`regress_db_link.x2`.`id3`)
Step Drop List = `gctmpdb`._tmp_2030479552_19_t160_2_1496193667_s

示例2：
explain extended select x1.id2 from x1, x2, x3 where x1.id2 = x2.id3 and x1.id3 =
x3.id2;
查询计划：
QueryPlan:
++++++++++++++++++++++++++++++++++++++++++++++
Leaf type: REGULAR STEP
Need combiner: false
Target temp table: _tmp_2030479552_19_t161_1_1496193667_s
Temp table definition: CREATE TABLE `gctmpdb`._tmp_2030479552_19_t16
1_1_1496193667_s AS SELECT /*192.168.6.121_19_16_2017-05-31_09:56:34
*/ /*+ TID('131281') */ `regress_db_link.x3`.`id2` AS `id2` FROM `regress_d
b_link`.`x3` `regress_db_link.x3` LIMIT 0
Optimization:
Query String: SELECT /*192.168.6.121_19_16_2017-05-31_09:56:3
4*/ /*+ TID('131281') */ `regress_db_link.x3`.`id2` AS `id2` FROM `regress_
db_link`.`x3` `regress_db_link.x3`
CostInfo: Start(0), Run(0.13), Selectivity(1), Width(4), Rows(13)
++++++++++++++++++++++++++++++++++++++++++++++
Leaf type: REGULAR STEP
Need combiner: false
Target temp table: _tmp_rht_2030479552_19_t161_2_1496193667_s
Temp table definition: CREATE TABLE `gctmpdb`._tmp_rht_2030479552_19_
t161_2_1496193667_s AS SELECT /*192.168.6.121_19_16_2017-05-31_09:56:
34*/ /*+ TID('131281') */ `regress_db_link.x1`.`id2` AS `id2` FROM `regress
_db_link`.`x1` `regress_db_link.x1` INNER JOIN `gctmpdb`._tmp_2030479552
_19_t161_1_1496193667_s ON (`regress_db_link.x1`.`id3` = `_tmp_203047955
2_19_t161_1_1496193667_s`.`id2`) LIMIT 0
Optimization: {hash redist} 【是HASH 重分布步骤】
Hash Redist Indexes: 1【HASH 重分布下标(从1 开始)】
Query String: SELECT /*192.168.6.121_19_16_2017-05-31_09:56:3
4*/ /*+ TID('131281') */ `regress_db_link.x1`.`id2` AS `id2` FROM `regress_

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
1158
db_link`.`x1` `regress_db_link.x1` INNER JOIN `gctmpdb`._tmp_2030479552_
19_t161_1_1496193667_s ON (`regress_db_link.x1`.`id3` = `_tmp_2030479552
_19_t161_1_1496193667_s`.`id2`)
CostInfo: Start(0.812), Run(0.74), Selectivity(0.0608974), Width(1
2), Rows(19)
++++++++++++++++++++++++++++++++++++++++++++++
Leaf type: REGULAR STEP
Need combiner: false
Target temp table: _tmp_rht_2030479552_19_t161_3_1496193667_s
Temp table definition: CREATE TABLE `gctmpdb`._tmp_rht_2030479552_19_
t161_3_1496193667_s AS SELECT /*192.168.6.121_19_16_2017-05-31_09:56:
34*/ /*+ TID('131281') */ `regress_db_link.x2`.`id3` AS `id3` FROM `regress
_db_link`.`x2` `regress_db_link.x2` LIMIT 0
Optimization: {hash redist}
Hash Redist Indexes: 1
Query String: SELECT /*192.168.6.121_19_16_2017-05-31_09:56:3
4*/ /*+ TID('131281') */ `regress_db_link.x2`.`id3` AS `id3` FROM `regress_
db_link`.`x2` `regress_db_link.x2`
CostInfo: Start(0), Run(0.19), Selectivity(1), Width(4), Rows(19)
++++++++++++++++++++++++++++++++++++++++++++++
Leaf type: REGULAR STEP
Need combiner: false
Target temp table: _tmp_2030479552_19_t161_4_1496193667_s
Temp table definition: CREATE TABLE `gctmpdb`._tmp_2030479552_19_t16
1_4_1496193667_s AS SELECT /*192.168.6.121_19_16_2017-05-31_09:56:34
*/ /*+ TID('131281') */ `_tmp_rht_2030479552_19_t161_2_1496193667_s`.`id2
` AS `id2` FROM `gctmpdb`._tmp_rht_2030479552_19_t161_2_1496193667_s
INNER JOIN `gctmpdb`._tmp_rht_2030479552_19_t161_3_1496193667_s ON
(`_tmp_rht_2030479552_19_t161_2_1496193667_s`.`id2` = `_tmp_rht_203047
9552_19_t161_3_1496193667_s`.`id3`) LIMIT 0
Optimization:
Query String: SELECT /*192.168.6.121_19_16_2017-05-31_09:56:3
4*/ /*+ TID('131281') */ `_tmp_rht_2030479552_19_t161_2_1496193667_s`.`id
2` AS `id2` FROM `gctmpdb`._tmp_rht_2030479552_19_t161_2_1496193667_
s INNER JOIN `gctmpdb`._tmp_rht_2030479552_19_t161_3_1496193667_s O
N (`_tmp_rht_2030479552_19_t161_2_1496193667_s`.`id2` = `_tmp_rht_20304

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
1159
79552_19_t161_3_1496193667_s`.`id3`)
CostInfo: Start(1.40733), Run(1.31), Selectivity(0.0789474), Wi
dth(16), Rows(28)