人大金仓数据库KingbaseES 新特性——mysql 支持的函数

金仓数据库KingbaseES 字符串截断特性

关键字：

KingbaseES、mysql、stringr、substring、人大金仓、KingbaseES

Kes在不同模式下的函数 substring，substr。

oracle 模式中，

substring(string [from int] [for int])

功能： 返回从字符串指定位置开始截取的子字符串。 参数说明： string：要截取子字符串的字符串。 from：整数类型，用于指定子串的起始位置。 for：用于指定截取子串的字符数。

SUBSTR [ SUBSTR |SUBSTRB |SUBSTRC |SUBSTR2 |SUBSTR4 ] ( char , position [, substring_length] )

参数说明： ? 如果 position 为 0，则将其视为 1。 ? 如果 position 为正，则 KingbaseES 数据库从头开始计数 char 以查找第一个字符。 ? 如果 position 为负数，则 KingbaseES 从末尾倒数 char。 513 第 8 章 函数 ? 如果 substring_length 省略，则 KingbaseES 将所有字符返回到 char. 如果 substring_length 小于 1，则 KingbaseES 返回 null。

mysql 模式中，

有4种调用形式：

• substring(str, pos)
• substring(str from pos)
• substring(str, pos, len)
• substring(str from pos for len)

同理，substr 也支持以上这四种方式，substring 的四种用法都先通过语法解析，再转换成substring函数调用。mysql和pg原生支持相同的语法。兼容mysql的用法为了和pg原生区分开，增加guc参数mysql_substring_compatible。当mysql_substring_compatible=true时，调用兼容mysql行为的substring函数；当mysql_substring_compatible=false时，调用pg原生行为的substring函数。

函数定义：text substring(text str, int pos)

函数功能：从字符串str中截取从起始位置pos开始到结束的字符串。

• 如果任一参数是null则返回null，任一参数是空字符串则返回空字符串；
• 如果pos是0则返回空字符串；
• pos大于0表示从字符串开头开始计算起始位置，第一个位置为1；pos小于0表示从字符串结尾开始计算起始位置，最后一个位置为-1；
• pos可以为小数，四舍五入到整数进行计算；

如果pos超出了str实际的长度范围，则返回空字符串。

函数定义：text substring(text str, int pos, int len)

函数功能：从字符串str中截取从起始位置pos开始长度为len的字符串。

• 如果任一参数是null则返回null，任一参数是空字符串则返回空字符串；
• 如果pos是0则返回空字符串；
• pos大于0表示从字符串开头开始计算起始位置，第一个位置为1；pos小于0表示从字符串结尾开始计算起始位置，最后一个位置为-1；
• pos可以为小数，四舍五入到整数进行计算；
• 如果pos超出了str实际的长度范围，则返回空字符串。

如果len小于1则返回空字符串；如果len大于str长度则截取到str结束。

Substr，substring在不同模式的实际差别

Mysql 在开启mysql_substring_compatible参数前后

set mysql_substring_compatible=on;

select substring('kingbaseES',3.45);

substring

-----------

ngbaseES

(1 row)

select substring('kingbaseES',-3);

substring

-----------

eES

(1 row)

set mysql_substring_compatible=off;

select substring('kingbaseES',3.45);

substring

-----------

(1 row)

select substring('kingbaseES',-3);

substring

------------

kingbaseES

同时，在pg 模式中的表现为：

select substring('kingbaseES',3.45);

ksql:/tmp/tempdb/test/test.sql:20: ERROR: function pg_catalog.substring(unknown, numeric) does not exist

LINE 1: select substring('kingbaseES',3.45);

select substring('kingbaseES',-3);

substring

------------

kingbaseES

综上，可以得到结论，在未开启参数时，行为同pg 一般，上述例子的报错在于cast 转换的报错，不能转到整数进行截取操作。

在 oracle 模式 的表现为：

select substring('kingbaseES',-3);

substring

------------

kingbaseES

select substring('kingbaseES',3.45);

substring

-----------

(1 row)

其行为和 mysql 模式未开启参数一致。

Mysql 模式：

set mysql_substring_compatible=off;

select substr('kingbaseES',-3);

substr

------------

kingbaseES

(1 row)

select substr('abcd' from -1 for 1);

ksql:/tmp/tempdb/test/test.sql:46: ERROR: syntax error at or near "from"

LINE 1: select substr('abcd' from -1 for 1);

set mysql_substring_compatible=on;

select substr('kingbaseES',-3);

substr

------------

kingbaseES

select substr('abcd' from -1 for 1);

substr

--------

d

(1 row)

Pg模式：

select substr('kingbaseES',-3);

substr

------------

kingbaseES

Oracle 模式：

select substr('kingbaseES',-3);

substr

--------

eES

(1 row)

select substr('abcd' from -1 for 1);

ksql:/tmp/tempdb/test/test.sql:46: ERROR: syntax error at or near "from"

LINE 1: select substr('abcd' from -1 for 1);

综上对比可以得出，在mysql 中 substr 和 substring 的表现是一致的，没有什么差别。相比之下，msql 的 substr 的语法支持 from for, 同时，在对于参数存在负数的情况下，oracle模式的表现和 pg 模式存在的差异，在截取的逻辑上发生了变化。