1.3.3.2.4. 转换为 OceanBase-Oracle

1.3.3.2.4.1. 时间日期函数

1.3.3.2.4.1.1. NOW

语法
NOW()
描述
用于返回当前的日期和时间。

返回类型

DATETIME

警告

  • 统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异，差异点如下：

    1. mysql和mariadb的now函数受其时区timezone的影响，目标库返回current_timestamp函数,既遵循会话时区，又在结果中包含了时区偏移，两者包含相同的时间和日期信息。

    2. mysql和mariadb的now函数的结果样式固定，目标库返回current_timestamp函数,显示样式受参数NLS_TIMESTAMP_TZ_FORMAT控制.

示例

-- 转换前MySQL SQL：
select now();
now()                  |
-----------------------+
2025-05-15 10:31:56.000|

-- 转换后OceanBase-Oracle  SQL：
SELECT current_timestamp(0) FROM DUAL
 CURRENT_TIMESTAMP                   |
-------------------------------------+
 15-MAY-25 10:31:56. PM +08:00       |

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3';
SELECT current_timestamp(0) FROM DUAL;
 CURRENT_TIMESTAMP                   |
-------------------------------------+
 2025-05-15 10:31:56.000             |

1.3.3.2.4.1.2. SYSDATE

语法
SYSDATE()
描述
用于返回当前的日期和时间。

返回类型

DATETIME

警告

  • 统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异，差异点如下：

    1. mysql和mariadb的sysdate函数受其时区timezone的影响，目标库返回current_timestamp函数,既遵循会话时区，又在结果中包含了时区偏移，两者包含相同的时间和日期信息。

    2. mysql和mariadb的sysdate函数的结果样式固定，目标库返回current_timestamp函数,显示样式受参数NLS_TIMESTAMP_TZ_FORMAT控制。

    3. mysql和mariadb的sysdate函数每次调用都会重新返回系统时间，能反应函数被调用的真实时刻，而目标库返回current_timestamp函数始终返回语句开始时刻的同一值。

示例

-- 转换前MySQL SQL：
select sysdate();
sysdate()              |
-----------------------+
2025-05-16 16:03:01.000|

-- 转换后OceanBase-Oracle  SQL：
SELECT current_timestamp(0) FROM DUAL
 CURRENT_TIMESTAMP                   |
-------------------------------------+
 16-MAY-25 16:03:01. PM +08:00       |

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3';
SELECT current_timestamp(0) FROM DUAL;
 CURRENT_TIMESTAMP                   |
-------------------------------------+
 2025-05-16 16:03:01.000             |

1.3.3.2.4.1.3. CURDATE

语法
CURDATE()
描述
用于返回当前的日期和时间。

返回类型

DATE

警告

  • 统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异，差异点如下：

    1. mysql和mariadb的CURDATE函数受其时区timezone的影响，目标库返回SYSDATE函数,依赖数据库服务器操作系统时区。

    2. mysql和mariadb的CURDATE函数的结果样式固定，仅日期信息，目标库返回SYSDATE函数,显示样式受参数NLS_DATE_FORMAT控制，默认返回日期信息，但是实际包含日期和时间信息。

示例

-- 转换前MySQL SQL：
select curdate();
curdate() |
----------+
2025-05-19|

-- 转换后OceanBase-Oracle  SQL：
SELECT sysdate FROM DUAL
SYSDATE   |
----------+
2025-05-19|

1.3.3.2.4.1.4. CURRENT_TIME

语法
CURRENT_TIME(n)
描述
用于返回当前的时间。

参数解释

参数

说明

n

整数，范围[0,6]，表示秒的小数位数（精度,最多6位）

返回类型

TIME

警告

  • 统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异，差异点如下：

    1. mysql和mariadb的CURRENT_TIME函数返回值仅含时分秒，目标库CURRENT_TIMESTAMP函数返回日期、时分秒、时区信息。

    2. mysql和mariadb的CURRENT_TIME函数受其时区TIMEZONE的影响，目标库返回CURRENT_TIMESTAMP函数,既遵循当前会话时区，又在结果中包含了时区偏移。

    3. mysql和mariadb的CURRENT_TIME函数的结果样式固定，仅日期信息，目标库返回CURRENT_TIMESTAMP函数,显示样式受参数NLS_TIMESTAMP_TZ_FORMAT控制

示例

-- 转换前MySQL SQL：
select current_time(4)
   current_time(4) |
-------------------+
   13:44:30.7459   |

-- 转换后OceanBase-Oracle  SQL：
SELECT current_timestamp(4) FROM DUAL
 CURRENT_TIMESTAMP                   |
-------------------------------------+
 20-MAY-25 13:44:30.7459 PM +08:00   |

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3';
SELECT current_timestamp(4) FROM DUAL;
 CURRENT_TIMESTAMP                   |
-------------------------------------+
 2025-05-20 13:44:30.7459            |

1.3.3.2.4.1.5. DATE_FORMAT

语法
DATE_FORMAT(date,format)
描述
用于将日期/时间格式化为字符串的函数

参数解释

参数

说明

date

支持DATE、DATETIME、TIMESTAMP数据类型的日期时间表达式,字符串范围为年月日、年月日 时分秒；日期分隔符（-、/、.），时间分隔符（:、.）不可混用每次只能用一种,不支持中文和时区；

format

格式化字符串，只支持常量字符串,支持的格式转化见下表

原格式

目标格式

说明

%c

MM

月份，数值

%d

DD

日，数值(00-31)

%e

DD

日，数值(0-31)

%f

FF6

微秒

%H

HH24

小时 (00-23)

%h

HH12

小时 (01-12)

%I

HH12

小时 (01-12)

%i

MI

分钟，数值(00-59)

%k

HH24

小时 (0-23)

%l

HH12

小时 (1-12)

%M

Month

月份名

%m

MM

月，数值(00-12)

%S

SS

秒(00-59)

%s

SS

秒(00-59)

%T

HH24:MI:SS

时间, 24-小时 (hh:mm:ss)

%Y

YYYY

年，4 位

%y

YY

年，2 位

%W

Day

星期名（如 Monday）

%p

AM

上午/下午（AM 或 PM）

%r

HH:MI:SS AM

12 小时制时间（含 AM/PM）

返回类型

VARCHAR

警告

  • 统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异，差异点如下：

    1. mysql和mariadb的日期格式部分日期格式，如%c、%e、%k、%l，不会补前导零，目标库返回对应的日期格式会有前导零。

    2. mysql和mariadb的date数据类型支持%f的日期格式，目标库的date类型不支持转化的FF6日期格式。

    3. mysql和mariadb的日期格式%W（星期名）返回都是英文，目标库通过设置 NLS_DATE_LANGUAGE 来设定返回对应的语言。

    4. 字符串日期格式非法mysql返回的是null，目标库则会报错;

    5. 日期时间需要为标准格式，年4位，月、日、时、分、秒2位

    6. date、timestamp和timestamptz目标库返回显示样式受参数NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT、NLS_TIMESTAMP_TZ_FORMAT控制

示例

-- 转换前MySQL SQL：
select date_format(STR_TO_DATE('2020-01-01 08:13:13','%Y-%m-%d %H:%i:%s'), '%Y/%m/%d %h %i %s');
date_format(STR_TO_DATE('2020-01-01 08:13:13','%Y-%m-%d %H:%i:%s'), '%Y/%m/%d %h %i %s')|
----------------------------------------------------------------------------------------+
2020/01/01 08 13 13                                                                     |

-- 转换后OceanBase-Oracle  SQL：
SELECT to_char(unisql.str_to_timestamp(to_timestamp('2020-01-01 08:13:13', 'YYYY-MM-DD HH24:MI:SS')), 'YYYY/MM/DD HH MI SS') FROM DUAL
TO_CHAR(UNISQL.STR_TO_TIMESTAMP(TO_TIMESTAMP('2020-01-0108:13:13','YYYY-MM-DDHH24:MI:SS')),'YYYY/MM/DDHHMISS')|
----------------------------------------------------------------------------------------------------------------------+
2020/01/01 08 13 13                                                                                                   |

1.3.3.2.4.1.6. DAY

语法
DAY(date)
描述
用于从日期或日期时间值中提取”日”部分的函数。

参数解释

参数

说明

date

一个有效的日期或日期时间表达式，支持date类型的参数;字符串范围为年月日、年月日 时分秒；日期分隔符（-、/、.），时间分隔符（:、.）不可混用每次只能用一种,不支持中文和时区；

返回类型

整型

警告

  • 统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异，差异点如下：

    1. 如果入参为非法时间格式如: 2024-01-32， 10000-01-32， 0000-00-00，mysql和mariadb会返回NULL或者0，而OceanBase-Oracle转换后会执行报错。

    2. 尽量保证入参日期范围和格式标准，对于非标准的日期格式，例如： 024-01-32， 2024-01-1 统一SQL可能会成功转换，但不保证目标端执行结果准确。

    3. 字符串日期格式非法mysql返回的是null，目标库则会报错;

    4. 日期时间需要为标准格式，年4位，月、日、时、分、秒2位

    5. date、timestamp和timestamptz目标库返回显示样式受参数NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT、NLS_TIMESTAMP_TZ_FORMAT控制

示例

-- 转换前MySQL SQL：
select day(STR_TO_DATE('2024-01-31 01:00:00', '%Y-%m-%d %H:%i:%s'));
day(STR_TO_DATE('2024-01-31 01:00:00', '%Y-%m-%d %H:%i:%s'))|
------------------------------------------------------------+
                                                         31|


-- 转换后OceanBase-Oracle  SQL：
SELECT extract(DAY FROM unisql.str_to_timestamp(to_timestamp('2024-01-31 01:00:00', 'YYYY-MM-DD HH24:MI:SS'))) FROM DUAL
EXTRACT(DAYFROMUNISQL.STR_TO_TIMESTAMP(TO_TIMESTAMP('2024-01-3101:00:00','YYYY-MM-DDHH24:MI:SS')))|
----------------------------------------------------------------------------------------------------------+
                                                                                                      31|

1.3.3.2.4.1.7. YEAR

语法
YEAR(date)
描述
用于从日期或日期时间值中提取年份部分的函数。

参数解释

参数

说明

date

一个有效的日期或日期时间表达式，支持date类型的参数;字符串范围为年月日、年月日 时分秒；日期分隔符（-、/、.），时间分隔符（:、.）不可混用每次只能用一种,不支持中文和时区；

返回类型

整型

警告

  • 统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异，差异点如下：

    1. 如果入参为非法时间格式如: 2024-01-32， 10000-01-32， 0000-00-00，mysql和mariadb会返回NULL或者0，而OceanBase-Oracle转换后会执行报错。

    2. 尽量保证入参日期范围和格式标准，对于非标准的日期格式，例如： 024-01-32， 2024-01-1 统一SQL可能会成功转换，但不保证目标端执行结果准确。

    3. 字符串日期格式非法mysql返回的是null，目标库则会报错;

    4. 日期时间需要为标准格式，年4位，月、日、时、分、秒2位

    5. date、timestamp和timestamptz目标库返回显示样式受参数NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT、NLS_TIMESTAMP_TZ_FORMAT控制

示例

-- 转换前MySQL SQL：
select year(STR_TO_DATE('2024-01-31 01:00:00', '%Y-%m-%d %H:%i:%s')) as year;
year|
----+
2024|


-- 转换后OceanBase-Oracle  SQL：
SELECT extract(YEAR FROM unisql.str_to_timestamp(to_timestamp('2024-01-31 01:00:00', 'YYYY-MM-DD HH24:MI:SS'))) AS year FROM DUAL   YEAR                      |
YEAR|
----+
2024|

1.3.3.2.4.1.8. DAYOFWEEK

语法
dayofweek(date)
描述
用于获取日期对应的星期几的函数

参数解释

参数

说明

date

一个有效的日期或日期时间表达式，支持date类型的参数;字符串范围为年月日、年月日 时分秒；日期分隔符（-、/、.），时间分隔符（:、.）不可混用每次只能用一种,不支持中文和时区；

返回类型

整型:[1-7] 对应为 [星期日-星期六]

警告

  • 统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异，差异点如下：

    1. 如果入参为非法时间格式如: 2024-01-32， 10000-01-32， 0000-00-00，mysql和mariadb会返回NULL或者0，而OceanBase-Oracle转换后会执行报错。

    2. 尽量保证入参日期范围和格式标准，对于非标准的日期格式，例如： 024-1-32， 2024-01-1 统一SQL可能会成功转换，但不保证目标端执行结果准确。

    3. 字符串日期格式非法mysql返回的是null，目标库则会报错;

    4. 日期时间需要为标准格式，年4位，月、日、时、分、秒2位

    5. date、timestamp和timestamptz目标库返回显示样式受参数NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT、NLS_TIMESTAMP_TZ_FORMAT控制

