3.3.2.4. 转换为 OceanBase-Oracle
3.3.2.4.1. 时间日期函数
3.3.2.4.1.1. NOW
- 语法
NOW()
- 描述
- 用于返回当前的日期和时间。
返回类型
DATETIME
警告
统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异,差异点如下:
mysql和mariadb的now函数受其时区timezone的影响,目标库返回current_timestamp函数,既遵循会话时区,又在结果中包含了时区偏移,两者包含相同的时间和日期信息。
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 |
3.3.2.4.1.2. SYSDATE
- 语法
SYSDATE()
- 描述
- 用于返回当前的日期和时间。
返回类型
DATETIME
警告
统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异,差异点如下:
mysql和mariadb的sysdate函数受其时区timezone的影响,目标库返回current_timestamp函数,既遵循会话时区,又在结果中包含了时区偏移,两者包含相同的时间和日期信息。
mysql和mariadb的sysdate函数的结果样式固定,目标库返回current_timestamp函数,显示样式受参数NLS_TIMESTAMP_TZ_FORMAT控制。
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 |
3.3.2.4.1.3. CURDATE
- 语法
CURDATE()
- 描述
- 用于返回当前的日期和时间。
返回类型
DATE
警告
统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异,差异点如下:
mysql和mariadb的CURDATE函数受其时区timezone的影响,目标库返回SYSDATE函数,依赖数据库服务器操作系统时区。
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|
3.3.2.4.1.4. CURRENT_TIME
- 语法
CURRENT_TIME(n)
- 描述
- 用于返回当前的时间。
参数解释
参数 |
说明 |
---|---|
n |
整数,范围[0,6],表示秒的小数位数(精度,最多6位) |
返回类型
TIME
警告
统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异,差异点如下:
mysql和mariadb的CURRENT_TIME函数返回值仅含时分秒,目标库CURRENT_TIMESTAMP函数返回日期、时分秒、时区信息。
mysql和mariadb的CURRENT_TIME函数受其时区TIMEZONE的影响,目标库返回CURRENT_TIMESTAMP函数,既遵循当前会话时区,又在结果中包含了时区偏移。
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 |
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存在一些差异,差异点如下:
mysql和mariadb的日期格式部分日期格式,如%c、%e、%k、%l,不会补前导零,目标库返回对应的日期格式会有前导零。
mysql和mariadb的date数据类型支持%f的日期格式,目标库的date类型不支持转化的FF6日期格式。
mysql和mariadb的日期格式%W(星期名)返回都是英文,目标库通过设置 NLS_DATE_LANGUAGE 来设定返回对应的语言。
示例
-- 转换前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(to_timestamp('2020-01-01 08:13:13', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY/MM/DD HH MI SS') FROM DUAL
TO_CHAR(TO_TIMESTAMP('2020-01-0108:13:13','YYYY-MM-DDHH24:MI:SS'),'YYYY/MM/DDHHMISS')|
-------------------------------------------------------------------------------------+
2020/01/01 08 13 13 |
3.3.2.4.1.6. DAY
- 语法
DAY(date)
- 描述
- 用于从日期或日期时间值中提取”日”部分的函数。
参数解释
参数 |
说明 |
---|---|
date |
一个有效的日期或日期时间表达式,目前只支持date类型的参数 |
返回类型
整型
警告
统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异,差异点如下:
如果入参为非法时间格式如:
2024-01-32
,10000-01-32
,0000-00-00
,mysql和mariadb会返回NULL或者0,而OceanBase-Oracle转换后会执行报错。尽量保证入参日期范围和格式标准,对于非标准的日期格式,例如:
024-01-32
,2024-01-1
统一SQL可能会成功转换,但不保证目标端执行结果准确。如果入参为合法的时间格式的字符串类型时,mysql和mariadb会返回正确结果,转换后OceanBase-Oracle会执行报错。
示例
-- 转换前MySQL SQL:
select day(STR_TO_DATE('2024-01-31 01:00:00', '%Y-%m-%d %H:%i:%s'));
+-------------------------------+
| day |
+-------------------------------+
| 31|
+-------------------------------+
-- 转换后OceanBase-Oracle SQL:
SELECT extract(DAY FROM to_date('2024-01-31 01:00:00', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;
DAY |
--------------------------+
31|
3.3.2.4.1.7. YEAR
- 语法
YEAR(date)
- 描述
- 用于从日期或日期时间值中提取年份部分的函数。
参数解释
参数 |
说明 |
---|---|
date |
一个有效的日期或日期时间表达式,目前只支持date类型的参数 |
返回类型
整型
警告
统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异,差异点如下:
如果入参为非法时间格式如:
2024-01-32
,10000-01-32
,0000-00-00
,mysql和mariadb会返回NULL或者0,而OceanBase-Oracle转换后会执行报错。尽量保证入参日期范围和格式标准,对于非标准的日期格式,例如:
024-01-32
,2024-01-1
统一SQL可能会成功转换,但不保证目标端执行结果准确。如果入参为合法的时间格式的字符串类型时,mysql和mariadb会返回正确结果,转换后OceanBase-Oracle会执行报错。
示例
-- 转换前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(DAY FROM to_date('2024-01-31 01:00:00', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;
YEAR |
--------------------------+
2024|
3.3.2.4.1.8. DAYOFWEEK
- 语法
dayofweek(date)
- 描述
- 用于获取日期对应的星期几的函数
参数解释
参数 |
说明 |
---|---|
date |
一个有效的日期或日期时间表达式,目前只支持date类型的参数 |
返回类型
整型:[1-7] 对应为 [星期日-星期六]
警告
统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异,差异点如下:
如果入参为非法时间格式如:
2024-01-32
,10000-01-32
,0000-00-00
,mysql和mariadb会返回NULL或者0,而OceanBase-Oracle转换后会执行报错。尽量保证入参日期范围和格式标准,对于非标准的日期格式,例如:
024-1-32
,2024-01-1
统一SQL可能会成功转换,但不保证目标端执行结果准确。如果入参为合法的时间格式的字符串类型时,mysql和mariadb会返回正确结果,转换后OceanBase-Oracle会执行报错。
示例
-- 转换前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(to_date('2025-04-05', 'YYYY-MM-DD'), 'D')) AS dayofweekalias FROM DUAL;
DAYOFWEEKALIAS |
--------------------------+
7 |
3.3.2.4.1.9. WEEKOFYEAR
- 语法
weekofyear(date)
- 描述
- 用于返回一个日期处于当年的周数,其返回值范围是 1 到 53之间(大多数年份有52周,闰年可能有53周)
参数解释
参数 |
说明 |
---|---|
date |
一个有效的日期或日期时间表达式,当前支持符合时间格式的字符串,date或datetime字段类型的数据库字段,时间函数 |
返回类型
整型:[1 到 53 之间的整数值]
警告
统一SQL转换后在目标端数据库执行结果与mysql和mariadb存在一些差异和限制,如下:
目前转换依赖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
尽量保证入参日期范围和格式标准,对于非标准的日期格式,例如:
024-1-32
,2024-01-1
统一SQL可能会成功转换,但不保证目标端执行结果准确。如果入参为合法的时间格式的
字符串类型
时,当前版本支持的时间格式为:[年月日]或[年月日 时分秒],必须使用如下分隔符分割,日期部分支持:[-
,/
,.
],时间部分支持:[:
,.
],分隔符不支持混用,不支持无分隔符,否则返回结果会与mariadb会返结果不一致。如果入参为
时间函数
时,当前版本支持的时间函数有: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 |
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 位 |
警告
因为在目标库中空串’’等价NULL值,所以当函数入参为空串时,会返回NULL。这点与MySQL和MariaDB不同。
转换时支持的格式串见格式串转换说明,不支持的格式串会被直接透传到目标库(如%p %r %W)。在转换上语义上是等价的,但是由于返回值类型存在差异,所以在目标库执行结果可能会存在展示差异以实际为准。
当format仅包含时间类型时,MYSQL返回的是TIME类型只包含时间部分,但是在目标库中返回的是TIMESTAMP类型,包含日期部分。参见 数据类型转换映射表。
当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
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|
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|
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 以来的秒数。
参数解释
参数 |
说明 |
---|---|
timestmap |
日期时间,该参数为 timestamp 数据类型。 |
返回类型
number 类型
示例
-- 转换前MySQL SQL:
SELECT UNIX_TIMESTAMP(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|
3.3.2.4.1.14. FROM_UNIXTIME
- 语法
FROM_UNIXTIME (unix_timestamp)
- 描述
- 转换自 (1970-01-01 00:00:00 UTC) 以来的秒数到 TIMESTAMP WITH TIME ZONE 类型的值, 和 UNIX_TIMESTAMP() 功能相反。
参数解释
参数 |
说明 |
---|---|
unix_timestmap |
自 (1970-01-01 00:00:00 UTC) 以来的秒数 |
返回类型
TIMESTAMP WITH TIME ZONE 类型
示例
-- 转换前MySQL SQL:
SELECT from_unixtime(2147483647) as timestamp;
timestamp |
-------------------+
2038-01-19 11:14:07|
-- 转换后OceanBase-Oracle SQL:
SELECT (
(TIMESTAMP '1970-01-01 00:00:00 +00:00'+NUMTODSINTERVAL(2147483647, 'SECOND')) AT TIME ZONE SESSIONTIMEZONE
) as timestamp FROM DUAL
timestamp |
------------+
[TIMESTAMPTZ]
3.3.2.4.1.15. TIMESTAMP
- 语法
TIMESTAMP (expr)
- 描述
- 把 mariadb 的 date 或 datetime 类型的表达式 expr 转换为 datetime 类型的值
参数解释
参数 |
说明 |
---|---|
expr |
mariadb 的 date 或 datetime 类型的表达式 |
返回类型
timestamp
示例
-- 转换前MySQL SQL:
SELECT timestamp(timestamp '2025-05-21 20:59:59') as timestamp;
timestamp |
-------------------+
2025-05-21 20:59:59|
-- 转换后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|
3.3.2.4.2. 字符串函数
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)|
-----------------------+
|
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 |
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|
3.3.2.4.3. 其他函数
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 |
+------------------+
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 |
+-------------------------------------+
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 |
+------------------------------------------------------------------------------------------+
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 |
警告
- 将 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。 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(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 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 |
+---------------------+
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 |
+---------------------+
3.3.2.4.4. 条件函数
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 |
+-----------------------------------------------------+
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 |
+------------+
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 |
+------------+
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