MySql和Oracle的常见使用函数对比

本文档是一个记录文档，会不定时更新
本文档记录MySql和Oracle在使用中要注意的问题，也包括两者使用上的差异问题

---

字符串拼接函数 CONCAT

MySql

MySql的 concat()函数可以拼接多个字符串，作用相当于Oracle中的||。在使用过程中要注意：在拼接多个字符串时：如果其中有null存在时，则拼接结果最终为null。所以在处理时，最好保证要拼接的所有字符串都不为null。例：

# 三字段拼接
 CONCAT('T','ES','T') ----> 'TEST'； 
# 有NULL的情况
 CONCAT('TE',null,'ST')  ----> NULL

Oracle

Oracle也存在concat()函数，与MySql不同是只能进行两个字符串的连接，当有多个字符串拼接时非常的不方便。不过，不存在MySql中有null拼接返回null的问题。因为它的繁琐，所以在Oracle推荐使用||进行字符串拼接。例：

# 三字段拼接
CONCAT ( CONCAT('T', 'E'), 'ST') ----> 'TEST'
# 有NULL的情况
CONCAT ( 'TEST', null) ----> 'TEST'

---

NULL 处理

在查询数据的时候，有的数据会出现一些空值。如果我们要处理这些空值，将其替换为默认值，那么就会用到下面这些函数。

Mysql

MySql中，可使用的函数有 ifnull(expr1,expr2) 以及 if(expr1,expr2,expr3)。

• ifnull(expr1,expr2)：若 expr1 为 null，则返回 expr2
• if(expr1,expr2,expr3)：若表达式 expr1 成立，返回 expr2，否则返回 expr3

 # ifnull 函数
 ifnull('测试', '有值')   -----> '有值'
 ifnull(null, '无值')  -----> '无值'
 # if 函数
 if('测试', '值1', '值2')   -----> '值1'
 if(null, '值1', '值2')   -----> '值2'

Oracle

Oracle中，可使用的函数有 NVL(expr1,expr1) 以及 NVL2(expr1,expr2,expr3)

• NVL(expr1,expr1)：若 expr1 为 null，则返回 expr2
• NVL2(expr1,expr2,expr3)：若表达式 expr1 成立，返回 expr2，否则返回 expr3

 # NVL 函数
 nvl('测试', '有值')   -----> '有值'
 nvl(null, '无值')   -----> '无值'
 # NVL2 函数
 nvl2('测试', '值1', '值2')  -----> '值1'
 nvl2(null, '值1', '值2')   -----> '值2'

---

获取字符串长度

Mysql（5.0及以上版本）

获取字符串长度有两个函数 length(expr)、char_length(expr)

• length(expr) 计算的是字符串所占的字节数。即数字和字母算一个字节，而汉字在不同的编码格式下占的字节数不同。UTF8 编码下，汉字占 3~4 个字节(一般的占3个，中日韩超大字符集的占4个)，GBK编码下占 2 个字节。
• char_length(expr)计算的是所占的字符数，即汉字，字母，数字都是算一个字符。
  可以利用 char_length(expr) = length(expr) 来判断字符串中是否包含中文。

# UTF8 编码下
length('123456')   -----> 长度为 6
char_length('123456')   -----> 长度为6

length('helle世界')   -----> 长度为 11
char_length('hello世界')   -----> 长度为 7

Oracle

同样有两个函数 length(expr)、lengthb(expr)。不同的是 length(expr) 计算字符数，而lengthb(expr)计算字节数

# UTF8 编码下
length('123456')   -----> 长度为 6
lengthb('123456')   -----> 长度为6

length('hello世界')   -----> 长度为 7
lengthb('helle世界')   -----> 长度为 11

---

截取字符串

MySql

在 MySql 中常用的字符串截取函数如下：

• left(str, length)：从左开始。截取 length 个字符返回
• right(str,length)：从右开始。截取 length 个字符返回
• substr(str,start,length)：从左开始。以 start 为起始位，截取 length 个字符返回。（length可省略，省略情况下以 start 为起始位，返回剩余全部字符）
  • start 的取值可以是负值，此情况下 start 计算从右边开始
  • substr 函数还有其他的变种，这里不再详细描述，可自行了解
• substring_index(str,delimiter,number)：返回 str 中第 number 个出现的分隔符 delimiter 之前的子字符串。
  • 当 number 是正数，计算和截取方式从左边开始算 —> 即左为前
  • 当 number 是负数，计算和截取方式从右边开始算 —> 即右为前

# left
left('abcde', 2)  ----> ab
# right
right('abcde', 3)  ----> cde
# substr
substr('abcde', 3, 2) ----> cd
substr('abcde', 3)  ----> cde
substr('abcde', -4, 3)  ----> bcd
# substring_index
substring_index('a*b*c*d*e', '*', 2) -----> 'a*b'
substring_index('a*b*c*d*e', '*', -3) -----> 'c*d*e'

Oracle

Oracle 中字符串截取用到的函数有 substr 和 instr 这两个，经常一起搭配使用

• substr(str, start, [length])：从 start 位置开始，截取 length 个字符组成的子串并返回。
  • length 是可选值。
  • start 可为正也可为负。当 start 为负数时，其计算从右边（末尾）开始
• instr(str, child_str, [start], [show_time])：返回 child_str 子串在源串 str 中的位置
  • start 可选值。默认为 1，从左往右检索。为负数时，从右往左检索
  • show_time 可选值。子字符串在第几次出现在源字符串中。默认为 1。

# substr
substr('abcde', 3, 2)  ----> cd
substr('abcde', -2, 3)  ----> de 因剩余长度不足 3，所以只截取到末尾

# instr
/*
* 从左边位置 2 开始计算，返回 * 第二次出现的位置
*/
instr('a*b*c*d*e', '*', 2, 2)  ----> 4
/*
* 从右边位置开始算，返回 * 第二次出现的位置
*/
instr('a*b*c*d*e', '*', -2, 2)  ----> 6

# 联合运用
/**
* instr('a*b*c*d*e*f', '*', 2, 2) ----> 4
* substr('a*b*c*d*e*f', instr('a*b*c*d*e*f', '*', 2, 2), 3) ----> substr('a*b*c*d*e*f', 4, 3)
*/
substr('a*b*c*d*e*f', instr('a*b*c*d*e*f', '*', 2, 2), 3)  ----> '*c*'

时间的相关处理

获取当前时间

MySql

经常使用到的是下面几个时间函数

• now() 获取当前时间。精确到 秒
• curdate() 获取当前时间。精确到 日
• curtime() 获取当前时间。只获取 时 分 秒

select now() ----> 2019-11-22 16:30:26
select curdate() ----> 2019-11-22
select curtime() ----> 16:32:05

Oracle

oracle获取当前系统时间好像只有一个函数：

• sysdate 获取当前时间。精确到 秒

select sysdate from dual ----> 2019-11-22 16:33:26