示例

-- 转换前MySQL SQL：
select dayofweek(STR_TO_DATE('2025-04-05', '%Y-%m-%d')) as dayofweekalias;
dayofweekalias|
--------------+
            7|


-- 转换后OceanBase-Oracle  SQL：
SELECT to_number(to_char(unisql.str_to_timestamp(to_timestamp('2025-04-05', 'YYYY-MM-DD')), 'D')) AS dayofweekalias FROM DUAL   DAYOFWEEKALIAS            |
DAYOFWEEKALIAS|
--------------+
            7|

1.3.3.2.4.1.9. WEEKOFYEAR

语法
weekofyear(date)
描述
用于返回一个日期处于当年的周数，其返回值范围是 1 到 53之间（大多数年份有52周，闰年可能有53周）

参数解释

参数

说明

date

一个有效的日期或日期时间表达式，当前支持符合时间格式的字符串,date或datetime字段类型的数据库字段，时间函数

返回类型

整型:[1 到 53 之间的整数值]

警告

  • 统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异和限制，如下：

    1. 目前转换依赖OceanBase-Oracle数据库的 NLS 配置值, 可以通过执行SQL： SELECT * FROM NLS_SESSION_PARAMETERS; 查看, 当前版本支持的配置值为：

      • NLS_DATE_FORMAT ： YYYY-MM-DD HH24:MI:SS

      • NLS_TIMESTAMP_FORMAT ： DD-MON-RR HH.MI.SSXFF AM

      • NLS_TIMESTAMP_TZ_FORMAT : DD-MON-RR HH.MI.SSXFF AM TZR

    2. 尽量保证入参日期范围和格式标准，对于非标准的日期格式，例如： 024-1-32， 2024-01-1 统一SQL可能会成功转换，但不保证目标端执行结果准确。

    3. 如果入参为合法的时间格式的 字符串类型 时，当前版本支持的时间格式为:[年月日]或[年月日 时分秒]，必须使用如下分隔符分割，日期部分支持:[ -, /, .]，时间部分支持:[:, .]，分隔符不支持混用，不支持无分隔符，否则返回结果会与mariadb会返结果不一致。

    4. 如果入参为 时间函数 时，当前版本支持的时间函数有：NOW()、SYSDATE()、TIMESTAMP()、CURDATE()、CURRENT_TIME()、FROM_UNIXTIME()、STR_TO_DATE()、DATE_FORMAT()，如果入参为其他时间函数时，返回结果会与mariadb会返结果不一致。 时间函数的format的格式支持范围为：[%Y-%m-%d %H:%i:%s]、[%Y%m%d%H%i%S]、[%Y%m%d%H%i%s]、[Y%m%d]

示例

-- 转换前MySQL SQL：
SELECT WEEKOFYEAR('2023-01-02') as a;
+-------------------------------+
| a                             |
+-------------------------------+
|                         1     |
+-------------------------------+


-- 转换后OceanBase-Oracle  SQL：
SELECT unisql.weekofyear('2023-01-02') as a FROM dual;
               a          |
--------------------------+
                      1   |

-- 转换前MySQL SQL：
select weekofyear(now()) as a;
+-------------------------------+
| a                             |
+-------------------------------+
|                         21    |
+-------------------------------+


-- 转换后OceanBase-Oracle  SQL：
SELECT unisql.weekofyear(current_timestamp(0)) as a FROM DUAL
              a           |
--------------------------+
                      21  |

1.3.3.2.4.1.10. STR_TO_DATE

语法
STR_TO_DATE(str,format)
描述
将字符串转换为日期的函数

参数解释

参数

说明

str

日期字符串，只支持源库类型VARCHAR

format

格式化字符串，只支持常量字符串

返回类型

在源库中，当format中包含日期和时间部分时返回datetime类型，当format中只包含日期部分时返回date类型，当format中只包含时间部分时返回time类型。
转换后在目标库中，统一都返回TIMESTAMP类型

格式串转换说明

原格式

目标格式

说明

%c

MM

月份，数值

%d

DD

日，数值(00-31)

%e

DD

日，数值(0-31)

%f

FF6

微秒

%H

HH24

小时 (00-23)

%h

HH12

小时 (01-12)

%I

HH12

小时 (01-12)

%i

MI

分钟，数值(00-59)

%k

HH24

小时 (0-23)

%l

HH12

小时 (1-12)

%M

Month

月份名

%m

MM

月，数值(00-12)

%S

SS

秒(00-59)

%s

SS

秒(00-59)

%T

HH24:MI:SS

时间, 24-小时 (hh:mm:ss)

%Y

YYYY

年，4 位

%y

YY

年，2 位

警告

    1. 因为在目标库中空串’’等价NULL值，所以当函数入参为空串时，会返回NULL。这点与MySQL和MariaDB不同。

    1. 转换时支持的格式串见格式串转换说明，不支持的格式串会被直接透传到目标库（如%p %r %W）。在转换上语义上是等价的，但是由于返回值类型存在差异，所以在目标库执行结果可能会存在展示差异以实际为准。

    1. 当format仅包含时间类型时，MYSQL返回的是TIME类型只包含时间部分，但是在目标库中返回的是TIMESTAMP类型，包含日期部分。参见 数据类型转换映射表。

    1. 当str中的月份不满4位时，如24，MYSQL中会前补20，OCEANBASE-ORACLE中是前补00，导致转换后结果不一致，建议用户使用时写4位的年份。

示例

-- 转换前MySQL SQL：
SELECT STR_TO_DATE('2025/01/06 14:30:29', '%Y/%m/%d %H:%i:%s');
2025-01-06 14:30:29

