3.3.2.3. 转换为 Oracle19c
3.3.2.3.1. 时间日期函数
3.3.2.3.1.1. STR_TO_DATE
- 语法
STR_TO_DATE(date,format)- 描述
- 该函数功能是返回日期。
参数解释
参数 |
说明 |
|---|---|
date |
指定日期时间，该参数可以为 text 数据类型 |
format |
日期显示格式，该参数为字符串类型，支持清单详见下表 |
序号 |
日期时间显示格式 |
含义 |
|---|---|---|
1 |
%c |
月，数值(00-12) |
2 |
%d |
月的天，数值(00-31) |
3 |
%e |
月的天，数值(00-31) |
4 |
%H |
小时 (00-23) |
5 |
%h |
小时 (01-12) |
6 |
%I |
小时 (01-12) |
7 |
%i |
分钟，数值(00-59) |
8 |
%k |
小时 (00-23) |
9 |
%l |
小时 (01-12) |
10 |
%M |
月名 |
11 |
%m |
月，数值(00-12) |
12 |
%p |
AM 或 PM |
13 |
%r |
时间，12-小时（hh:mm:ss AM 或 PM） |
14 |
%S |
秒(00-59) |
15 |
%s |
秒(00-59) |
16 |
%T |
时间，24-小时（hh:mm:ss） |
17 |
%W |
星期名 |
18 |
%Y |
年，4位 |
19 |
%y |
年，2位 |
警告
date的范围需要与format范围一致，例如date精确到秒，那么format也需要精确到秒
format的时间格式以转换后Oracle19C的数据库支持为准，’YYYY-MM-MM-DD’与’YYYY-ASD-MM-DD’是不支持的格式
mysql二位数的年份在小于70时是高位补充20，大于70时高位补充19,而oracle二位数的年份高位补充的是20
转换后Oracle19C的sql执行结果存在微秒
示例
-- 转换前MySQL SQL：
SELECT STR_TO_DATE('2024-12-31','%Y-%m-%d %T') FROM DUAL;
+-----------------------------------------+
| STR_TO_DATE('2024-12-31','%Y-%m-%d %T') |
+-----------------------------------------+
| 2024-12-31 00:00:00 |
+-----------------------------------------+
SELECT STR_TO_DATE('70-12-31','%y-%m-%d %T') FROM DUAL;
+---------------------------------------+
| STR_TO_DATE('70-12-31','%y-%m-%d %T') |
+---------------------------------------+
| 1970-12-31 00:00:00 |
+---------------------------------------+
-- 转换后Oracle19C SQL：
SELECT to_date('2024-12-31', 'YY-MM-DD HH24:MI:SS') FROM dual;
TO_DATE('2024-12-31','YY-MM-DDHH24:MI:SS')|
------------------------------------------+
2024-12-31 00:00:00.000|
SELECT to_date('70-12-31', 'YYYY-MM-DD HH24:MI:SS') FROM dual;
TO_DATE('70-12-31','YY-MM-DDHH24:MI:SS')|
----------------------------------------+
2070-12-31 00:00:00.000|
3.3.2.3.1.2. TIME_FORMAT
- 语法
TIME_FORMAT(date,format)- 描述
- 该函数功能是返回字符串。
参数解释
参数 |
说明 |
|---|---|
time |
指定日期。该参数可以为 timestamp 数据类型 |
format |
日期显示格式,支持清单详见STR_TO_DATE一节 |
警告
format的时间格式不允许出现其它字符。’YYYY-ASD-MM-DD’是不支持的格式
示例
-- 转换前MySQL SQL：
SELECT TIME_FORMAT(SYSDATE(), '%Y-%m-%d %T') FROM dual;
+----------------------------------------+
| TIME_FORMAT(SYSDATE() , '%Y-%m-%d %T') |
+----------------------------------------+
| 0000-00-00 13:14:09 |
+----------------------------------------+
-- 转换后Oracle19C SQL：
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')|
---------------------------------------+
2025-03-28 15:07:40 |
3.3.2.3.2. 条件函数
3.3.2.3.2.1. IF
- 语法
IF (expr1, expr2, expr3)- 描述
- 如果第一个参数为TRUE，则返回第二个参数，否则返回第三个参数。
参数解释
参数 |
说明 |
|---|---|
expr1 |
判断该参数是否为true |
expr2 |
如果expr1为true，则返回expr2 |
expr3 |
如果expr1不为true，则返回expr3 |
警告
MySQL 中，null和空字符串不相等，而 Oracle 19c 中，null和空字符串相等都是null。
Oracle 19c 中null相关表达式操作会返回null
expr1中null相关表达式操作会返回null
Oracle 19c 类型转换规则方面与 MySQL 不同，expr2 与 expr3 必须是相同的类型，否则会报错
ORA-00932: 数据类型不一致； 如select if (1 > 2, 1, '假')是不支持的当参数expr1非表达式时，会调用函数is_nonzero判断是否为真。
is_nonzero的入参是VARCHAR2，因此如果参数expr1隐式转换为字符串时，mysql和oceanbase-oracle存在差异，如日期类型转字符串，会受到日期格式的影响。 参数expr1如果是数值类型的字符串会转为数值和0比较，非0则返回TRUE。 参数expr1如果是非数值类型的字符串则返回FALSE。 以数值开头的非数值类型字符串如1abc，Mysql会进行截取为数值1和0比较，暂不支持，Oracle 19c将返回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|
-- 转换后 Oracle 19c 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|
3.3.2.3.2.2. IFNULL
- 语法
IFNULL (anycompatible1, anycompatible2)- 描述
- 根据参数是否为空，返回相应的值。
参数解释
参数 |
说明 |
|---|---|
anycompatible1 |
判断该参数是否为null，如果不为null，则返回该参数 |
anycompatible2 |
判断第一个参数是否为null，如果为null，则返回该参数 |
警告
由于Oracle19C中无法区分空串与NULL,所以在Oracle19C中,只能将’’与NULL一视同仁来处理
示例
-- 转换前MySQL SQL：
SELECT ifnull(1, 15) FROM dual;
+---------------+
| ifnull(1, 15) |
+---------------+
| 1 |
+---------------+
select ifnull(null, 15) FROM dual;
+------------------+
| ifnull(null, 15) |
+------------------+
| 15 |
+------------------+
select ifnull('', 15) FROM dual;
+----------------+
| ifnull('', 15) |
+----------------+
| |
+----------------+
-- 转换后Oracle19C 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.3.2.3. ISNULL
- 语法
ISNULL (anycompatible)- 描述
- 判断是否为null，为null则返回1，非null返回0
参数解释
参数 |
说明 |
|---|---|
anycompatible |
判断该参数是否为null |
警告
由于Oracle19C中无法区分空串与NULL,所以在Oracle19C中,只能将’’与NULL一视同仁来处理
示例
create table t1(id text);
insert into t1 values(123), (null), ('a');
-- 转换前MySQL SQL：
select isnull(1) FROM dual;
+-----------+
| isnull(1) |
+-----------+
| 0 |
+-----------+
select isnull(null) FROM dual;
+--------------+
| isnull(null) |
+--------------+
| 1 |
+--------------+
select isnull('') FROM dual;
+------------+
| isnull('') |
+------------+
| 0 |
+------------+
-- 转换后Oracle19C SQL：
SELECT CASE WHEN 1 IS NULL THEN 1 ELSE 0 END FROM dual;
CASEWHEN1ISNULLTHEN1ELSE0END|
----------------------------+
0|
SELECT CASE WHEN null IS NULL THEN 1 ELSE 0 END FROM dual;
CASEWHENNULLISNULLTHEN1ELSE0END|
-------------------------------+
1|
SELECT CASE WHEN '' IS NULL THEN 1 ELSE 0 END FROM dual;
CASEWHEN''ISNULLTHEN1ELSE0END|
-----------------------------+
1|
3.3.2.3.3. 字符串函数
3.3.2.3.3.1. FIND_IN_SET
- 语法
FIND_IN_SET (str, strlist)- 描述
- 返回一个字符串 str 在另一个由逗号隔开的字符串 strlist 中第一次出现的位置。
参数解释
参数 |
说明 |
|---|---|
str |
需要查找的字符串，不能包含逗号，包含逗号则该函数无法正常工作 |
strlist |
字符串清单，是一个以逗号分隔的字符串 |
警告
MySQL 中，null和空字符串不相等，而oracle19c中，null和空字符串相等都是null；所以无法实现在 strlist 中寻找空串的功能。
只要 str 或 strlist 为 null 或空串，函数就会返回 NULL
大小写处理方面，MySQL 是根据数据库或列的字符集来决定是否区分大小写的；而 Oracle 19c 中，本函数 区分大小写
入参仅支持字符串类型，以及可以隐式转换成字符串的类型如 NUMBER
如果您的表中含义一个定长的
CHAR(n)列，且内容未达到 n 长度，则最后一项的匹配会有差异！比如FIND_IN_SET('5', char_col)，char_col 为 ‘1,2,3,4,5’ ，但 char_col 类型的长度超过 9，则在 MySQL 中可以匹配返回 5 ， 但在 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|
-- 转换后 Oracle 19c SQL：
SELECT FIND_IN_SET('橙子', '苹果,香蕉,橙子') from dual;
UNISQL.FIND_IN_SET('橙子','苹果,香蕉,橙子')|
-----------------------------------+
3|
SELECT FIND_IN_SET(' apple ', ' apple , banana , orange ') from dual;
UNISQL.FIND_IN_SET('APPLE','APPLE,BANANA,ORANGE')|
-------------------------------------------------+
1|
3.3.2.3.3.2. SUBSTRING_INDEX
- 语法
SUBSTRING_INDEX (str, delimiter, count)- 描述
- 根据指定的分隔符 delimiter 和计数 count，从字符串 str 中提取子字符串。 若 count 为正数，则返回从字符串开头到第 count 个分隔符的所有内容； 若 count 为负数，则返回从字符串末尾到第 count 个分隔符的所有内容。
参数解释
参数 |
说明 |
|---|---|
str |
要处理的原始字符串 |
delimiter |
用于分割字符串的分隔符，必须为字符串类型 |
count |
整数，指定分隔符的计数。正数表示从开头计数，负数表示从末尾计数 |
警告
MySQL 中，null和空字符串不相等，而oracle19c中，null和空字符串相等，都为null；若 str、delimiter 或 count 为 null，则函数会返回 NULL。
本函数 区分大小写
入参仅支持字符串类型，以及可以隐式转换成字符串的类型如 NUMBER
如果您的表中含有一个定长的
CHAR(n)列，且内容未达到 n 长度，则最后一项的匹配会有差异。 比如 SUBSTRING_INDEX(char_col, ‘,’, 2) ，char_col 为 ‘1,2,3’ ，但 char_col 类型的长度超过 5，则在 MySQL 中可以正常提取子字符串， 而在 Oracle 中由于字符串会视为 ‘1,2,3 ‘ （带有空格补全），可能会出现提取结果不一致的情况
示例
-- 转换前MySQL SQL：
SELECT SUBSTRING_INDEX('www.example.com', '.', 2) from dual;
SUBSTRING_INDEX('www.example.com', '.', 2)|
-----------------------------------------+
www.example|
SELECT SUBSTRING_INDEX('www.example.com', '.', -2) from dual;
SUBSTRING_INDEX('www.example.com', '.', -2)|
------------------------------------------+
example.com|
-- 转换后 Oracle 19c SQL：
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|
3.3.2.3.3.3. LOCATE
- 语法
LOCATE (substr, str [, position])- 描述
- 返回子字符串第一次出现的位置。
参数解释
参数 |
说明 |
|---|---|
substr |
需要查找的字串，该参数为字符串类型 |
str |
需要被查找的父串，该参数为字符串类型 |
position |
从父串中指定位置开始查找子串（初始值为1），该参数为整形类型，可选参数，不填时默认从父串启示位置开始查找子串 |
警告
由于Oracle19C中无法区分空串与NULL，所以在Oracle19C中只能将’’与NULL一视同仁来处理
Mysql根据排序规则可以设置区分大小写，而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 |
+------------------------------+
-- 转换后Oracle19C 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.3.3.4. CAST
- 语法
CAST (expr AS type)- 描述
- 将expr转换为指定的数据类型。
参数解释
参数 |
说明 |
|---|---|
expr |
需要被转换类型的值。 |
type |
指定转换的类型，signed,unsigned，decimal，douoble，date，char |
警告
使用CAST将值转为unsinged，值必须是正整型、正整型字符串和正浮点型，且其值小于等于2^64 - 1; 不支持负值
oracle的CAST函数转换成signed,unsigned类型时会四舍五入
mysql的char转换成varchar2(4000),mysql的char的长度是字符长度，而oracle的varchar2是字节长度。char对应数据的字节长度超过4000不支持
示例
-- 转换前MySQL SQL：
SELECT cast('312' as SIGNED) from dual;
+-----------------------+
| cast('312' as SIGNED) |
+-----------------------+
| 312 |
+-----------------------+
SELECT cast(0.55 as UNSIGNED) from dual;
+------------------------+
| cast(0.55 as UNSIGNED) |
+------------------------+
| 1 |
+------------------------+
SELECT cast('hello' as CHAR) from dual;
+-----------------------+
| cast('hello' as CHAR) |
+-----------------------+
| hello |
+-----------------------+
-- 转换后Oracle19C SQL：
SELECT CAST('312' AS number(20,0)) FROM dual;
CAST('312'ASNUMBER(20,0))|
-------------------------+
312|
SELECT CAST(0.55 AS number(20,0)) FROM dual;
CAST(0.55ASNUMBER(20,0))|
------------------------+
1|
SELECT CAST('hello' AS varchar2(4000)) FROM dual;
CAST('HELLO'ASVARCHAR2(4000))|
-----------------------------+
hello |
3.3.2.3.3.5. CONCAT
- 语法
CONCAT (str1, str2, ...)- 描述
- 将入参字符串拼接在一起。
参数解释
参数 |
说明 |
|---|---|
str |
需要拼接的字符串，该参数为字符串数据类型 |
警告
由于Oracle19C中无法区分空串与NULL,所以在Oracle19C中,只能将’’与NULL一视同仁来处理
MySQL的函数参数如果有NULL则会返回NULL，而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 |
+------------------+
-- 转换后Oracle19C 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.3.3.6. GROUP_CONCAT
- 语法
GROUP_CONCAT ([DISTINCT] column_name… [ORDER BY ASC/DESC column_name] [Separator sep_str])- 描述
- 将来自多个行的列值组合成一个单独的字符串，每个值之间可以用分隔符隔开。
参数解释
参数 |
说明 |
|---|---|
column_name |
需要拼接的字符串列，该参数为字符串数据类型 |
sep_str |
分割标识符 |
警告
GROUP_CONCAT函数与listagg函数拼接顺序不完全一致
暂时不支持和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|
-- 转换后Oracle19C 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.3.3.7. CHAR_LENGTH
- 语法
CHAR_LENGTH (str)- 描述
- 计算字符串的长度
参数解释
参数 |
说明 |
|---|---|
str |
需要计算长度的字符串，该参数为字符串数据类型 |
警告
由于Oracle19C中无法区分空串与NULL,所以在Oracle19C中,只能将’’与NULL一视同仁来处理
示例
-- 转换前MySQL SQL：
SELECT char_length('zhangsan') FROM dual;
+-------------------------+
| char_length('zhangsan') |
+-------------------------+
| 8 |
+-------------------------+
SELECT char_length(NULL) FROM dual;
+-------------------+
| char_length(NULL) |
+-------------------+
| NULL |
+-------------------+
SELECT char_length('') FROM dual;
+-----------------+
| char_length('') |
+-----------------+
| 0 |
+-----------------+
-- 转换后Oracle19C SQL：
SELECT length('zhangsan') FROM dual;
LENGTH('ZHANGSAN')|
------------------+
8|
SELECT length(NULL) FROM dual;
LENGTH(NULL)|
------------+
|
SELECT length('') FROM dual;
LENGTH('')|
----------+
|
3.3.2.3.3.8. CONCAT_WS
- 语法
CONCAT_WS (separator, str1, str2, ...)- 描述
- 将入参字符串与字符串分隔符拼接在一起
参数解释
参数 |
说明 |
|---|---|
separator |
用于分割每个字符串的分隔符 |
str |
需要拼接的字符串，该参数为字符串数据类型 |
警告
由于Oracle19C中无法区分空串与NULL,所以在Oracle19C中,只能将’’与NULL一视同仁来处理
Mysql遇到了NULL不会使用连接符，而Oracle19C会使用连接符
示例
CREATE TABLE TEST_CONCATWS (school varchar(96),name varchar(96));
INSERT INTO TEST_CONCATWS VALUES ('hensheng','ZHANGSAN');
INSERT INTO TEST_CONCATWS VALUES ('hafuo','LISI');
INSERT INTO TEST_CONCATWS VALUES (NULL,'WANGWU');
-- 转换前MySQL SQL：
SELECT CONCAT_WS(':',school, name) FROM TEST_CONCATWS;
+-----------------------------+
| CONCAT_WS(':',school, name) |
+-----------------------------+
| hensheng:ZHANGSAN |
| hafuo:LISI |
| WANGWU |
+-----------------------------+
-- 转换后Oracle19C SQL：
SELECT concat(concat(school, ':'), name) FROM TEST_CONCATWS;
CONCAT(CONCAT(SCHOOL,':'),NAME)|
-------------------------------+
hensheng:ZHANGSAN |
hafuo:LISI |
:WANGWU |
3.3.2.3.3.9. LEFT
- 语法
LEFT (str, length)- 描述
- 截取指定长度的字符串
参数解释
参数 |
说明 |
|---|---|
str |
被截取的源字符串，该参数为字符串数据类型 |
length |
需要截取的字符串长度，该参数为数值类型 |
示例
-- 转换前MySQL SQL：
SELECT LEFT('nihao', 2) FROM dual;
+------------------+
| LEFT('nihao', 2) |
+------------------+
| ni |
+------------------+
-- 转换后Oracle19C SQL：
SUBSTR('NIHAO',1,2)|
-------------------+
ni |
3.3.2.3.3.10. SUBSTRING
- 语法
SUBSTRING (str, pos [, len])- 描述
- 返回 str 的子字符串，起始位置为 pos，长度为 len。
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串，支持varchar2、char、clob、number(n,m)类型 |
pos |
整数，子字符串的起始位置 |
len |
正整数，子字符串的长度 |
警告
由于Oracle中无法区分空串与NULL，所以在Oracle 中只能将’’与NULL，都返回NULL。
pos 和 len 仅支持整数或整数字符串。若为浮点数数值将存在差异，mysql为四舍五入，Oracle向下取整。
数据类型隐式转换存在差异会存在截取结果有差异。如char类型、日期类型等。 比如 SUBSTRING(char_col, 5 , 2) ，char_col 为 ‘1,2,3’ ，但 char_col 类型的长度超过 5，而在 Oracle 中由于字符串会视为 ‘1,2,3 ‘ （带有空格补全）， PostgreSQL字符串为’1,2,3’。
Oracle 和 PostgreSQL 在数值的内部存储或显示格式上存在差异。如：对于数值 0.01，Oracle 在某些场景（如使用默认格式化规则时）可能将其显示为 .01，而 Mysql 则保留前导 0，显示为 0.01； 对于精度超过38位的数值和Char数据类型存储字符数的数据，两边数据库处理不一致。
示例
-- 转换前Mysql SQL：
SELECT SUBSTRING('abcdefg', 3) as str1,SUBSTRING('abcdefg', 3, 2) as str2,SUBSTRING('abcdefg', -3) as str3,SUBSTRING('abcdefg', 3, -2) as str4;
+-------+------+------+------+
| str1 | str2 | str3 | str4 |
+-------+------+------+------+
| cdefg | cd | efg | |
+-------+------+------+------+
SELECT SUBSTRING(20250217,2,5) as str1, SUBSTRING(2025.0217,1,6) as str2;
+-------+--------+
| str1 | str2 |
+-------+--------+
| 02502 | 2025.0 |
+-------+--------+
-- 转换后Oracle19C SQL：
SELECT substr('abcdefg', CASE WHEN 3=0 THEN NULL ELSE 3 END) AS str1,substr('abcdefg', CASE WHEN 3=0 THEN NULL ELSE 3 END, 2) AS str2,substr('abcdefg', CASE WHEN -3=0 THEN NULL ELSE -3 END) AS str3,substr('abcdefg', CASE WHEN 3=0 THEN NULL ELSE 3 END, -2) AS str4 FROM DUAL;
STR1 STR2 STR3 S
---------------------------- -------- ---------------------------- -
cdefg cd efg
SELECT substr(20250217, CASE WHEN 2=0 THEN NULL ELSE 2 END, 5) AS str1,substr(2025.0217, CASE WHEN 1=0 THEN NULL ELSE 1 END, 6) AS str2 FROM DUAL;
STR1 STR2
-------------------- ------------------------
02502 2025.0
3.3.2.3.4. 其它函数
3.3.2.3.4.1. STDDEV
- 语法
STDDEV (expr)- 描述
- 计算一组数值的标准差
参数解释
参数 |
说明 |
|---|---|
expr |
用与就算标准差的数值列，或者表达式 |
警告
计算标准差时，Oracle19C数据库stddev_pop函数计算的标准差精度大于Mysql数据库函数stddev函数计算的标准差
示例
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|
-- 转换后Oracle19C SQL：
SELECT stddev_pop(value), stddev_pop(value_double) FROM TEST_STDDEV;
STDDEV_POP(VALUE) |STDDEV_POP(VALUE_DOUBLE)|
----------------------------------------+------------------------+
8.16496580927726032732428024901963797322| 8.16496580927726|
3.3.2.3.4.2. TRUNCATE
- 语法
TRUNCATE (number, decimal_places)- 描述
- 用于截取指定的位数
参数解释
参数 |
说明 |
|---|---|
number |
被截取的目标数值 |
decimal_places |
为正时，表示截取的小数位数；为负时表示截取整数的位数 |
示例
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 TRUNCATE(123.456, -2) FROM dual;
+-----------------------+
| TRUNCATE(123.456, -2) |
+-----------------------+
| 100 |
+-----------------------+
SELECT TRUNCATE(123.456, 0) FROM dual;
+----------------------+
| TRUNCATE(123.456, 0) |
+----------------------+
| 123 |
+----------------------+
SELECT TRUNCATE(123.456, 2) FROM dual;
+----------------------+
| TRUNCATE(123.456, 2) |
+----------------------+
| 123.45 |
+----------------------+
-- 转换后Oracle19C SQL：
SELECT TRUNC(123.456, -2) FROM dual;
TRUNC(123.456,-2)|
-----------------+
100|
SELECT TRUNC(123.456, 0) FROM dual;
TRUNC(123.456,0)|
----------------+
123|
SELECT TRUNC(123.456, 2) FROM dual;
TRUNC(123.456,2)|
----------------+
123.45|
3.3.2.3.4.3. DATABASE
- 语法
DATABASE ()- 描述
- 获取数据库名称
示例
-- 转换前MySQL SQL：
SELECT DATABASE() FROM dual;
+------------+
| DATABASE() |
+------------+
| mysql |
+------------+
-- 转换后Oracle19C SQL：
SELECT sys_context('userenv', 'DB_NAME') FROM dual;
SYS_CONTEXT('USERENV','DB_NAME')|
--------------------------------+
test |
3.3.2.3.4.4. SCHEMA
- 语法
SCHEMA ()- 描述
- 获取数据库名称
示例
-- 转换前MySQL SQL：
SELECT SCHEMA() FROM dual;
+----------+
| SCHEMA() |
+----------+
| mysql |
+----------+
-- 转换后Oracle19C SQL：
SELECT sys_context('userenv', 'DB_NAME') FROM dual;
SYS_CONTEXT('USERENV','DB_NAME')|
--------------------------------+
test |
3.3.2.3.4.5. CONVERT
- 语法
CONVERT (expr, type)CONVERT (expr using transcoding_name)- 描述
- 将expr转换为指定的数据类型或指定的编码类型。
参数解释
参数 |
说明 |
|---|---|
expr |
要转换的表达式 |
type |
指定转换的类型，该参数支持unsigned、signed、date、datetime、decimal数据类型 |
transcoding_name | 指定的编码格式，目前只支持utf8 |
|
警告
将expr转为singed、unsigned数据类型仅支持整型或整型字符串，且必须在可支持范围内。 若为字符串类型的浮点数，mysql是向下取整，oracle是四舍五入。 unsigned的取值是[0,18446744073709551615]，signed的取值是[-9223372036854775808,9223372036854775807]。如果超过这个范围，数据结果将不一致。 如convert(18446744073709551616,unsigned),mysql的值为18446744073709551615，oracle的值为18446744073709551616。 mysql和oceanbase-oracle数据类型存在差异，如
cast('2024-01-31 14:30:00' as date ),mysql的date记录年月日，结果为2024-01-31；oracle的记录年月日时间信息，结果为2024-01-31 14:30:00。表达式expr转为目标数据类型仅支持相同类型或可以隐式转换的数据类型。如非数值字符串转为decimal，oracle会报错
ORA-01722: 无效数字。将表达式expr转换为指定的编码类型，目前只支持utf8。 oracle的convert转换表达式编码时，expr支持的类型是CHAR, VARCHAR2, NVARCHAR2, CLOB, NCLOB，或者可以隐式转换为以上类型。 如果参数expr隐式转换为字符串时，mysql和oceanbase-oracle存在差异，如日期类型转字符串，会受到日期格式的影响。
示例
示例
-- 转换前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 |
+---------------------+
-- 转换前Oracle19C 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
-------------------
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