金仓数据库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 模式存在的差异,在截取的逻辑上发生了变化。