-- 转换后OceanBase-Oracle  SQL：
SELECT to_timestamp('2025/01/06 14:30:29', 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
2025-01-06 14:30:29.000

1.3.3.2.4.1.11. DATE_ADD

语法
DATE_ADD (timestamp, interval)
描述
用于将指定的时间间隔添加到日期或时间值上,目标库返回的日期时间类型为TIMESTAMP。

参数解释

参数

说明

timestmap

日期时间，该参数可以为date、datetime、timestamp数据类型或者int、number、字符串。 范围为年月日、年月日 时分秒；日期分隔符（-、/、.），时间分隔符（:、.）不可混用每次只能用一种；

interval

时间间隔，该参数为interval类型，支持清单：年（year）/月（month）/日（day）/时（hour）/分（minute）/秒（second）

警告

  • 经统一sql转换后返回的类型是TIMESTAMP;

  • interval暂不支持小数;

  • timestmap的入参format的格式为：年月日时分秒的顺序格式;

  • timestmap参数中支持的函数作为入参的有：NOW()、SYSDATE()、TIMESTAMP()、CURDATE()、CURRENT_TIME()、FROM_UNIXTIME()、STR_TO_DATE()、DATE_FORMAT()，format的格式为：年月日时分秒的顺序格式;

  • 返回的内容值精度在不同客户端工具显示毫秒有差异，用自身的客户端查看实际返回的时间精度是正确的。也可以配合设置 NLS_TIMESTAMP_FORMAT使用。例如：ALTER SESSION SET NLS_TIMESTAMP_FORMAT =’YYYY-MM-DD HH24:MI:SS.FF’;

  • 目标库默认的NLS_DATE_FORMAT 不包含时间只包含日期，导致用户date 类型的时间部分会被截掉。可以在Oracle服务端配置session级别：ALTER SESSION SET NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’;

  • 返回的格式受NLS控制，业务可自行调整。例如：ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY-MM-DD’; ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ‘YYYY-MM-DD HH24:MI:SS.FF6’; ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = ‘YYYY-MM-DD HH24:MI:SS.FF3 TZR’;

示例

-- 转换前MySQL SQL：
SELECT DATE_ADD('2024-02-29', INTERVAL 1 year);
DATE_ADD('2024-02-29', INTERVAL 1 year)|
---------------------------------------+
2025-02-28                             |

-- 转换后OceanBase-Oracle SQL：
SELECT unisql.date_add('2024-02-29', 1, 'year') FROM DUAL
UNISQL.DATE_ADD('2024-02-29',1,'YEAR')|
--------------------------------------+
               2025-02-28 00:00:00.000|

1.3.3.2.4.1.12. DATE_SUB

语法
DATE_SUB (timestamp, interval)
描述
用于从指定的日期或时间值中减去一个时间间隔,目标库返回的日期时间类型为TIMESTAMP。

参数解释

参数

说明

timestmap

日期时间，该参数可以为date、datetime、timestamp数据类型或者int、number、字符串。 范围为年月日、年月日 时分秒；日期分隔符（-、/、.），时间分隔符（:、.）不可混用每次只能用一种；

interval

时间间隔，该参数为interval类型，支持清单：年（year）/月（month）/日（day）/时（hour）/分（minute）/秒（second）

警告

  • 经统一sql转换后返回的类型是TIMESTAMP;

  • interval暂不支持小数;

  • timestmap的入参format的格式为：年月日时分秒的顺序格式;

  • timestmap参数中支持的函数作为入参的有：NOW()、SYSDATE()、TIMESTAMP()、CURDATE()、CURRENT_TIME()、FROM_UNIXTIME()、STR_TO_DATE()、DATE_FORMAT()，format的格式为：年月日时分秒的顺序格式;

  • 返回的内容值精度在不同客户端工具显示毫秒有差异，用自身的客户端查看实际返回的时间精度是正确的。也可以配合设置 NLS_TIMESTAMP_FORMAT使用。例如：ALTER SESSION SET NLS_TIMESTAMP_FORMAT =’YYYY-MM-DD HH24:MI:SS.FF’;

  • 目标库默认的NLS_DATE_FORMAT 不包含时间只包含日期，导致用户date 类型的时间部分会被截掉。可以在Oracle服务端配置session级别：ALTER SESSION SET NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’;

  • 返回的格式受NLS控制，业务可自行调整。例如：ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY-MM-DD’; ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ‘YYYY-MM-DD HH24:MI:SS.FF6’; ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = ‘YYYY-MM-DD HH24:MI:SS.FF3 TZR’;

示例

-- 转换前MySQL SQL：
SELECT DATE_SUB('2025-02-28', INTERVAL 1 year);
DATE_SUB('2025-02-28', INTERVAL 1 year)|
---------------------------------------+
2024-02-28                             |


-- 转换后OceanBase-Oracle SQL：
SELECT unisql.date_sub('2025-02-28', 1, 'year') FROM DUAL
UNISQL.DATE_SUB('2025-02-28',1,'YEAR')|
--------------------------------------+
               2024-02-28 00:00:00.000|

1.3.3.2.4.1.13. UNIX_TIMESTAMP

语法
UNIX_TIMESTAMP ([timestamp])
描述
获取自 ‘1970-01-01 00:00:00’ UTC 到 timestamp 指定的时间的秒数。如果没有参数，
则返回从 ‘1970-01-01 00:00:00’ UTC 以来的秒数。

参数解释

参数

说明

timestamp

日期时间，该参数可以为date、datetime、timestamp数据类型或者整型、字符串。

返回类型

number 类型

警告

  • 字符串格式只支持年月日时分秒或年月日，不支持年月日时分，年月日时等不全的格式;

  • 字符串格式月，日，时，分不支持单位时间，必须为双位，即不支持 '2023-1-5 14:3:0' 这种，需要补0为 '2023-01-05 14:03:0';

  • 字符串格式不支持时区，mysql8支持时区，mariadb 不支持，统一SQL转换不支持时区;

  • 对于date、datetime、timestamp数据类型入参，当超出mariadb范围时，其结果受NLS（NLS_DATE_FORMAT，NLS_TIMESTAMP_FORMAT，NLS_TIMESTAMP_TZ_FORMAT）参数控制，

    • mariadb 中unix_timestamp最大值为 '2038-01-19 05:14:07' utc

    • 在MYSQL8 中(大于8.0.28)，在64位平台，最大值为 '3001-01-18 23:59:59' utc;

    • 当超出范围，mariadb 返回null, mysql 返回0

    • 默认参数格式下，若入参超出unix_timestamp支持范围, 不支持，结果和MYSQL，mariadb 不同，如 SELECT UNIX_TIMESTAMP(timestamp'2999-11-11 12:12:12') 在 OB-Oracle下为 942293532

    • 在YYYY-MM-DD HH24:MI:SS.FF 格式下, 结果也不同，ob-oracle 小于最小值为负数，大于最大值为实际值（相当于没有mariadb 的最大最小限制）， 如 SELECT UNIX_TIMESTAMP(timestamp'2999-11-11 12:12:12') 在 OB-Oracle下为 32499288732

    • 其他格式不支持，不保证目标端执行结果准确（比如：若会丢失日期时间信息，则结果不一致，如：YYYY-MM-DD 没有时分秒。不符合上述对字符串格式的限制，也不支持。），

    • 对于4.2.1 版本可以通过修改参数unisql.target.database.version为040201，使用另一转换方案，忽略NLS参数影响

示例

-- 转换前MySQL SQL：
SELECT UNIX_TIMESTAMP(timestamp '2025-05-19 20:29:30') as timestamp;
timestamp |
----------+
1747657770|

SELECT UNIX_TIMESTAMP('2025-05-19 20:29:30') as timestamp;
timestamp |
----------+
1747657770|


-- 转换后OceanBase-Oracle SQL：
SELECT (extract(day FROM (TIMESTAMP '2025-05-19 20:29:30'-
                to_timestamp('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF6'))
               )*86400+
        extract(hour FROM (TIMESTAMP '2025-05-19 20:29:30'-
                to_timestamp('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF6'))
               )*3600-
        extract(TIMEZONE_HOUR FROM systimestamp)*3600+
        extract(minute FROM (TIMESTAMP '2025-05-19 20:29:30'-
                to_timestamp('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF6'))
               )*60+
        extract(second FROM (TIMESTAMP '2025-05-19 20:29:30'-
                to_timestamp('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF6'))
               )
       ) AS timestamp
 FROM DUAL
timestamp |
----------+
1747657770|

SELECT (extract(day FROM (unisql.str_to_timestamp('2025-05-19 20:29:30')-to_timestamp('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF6')))*86400+
        extract(hour FROM (unisql.str_to_timestamp('2025-05-19 20:29:30')-to_timestamp('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF6')))*3600-
   extract(TIMEZONE_HOUR FROM systimestamp)*3600+
   extract(minute FROM (unisql.str_to_timestamp('2025-05-19 20:29:30')-to_timestamp('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF6')))*60+
   extract(second FROM (unisql.str_to_timestamp('2025-05-19 20:29:30')-to_timestamp('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF6')))
   ) AS timestamp FROM DUAL
timestamp |
----------+
1747657770|

1.3.3.2.4.1.14. FROM_UNIXTIME

语法
FROM_UNIXTIME (unix_timestamp[, format])
描述
转换自 (1970-01-01 00:00:00 UTC) 以来的秒数到 TIMESTAMP WITH TIME ZONE 类型的值, 和 UNIX_TIMESTAMP() 功能相反。

参数解释

参数

说明

unix_timestamp

自 (1970-01-01 00:00:00 UTC) 以来的秒数

format

可选参数，指定输出的日期时间格式。只支持常量字符串，支持的格式转化见DATE_FORMAT函数

返回类型

  • TIMESTAMP WITH TIME ZONE 类型

  • 如果指定了 format，则返回类型为varchar2。

警告

  • 统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异，差异点如下：

    1. mysql和mariadb的日期格式部分日期格式，如%c、%e、%k、%l，不会补前导零，目标库返回对应的日期格式会有前导零。

    2. mysql和mariadb的日期格式%W（星期名）返回都是英文，目标库通过设置 NLS_DATE_LANGUAGE 来设定返回对应的语言。

    3. 无format 显示结果受NLS_TIMESTAMP_TZ_FORMAT参数控制。

示例

-- 转换前MySQL SQL：
SELECT from_unixtime(2147483647) as timestamp;
+---------------------+
| timestamp           |
+---------------------+
| 2038-01-19 11:14:07 |


SELECT FROM_UNIXTIME(1672531200, '%Y-%m-%d %W') as timestamp;
+-------------------+
| timestamp         |
+-------------------+
| 2023-01-01 Sunday |
+-------------------+



-- 转换后OceanBase-Oracle SQL, 无format 显示结果受NLS_TIMESTAMP_TZ_FORMAT参数控制：
SELECT (
  (TIMESTAMP '1970-01-01 00:00:00 +00:00'+NUMTODSINTERVAL(2147483647, 'SECOND')) AT TIME ZONE SESSIONTIMEZONE
 ) as timestamp FROM DUAL
+----------------------------------------+
| TIMESTAMP                              |
+----------------------------------------+
| 19-JAN-38 11.14.07.000000000 AM +08:00 |
+----------------------------------------+


SELECT to_char(((TIMESTAMP '1970-01-01 00:00:00 +00:00'+NUMTODSINTERVAL(1672531200, 'SECOND')) AT TIME ZONE SESSIONTIMEZONE), 'YYYY-MM-DD Day') AS timestamp FROM DUAL;
+----------------------+
| TIMESTAMP            |
+----------------------+
| 2023-01-01 Sunday    |
+----------------------+

1.3.3.2.4.1.15. TIMESTAMP

语法
TIMESTAMP (expr)
描述
把 mariadb 的 date、datetime或字符串类型的表达式 expr 转换为 datetime 类型的值

参数解释

参数

说明

expr

mariadb 的 date 或 datetime 类型的表达式、字符串

返回类型

timestamp

警告

  • 统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异，差异点如下：

    1. expr为字符串类型时，字符串包含年月日时分秒（不支持毫秒），且均有分隔符进行分割或者仅包含年月日。

    2. expr为字符串类型时，年月日支持分隔符’-‘、’/’、’.’,时分秒支持的分隔符为’:’。

    3. expr为字符串类型时，字符串仅为年月日，要求长度等于8，最小值为1，月的最小值为1，日的最小值为1。

    4. OceanBase-Oracle显示的是12小时制，会存在差异。

    5. 不支持函数表达式和字符串类型的字段。

    6. timestamp函数边界值，源库和目标库不一致，OceanBase-Oracle返回null。

示例

-- 转换前MySQL SQL：
SELECT timestamp(timestamp '2025-05-21 20:59:59') as timestamp;
timestamp          |
-------------------+
2025-05-21 20:59:59|

SELECT TIMESTAMP('2023/10/25 4:30:45') AS result;
result             |
-------------------+
2023-10-25 04:30:45|


-- 转换后OceanBase-Oracle SQL：
SELECT CAST(TIMESTAMP '2025-05-21 20:59:59' AS timestamp) AS timestamp FROM DUAL
timestamp              |
-----------------------+
2025-05-21 20:59:59.000|

SELECT to_timestamp('2023/10/25 4:30:45', 'YYYY-MM-DD HH24:MI:SS') AS result FROM DUAL
RESULT                 |
-----------------------+
2023-10-25 04:30:45.000|

1.3.3.2.4.1.16. TIME_FORMAT

语法
TIME_FORMAT(expr，format)
描述
用于根据指定格式格式化时间值。它返回一个字符串，表示根据格式字符串格式化的时间。

参数解释

参数

说明

expr

timestamp类型或者字符串类型

format

时间显示格式，支持 date_format 清单中的%f、%H、%h、%I、%i、%k、%l、%S、%s、%T

警告

  • 统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异，差异点如下：

    1. expr为字符串类型时，字符串包含年月日时分秒，且均有分隔符进行分割或者仅包含年月日。

    2. expr为字符串类型时，年月日支持分隔符’-‘、’/’、’.’,时分秒支持的分隔符为’:’。

    3. 年月日与时分秒之间只支持空格。

    4. 字符串类型为’00010101’时显示格式不一致。

    5. 不支持格式中带字符串例:’time is %Hh%im%ss’。

    6. format格式中存在%k或者%l时显示格式不一致。

    7. format 只能为常量字符串，不然转换报错。

    8. 字符串小时部分大于23分钟秒大与59转换成功执行不支持。

    9. format为NULL转换成功执行为NULL。

    10. format为’%%’转换成功但是不支持。

    11. OceanBase-Oracle显示的是12小时制，会存在差异。

    12. timestamp函数边界值，源库和目标库不一致，OceanBase-Oracle返回null。

    13. OceanBase-Oracle数据库转换依赖的 NLS 配置值, 可以通过执行SQL： SELECT * FROM NLS_SESSION_PARAMETERS; 查看, 当前版本支持的配置值为：

      • NLS_DATE_FORMAT ： YYYY-MM-DD HH24:MI:SS

      • NLS_TIMESTAMP_FORMAT ： DD-MON-RR HH.MI.SSXFF AM

      • NLS_TIMESTAMP_TZ_FORMAT : DD-MON-RR HH.MI.SSXFF AM TZR

示例

-- 转换前MySQL SQL：
SELECT TIME_FORMAT('2021/11/11 14:30:45', '%H:%i:%s') AS basic_format;
basic_format|
------------+
14:30:45    |

-- 转换后 SQL：
SELECT unisql.TIME_FORMAT('2021/11/11 14:30:45', 'HH24:MI:SS') AS basic_format FROM DUAL
BASIC_FORMAT|
------------+
14:30:45    |

1.3.3.2.4.2. 字符串函数

1.3.3.2.4.2.1. LOCATE

语法
LOCATE (substr, str [, position])
描述
返回子字符串第一次出现的位置。 若任一入参是NULL或’’，则返回NULL。

参数解释

参数

说明

substr

需要查找的字串，该参数为字符串类型

str

需要被查找的父串，该参数为字符串类型

position

正整数，从父串中指定位置开始查找子串（初始值为1），该参数为整形类型，可选参数，不填时默认从父串启示位置开始查找子串

警告

  • 由于OceanBase-Oracle中无法区分空串与NULL，所以在OceanBase-Oracle 中只能将’’与NULL，都返回NULL。

  • Mysql根据排序规则可以设置区分大小写，而OceanBase-Oracle不行，所以转换后的函数大小写敏感。

  • position 仅支持正整数或正整数字符串。若为浮点数数值将存在差异，mysql为四舍五入，OceanBase-Oracle向下取整。

示例

-- 转换前MySQL SQL：
SELECT LOCATE('bar', 'foobarbar', 5) FROM dual;
+-------------------------------+
| LOCATE('bar', 'foobarbar', 5) |
+-------------------------------+
|                             7 |
+-------------------------------+

SELECT LOCATE('bar', 'foobarbar', 1) FROM dual;
+-------------------------------+
| LOCATE('Bar', 'foobarbar', 1) |
+-------------------------------+
|                             4 |
+-------------------------------+


SELECT LOCATE('Bar', 'foobarbar', 1) FROM dual;
+-------------------------------+
| LOCATE('Bar', 'foobarbar', 1) |
+-------------------------------+
|                             4 |
+-------------------------------+


SELECT LOCATE('', 'foobarbar', 1) FROM dual;
+----------------------------+
| LOCATE('', 'foobarbar', 1) |
+----------------------------+
|                          1 |
+----------------------------+


SELECT LOCATE(NULL, 'foobarbar', 1) FROM dual;
+------------------------------+
| LOCATE(NULL, 'foobarbar', 1) |
+------------------------------+
|                         NULL |
+------------------------------+


-- 转换后OceanBase-Oracle  SQL：
SELECT instr('foobarbar', 'bar', 5) FROM dual;
INSTR('FOOBARBAR','BAR',5)|
--------------------------+
                         7|


SELECT instr('foobarbar', 'bar', 1) FROM dual;
INSTR('FOOBARBAR','BAR',1)|
--------------------------+
                         4|


SELECT instr('foobarbar', 'Bar', 1) FROM dual;
INSTR('FOOBARBAR','BAR',1)|
--------------------------+
                         0|


SELECT instr('foobarbar', NULL, 1) FROM dual;
INSTR('FOOBARBAR',NULL,1)|
-------------------------+
                         |


SELECT instr('foobarbar', '', 1) FROM dual;
INSTR('FOOBARBAR','',1)|
-----------------------+
                       |

1.3.3.2.4.2.2. CONCAT

语法
CONCAT (str1, str2, ...)
描述
将入参字符串拼接在一起。

参数解释

参数

说明

str

需要拼接的字符串，该参数为字符串数据类型

警告

  • 由于oceanbase-oracle中无法区分空串与NULL,所以在oceanbase-oracle中,只能将’’与NULL一视同仁来处理。

  • MySQL的函数参数如果有NULL则会返回NULL，而oceanbase-oracle不会。

示例

-- 转换前MySQL SQL：
select concat('123', 'abcAA', 'nihao') from dual;
+---------------------------------+
| concat('123', 'abcAA', 'nihao') |
+---------------------------------+
| 123abcAAnihao                   |
+---------------------------------+


select concat('123', '', 'nihao') from dual;
+----------------------------+
| concat('123', '', 'nihao') |
+----------------------------+
| 123nihao                   |
+----------------------------+



select concat('123', NULL, 'nihao') from dual;
+------------------------------+
| concat('123', NULL, 'nihao') |
+------------------------------+
| NULL                         |
+------------------------------+



select concat('123abc') from dual;
+------------------+
| concat('123abc') |
+------------------+
| 123abc           |
+------------------+


-- 转换后oceanbase-oracle SQL：
SELECT concat(concat('123', 'abcAA'), 'nihao') FROM dual;
CONCAT(CONCAT('123','ABCAA'),'NIHAO')|
-------------------------------------+
123abcAAnihao                        |


SELECT concat(concat('123', ''), 'nihao') FROM dual;
CONCAT(CONCAT('123',''),'NIHAO')|
--------------------------------+
123nihao                        |


SELECT concat(concat('123', NULL), 'nihao') FROM dual;
CONCAT(CONCAT('123',NULL),'NIHAO')|
----------------------------------+
123nihao                          |


SELECT concat('123abc', '') FROM dual;
CONCAT('123ABC','')|
-------------------+
123abc             |

1.3.3.2.4.2.3. GROUP_CONCAT

语法
GROUP_CONCAT ([DISTINCT] column_name… [ORDER BY ASC/DESC column_name] [Separator sep_str])
描述
将来自多个行的列值组合成一个单独的字符串，每个值之间可以用分隔符隔开。 column_name 仅支持为一个。

参数解释

参数

说明

column_name

需要拼接的字符串列，该参数为字符串数据类型

sep_str

分割标识符，默认为逗号(,)分割

警告

  • GROUP_CONCAT函数与listagg函数拼接顺序不完全一致。行展示顺序不一致，建议使用该函数时根据列名进行排序。

  • 存在表达式计算时精度、标度和oceanbase-oracle不完全一致。

  • 暂时不支持和keep、over联合使用和聚合函数使用时不支持嵌套使用

  • GROUP BY分组顺序存在不一致的情况。

  • 和聚合函数使用时不支持嵌套使用。

示例

 CREATE TABLE sales (
 id int,
 product VARCHAR(50),
 quantity INT,
 sale_date varchar(50)
 );

 INSERT INTO sales VALUES (4, 'Apple', 10, '2023-01-15');
 INSERT INTO sales VALUES (4, 'Banana', 5, '2023-01-20');
 INSERT INTO sales VALUES (1, 'Orange', 8, '2023-02-01');
 INSERT INTO sales VALUES (1, 'Grape', 12, '2023-01-10');
 INSERT INTO sales VALUES (3, 'Apple', 3, '2023-03-01');


 -- 转换前MySQL SQL：
 SELECT id, GROUP_CONCAT( product  ORDER BY id SEPARATOR ',') AS products FROM sales GROUP BY id;
 id|products    |
 --+------------+
  1|Grape,Orange|
  3|Apple       |
  4|Banana,Apple|

 SELECT id, GROUP_CONCAT( product  SEPARATOR ',') AS products FROM sales GROUP BY id;
 id|products    |
 --+------------+
  1|Orange,Grape|
  3|Apple       |
  4|Apple,Banana|

 SELECT id, GROUP_CONCAT(DISTINCT product  SEPARATOR ',') AS products FROM sales GROUP BY id;
id|products    |
--+------------+
 1|Grape,Orange|
 3|Apple       |
 4|Apple,Banana|


 -- 转换后oceanbase-oracle SQL：
 SELECT id,listagg(product, ',') WITHIN GROUP (ORDER BY id) AS products FROM sales GROUP BY id;
 ID|PRODUCTS    |
 --+------------+
  1|Grape,Orange|
  3|Apple       |
  4|Apple,Banana|


 SELECT id,listagg(product, ',') AS products FROM sales GROUP BY id;
 ID|PRODUCTS    |
 --+------------+
 1|Orange,Grape|
 4|Apple,Banana|
 3|Apple       |


  SELECT id,listagg(DISTINCT product, ',') AS products FROM sales GROUP BY id;
  ID|PRODUCTS    |
 --+------------+
  1|Grape,Orange|
  3|Apple       |
  4|Apple,Banana|

1.3.3.2.4.2.4. LEFT

语法
LEFT (str, length)
描述
截取指定长度的字符串

参数解释

参数

说明

str

被截取的源字符串，该参数为字符串数据类型

length

需要截取的字符串长度，该参数为数值类型 (不支持小数)

警告

  • left函数转换逻辑: mariadb的函数left(str,length)转换到ob-oracle的函数substr(str,1,length)

  • 函数参数处理差异:

    • mariadb 中，NULL 和空字符串（""）不相等；

    • OB-Oracle 中，NULL 和空字符串（""）视为相等，均返回 NULL；ob-oracle的sql示例 select substr(‘’,1,2) from dual; select substr(‘abc’,1,’’) from dual;

    • 不论mariadb还是ob-oracle，若参数 str、 或 length 为 NULL，函数返回 NULL。 maridb的sql示例 select left(null,1); select left(‘abc’,null); select left(null,null)

  • left函数的参数类型限制:

    • 参数str仅支持字符串类型，或可隐式转换为字符串的类型（如 NUMBER）。sql示例，select left(123.34,4);

    • 参数length支持字符串类型数字，sql示例，select left(123.34,’1’);

  • 不一致行为说明:

    1. 函数参数count为0或者负数时:

      • MariaDB: 返回空字符串（示例: SELECT left('apple,banana,cherry',0)）。

      • OB-Oracle: 返回 NULL。 对应sql示例 SELECT substr(‘apple,banana,cherry’, 1, 0) FROM DUAL

    2. 空字符串输入时:

      • MariaDB: 输入空字符串（如 ""）返回空字符串（示例: SELECT left("", 1) , SELECT left('23bac', "") ）。

      • OB-Oracle: 返回 NULL。对应sql示例 SELECT substr(‘’, 1, 1) FROM DUAL; SELECT substr(‘23bac’, 1, ‘’) FROM DUAL

    3. 函数参数str作为字符串类型为空时:

      • MariaDB: 返回空字符串。 参考上方示例说明 SELECT left(“”, 1)

      • OB-Oracle: 返回 NULL。

    4. 函数参数length作为非字符串类型为空时:

      • MariaDB: 对空字符串返回空字符串。参考上方示例说明 SELECT left(‘23bac’, “”)

      • OB-Oracle: 将 NULL 或空字符串（""）均视为 NULL。

示例

-- 转换前MySQL SQL：
     mysql> select left('hello world',5);
     +-----------------------+
     | left('hello world',5) |
     +-----------------------+
     | hello                 |
     +-----------------------+


     mysql> select left('',2);
     +------------+
     | left('',2) |
     +------------+
     |            |
     +------------+

     mysql> select left('abc','');
     +----------------+
     | left('abc','') |
     +----------------+
     |                |
     +----------------+

     mysql> select left(null,1);
     +----------------------------+
     | left(null,1)               |
     +----------------------------+
     | NULL                       |
     +----------------------------+


     mysql> select left('abc',null);
     +------------------+
     | left('abc',null) |
     +------------------+
     | NULL             |
     +------------------+

     mysql> SELECT left('apple,banana,cherry',0);
     +-------------------------------+
     | left('apple,banana,cherry',0) |
     +-------------------------------+
     |                               |
     +-------------------------------+

     mysql> select left(123.34,'1');
     +------------------+
     | left(123.34,'1') |
     +------------------+
     | 1                |
     +------------------+

     mysql> SELECT left("", 1);
     +-------------+
     | left("", 1) |
     +-------------+
     |             |
     +-------------+

     mysql> SELECT left('23bac', "");
     +-------------------+
     | left('23bac', "") |
     +-------------------+
     |                   |
     +-------------------+



-- 转换后ob_oracle SQL：
     obclient [xxx]> SELECT substr('hello world', 1, 5) FROM DUAL;
     +--------------------------+
     | SUBSTR('HELLOWORLD',1,5) |
     +--------------------------+
     | hello                    |
     +--------------------------+

     obclient [xxx]> SELECT substr('', 1, 2) FROM DUAL;
     +----------------+
     | SUBSTR('',1,2) |
     +----------------+
     | NULL           |
     +----------------+

     obclient [xxx]> SELECT substr('abc', 1, '') FROM DUAL;
     +--------------------+
     | SUBSTR('ABC',1,'') |
     +--------------------+
     | NULL               |
     +--------------------+

     obclient [xxx]> SELECT substr(NULL, 1, 1) FROM DUAL;
     +------------------+
     | SUBSTR(NULL,1,1) |
     +------------------+
     | NULL             |
     +------------------+

     obclient [xxx]> SELECT substr('abc', 1, NULL) FROM DUAL;
     +----------------------+
     | SUBSTR('ABC',1,NULL) |
     +----------------------+
     | NULL                 |
     +----------------------+

     obclient [xxx]> SELECT substr('apple,banana,cherry', 1, 0) FROM DUAL;
     +-----------------------------------+
     | SUBSTR('APPLE,BANANA,CHERRY',1,0) |
     +-----------------------------------+
     | NULL                              |
     +-----------------------------------+

     obclient [xxx]> SELECT substr(123.34, 1, '1') FROM DUAL;
     +----------------------+
     | SUBSTR(123.34,1,'1') |
     +----------------------+
     | 1                    |
     +----------------------+

     obclient [xxx]> SELECT substr('', 1, 1) FROM DUAL;
     +----------------+
     | SUBSTR('',1,1) |
     +----------------+
     | NULL           |
     +----------------+

     obclient [SYS]> SELECT substr('23bac', 1, '') FROM DUAL;
     +----------------------+
     | SUBSTR('23BAC',1,'') |
     +----------------------+
     | NULL                 |
     +----------------------+

1.3.3.2.4.2.5. SUBSTRING

语法
SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
描述
返回 str 的子字符串，起始位置为 pos，长度为 len。

参数解释

参数

说明

str

要操作的字符串，支持varchar2、char、clob、number(n,m)类型

pos

整数，子字符串的起始位置

len

可选，正整数，子字符串的长度

警告

  • substring函数转换逻辑: mariadb的函数SUBSTRING(str,pos,len)转换到ob-oracle的函数substr(str,pos,len)

  • 函数参数处理差异:

    • mariadb 中，NULL 和空字符串（""）不相等；

    • OB-Oracle 中，NULL 和空字符串（""）视为相等，均返回 NULL；ob-oracle的sql示例 select substr('',1,2) from dual; , select substr('abc',1,'') from dual;

    • 不论mariadb还是ob-oracle，若参数 str、 pos、或 len 为 NULL，函数返回 NULL。 maridb的sql示例 select SUBSTRING(NULL, 1, 3); , select SUBSTRING('MariaDB', NULL, 3) , SUBSTRING('MariaDB', 1, NULL)

  • substring函数的参数类型限制:

    • 参数str仅支持字符串类型，或可隐式转换为字符串的类型（如 NUMBER）。sql示例，select SUBSTRING(123.34,4);

    • 参数pos支持字符串类型数字，sql示例，select SUBSTRING(‘MariaDB’, ‘2’,’3’);

    • 参数len支持字符串类型数字，sql示例，select SUBSTRING(‘MariaDB’, ‘2’,’3’);

  • 不一致行为说明:

    1. 函数参数pos为负数时，绝对值超过了字符串长度:

      • MariaDB: 返回空字符串（示例: select SUBSTRING('MariaDB', -10);）。

      • OB-Oracle: 返回 NULL。 对应sql示例 select SUBSTR('MariaDB', -10) from dual;

    2. 函数参数pos为0时:

      • MariaDB: 函数参数str不是空串也不是null，函数参数len无论取正数，负数，还是0,均返回 空字符串 。 示例: select SUBSTRING('MariaDB', 0,2); , select SUBSTRING('MariaDB', 0, -2); , select SUBSTRING('MariaDB', 0, 0);

      • OB-Oracle:

        • 函数参数str不是空串也不是null，函数参数len<=0，返回 NULL， 对应sql示例 select SUBSTR('MariaDB', 0, -2) from dual; , select SUBSTR('MariaDB', 0, 0) from dual;

        • 函数参数str不是空串也不是null，函数参数len>0，此时pos相当于1， 对应sql示例 select SUBSTR('MariaDB', 0, 2) from dual; , select SUBSTR('MariaDB', 0, 10) from dual;

    3. 空字符串输入时:

      • MariaDB: 输入空字符串（如 ""）返回空字符串（示例: select SUBSTRING('', 1, 3); , select SUBSTRING('23bac', 1, ''); ）。

      • OB-Oracle: 返回 NULL。对应sql示例 SELECT substr('', 1, 3) FROM DUAL; , SELECT substr('23bac', 1, '') FROM DUAL

    4. 函数参数str作为字符串类型为空时:

      • MariaDB: 返回空字符串。 参考上方示例说明 SELECT SUBSTRING("", 1)

      • OB-Oracle: 返回 NULL。 对应sql示例 SELECT substr('', 1) FROM DUAL;

    5. 函数参数len作为非字符串类型为空时:

      • MariaDB: 对空字符串返回空字符串。示例说明 SELECT SUBSTRING('23bac',0, "")

      • OB-Oracle: 将 NULL 或空字符串（""）均视为 NULL。

    6. 函数参数len为0或者负数:

      • MariaDB: 对空字符串返回空字符串。示例说明 select SUBSTRING('MariaDB', 2,0); , select SUBSTRING('MariaDB', 2,-3);

      • OB-Oracle: 返回null，对应sql示例 SELECT substr('MariaDB', 2, 0) FROM DUAL; , SELECT substr('MariaDB', 2, -3) FROM DUAL;

示例

-- 转换前Mysql SQL：
 mysql> select SUBSTRING(123.34,4);
 +---------------------+
 | SUBSTRING(123.34,4) |
 +---------------------+
 | .34                 |
 +---------------------+

 mysql> select SUBSTRING('MariaDB', '2','3');
 +-------------------------------+
 | SUBSTRING('MariaDB', '2','3') |
 +-------------------------------+
 | ari                           |
 +-------------------------------+


 mysql> select SUBSTRING('MariaDB', -10);
 +---------------------------+
 | SUBSTRING('MariaDB', -10) |
 +---------------------------+
 |                           |
 +---------------------------+

 mysql> select SUBSTRING('MariaDB', 0,-2);
 +----------------------------+
 | SUBSTRING('MariaDB', 0,-2) |
 +----------------------------+
 |                            |
 +----------------------------+

 mysql> select SUBSTRING('', 1, 3);
 +---------------------+
 | SUBSTRING('', 1, 3) |
 +---------------------+
 |                     |
 +---------------------+

 mysql> select SUBSTRING('23bac', 1, '');
 +---------------------------+
 | SUBSTRING('23bac', 1, '') |
 +---------------------------+
 |                           |
 +---------------------------+

 mysql> SELECT SUBSTRING("", 1);
 +------------------+
 | SUBSTRING("", 1) |
 +------------------+
 |                  |
 +------------------+

 mysql> SELECT SUBSTRING('23bac',1, "");
 +--------------------------+
 | SUBSTRING('23bac',1, "") |
 +--------------------------+
 |                          |
 +--------------------------+

 mysql> select SUBSTRING('MariaDB', 2,0);
 +---------------------------+
 | SUBSTRING('MariaDB', 2,0) |
 +---------------------------+
 |                           |
 +---------------------------+

 mysql> select SUBSTRING('MariaDB', 2,-3);
 +----------------------------+
 | SUBSTRING('MariaDB', 2,-3) |
 +----------------------------+
 |                            |
 +----------------------------+





-- 转换后Ob_oracle SQL：
 obclient [SYS]> select SUBSTR(123.34,4) from dual;
 +------------------+
 | SUBSTR(123.34,4) |
 +------------------+
 | .34              |
 +------------------+

 obclient [SYS]>  select SUBSTR('MariaDB', '2','3') from dual;
 +---------------------------+
 | SUBSTR('MARIADB','2','3') |
 +---------------------------+
 | ari                       |
 +---------------------------+

 obclient [SYS]> select SUBSTR('MariaDB', -10) from dual;
 +-----------------------+
 | SUBSTR('MARIADB',-10) |
 +-----------------------+
 | NULL                  |
 +-----------------------+

 obclient [SYS]> select SUBSTR('MariaDB', 0,-2) from dual;
 +------------------------+
 | SUBSTR('MARIADB',0,-2) |
 +------------------------+
 | NULL                   |
 +------------------------+

 obclient [SYS]> select SUBSTR('', 1, 3) from dual;
 +----------------+
 | SUBSTR('',1,3) |
 +----------------+
 | NULL           |
 +----------------+

 obclient [SYS]> SELECT substr('23bac', 1, '') FROM DUAL;
 +----------------------+
 | SUBSTR('23BAC',1,'') |
 +----------------------+
 | NULL                 |
 +----------------------+

 obclient [SYS]> SELECT substr('', 1) FROM DUAL;
 +--------------+
 | SUBSTR('',1) |
 +--------------+
 | NULL         |
 +--------------+

 obclient [SYS]> SELECT substr('23bac', 1, '') FROM DUAL;
 +----------------------+
 | SUBSTR('23BAC',1,'') |
 +----------------------+
 | NULL                 |
 +----------------------+

 obclient [SYS]> SELECT substr('MariaDB', 2, 0) FROM DUAL;
 +-----------------------+
 | SUBSTR('MARIADB',2,0) |
 +-----------------------+
 | NULL                  |
 +-----------------------+

 obclient [SYS]> SELECT substr('MariaDB', 2, -3) FROM DUAL;
 +------------------------+
 | SUBSTR('MARIADB',2,-3) |
 +------------------------+
 | NULL                   |
 +------------------------+

1.3.3.2.4.2.6. SUBSTRING_INDEX

语法
SUBSTRING_INDEX (str, delimiter, count)
描述
根据指定的分隔符 delimiter 和计数 count，从字符串 str 中提取子字符串。 若 count 为正数，则返回从字符串开头到第 count 个分隔符的所有内容； 若 count 为负数，则返回从字符串末尾到第 count 个分隔符的所有内容。

参数解释

参数

说明

str

要处理的原始字符串

delimiter

用于分割字符串的分隔符，必须为字符串类型

count

整数，指定分隔符的计数。正数表示从开头计数，负数表示从末尾计数

警告

  • 前提:ob oracle 3.2.x 和 4.2.x 版本都不支持 substring_index 函数，也没有与之等价的函数，所以这次是通过自定义函数unisql.substring_index的方式，实现了 mariadb 中 substring_index 函数的功能。

  • 参数处理差异:

    • MySQL 中，NULL 和空字符串（""）不相等；

    • OB-Oracle 中，NULL 和空字符串（""）视为相等，均返回 NULL；

    • 若参数 str、delimiter 或 count 为 NULL，函数返回 NULL。

  • 大小写敏感:

    • 本函数 区分大小写，分隔符匹配时会严格区分大小写。

    • mariadb sql示例 SELECT SUBSTRING_INDEX('Apple,Banana,Cherry', 'b', 1); 分隔符b在字符串不存在返回全部字符串

    • 对应 ob oracle sql示例 SELECT unisql.substring_index('Apple,Banana,Cherry', 'b', 1) FROM DUAL; 分隔符b在字符串不存在返回全部字符串

  • 参数类型限制:

    • 入参str仅支持字符串类型，或可隐式转换为字符串的类型（如 NUMBER）。

  • 不一致行为说明:

    1. 函数参数count 为 0 时:

      • MariaDB: 返回空字符串（示例: SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', 0)）。

      • OB-Oracle: 返回 NULL。对应sql示例为 SELECT unisql.substring_index('apple,banana,cherry', ',', 0) FROM DUAL;

    2. 空字符串输入时:

      • MariaDB: 输入空字符串（如 ""）返回空字符串（示例: SELECT SUBSTRING_INDEX("", ',', 1)）。

      • OB-Oracle: 返回 NULL。对应sql示例 SELECT unisql.substring_index('', ',', 1) FROM DUAL

    3. 分隔符位于字符串首尾时:

      • MariaDB: 分隔符在首部（如 ‘,a,b’ 且 count=1）或在尾部（如 'a,b,' 且 count=-1）均返回空字符串。

      • 示例1:分隔符在首部 SELECT SUBSTRING_INDEX(',apple,banana,cherry', ',', 1)。

      • 示例2:分隔符在尾部 SELECT SUBSTRING_INDEX('apple,banana,cherry,', ',', -1)。

      • OB-Oracle: 返回 NULL。对应sql 示例，以分隔符在尾部为例 SELECT unisql.substring_index('apple,banana,cherry,', ',', -1) FROM DUAL

    4. 入参字符串类型为空时:

      • MariaDB: 返回空字符串。 sql示例 SELECT SUBSTRING_INDEX('abc', '', 1); , SELECT SUBSTRING_INDEX('', ',', 1);

      • OB-Oracle: 返回 NULL。 对应sql示例 SELECT unisql.substring_index('abc', '', 1) FROM DUAL; , SELECT unisql.substring_index('', ',', 1) FROM DUAL;

    5. 入参非字符串类型为空时:

      • MariaDB: 对空字符串返回空字符串（示例: SELECT SUBSTRING_INDEX(123, ',', "")）。

      • OB-Oracle: 将 NULL 或空字符串（""）均视为 NULL。 对应的sql示例 SELECT unisql.substring_index(123, ',', '') FROM DUAL

    6. ob oracle 3.2.3.x 版本不支持嵌套listagg函数:

      • 数据准备: CREATE TABLE test_substring_index (id INT, str VARCHAR(50));INSERT INTO test_substring_index VALUES (1, 'apple'), (2, 'banana');

      • MariaDB: SELECT SUBSTRING_INDEX(GROUP_CONCAT(str), ',', 1) as a FROM test_substring_index; 返回 apple

      • OB-Oracle: 执行转换后sql SELECT unisql.substring_index(listagg(str, ','), ',', 1) AS a FROM test_substring_index 在ob-oracle执行报错 ORA-30496 Argument should be a constant.

    7. ob oracle 3.2.3.x 版本不支持嵌套ISNULL函数:

      • 数据准备: CREATE TABLE test_substring_index (id INT, str VARCHAR(50));INSERT INTO test_substring_index VALUES (1, NULL);

      • MariaDB: SELECT SUBSTRING_INDEX(ISNULL(str), ',', 1) as a FROM test_substring_index WHERE id = 1; 返回 1

      • OB-Oracle: 执行转换后sql SELECT unisql.substring_index(ISNULL(str), ',', 1) AS a FROM test_substring_index WHERE id=1 在ob-oracle执行报错 ORA-00600 Incorrect number of arguments

      • ob oracle 3.2.3.x 报错修复方案: 使用子查询 ，子查询中先对str字段进行ISNULL处理， SELECT unisql.substring_index(processed_str, ',', 1) AS a FROM ( SELECT ISNULL(str) AS processed_str FROM test_substring_index WHERE id = 1 ) subquery;

示例

-- 转换前MySQL SQL：
 mysql> SELECT SUBSTRING_INDEX('Apple,Banana,Cherry', 'b', 1);
 +------------------------------------------------+
 | SUBSTRING_INDEX('Apple,Banana,Cherry', 'b', 1) |
 +------------------------------------------------+
 | Apple,Banana,Cherry                            |
 +------------------------------------------------+

 mysql> SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', 0);
 +------------------------------------------------+
 | SUBSTRING_INDEX('apple,banana,cherry', ',', 0) |
 +------------------------------------------------+
 |                                                |
 +------------------------------------------------+

 mysql> SELECT SUBSTRING_INDEX("", ',', 1);
 +-----------------------------+
 | SUBSTRING_INDEX("", ',', 1) |
 +-----------------------------+
 |                             |
 +-----------------------------+

 mysql> SELECT SUBSTRING_INDEX('abc', '', 1);
 +-------------------------------+
 | SUBSTRING_INDEX('abc', '', 1) |
 +-------------------------------+
 |                               |
 +-------------------------------+


 mysql> SELECT SUBSTRING_INDEX('apple,banana,cherry,', ',', -1);
 +--------------------------------------------------+
 | SUBSTRING_INDEX('apple,banana,cherry,', ',', -1) |
 +--------------------------------------------------+
 |                                                  |
 +--------------------------------------------------+

 mysql> SELECT SUBSTRING_INDEX(123, ',', "");
 +-------------------------------+
 | SUBSTRING_INDEX(123, ',', "") |
 +-------------------------------+
 |                               |
 +-------------------------------+

SELECT SUBSTRING_INDEX('www.example.com', '.', 2) ;
SUBSTRING_INDEX('www.example.com', '.', 2)|
-----------------------------------------+
                           www.example|

SELECT SUBSTRING_INDEX('www.example.com', '.', -2) ;
SUBSTRING_INDEX('www.example.com', '.', -2)|
------------------------------------------+
                          example.com|

-- 转换后 ob_oracle SQL：
 obclient [SYS]>  SELECT unisql.substring_index('Apple,Banana,Cherry', 'b', 1) FROM DUAL;
 +-----------------------------------------------------+
 | UNISQL.SUBSTRING_INDEX('APPLE,BANANA,CHERRY','B',1) |
 +-----------------------------------------------------+
 | Apple,Banana,Cherry                                 |
 +-----------------------------------------------------+


 obclient [SYS]> SELECT unisql.substring_index('apple,banana,cherry', ',', 0) FROM DUAL;
 +-----------------------------------------------------+
 | UNISQL.SUBSTRING_INDEX('APPLE,BANANA,CHERRY',',',0) |
 +-----------------------------------------------------+
 | NULL                                                |
 +-----------------------------------------------------+

 obclient [SYS]> SELECT unisql.substring_index('', ',', 1) FROM DUAL;
 +----------------------------------+
 | UNISQL.SUBSTRING_INDEX('',',',1) |
 +----------------------------------+
 | NULL                             |
 +----------------------------------+

 obclient [SYS]> SELECT unisql.substring_index('apple,banana,cherry,', ',', -1) FROM DUAL;
 +-------------------------------------------------------+
 | UNISQL.SUBSTRING_INDEX('APPLE,BANANA,CHERRY,',',',-1) |
 +-------------------------------------------------------+
 | NULL                                                  |
 +-------------------------------------------------------+

 obclient [SYS]> SELECT unisql.substring_index('abc', '', 1) FROM DUAL;
 +------------------------------------+
 | UNISQL.SUBSTRING_INDEX('ABC','',1) |
 +------------------------------------+
 | NULL                               |
 +------------------------------------+

 obclient [SYS]> SELECT unisql.substring_index(123, ',', '') FROM DUAL;
 +------------------------------------+
 | UNISQL.SUBSTRING_INDEX(123,',','') |
 +------------------------------------+
 | NULL                               |
 +------------------------------------+

SELECT unisql.substring_index('www.example.com', '.', 2) FROM DUAL
+-------------------------------------------------+
| UNISQL.SUBSTRING_INDEX('WWW.EXAMPLE.COM','.',2) |
+-------------------------------------------------+
| www.example                                     |
+-------------------------------------------------+


SELECT unisql.substring_index('www.example.com', '.', -2) FROM DUAL
+--------------------------------------------------+
| UNISQL.SUBSTRING_INDEX('WWW.EXAMPLE.COM','.',-2) |
+--------------------------------------------------+
| example.com                                      |
+--------------------------------------------------+

1.3.3.2.4.2.7. CHAR_LENGTH

语法
CHAR_LENGTH (str)
描述
计算字符串的长度

参数解释

参数

说明

str

需要计算长度的字符串，该参数为字符串数据类型

警告

  • 约束:

    • MariaDB中char_length函数可以处理入参字段类型为blob

    • MariaDB中字段类型binary，当前统一sql 不支持。

  • 函数参数处理差异:

    • MariaDB中，NULL 和空字符串（""）不相等； NULL 返回NULL， 空字符串（""）返回0 ,见下方示例。

    • OB-Oracle 中，NULL 和空字符串（""）视为相等，均返回 NULL；见下方示例。

  • char数据类型在不同ob Oracle版本及参数配置下的行为差异:

    1. mariadb中char_length函数返回实际字符数，与字符集无关。 sql示例 select char_length(‘😀你好123abc?’) 返回10

    2. ob Oracle 3.2.x 版本:

      • 不受nls_length_semantics影响：无论该系统变量取值为char还是byte，length函数返回值均为： 字符数 + 待填充的空格数 其中，待填充的空格数 = 字段定义长度（n，如char(n)） - 实际字节数。

      • （注：char类型为固定长度，不足n时会用空格填充，此处返回的是填充后的总长度逻辑。）

      • 多字节字符举例，字段类型char(20)，字段内容多字节字符’😀你好123abc?’,这是10个字符。其中：😀占4字节，’你’和’好’各占3字节，’123abc?’共7个单字节字符（每个占1字节），总字节数为4+3+3+7=17字节。因17字节小于char(20)的20字节长度，会自动填充3个空格。填充后总字符数为10+3=13，此时length函数返回13

    3. ob Oracle 4.2.x 版本（char类型），受nls_length_semantics变量影响，具体行为分两种情况:

      • 当nls_length_semantics = char（按字符语义）： length函数返回实际字符数（不包含填充的空格）。

      • 当nls_length_semantics = byte（按字节语义）： length函数返回值与3.2.x版本一致，即： 字符数 + 待填充的空格数 其中，待填充的空格数 = 字段定义长度（n） - 实际字节数。

      • 多字节字符举例，当nls_length_semantics = char（按字符语义）,字段类型char(20)，字段内容多字节字符’😀你好123abc?’,length函数返回20

      • 多字节字符举例，当nls_length_semantics = byte（按字节语义）,字段类型char(20)，字段内容多字节字符’😀你好123abc?’,这是10个字符。其中：😀占4字节，’你’和’好’各占3字节，’123abc?’共7个单字节字符（每个占1字节），总字节数为4+3+3+7=17字节。因17字节小于char(20)的20字节长度，会自动填充3个空格。填充后总字符数为10+3=13，此时length函数返回13

示例

-- 转换前MySQL SQL：
mysql> SELECT CHAR_LENGTH('\U+1F600你好123abc?');
+-------------------------------+
| CHAR_LENGTH('?你好123abc?')   |
+-------------------------------+
|                            10 |
+-------------------------------+


mysql>  SELECT CHAR_LENGTH(NULL) AS null_test;
+-----------+
| null_test |
+-----------+
|      NULL |
+-----------+

 mysql>  SELECT CHAR_LENGTH('') AS null_test;
 +-----------+
 | null_test |
 +-----------+
 |         0 |
 +-----------+


SELECT id, CHAR_LENGTH(char_col) AS char_col_length, CHAR_LENGTH(varchar_col) AS varchar_col_length, CHAR_LENGTH(text_col) AS text_col_length FROM test_char_length;

-- 转换后ob oracle SQL：
-- 注意：这里是字符串字面量，没有明确的数据类型，这里默认是varchar数据类型，length返回的是字符数
obclient [xxx]> SELECT length('😀你好123abc?') FROM DUAL;
+-----------------------------+
| LENGTH('😀你好123ABC?')       |
+-----------------------------+
|                          10 |
+-----------------------------+

obclient [xxx]> SELECT length(NULL) AS null_test FROM DUAL;
+-----------+
| NULL_TEST |
+-----------+
|      NULL |
+-----------+

obclient [xxx]> SELECT length('') AS null_test FROM DUAL;
+-----------+
| NULL_TEST |
+-----------+
|      NULL |
+-----------+


SELECT id,length(char_col) AS char_col_length,length(varchar_col) AS varchar_col_length,length(text_col) AS text_col_length FROM test_char_length

1.3.3.2.4.2.8. CONCAT_WS

语法
CONCAT_WS (separator, str1, str2, ...)
描述
将入参字符串与字符串分隔符拼接在一起

参数解释

参数

说明

separator

用于分割每个字符串的分隔符

str

需要拼接的字符串，该参数为字符串数据类型

警告

  • 函数转换逻辑: 将mariadb中concat_ws函数转换到ob oracle这边concat函数嵌套。

  • sql示例 转换前 SELECT id,CONCAT_WS(' | ',CONCAT_WS('-', first_name, last_name),email) AS full_info FROM users;

  • 转换后 SELECT id,concat(concat(concat(concat(first_name, '-'), last_name), ' | '), email) AS full_info FROM users

  • concat_ws函数约束与限制:

    • concat_ws 是单行函数。

    • 最大长度受SQL语句最大长度限制为 max_allow_packet ，执行sql SHOW VARIABLES LIKE 'max_allowed_packet' ，查看 max_allowed_packet 大小， 不可以 会话级别修改，单位是字节。

    • concat_ws 函数 非分隔符入参 null值会被忽略，但是 ob oracle 函数 concat 会将null值作为空串参与到连接,sql示例 select CONCAT_WS('-','A',NULL,'B'); 返回 A-B，忽略null值。 ob oracle对应的sql示例 SELECT concat(concat(concat(concat('A', '-'), NULL), '-'), 'B') FROM DUAL; 返回 A--B，这里将null值作为空串处理。

    • concat_ws 函数 分隔符入参 null值，返回null，但是 ob oracle 函数 concat 会将null值作为空串参与到连接,sql示例 select CONCAT_WS(null,'A','B'); 返回 null。 ob oracle对应的sql示例 SELECT concat(concat('A', NULL), 'B') FROM DUAL 返回 AB，这里将null值作为空串处理。

    • concat_ws 函数入参空串会参与到字符串拼接，ob_oracle中concat函数入参空串也会参与拼接，ob_oracle不区分空串与null值，sql示例 select CONCAT_WS('','A',NULL,'B'); 返回 AB。 ob oracle对应的sql示例 SELECT concat(concat(concat(concat('A', ''), NULL), ''), 'B') FROM DUAL; 返回 AB。

    • 所有参数为 NULL，结果为 NULL，sql示例 SELECT CONCAT_WS(NULL, NULL, NULL, NULL) AS result; 返回 null。 ob oracle对应的sql示例 SELECT concat(concat(concat(concat(NULL, NULL), NULL), NULL), NULL) AS result FROM DUAL; 返回 null。

示例

-- 转换前MySQL SQL：
 mysql> select CONCAT_WS('-','A',NULL,'B');
 +-----------------------------+
 | CONCAT_WS('-','A',NULL,'B') |
 +-----------------------------+
 | A-B                         |
 +-----------------------------+

 mysql> select CONCAT_WS(null,'A','B');
 +-------------------------+
 | CONCAT_WS(null,'A','B') |
 +-------------------------+
 | NULL                    |
 +-------------------------+

 mysql> select CONCAT_WS('','A',NULL,'B');
 +----------------------------+
 | CONCAT_WS('','A',NULL,'B') |
 +----------------------------+
 | AB                         |
 +----------------------------+

 mysql> SELECT CONCAT_WS(NULL, NULL, NULL, NULL) AS result;
 +----------------+
 | result         |
 +----------------+
 | NULL           |
 +----------------+

 mysql> SELECT CONCAT_WS('', 'A', 'B', 'C') AS result;
 +--------+
 | result |
 +--------+
 | ABC    |
 +--------+

 mysql> SELECT CONCAT_WS('-', 'A', NULL, 'B') AS result;
 +--------+
 | result |
 +--------+
 | A-B    |
 +--------+


-- 转换后ob-oracle SQL：
 obclient [sys]>  SELECT concat(concat(concat(concat('A', '-'), NULL), '-'), 'B') FROM DUAL;
 +------------------------------------------------------+
 | CONCAT(CONCAT(CONCAT(CONCAT('A','-'),NULL),'-'),'B') |
 +------------------------------------------------------+
 | A--B                                                 |
 +------------------------------------------------------+

 obclient [sys]> SELECT concat(concat('A', NULL), 'B') FROM DUAL;
 +------------------------------+
 | CONCAT(CONCAT('A',NULL),'B') |
 +------------------------------+
 | AB                           |
 +------------------------------+


 obclient [sys]> SELECT concat(concat(concat(concat('A', ''), NULL), ''), 'B') FROM DUAL;
 +----------------------------------------------------+
 | CONCAT(CONCAT(CONCAT(CONCAT('A',''),NULL),''),'B') |
 +----------------------------------------------------+
 | AB                                                 |
 +----------------------------------------------------+

 obclient [sys]> SELECT concat(concat(concat(concat(NULL, NULL), NULL), NULL), NULL) AS result FROM DUAL;
 +--------+
 | RESULT |
 +--------+
 | NULL   |
 +--------+

 obclient [sys]> SELECT concat(concat(concat(concat('A', ''), 'B'), ''), 'C') AS result FROM DUAL;
 +--------+
 | RESULT |
 +--------+
 | ABC    |
 +--------+


 obclient [sys]> SELECT concat(concat(concat(concat('A', '-'), NULL), '-'), 'B') AS result FROM DUAL;
 +--------+
 | RESULT |
 +--------+
 | A--B   |
 +--------+

1.3.3.2.4.3. 其他函数

1.3.3.2.4.3.1. LAST_INSERT_ID

语法
LAST_INSERT_ID()
描述
返回最近一次 INSERT 语句产生的自增 ID (AUTO_INCREMENT)。

参数解释

该函数没有参数。

警告

  • 该功能需要开启 unisql.gen.lastinsertid.after.insert 配置。

  • 使用 last_insert_id 前必须使用 INSERT 语句且 INSERT 语句对应的表必须有 AUTO_INCREMENT 字段， 其他场景该函数的输出是未定义行为。

  • 使用一条`INSERT` 语句插入多条数据时，返回的是最后一条自增的 ID。

  • 不支持多会话/多线程。

示例

CREATE TABLE test_auto_inc (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));

