1.3.2.2.12. 转换为 goldendb-Mysql
1.3.2.2.12.1. 转换函数
1.3.2.2.12.1.1. TO_NUMBER
- 语法
TO_NUMBER(expr)TO_NUMBER(expr,fmt)- 描述
- 该函数功能是将 CHAR、VARCHAR2等类型的字符串转换为 NUMBER 数值数据类型的值
参数解释
参数 |
说明 |
|---|---|
expr |
必填参数，属于 CHAR、VARCHAR2、NCHAR、NVARCHAR2、BINARY_FLOAT 或 BINARY_DOUBLE 数据类型的数值。 |
fmt |
可选参数，格式模型（format model），仅当 expr 是字符类型时可用。用于指定字符串的数字格式（如 ‘9G999D99’） |
警告
goldendb-mysql，TO_NUMBER(expr, fmt, nlsparam) 3参数调用，第 3 参数（NLS），统一sql解析报错，示例sql如下：
SELECT TO_NUMBER(
'-AusDollars100',
'L9G999D99',
'NLS_NUMERIC_CHARACTERS=".,", NLS_CURRENCY="AusDollars"'
) AS "Amount"
FROM DUAL;
goldendb-mysql，参数expr DEFAULT … ON CONVERSION ERROR，统一 sql 解析报错，sql 示例如下：
select TO_NUMBER('abc' DEFAULT 0 ON CONVERSION ERROR) from dual;
在goldendb-mysql，参数fmt
不支持FM(填充模式),sql示例如下：
select TO_NUMBER('123.40', 'FM999.99') from dual;
在goldendb-mysql，参数fmt明确支持的取值范围如下：
元素
例子
基础数字占位符9
999.99
SELECT TO_NUMBER(‘123.45’, ‘999.99’) AS result FROM dual; 返回 123.45
基础数字占位符0
00.0
SELECT TO_NUMBER(‘12.3’, ‘00.0’) AS result FROM dual; 返回 12.3
固定分隔符（.小数点、,千分位）
9,999.99
SELECT TO_NUMBER(‘1,234.56’, ‘9,999.99’) AS result FROM dual; 返回 1234.56
本地化分隔符G（千分位）+ D（小数点）
9G999D99
SELECT TO_NUMBER(‘1,234.56’, ‘9G999D99’) AS result FROM dual; 返回 1234.56
-符号（显示负数负号）
-负号
SELECT TO_NUMBER(‘-123’, ‘999’) AS result FROM dual; 返回 -123
MI（负号在右侧）
比如999MI
SELECT TO_NUMBER(‘123-‘, ‘999MI’) AS result FROM dual; 返回 -123
固定$符号
$999.99
SELECT TO_NUMBER(‘$123.45’, ‘$999.99’) AS result FROM dual;返回123.45
科学计数法E格式
9.99EEEE
SELECT TO_NUMBER(‘1.23E+02’, ‘9.99EEEE’) FROM DUAL;返回123
示例
-- 转换前Oracle SQL：
SELECT TO_NUMBER('0123456'),TO_NUMBER('1.2'),TO_NUMBER('0'),TO_NUMBER(1.2),TO_NUMBER('-1'),TO_NUMBER(-1) FROM DUAL;
TO_NUMBER('0123456') TO_NUMBER('1.2') TO_NUMBER('0') TO_NUMBER(1.2) TO_NUMBER('-1') TO_NUMBER(-1)
-------------------- ---------------- -------------- -------------- --------------- -------------
123456 1.2 0 1.2 -1 -1
-- 转换后goldendb-MySQL：
SELECT TO_NUMBER('0123456'),TO_NUMBER('1.2'),TO_NUMBER('0'),TO_NUMBER(1.2),TO_NUMBER('-1'),TO_NUMBER(-1) FROM DUAL;
+----------------------+------------------+----------------+----------------+-----------------+---------------+
| TO_NUMBER('0123456') | TO_NUMBER('1.2') | TO_NUMBER('0') | TO_NUMBER(1.2) | TO_NUMBER('-1') | TO_NUMBER(-1) |
+----------------------+------------------+----------------+----------------+-----------------+---------------+
| 123456 | 1.2 | 0 | 1.2 | -1 | -1 |
+----------------------+------------------+----------------+----------------+-----------------+---------------+
1.3.2.2.12.2. 字符函数
1.3.2.2.12.2.1. LTRIM
- 语法
LTRIM(expr)LTRIM(expr,trim_str)- 描述
- 该函数功能从expr字符串左侧开始，移除属于 trim_str 中任意字符集合的字符，直到遇到非 trim_str 集合字符为止，返回处理后的新字符串。默认是删除expr左侧的空格字符。
参数解释
参数 |
说明 |
|---|---|
expr |
必填参数，属于 CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB、NCLOB 字符数据类型或可隐式转换为字符串的表达式。 |
trim_str |
可选参数，字符数据类型或可隐式转换为字符串的表达式。 |
警告
goldendb-mysql，字符数据类型CHAR、VARCHAR、TEXT、CLOB、LONGTEXT，比较时自动忽略右侧的尾随空格，示例sql如下：
create table testCharacter(id int,col1 longtext,col2 longtext); insert into testCharacter(id,col1,col2) values(1,'1 ','1'); insert into testCharacter(id,col1,col2) values(2,' 1','1'); select id,col1=col2 from testCharacter; id|col1=col2| --+---------+ 1| 1| 2| 0|
LTRIM函数的参数可以是可隐式转换为字符串的表达式，源库、目标库隐式转化规则有所差异，目前支持整数类型：
-- 转换前Oracle SQL： SELECT LTRIM(123 ,1),LTRIM(1.23 ,1.0) from DUAL LTRIM(123,1)|LTRIM(1.23,1.0)| ------------+---------------+ 23 |.23 | -- 转换后goldendb-MySQL： SELECT unisql.LTRIM(123, 1),unisql.LTRIM(1.23, 1.0) FROM dual; unisql.LTRIM(123, 1)|unisql.LTRIM(1.23, 1.0)| --------------------+-----------------------+ 23 |23 |
源库和目标库的转义字符不同，影响函数转化结果
-- 转换前Oracle SQL： SELECT '\' from DUAL '\'| ---+ \ | -- goldendb-MySQL需要转化： SELECT '\\' from DUAL \| -+ \|
考虑到客户的实际使用，在目标库中使该函数的所有入参类型为varchar或可隐式转换为varchar的相关类型，支持的最大字符长度为16383
示例
-- 转换前Oracle SQL：
SELECT LTRIM(' unisql'),LTRIM('lgunisql', 'gl') from dual;
LTRIM('UNISQL')|LTRIM('LGUNISQL','GL')|
---------------+----------------------+
unisql |unisql |
-- 转换后goldendb-MySQL：
SELECT LTRIM(' unisql'),unisql.LTRIM('lgunisql', 'gl') FROM dual
LTRIM(' unisql')|unisql.LTRIM('lgunisql', 'gl')|
----------------+------------------------------+
unisql |unisql |
1.3.2.2.12.2.2. RTRIM
- 语法
RTRIM(expr)RTRIM(expr,trim_str)- 描述
- 该函数功能从expr字符串右侧开始，移除属于 trim_str 中任意字符集合的字符，直到遇到非 trim_str 集合字符为止，返回处理后的新字符串。默认是删除expr右侧的空格字符。
参数解释
参数 |
说明 |
|---|---|
expr |
必填参数，属于 CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB、NCLOB 字符数据类型或可隐式转换为字符串的表达式。 |
trim_str |
可选参数，字符数据类型或可隐式转换为字符串的表达式。 |
警告
goldendb-mysql，字符数据类型CHAR、VARCHAR、TEXT、CLOB、LONGTEXT，比较时自动忽略右侧的尾随空格，示例sql如下：
create table testCharacter(id int,col1 longtext,col2 longtext);
insert into testCharacter(id,col1,col2) values(1,'1 ','1');
insert into testCharacter(id,col1,col2) values(2,' 1','1');
select id,col1=col2 from testCharacter;
id|col1=col2|
--+---------+
1| 1|
2| 0|
RTRIM函数的参数可以是可隐式转换为字符串的表达式，源库、目标库隐式转化规则有所差异，目前支持整数类型：
-- 转换前Oracle SQL：
SELECT RTRIM(321 ,1),RTRIM(32.1 ,1.0) from DUAL
RTRIM(321,1)|RTRIM(32.1,1.0)|
------------+---------------+
32 |32. |
-- 转换后goldendb-MySQL：
SELECT unisql.RTRIM(321 ,1),unisql.RTRIM(32.1 ,1.0) FROM dual;
unisql.RTRIM(321 ,1)|unisql.RTRIM(32.1 ,1.0)|
--------------------+-----------------------+
32 |32 |
源库和目标库的转义字符不同，影响函数转化结果
-- 转换前Oracle SQL：
SELECT '\' from DUAL
'\'|
---+
\ |
-- goldendb-MySQL需要转化：
SELECT '\\' from DUAL
\|
-+
\|
考虑到客户的实际使用，在目标库中使该函数的所有入参类型为varchar或可隐式转换为varchar的相关类型，支持的最大字符长度为16383
示例
-- 转换前Oracle SQL：
SELECT RTRIM('unisql '),RTRIM('unisqles', 'se') from DUAL
RTRIM('UNISQL')|RTRIM('UNISQLES','SE')|
---------------+----------------------+
unisql |unisql |
-- 转换后goldendb-MySQL：
SELECT RTRIM('unisql '),unisql.RTRIM('unisqles', 'se') FROM dual
RTRIM('unisql ')|unisql.RTRIM('unisqles', 'se')|
------------------+------------------------------+
unisql |unisql |
1.3.2.2.12.2.3. SUBSTR
语法
SUBSTR(str, pos)
SUBSTR(str, pos, len)
- 描述
- 返回 str 的子字符串，起始位置为 pos，长度为 len。参数中包含 NULL 时，返回 NULL
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
pos |
子字符串的起始位置 |
len |
子字符串的长度 |
警告
Oracle 和 GoldenDB-MySQL 在数值的内部存储和显示格式上存在差异。例如，对于数值 0.01，Oracle 在某些情况下（如使用默认格式化规则时）可能显示为 .01，而 GoldenDB-MySQL 则保留前导 0，显示为 0.01。此外，对于精度超过 38 位的数值和 Char 数据类型存储固定字符数的数值，两者的处理方式不一致。
日期类型的行为受日期格式影响，不同的日期格式可能导致 GoldenDB-MySQL 和 Oracle 的行为不一致。
在 GoldenDB-MySQL 中， pos 和 len 类型为整数。当处理浮点数时，Oracle 和 GoldenDB-MySQL 都会隐式转换为整数。然而，Oracle 向零取整，而 GoldenDB-MySQL 则是四舍五入。
在 GoldenDB-MySQL 中，null 和空字符串不相等，而在 Oracle 中，null 和空字符串被视为相等，均为 null。
pos 和 len 参数不支持空字符串。
示例
-- 转换前Oracle SQL：
SELECT SUBSTR('abcdefg', 3),SUBSTR('abcdefg', 3, 2),SUBSTR('abcdefg', -3),SUBSTR('abcdefg', 3, -2) FROM DUAL;
-------------------+---------------------+--------------------+----------------------+
SUBSTR('ABCDEFG',3)|SUBSTR('ABCDEFG',3,2)|SUBSTR('ABCDEFG',-3)|SUBSTR('ABCDEFG',3,-2)|
-------------------+---------------------+--------------------+----------------------+
cdefg |cd |efg | |
select substr (20250217,2,5), substr (2025.0217,1,6) from DUAL;
SUBSTR(20250217,2,5)|SUBSTR(2025.0217,1,6)|
--------------------+---------------------+
02502 |2025.0 |
-- 转换后goldendb-mysql SQL：
SELECT unisql.substr('abcdefg', 3, length('abcdefg')),unisql.substr('abcdefg', 3, 2),unisql.substr('abcdefg', -3, length('abcdefg')),unisql.substr('abcdefg', 3, -2) FROM DUAL;
+------------------------------------------------+--------------------------------+-------------------------------------------------+---------------------------------+
| unisql.substr('abcdefg', 3, length('abcdefg')) | unisql.substr('abcdefg', 3, 2) | unisql.substr('abcdefg', -3, length('abcdefg')) | unisql.substr('abcdefg', 3, -2) |
+------------------------------------------------+--------------------------------+-------------------------------------------------+---------------------------------+
| cdefg | cd | efg | |
+------------------------------------------------+--------------------------------+-------------------------------------------------+---------------------------------+
SELECT unisql.substr(20250217, 2, 5),unisql.substr(2025.0217, 1, 6) FROM DUAL;
+-------------------------------+--------------------------------+
| unisql.substr(20250217, 2, 5) | unisql.substr(2025.0217, 1, 6) |
+-------------------------------+--------------------------------+
| 02502 | 2025.0 |
+-------------------------------+--------------------------------+
1.3.2.2.12.3. 数值函数
1.3.2.2.12.3.1. TRUNC
- 语法
TRUNC (numeric[,precision])- 描述
- 该函数返回参数 numeric 按精度 precision 截取后的值
参数解释
参数 |
说明 |
|---|---|
numeric |
表示被截取的数字,数字类型 |
precision |
表示精度，整型，为可选项，默认值为 0 |
示例
-- 转换前Oracle SQL：
SELECT TRUNC(555.666,2.2),TRUNC(555.666,-2),TRUNC(555.666) FROM DUAL;
------------------+-----------------+--------------+
TRUNC(555.666,2.2)|TRUNC(555.666,-2)|TRUNC(555.666)|
------------------+-----------------+--------------+
555.66| 500| 555|
-- 转换后goldendb-MySQL：
SELECT TRUNC(555.666, 2.2),TRUNC(555.666, -2),TRUNC(555.666) FROM DUAL
TRUNC(555.666, 2.2)|TRUNC(555.666, -2)|TRUNC(555.666)|
-------------------+------------------+--------------+
555.66| 500| 555|
1.3.2.2.12.4. 时间日期函数
1.3.2.2.12.4.1. TRUNC
- 语法
TRUNC(date,[fmt])- 描述
- 该函数返回以参数 fmt 为单位距离的离指定日期 date 最近的日期时间值，并且返回的日期值在 date 之前
参数解释
参数 |
说明 |
|---|---|
date |
日期时间类型 |
fmt |
指定了函数返回值与 date 的距离单位,现支持DAY、MONTH、YEAR |
示例
-- 转换前Oracle SQL：
SELECT TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'YEAR'),TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'MONTH'),TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'DAY')FROM DUAL;
TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'YEAR')|TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'MONTH')|TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'DAY')|
------------------------------------------------------------------+-------------------------------------------------------------------+-----------------------------------------------------------------+
2022-01-01 00:00:00.000| 2022-04-01 00:00:00.000| 2022-04-17 00:00:00.000|
-- 转换后GaussDB-Oracle SQL：
SELECT TRUNC(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'YEAR'),TRUNC(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'MONTH'),TRUNC(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'DAY') FROM DUAL
TRUNC(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'YEAR')|TRUNC(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'MONTH')|TRUNC(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'DAY')|
----------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+
2022-01-01 00:00:00.000| 2022-04-01 00:00:00.000| 2022-04-17 00:00:00.000|
1.3.2.2.12.5. 正则函数
1.3.2.2.12.5.1. REGEXP_REPLACE
语法
REGEXP_REPLACE(source_char, pattern
[, replace_string
[, position
[, occurrence
[, match_param ]
]
]
]
)
- 描述
- 该函数将字符串 source_char 中与正则表达式相匹配的字符替换为 replace_string 中的字符。
参数解释
参数 |
说明 |
|---|---|
source_char |
必填参数，指定用作搜索值的字符表达式。它通常是一种字符列。数据类型可以是 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB 。 |
pattern |
必填参数，指定正则表达式，它通常是一个文本文字，数据类型可以是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2。。 |
replace_string |
表示替换的字符，可以是 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB 类型。 |
position |
开始匹配的位置（可选，默认1） |
occurrence |
替换的匹配项次数（可选，默认0表示全部替换） |
match_param |
匹配参数（可选，默认空串’’表示区分大小写、单行模式） |
1.3.2.2.12.5.1.1. match_param 取值说明
参数match_param取值 |
说明 |
|---|---|
i |
表示大小写不敏感。 |
c |
表示大小写敏感。 |
n |
表示句点 . 可以匹配换行符。 |
m |
表示多行模式。 |
x |
表示忽略空格字符，默认情况下，空格字符会相互匹配。goldendb-mysql不支持参数值x |
警告
Oracle REGEXP_REPLACE 参数数量限制：2~6个,若不足6个，统一sql转换后是固定6个参数，示例如下
-- 转换前Oracle，3个参数
SELECT REGEXP_REPLACE('12345', '[0-9]', '#') AS result FROM dual;
-- 执行结果如下
SQL> SELECT REGEXP_REPLACE('12345', '[0-9]', '#') AS result FROM dual;
RESUL
-----
#####
-- 转换后goldendb-mysql，固定6个参数，不足的参数取默认值
SELECT REGEXP_REPLACE('12345', '[0-9]', '#', 1, 0, '') AS result FROM dual
-- 执行结果如下：
mysql> SELECT REGEXP_REPLACE('12345', '[0-9]', '#', 1, 0, '') AS result FROM dual;
+--------+
| result |
+--------+
| ##### |
+--------+
1 row in set (0.00 sec)
2.参数2：pattern 限制
类型必须为ValueExpr（字符串/数值字面量），不支持列、函数、子查询等动态表达式
统一sql会将Oracle的perl正则语法转换为goldendb-MySQL的POSIX兼容格式，支持的perl正则范围，
\d，\D，\w，\W，\s，\S，\A，\Z3.支持的sql用例
-- 转换前Oracle SELECT REGEXP_REPLACE('Oracle123MySQL456', '\d+', '数字') FROM dual; -- 转换后goldendb-mysql，将\d转换到[[:digit:]] mysql> SELECT REGEXP_REPLACE('Oracle123MySQL456', '[[:digit:]]+', '数字', 1, 0, '') FROM dual; +-------------------------------------------------------------------------+ | REGEXP_REPLACE('Oracle123MySQL456', '[[:digit:]]+', '数字', 1, 0, '') | +-------------------------------------------------------------------------+ | Oracle数字MySQL数字 | +-------------------------------------------------------------------------+ 1 row in set (0.00 sec)
4.不支持的sql用例
-- 这个oracle语法在统一sql转换报错 SELECT REGEXP_REPLACE(t_table.source_col, t_table.pattern_col, '替换值') FROM t_table; -- 报错内容为,[LTU00008]函数参数错误: 函数 regexp_replace 的参数2（pattern）类型非法：实际类型为 *ast.ColumnNameExpr，仅支持值表达式（ValueExpr）。 -- 错误原因：pattern为列表达式（t_table.pattern_col），非ValueExpr，不支持 -- 这个oracle语法在统一sql转换报错 SELECT REGEXP_REPLACE('abc123def', SUBSTR('\d+', 1, 2), '数字') FROM dual; -- 报错内容为,[LTU00008]函数参数错误: 函数 regexp_replace 的参数2（pattern）类型非法：实际类型为 *ast.FuncCallExpr，仅支持值表达式（ValueExpr）。 -- 错误原因：pattern为函数调用（SUBSTR('\d+',1,2)），非ValueExpr，不支持 -- 这个oracle语法在统一sql转换报错 SELECT REGEXP_REPLACE('abc123def', SUBSTR('\d+', 1, 2), '数字') FROM dual; -- 报错内容为,[LTU00008]函数参数错误: 函数 regexp_replace 的参数2（pattern）类型非法：实际类型为 *ast.SubqueryExpr，仅支持值表达式（ValueExpr）。 -- 错误原因：pattern为子查询（SELECT '\d+' FROM dual），非ValueExpr，不支持
3.参数3：replace_string 限制
类型必须为ValueExpr（字符串/数值字面量），不支持动态表达式
仅处理1-9的反向引用（1-9），转换为MySQL的$1-$9，不支持10+（Oracle本身不支持）
若参数缺失，默认补空串
sql示例如下：
-- 转换前sql SELECT REGEXP_REPLACE('姓名:张三,年龄:25', '姓名:(\w+),年龄:(\d+)', '\2（\1岁）') FROM dual; -- 转换后sql和执行结果 mysql> SELECT REGEXP_REPLACE('姓名:张三,年龄:25', '姓名:([[:alnum:]_]+),年龄:([[:digit:]]+)', '$2（$1岁）', 1, 0, '') FROM dual; +----------------------------------------------------------------------------------------------------------------------+ | REGEXP_REPLACE('姓名:张三,年龄:25', '姓名:([[:alnum:]_]+),年龄:([[:digit:]]+)', '$2（$1岁）', 1, 0, '') | +----------------------------------------------------------------------------------------------------------------------+ | 25（张三岁） | +----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -- 错误原因：replace_string为列表达式（t_table.replace_col），非ValueExpr，不支持 SELECT REGEXP_REPLACE(t_table.source_col, '(\d+)', t_table.replace_col) FROM t_table; -- 错误原因：replace_string为函数调用（CONCAT('\1', '替换')），非ValueExpr，不支持 SELECT REGEXP_REPLACE('abc123def', '(\d+)', CONCAT('\1', '替换')) FROM dual; -- 错误原因：replace_string为子查询（SELECT '\1替换' FROM dual），非ValueExpr，不支持 SELECT REGEXP_REPLACE('abc123def', '(\d+)', (SELECT '\1替换' FROM dual)) FROM dual;
Oracle 与 GoldenDB MySQL 中 REGEXP_REPLACE 函数处理反斜杠时的行为差异。
oracle 执行语句 SELECT
REGEXP_REPLACE('abc\def', '\\') AS result FROM dual;返回 abcdef
goldendb mysql 执行语句
SELECT REGEXP_REPLACE('abc\def', '\\') AS result FROM dual;返回错误 Unrecognized escape sequence in regular expression.
GoldenDB MySQL 中反斜杠的转义规则
字符串层：要表示1个实际的
\，单引号内需要写\\（因为 MySQL 字符串会把\\解析为 1 个\）正则层：正则中
\是转义符，要匹配1个实际的\，正则模式需要写\\；叠加后：正则模式本身是字符串，因此需要写
\\\\（字符串层解析为\\，正则层再解析为\，最终匹配 1 个实际的\）。
参数6：match_param取值限制
参数6match_param取值x在goldendb-mysql执行报错，具体报错sql和报错信息示例如下:
mysql> SELECT REGEXP_REPLACE('a b c', 'a b c', 'X', 1, 1, 'x'); ERROR 1210 (HY000): Incorrect arguments to regexp_replace
示例
-- 转换前Oracle SQL： SELECT REGEXP_REPLACE('china', '(.)', '\1 '),REGEXP_REPLACE('Jane Doe','Jane', 'John'),REGEXP_REPLACE('515.123.4444','([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})','(\1) \2-\3') "REGEXP_REPLACE",REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA','( ){2,}', ' ') FROM DUAL; REGEXP_REP REGEXP_R REGEXP_REPLACE REGEXP_REPLACE('500ORACLEPARKWAY,REDWO ---------- -------- -------------- -------------------------------------- c h i n a John Doe (515) 123-4444 500 Oracle Parkway, Redwood Shores, CA -- 转换后goldendb SQL： SELECT REGEXP_REPLACE('china', '(.)', '$1 ', 1, 0, ''),REGEXP_REPLACE('Jane Doe', 'Jane', 'John', 1, 0, ''),REGEXP_REPLACE('515.123.4444', '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '($1) $2-$3', 1, 0, '') AS `REGEXP_REPLACE`,REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA', '( ){2,}', ' ', 1, 0, '') FROM DUAL; +-------------------------------------------------+------------------------------------------------------+----------------+----------------------------------------------------------------------------------------------+ | REGEXP_REPLACE('china', '(.)', '$1 ', 1, 0, '') | REGEXP_REPLACE('Jane Doe', 'Jane', 'John', 1, 0, '') | REGEXP_REPLACE | REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA', '( ){2,}', ' ', 1, 0, '') | +-------------------------------------------------+------------------------------------------------------+----------------+----------------------------------------------------------------------------------------------+ | c h i n a | John Doe | (515) 123-4444 | 500 Oracle Parkway, Redwood Shores, CA | +-------------------------------------------------+------------------------------------------------------+----------------+----------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
1.3.2.2.12.5.2. REGEXP_SUBSTR
语法
REGEXP_SUBSTR(source_char, pattern
[, position [, occurrence [,match_param [, subexpr ] ] ] ])
- 描述
- 该函数允许使用正则表达式搜索字符串并返回匹配字符串
参数解释
参数 |
说明 |
|---|---|
source_char |
作为正则匹配的源字符串，即被搜索、截取的目标对象。支持任何字符类型的输入（如 VARCHAR2、CHAR、CLOB 等），为函数必选参数。 |
pattern |
定义匹配规则的正则表达式模式，用于识别源字符串中需要截取的内容。必选参数，通常为文本字面量，可包含正则元字符、子表达式（分组）等 |
position |
可选参数，指定从源字符串的第几个字符开始执行正则匹配，取值是一个正整数。默认值是 1，表示从第一个字符开始搜索 |
occurrence |
可选参数，指定返回源字符串中第 N 个符合 pattern 规则的匹配项。默认值为 1 |
match_param |
可选参数，控制正则匹配的行为模式，可组合多个参数（如 ‘im’）。取值范围见下方match_param 取值说明表格 |
subexpr |
可选参数，指定返回 pattern 中 第 N 个子表达式（分组）的匹配结果。是一个介于 0 到 9 之间的整数， 值为 0 时返回整个正则表达式的匹配结果；值为 1-9 时，返回对应序号分组的匹配内容 |
1.3.2.2.12.5.2.1. match_param 取值说明
参数match_param取值 |
说明 |
|---|---|
i |
表示大小写不敏感。 |
c |
大小写敏感匹配（默认）。 |
n |
允许 . 匹配换行符 |
m |
多行模式 |
x |
表示忽略空格字符，默认情况下，空格字符会相互匹配。 goldendb-mysql不支持参数值x |
警告
oracle REGEXP_SUBSTR 函数6个参数，统一sql转换后依赖 GoldenDB-MySQL 支持 ‘REGEXP_REPLACE’ 函数的 6 参数语法（source, pattern,replacement, pos, occurrence, match_type），参考 REGEXP_REPLACE
Oracle 与 GoldenDB MySQL 中 REGEXP_REPLACE 函数处理反斜杠时的行为差异。
oracle 执行语句 SELECT
REGEXP_REPLACE('abc\def', '\\') AS result FROM dual;返回 abcdefgoldendb mysql 执行语句
SELECT REGEXP_REPLACE('abc\def', '\\') AS result FROM dual;返回错误 Unrecognized escape sequence in regular expression.GoldenDB MySQL 中反斜杠的转义规则
字符串层：要表示1个实际的
\，单引号内需要写\\（因为 MySQL 字符串会把\\解析为 1 个\）正则层：正则中
\是转义符，要匹配1个实际的\，正则模式需要写\\；叠加后：正则模式本身是字符串，因此需要写
\\\\（字符串层解析为\\，正则层再解析为\，最终匹配 1 个实际的\）。oracle REGEXP_SUBSTR 函数参数个数 2~5，统一sql转换后依赖 GoldenDB-MySQL 支持 ‘REGEXP_REPLACE’ 函数固定5参数语法。
-- oracle语句执行 SQL> SELECT REGEXP_SUBSTR('abc123def', '[0-9]+') FROM DUAL; REG --- 123 --转换后goldendb-mysql，regexp_substr固定5参数 mysql> SELECT REGEXP_SUBSTR('abc123def', '[0-9]+', 1, 1, '') FROM DUAL; +------------------------------------------------+ | REGEXP_SUBSTR('abc123def', '[0-9]+', 1, 1, '') | +------------------------------------------------+ | 123 | +------------------------------------------------+ 1 row in set (0.01 sec)
参数2：pattern 限制如下
类型必须为ValueExpr（字符串/数值字面量），不支持列、函数、子查询等动态表达式
统一sql会将Oracle的perl正则语法转换为goldendb-MySQL的POSIX兼容格式，支持的perl正则范围，
\d，\D，\w，\W，\s，\S，\A，\Z-- 转换失败，函数 regexp_substr 的参数2（pattern）类型非法：实际类型为 *ast.ColumnNameExpr，仅支持值表达式（ValueExpr） SELECT REGEXP_SUBSTR('abc', column_name, 1, 1) FROM test_table;参数6: subexpr说明，goldendb MySQL 原生的 REGEXP_SUBSTR 函数不支持第6个参数（subexpr，即提取指定捕获组），为了在兼容 goldendb MySQL 的语法中实现 Oracle 第6参数的功能，将 Oracle 风格的 6 参数 REGEXP_SUBSTR 转换为 goldendb MySQL 的 REGEXP_REPLACE 调用。参数映射关系如下图：
oracle参数位置
Oracle 参数名
映射目标
GoldenDB-MySQL 参数位置
1
source_char
REGEXP_REPLACE
1 source_char
2
pattern
REGEXP_REPLACE
2 pattern
3
position
REGEXP_REPLACE
4 position
4
occurrence
REGEXP_REPLACE
5 occurrence
5
match_param
REGEXP_REPLACE
6 match_param
6
subexpr
REGEXP_REPLACE
3 replace_string
-- oracle执行下面sql SQL> SELECT REGEXP_SUBSTR('123-456-7890', '([0-9]{3})-([0-9]{3})-([0-9]{4})', 1, 1, 'c', 2) from dual; REG --- 456 -- 转换后goldendb-mysql ，6参数的函数reg_replace mysql> SELECT regexp_replace('123-456-7890', '([0-9]{3})-([0-9]{3})-([0-9]{4})', '$2', 1, 1, 'c') FROM DUAL; +-------------------------------------------------------------------------------------+ | regexp_replace('123-456-7890', '([0-9]{3})-([0-9]{3})-([0-9]{4})', '$2', 1, 1, 'c') | +-------------------------------------------------------------------------------------+ | 456 | +-------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -- 转换报错函数 regexp_substr 的参数6必须是0-9之间的直接整数字面量（用于指定提取的捕获组编号），当前使用的不是合法的整数字面量，建议：使用0-9之间的 SELECT REGEXP_SUBSTR('123-456', '(\\d+)-(\\d+)', 1, 1, 'c', column_name) FROM test_table;
参数5：match_param取值限制
参数5match_param取值x在goldendb-mysql执行报错，具体报错sql和报错信息示例如下:
mysql> SELECT REGEXP_SUBSTR('dacdACD', 'a .d', 1, 1, 'x') AS a FROM dual; ERROR 1210 (HY000): Incorrect arguments to regexp_substr
示例
-- 转换前Oracle SQL：
SQL> SELECT REGEXP_SUBSTR('500 Oracle Parkway', '[[:alpha:]]+') FROM DUAL;
REGEXP
------
Oracle
SQL> select REGEXP_SUBSTR('123-456-7890', '([0-9]{3})-([0-9]{3})-([0-9]{4})', 1, 1, 'c', 2) from dual;
REG
---
456
-- 转换后goldendb
mysql> SELECT REGEXP_SUBSTR('500 Oracle Parkway', '[[:alpha:]]+', 1, 1, '') FROM DUAL;
+---------------------------------------------------------------+
| REGEXP_SUBSTR('500 Oracle Parkway', '[[:alpha:]]+', 1, 1, '') |
+---------------------------------------------------------------+
| Oracle |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT regexp_replace('123-456-7890', '([0-9]{3})-([0-9]{3})-([0-9]{4})', '$2', 1, 1, 'c') FROM DUAL;
+-------------------------------------------------------------------------------------+
| regexp_replace('123-456-7890', '([0-9]{3})-([0-9]{3})-([0-9]{4})', '$2', 1, 1, 'c') |
+-------------------------------------------------------------------------------------+
| 456 |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1.3.2.2.12.6. 分析函数
1.3.2.2.12.6.1. ROW_NUMBER
语法
ROW_NUMBER() OVER ([PARTITION BY expr [, ...]] [ORDER BY expr [ASC DESC] [, ...]])
描述
该函数为结果集中的每一行分配一个唯一的、连续的整数（从 1 开始），编号依据 ORDER BY 子句指定的顺序生成。
若使用 PARTITION BY，则在每个分区内独立编号；否则对整个结果集编号。
常用于实现 Top-N 查询、分页、去重、分组取最新记录等场景。
⚠️ 注意：ROW_NUMBER() 是分析函数（Analytic Function），必须与 OVER() 子句一起使用。
参数解释
关键字 |
说明 |
|---|---|
PARTITION BY |
可选。将输入数据划分为多个逻辑分区，在每个分区内独立计算行号。支持多个表达式，用逗号分隔。 |
ORDER BY |
可选。定义行号分配的排序规则。支持多列、升序（ASC）或降序（DESC）。 |
expr |
表达式 |
警告
- ORDER BY 子句
在 Oracle 中，若省略 ORDER BY，将报错：ORA-30485: missing ORDER BY expression in the window specification
统一 SQL 转换后，GoldenDB-MySQL 不要求ORDER BY 存在。
- 不能在 WHERE 子句中直接引用
分析函数在 SELECT 阶段计算，无法在 WHERE 中使用。需通过子查询或 CTE 包装后过滤：
SELECT FROM ( SELECT col1, ROW_NUMBER() OVER (ORDER BY col2) AS rn FROM table_name ) WHERE rn <= 10;
- 非确定性排序风险
若 ORDER BY 列存在重复值且未包含唯一标识列（如主键），多次执行可能返回不同行号顺序。
建议：在 ORDER BY 中加入唯一列以确保结果可重现：
ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id ASC)
- 与 GROUP BY 混用限制
在oracle，若 SELECT 中包含非聚合列（如 salary），但未出现在 GROUP BY 中，则不能与 ROW_NUMBER() 同时使用（报 ORA-00979）。
但若 ROW_NUMBER() 作用于聚合结果（如 ORDER BY AVG(salary)），则是合法的。
goldendb-mysql ，不受上面限制，即可以再group by混用的时候，order by 后跟非聚合列。
- 物化视图（Materialized View）兼容性
Oracle 中含 ROW_NUMBER() 的物化视图 不支持快速刷新（FAST REFRESH），仅支持全量刷新（COMPLETE）。
统一sql解析失败，不支持创建物化视图。
- 性能影响
对大数据集，ROW_NUMBER() 需要排序，开销较大。
优化建议：在 ORDER BY 列上创建索引，Oracle 可能使用 WINDOW SORT PUSHED RANK 优化 Top-N 查询。
示例
-- 示例1：基本用法（全局编号）
SELECT employee_id, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;
转换后sql
SELECT
employee_id,
salary,
ROW_NUMBER() OVER (
ORDER BY salary DESC
) AS rn
FROM employees;
-- 示例2：分组内编号（每部门薪资排名）
SELECT department_id, employee_id, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, employee_id) AS rn
FROM employees;
转换后sql
SELECT
department_id,
employee_id,
salary,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC, employee_id
) AS rn
FROM
employees;
-- 示例3：Top-2 高薪员工（每部门）
SELECT department_id, employee_id, salary
FROM (
SELECT department_id, employee_id, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, employee_id) AS rn
FROM employees
)
WHERE rn <= 2
ORDER BY department_id, salary DESC;
转换后sql
SELECT
department_id,
employee_id,
salary
FROM
(
SELECT
department_id,
employee_id,
salary,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC, employee_id
) AS rn
FROM
employees
) AS uni_sub
WHERE
rn <= 2
ORDER BY
department_id,
salary DESC;
-- 示例4：去重（保留 employee_id 最大的记录）
DELETE FROM employees
WHERE employee_id NOT IN (
SELECT employee_id FROM (
SELECT employee_id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY employee_id DESC) AS rn
FROM employees
) WHERE rn = 1
);
转换后sql
DELETE FROM
employees
WHERE
employee_id NOT IN (
SELECT
*
FROM
(
SELECT
employee_id
FROM
(
SELECT
employee_id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY employee_id DESC
) AS rn
FROM
employees
) AS uni_sub
WHERE
rn = 1
) AS unisub
);
1.3.2.2.12.6.2. TO_CHAR
- 语法
TO_CHAR(expr)TO_CHAR(expr,fmt)- 描述
- 该函数功能是将 数值、时间 等类型的数据转换为 字符 类型的值
参数解释
参数 |
说明 |
|---|---|
expr |
必填参数，属于 timestamp/date/int/number/decimal/float/double 数据类型的数值。 |
fmt |
可选参数，支持时间格式 YYYY/MM/DD/HH/MI/SS/MON，支持数值常量格式 9/0/./FM |
警告
仅支持一个和两个参数的场景;
to_char(null) 在oracle和golden_mysql表现不一致;
to_char的第二个参数仅支持字符串字面量;
to_char第一个参数支持类型timestamp, date, int, number, decimal, float, double, number和decimal需要带精度;
to_char第二个参数支持时间格式 YYYY/YY/MM/DD/HH24/HH12/MI/SS/MON, 数值常量格式 9/0/./FM;
示例
-- 转换前Oracle SQL：
select to_char(10), to_char(123.45), to_char(1234.56, '99999.999'), to_char(DATE '2023-12-15', 'YYYYDDMM'), to_char(TIMESTAMP '2023-12-15 14:30:45.123456', 'YYYY-MM-DD HH24:MI:SS') FROM dual;
TO_CHAR(10)|TO_CHAR(123.45)|TO_CHAR(1234.56,'99999.999')|TO_CHAR(DATE'2023-12-15','YYYYDDMM')|TO_CHAR(TIMESTAMP'2023-12-1514:30:45.123456','YYYY-MM-DDHH24:MI:SS')|
-----------+---------------+----------------------------+------------------------------------+--------------------------------------------------------------------+
10 |123.45 | 1234.560 |20231512 |2023-12-15 14:30:45 |
-- 转换后goldendb-MySQL：
SELECT to_char(10),to_char(123.45),to_char(1234.56, '99999.999'),to_char(DATE '2023-12-15', 'YYYYDDMM'),to_char(TIMESTAMP '2023-12-15 14:30:45.123456', 'YYYY-MM-DD HH24:MI:SS') FROM dual
+-------------+-----------------+-------------------------------+----------------------------------------+--------------------------------------------------------------------------+
| to_char(10) | to_char(123.45) | to_char(1234.56, '99999.999') | to_char(DATE '2023-12-15', 'YYYYDDMM') | to_char(TIMESTAMP '2023-12-15 14:30:45.123456', 'YYYY-MM-DD HH24:MI:SS') |
+-------------+-----------------+-------------------------------+----------------------------------------+--------------------------------------------------------------------------+
| 10 | 123.45 | 1234.560 | 20231512 | 2023-12-15 14:30:45 |
+-------------+-----------------+-------------------------------+----------------------------------------+--------------------------------------------------------------------------+