-- 转换前MySQL SQL / 转换后oceanbase-oracle SQL：
INSERT INTO test_auto_inc (name) VALUES ('apple');
SELECT LAST_INSERT_ID();

+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1                |
+------------------+

1.3.3.2.4.3.2. COMPRESS

语法
compress (plaintext)
描述
压缩字符串并返回二进制数据。

参数解释

参数

说明

plaintext

需要压缩的明文，该参数为字符串类型

警告

  • 未实现压缩行为，仅将内容转成二进制类型。

  • 参数不能超过2000个字符

  • 只支持varchar类型

  • 参数为空串时返回NULL，mysql返回空串

示例

-- 转换前MySQL SQL：
select compress('123abc你好');
+----------------------------------------------------+
| compress('123abc你好')                             |
+----------------------------------------------------+
| 0x0C000000789C3334324E4C4A7EB277C1D3A57B011FE90645 |
+----------------------------------------------------+

-- 转换后oceanbase-oracle SQL：
SELECT UTL_RAW.CAST_TO_RAW('123abc你好') from dual;
+-------------------------------------+
| UTL_RAW.CAST_TO_RAW('123ABC你好')   |
+-------------------------------------+
| 313233616263E4BDA0E5A5BD            |
+-------------------------------------+

1.3.3.2.4.3.3. UNCOMPRESS

语法
uncompress (ciphertext)
描述
解压由compress()函数压缩的二进制数据，并恢复原始字符串。

警告

  • 未实现解压行为，仅将参数转为blob类型。

  • 只支持BLOB和RAW类型参数

  • 参数为空串时返回NULL，mysql返回空串

参数解释

参数

说明

ciphertext

需要解压缩的密文，该参数为二进制类型RAW。

示例

-- 转换前MySQL SQL：
select uncompress(compress('123abc你好'));
+------------------------------------+
| uncompress(compress('123abc你好')) |
+------------------------------------+
| 0x313233616263E4BDA0E5A5BD         |
+------------------------------------+

-- 转换后oceanbase-oracle SQL：
SELECT to_blob(UTL_RAW.CAST_TO_RAW('123abc你好')) from dual;
+------------------------------------------------------------------------------------------+
| TO_BLOB(UTL_RAW.CAST_TO_RAW('123ABC你好'))                                               |
+------------------------------------------------------------------------------------------+
| 313233616263E4BDA0E5A5BD                                                                 |
+------------------------------------------------------------------------------------------+

1.3.3.2.4.3.4. CONVERT

语法
CONVERT (expr, type)
CONVERT (expr using transcoding_name)
描述
将expr转换为指定的数据类型或指定的编码类型。

参数解释

参数

说明

expr

要转换的表达式

type

指定转换的类型，该参数支持unsigned、signed、date、datetime、decimal数据类型

transcoding_name

指定的编码格式，目前只支持utf8、GBK

警告

  • 将 expr 转换为 signed 或 unsigned 类型仅支持整型或整型字符串，并且必须在其合法范围内。

    对于字符串类型的浮点数，MySQL 采用向下取整，而 OceanBase-Oracle 采用四舍五入。

    unsigned 的合法范围为 [0, 18446744073709551615]，signed 的范围为 [-9223372036854775808, 9223372036854775807]。 如果超出这个范围，数据结果可能不一致。 例如，convert(18446744073709551616, unsigned) 会在 MySQL 中返回 18446744073709551615，而在 OceanBase-Oracle 中返回 18446744073709551616。

  • MySQL 和 OceanBase-Oracle 在数据类型上存在差异，例如对 cast(‘2024-01-31 14:30:00’ as date)，MySQL 只记录日期，结果为 2024-01-31，而 OceanBase-Oracle 则记录完整的时间信息，结果为 2024-01-31 14:30:00。

  • 表达式 expr 转换为目标数据类型仅支持相同类型或可以隐式转换的数据类型。 例如，非数值字符串转换为 decimal 时，Oracle 会报错 ORA-01722: 无效数字。

  • 将表达式 expr 转换为指定编码类型时，目前只支持 utf8， GBK。 OceanBase-Oracle 在进行编码转换时，支持的 expr 类型为 CHAR、VARCHAR2、NVARCHAR2、CLOB、NCLOB，或可以隐式转换为上述类型的类型。 当参数 expr 需要隐式转为字符串时，MySQL 和 OceanBase-Oracle 存在差异，例如日期类型转字符串时，会受到日期格式的影响。

  • MySQL 中，null和空字符串不相等，而 oceanbase-oracle 中，null和空字符串相等都是 null。

示例

-- 转换前MySQL SQL：
SELECT  CONVERT('你好, world!' USING utf8) AS utf8_value;
+----------------+
| utf8_value     |
+----------------+
| 你好, world!   |
+----------------+

SELECT  CONVERT('你好, world!' USING gbk) AS gbk_value;
+---------------+
| gbk_value     |
+---------------+
| 你好, world!  |
+---------------+

SELECT CONVERT(1.5,SIGNED) as col_signed;
+------------+
| col_signed |
+------------+
|          2 |
+------------+

SELECT CONVERT(1.5,UNSIGNED) as col_unsigned;
+--------------+
| col_unsigned |
+--------------+
|            2 |
+--------------+

SELECT CONVERT('1.5',SIGNED) as col_signed;
+------------+
| col_signed |
+------------+
|          1 |
+------------+

SELECT CONVERT('1.5',UNSIGNED) as col_unsigned;
+--------------+
| col_unsigned |
+--------------+
|            1 |
+--------------+

SELECT CONVERT(1234,DECIMAL) as  col_decimal;
+-------------+
| col_decimal |
+-------------+
|        1234 |
+-------------+


SELECT CONVERT(STR_TO_DATE('2024-01-31 14:30:00', '%Y-%m-%d %H:%i:%s'),DATE) as col_date;
+------------+
| col_date   |
+------------+
| 2024-01-31 |
+------------+


SELECT CONVERT(STR_TO_DATE('2024-01-31 14:30:00', '%Y-%m-%d %H:%i:%s'),DATETIME) as col_datetime;
+---------------------+
| col_datetime        |
+---------------------+
| 2024-01-31 14:30:00 |
+---------------------+


-- 转换后oceanbase-oracle SQL：

SELECT convert('你好, world!', 'utf8') AS utf8_value FROM DUAL;
+----------------+
| UTF8_VALUE     |
+----------------+
| 你好, world!   |
+----------------+

SELECT convert('你好, world!', 'zhs16gbk') AS gbk_value FROM DUAL;
+---------------+
| GBK_VALUE     |
+---------------+
| 你好, world!  |
+---------------+

SELECT CAST(1.5 AS number(20,0)) AS col_signed FROM DUAL;
+------------+
| COL_SIGNED |
+------------+
|          2 |
+------------+


SELECT CAST(1.5 AS number(20,0)) AS col_unsigned FROM DUAL;
+--------------+
| COL_UNSIGNED |
+--------------+
|            2 |
+--------------+


SELECT CAST('1.5' AS number(20,0)) AS col_signed FROM DUAL;
+------------+
| COL_SIGNED |
+------------+
|          2 |
+------------+


SELECT CAST('1.5' AS number(20,0)) AS col_unsigned FROM DUAL;
+--------------+
| COL_UNSIGNED |
+--------------+
|            2 |
+--------------+


SELECT CAST(1234 AS decimal) AS col_decimal FROM DUAL;
+-------------+
| COL_DECIMAL |
+-------------+
|        1234 |
+-------------+

-- 使用默认的日期格式
SELECT CAST(to_date('2024-01-31 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS date) AS col_date FROM DUAL;
+-----------+
| COL_DATE  |
+-----------+
| 31-JAN-24 |
+-----------+

SELECT CAST(to_date('2024-01-31 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp) AS col_datetime FROM DUAL;
+------------------------------+
| COL_DATETIME                 |
+------------------------------+
| 31-JAN-24 02.30.00.000000 PM |
+------------------------------+


-- 日期格式已经设置为NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' 和 NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
SELECT CAST(to_date('2024-01-31 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS date) AS col_date FROM DUAL;
+---------------------+
| COL_DATE            |
+---------------------+
| 2024-01-31 14:30:00 |
+---------------------+

SELECT CAST(to_date('2024-01-31 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp) AS col_datetime FROM DUAL;
+---------------------+
| COL_DATETIME        |
+---------------------+
| 2024-01-31 14:30:00 |
+---------------------+

1.3.3.2.4.3.5. CAST

语法
CAST (expr AS type)
描述
将 expr 转换为指定的数据类型。

参数

参数

说明

expr

要转换类型的值。

type

转换的目标类型，可选值：signed, unsigned, decimal, datetime,date

注意

  • 将 expr 转换为 signed 或 unsigned 类型仅支持整型或类似整型的字符串，并且必须在合法范围内。

    字符串类型的浮点数：MySQL 采用向下取整，OceanBase-Oracle 采用四舍五入。

    unsigned 的合法范围为 [0, 18446744073709551615] ， signed 的范围为 [-9223372036854775808, 9223372036854775807] 。 如果超出范围，数据结果可能不一致。 例如， convert(18446744073709551616, unsigned) 在 MySQL 中返回 18446744073709551615，而 OceanBase-Oracle 中为 18446744073709551616。

  • MySQL 和 OceanBase-Oracle 在数据类型 date 处理上存在差异： 对于 cast('2024-01-31 14:30:00' as date)，MySQL 返回 2024-01-31，OceanBase-Oracle 保留完整时间信息，返回 2024-01-31 14:30:00。

  • 转换仅支持相同类型或可隐式转换的数据类型。 例如：非数值字符串转换为 decimal 时，Oracle 会报错 ORA-01722: 无效数字。

  • decimal 类型转换的处理不同： 如果输入值的整数部分超过 p - s 位，ob_oracle 会报错 ORA-01438: value larger than specified precision allowed for this column ，而 mysql 会截取不报错。 例：将 999.99 转为 decimal(3,1) ，mysql 返回 99.9 ，而 ob_oracle 会报错。

  • MySQL 中，null和空字符串不相等，而 oceanbase-oracle 中，null和空字符串相等都是 null。

示例

-- 转换前MySQL SQL：

SELECT cast(1.5 as SIGNED) as col_signed;
+------------+
| col_signed |
+------------+
|          2 |
+------------+

SELECT cast(1.5 as UNSIGNED) as col_unsigned;
+--------------+
| col_unsigned |
+--------------+
|            2 |
+--------------+

SELECT cast('1.5' as SIGNED) as col_signed;
+------------+
| col_signed |
+------------+
|          1 |
+------------+

SELECT cast('1.5' as UNSIGNED) as col_unsigned;
+--------------+
| col_unsigned |
+--------------+
|            1 |
+--------------+

SELECT cast(1234 as DECIMAL) as  col_decimal;
+-------------+
| col_decimal |
+-------------+
|        1234 |
+-------------+

SELECT cast(STR_TO_DATE('2024-01-31 14:30:00', '%Y-%m-%d %H:%i:%s') as DATE) as col_date;
+------------+
| col_date   |
+------------+
| 2024-01-31 |
+------------+

SELECT cast(STR_TO_DATE('2024-01-31 14:30:00', '%Y-%m-%d %H:%i:%s') as DATETIME) as col_datetime;
+---------------------+
| col_datetime        |
+---------------------+
| 2024-01-31 14:30:00 |
+---------------------+



-- 转换后oceanbase-oracle SQL：

SELECT CAST(1.5 AS number(20,0)) AS col_signed FROM DUAL;
+------------+
| COL_SIGNED |
+------------+
|          2 |
+------------+


SELECT CAST(1.5 AS number(20,0)) AS col_unsigned FROM DUAL;
+--------------+
| COL_UNSIGNED |
+--------------+
|            2 |
+--------------+


SELECT CAST('1.5' AS number(20,0)) AS col_signed FROM DUAL;
+------------+
| COL_SIGNED |
+------------+
|          2 |
+------------+


SELECT CAST('1.5' AS number(20,0)) AS col_unsigned FROM DUAL;
+--------------+
| COL_UNSIGNED |
+--------------+
|            2 |
+--------------+


SELECT CAST(1234 AS decimal) AS col_decimal FROM DUAL;
+-------------+
| COL_DECIMAL |
+-------------+
|        1234 |
+-------------+

-- 使用默认的日期格式
SELECT CAST(to_date('2024-01-31 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS date) AS col_date FROM DUAL;
+-----------+
| COL_DATE  |
+-----------+
| 31-JAN-24 |
+-----------+

SELECT CAST(to_date('2024-01-31 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp) AS col_datetime FROM DUAL;
+------------------------------+
| COL_DATETIME                 |
+------------------------------+
| 31-JAN-24 02.30.00.000000 PM |
+------------------------------+


-- 日期格式已经设置为NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' 和 NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
SELECT CAST(to_date('2024-01-31 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS date) AS col_date FROM DUAL;
+---------------------+
| COL_DATE            |
+---------------------+
| 2024-01-31 14:30:00 |
+---------------------+

SELECT CAST(to_date('2024-01-31 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp) AS col_datetime FROM DUAL;
+---------------------+
| COL_DATETIME        |
+---------------------+
| 2024-01-31 14:30:00 |
+---------------------+

1.3.3.2.4.4. 条件函数

1.3.3.2.4.4.1. IF

语法
IF (expr1, expr2, expr3)
描述
如果第一个参数为TRUE，则返回第二个参数，否则返回第三个参数。

参数解释

参数

说明

expr1

判断该参数是否为true

expr2

如果expr1为true，则返回expr2

expr3

如果expr1不为true，则返回expr3

警告

  • MySQL 中，null和空字符串不相等，而 oceanbase-oracle 中，null和空字符串相等都是 null。

  • oceanbase-oracle 中null相关表达式操作会返回 null 。

  • expr1中null相关表达式操作会返回 null 。

  • oceanbase-oracle 类型转换规则方面与 MySQL 不同，expr2 与 expr3 必须是相同的类型，否则会报错 ORA-00932: 数据类型不一致 ； 如 select if (1 > 2, 1, '假') 是不支持的。

  • 当参数expr1非条件表达式时（当前条件表达式只支持 > < = <= >= != AND OR [IS NULL] [IS NOT NULL] NOT ），会调用函数is_nonzero判断是否为真。 is_nonzero 的入参是 VARCHAR2 ，因此如果参数expr1隐式转换为字符串时，mysql和oceanbase-oracle存在差异，如日期类型转字符串，会受到日期格式的影响。 参数expr1如果是数值开头的字符串会进行截取数值在和0比较，非0则返回 TRUE 。 参数expr1如果是非数值类型开头的字符串则返回 FALSE 。

示例

-- 转换前MySQL SQL：
SELECT IF(1 > 2, 2, 3);
+-----------------+
| IF(1 > 2, 2, 3) |
+-----------------+
|               3 |
+-----------------+

SELECT IF(1, 2, 3);
+-------------+
| IF(1, 2, 3) |
+-------------+
|           2 |
+-------------+

SELECT IF('123', 2, 3);
+-----------------+
| IF('123', 2, 3) |
+-----------------+
|               2 |
+-----------------+

select IF('1abc123',2,3);
+-------------------+
| IF('1abc123',2,3) |
+-------------------+
|                 2 |
+-------------------+

select IF('0.1abc123',2,3);
+---------------------+
| IF('0.1abc123',2,3) |
+---------------------+
|                   2 |
+---------------------+

select IF('abc123',2,3);
+------------------+
| IF('abc123',2,3) |
+------------------+
|                3 |
+------------------+



-- 转换后 oceanbase-oracle SQL：
SELECT case when 1>2 then 2 else 3 end from dual;
+--------------------------+
| CASEWHEN1>2THEN2ELSE3END |
+--------------------------+
|                        3 |
+--------------------------+

SELECT case when unisql.is_nonzero(1) != 0 then 2 else 3 end from dual;
+----------------------------------------------+
| CASEWHENUNISQL.IS_NONZERO(1)!=0THEN2ELSE3END |
+----------------------------------------------+
|                                            2 |
+----------------------------------------------+

SELECT case when unisql.is_nonzero('123') != 0 then 2 else 3 end from dual;
+--------------------------------------------------+
| CASEWHENUNISQL.IS_NONZERO('123')!=0THEN2ELSE3END |
+--------------------------------------------------+
|                                                2 |
+--------------------------------------------------+

SELECT case when unisql.is_nonzero('1abc123') != 0 then 2 else 3 end from dual;
+------------------------------------------------------+
| CASEWHENUNISQL.IS_NONZERO('1ABC123')!=0THEN2ELSE3END |
+------------------------------------------------------+
|                                                    2 |
+------------------------------------------------------+

SELECT case when unisql.is_nonzero('0.1abc123') != 0 then 2 else 3 end from dual;
+--------------------------------------------------------+
| CASEWHENUNISQL.IS_NONZERO('0.1ABC123')!=0THEN2ELSE3END |
+--------------------------------------------------------+
|                                                      2 |
+--------------------------------------------------------+

SELECT case when unisql.is_nonzero('abc123') != 0 then 2 else 3 end from dual;
+-----------------------------------------------------+
| CASEWHENUNISQL.IS_NONZERO('ABC123')!=0THEN2ELSE3END |
+-----------------------------------------------------+
|                                                   3 |
+-----------------------------------------------------+

1.3.3.2.4.4.2. IFNULL

语法
IFNULL (anycompatible1, anycompatible2)
描述
根据参数是否为空，返回相应的值。

参数解释

参数

说明

anycompatible1

判断该参数是否为null，如果不为null，则返回该参数

anycompatible2

判断第一个参数是否为null，如果为null，则返回该参数

警告

  • 由于oceanbase-oracle中无法区分空串与NULL,所以在oceanbase-oracle中,只能将’’与null一视同仁来处理。

  • anycompatible1和anycompatible2必须是相同的类型，或者可以隐式转换为相同的类型。

  • 因数据库某些特性存差异，导致执行结果不一致。如除0操作，mysql的1/0的返回值为 null,oceanbase-oracle不支持，报错 ORA-01476: divisor is equal to zero。

  • anycompatible1不支持条件表达式， nvl(1>2,2)``oceanbase-oracle报错： ``ORA-00900：SQL 语句存在语法错误。

示例

-- 转换前MySQL SQL：
SELECT ifnull(1, 15);
+---------------+
| ifnull(1, 15) |
+---------------+
|             1 |
+---------------+


select ifnull(null, 15);
+------------------+
| ifnull(null, 15) |
+------------------+
|               15 |
+------------------+


select ifnull('', 15);
+----------------+
| ifnull('', 15) |
+----------------+
|                |
+----------------+




-- 转换后oceanbase-oracle SQL：
SELECT nvl(1, 15) FROM dual;
+-----------+
| NVL(1,15) |
+-----------+
|         1 |
+-----------+

SELECT nvl(NULL, 15) FROM dual;
+--------------+
| NVL(NULL,15) |
+--------------+
|           15 |
+--------------+

SELECT nvl('', 15) FROM dual;
+------------+
| NVL('',15) |
+------------+
| 15         |
+------------+

1.3.3.2.4.4.3. ISNULL

语法
ISNULL (anycompatible)
描述
判断是否为null，为null则返回1，非null返回0。

参数解释

参数

说明

anycompatible

判断该参数是否为null

警告

  • 由于oceanbase-oracle中无法区分空串与NULL,所以在oceanbase-oracle中,只能将’’与NULL一视同仁来处理。

  • 因数据库某些特性存差异，导致执行结果不一致。如除0操作，mysql的1/0的返回值为 null,oceanbase-oracle不支持，报错 ORA-01476: divisor is equal to zero。

  • 在 oceanbase-oracle 中，自定义函数不支持将 ISNULL 作为参数传入，否则会引发错误： ORA-00600: internal error code, arguments: -5555, Incorrect number of arguments。 例如，对于一个将两个数值相加的自定义函数 sum，若使用 sum(ISNULL(1), 2) 将会导致执行错误。为解决此问题，可以将 ISNULL(1) 的返回值显式转换为合适的类型，例如 sum(cast(ISNULL(1) as number), 2)。

示例

-- 转换前MySQL SQL：
select ISNULL(1);
+-----------+
| ISNULL(1) |
+-----------+
|         0 |
+-----------+

select ISNULL(NULL);
+--------------+
| ISNULL(NULL) |
+--------------+
|            1 |
+--------------+

select ISNULL('');
+------------+
| ISNULL('') |
+------------+
|          0 |
+------------+


-- 转换后oceanbase-oracle SQL：
select ISNULL(1) from dual;
+-----------+
| ISNULL(1) |
+-----------+
|         0 |
+-----------+

select ISNULL(NULL) from dual;
+--------------+
| ISNULL(NULL) |
+--------------+
|            1 |
+--------------+

select ISNULL('') from dual;
+------------+
| ISNULL('') |
+------------+
|          1 |
+------------+

1.3.3.2.4.4.4. UUID

语法
UUID ()
描述
用于生成一个唯一的标识符(UUID),保证在空间和时间上的唯一性

示例

-- 转换前MySQL SQL:
select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 664c5710-5ba0-11ef-a608-005056c00001 |
+--------------------------------------+

-- 转换后oceanbase-oracle SQL:
SELECT unisql.uuid() from dual;
UNISQL.UUID()
--------------------------------------
 8d7c2148-31fc-11f0-af0a-005056a1935a

1.3.3.2.4.4.5. STDDEV

语法
STDDEV (expr)
描述
计算一组数值的标准差

参数解释

参数

说明

expr

用与计算总体标准差的数值列，或者表达式

警告

  • 计算标准差时，OceanBase-Oracle数据库stddev_pop函数计算的标准差精度大于Mysql数据库函数stddev函数计算的标准差

  • 入参为空串时，返回NULL

示例

CREATE TABLE TEST_STDDEV (id int,value int,value_double double);
INSERT INTO TEST_STDDEV VALUES (1,10,10.0);
INSERT INTO TEST_STDDEV VALUES (1,20,20.0);
INSERT INTO TEST_STDDEV VALUES (1,30,30.0);

-- 转换前MySQL SQL：
SELECT stddev(value), stddev(value_double) FROM TEST_STDDEV;
stddev(value)   |stddev(value_double)|
----------------+--------------------+
8.16496580927726|    8.16496580927726|


-- 转换后OceanBase-Oracle SQL：
SELECT stddev_pop(value), stddev_pop(value_double) FROM TEST_STDDEV;
+------------------------------------------+--------------------------+
| STDDEV_POP(VALUE)                        | STDDEV_POP(VALUE_DOUBLE) |
+------------------------------------------+--------------------------+
| 8.16496580927726032732428024901963797322 |  8.1649658092772608E+000 |
+------------------------------------------+--------------------------+

1.3.3.2.4.4.6. FIND_IN_SET

语法
FIND_IN_SET (str, strlist)
描述
返回一个字符串 str 在另一个由逗号隔开的字符串 strlist 中第一次出现的位置。

参数解释

参数

说明

str

需要查找的字符串，不能包含逗号，包含逗号则该函数无法正常工作

strlist

字符串清单，是一个以逗号分隔的字符串

警告

  • MySQL 中，null和空字符串不相等，而OceanBase-Oracle中，null和空字符串相等都是null，所以null按照空串处理

  • 只要 str 或 strlist 为 null 或空串，函数就会返回 0

  • 大小写处理方面，MySQL 是根据数据库或列的字符集来决定是否区分大小写的；而OceanBase-Oracle中，本函数 区分大小写

  • 入参仅支持字符串类型，以及可以隐式转换成字符串的类型如 NUMBER；但是123.0转成字符串后，OceanBase-Oracle中会变成123，执行结果和mysql不同；

  • 如果您的表中含义一个定长的 CHAR(n) 列，且内容未达到 n 长度，则最后一项的匹配会有差异！比如 FIND_IN_SET('5', char_col) ，char_col 为 ‘1,2,3,4,5’ ，但 char_col 类型的长度超过 9，则在 MySQL 中可以匹配返回 5 ， 但在OceanBase-Oracle中会返回 0 ，因为字符串会视为 ‘1,2,3,4,5 ‘ （带有空格补全）

示例

-- 转换前MySQL SQL：
SELECT FIND_IN_SET('橙子', '苹果,香蕉,橙子') from dual;
FIND_IN_SET('橙子', '苹果,香蕉,橙子')|
-----------------------------+
                            3|

SELECT FIND_IN_SET('  apple  ', '  apple  ,  banana  ,  orange  ') from dual;
FIND_IN_SET('  apple  ', '  apple  ,  banana  ,  orange  ')|
-----------------------------------------------------------+
                                                          1|

-- 转换后 OceanBase-Oracle SQL：
SELECT UNISQL.FIND_IN_SET('橙子', '苹果,香蕉,橙子') from dual;
UNISQL.FIND_IN_SET('橙子','苹果,香蕉,橙子')|
-----------------------------------+
                                  3|

SELECT UNISQL.FIND_IN_SET('  apple  ', '  apple  ,  banana  ,  orange  ') from dual;
UNISQL.FIND_IN_SET('APPLE','APPLE,BANANA,ORANGE')|
-------------------------------------------------+
                                                1|

1.3.3.2.4.4.7. DATABASE

语法
DATABASE()
描述
用于返回当前会话中正在使用的数据库（当前数据库） 的名称。

示例

-- 转换前MySQL SQL：
SELECT DATABASE() FROM DUAL;
DATABASE()|
-----------------------------+
                          xxx|

-- 转换后 OceanBase-Oracle SQL：
SELECT sys_context('userenv', 'CURRENT_SCHEMA') FROM DUAL;
sys_context('userenv', 'CURRENT_SCHEMA')|
----------------------------------------+
                                    xxx |