oracle和mysql函数_Oracle和MySQL的常用函数比较（一）

image.png

按照db-engines的最新排名，Oracle和MySQL依旧占据榜单的前列，作为业界影响力的老大和老二，两者在具体应用中也存在不少差异，我们今天就其支持的函数做一个对比。

数据库函数给用户提供了一种便利的扩展手段，只要指定参数，函数就会返回期待的结果。中间实现细节用户不用关心。

我们将常用函数分为以下几类：字符串函数、数值函数、日期函数、转换函数

我们一一做些比较和学习，我们先就常用的字符串函数进行比较和学习。

字符串函数

大小写转换(upper,lower)：

Oracle

SQL> select upper('hello world') from dual;

UPPER('HELL

-----------

HELLO WORLD

SQL> select lower('HELLO WORLD') from dual;

LOWER('HELL

-----------

hello world

SQL>

MySQL

mysql> select upper('hello world') ;

+----------------------+

| upper('hello world') |

+----------------------+

| HELLO WORLD |

+----------------------+

1 row in set (0.00 sec)

mysql> select lower('HELLO WORLD') ;

+----------------------+

| lower('HELLO WORLD') |

+----------------------+

| hello world |

+----------------------+

1 row in set (0.00 sec)

mysql>

字符串连接(concat)：

Oracle

SQL> select concat('hello','world') from dual;

CONCAT('HE

----------

helloworld

MySQL

mysql> select concat('hello','world');

+-------------------------+

| concat('hello','world') |

+-------------------------+

| helloworld |

+-------------------------+

1 row in set (0.00 sec)

mysql>

字符串截取(substr，left，right)：

Oracle

SQL> select substr('hello world',1,5) from dual;

SUBST

-----

hello

MySQL

mysql> select substring('hello world',1,5);

+------------------------------+

| substring('hello world',1,5) |

+------------------------------+

| hello |

+------------------------------+

1 row in set (0.00 sec)

mysql> select substr('hello world',1,5);

+---------------------------+

| substr('hello world',1,5) |

+---------------------------+

| hello |

+---------------------------+

1 row in set (0.00 sec)

mysql> select left('hello world',5);

+-----------------------+

| left('hello world',5) |

+-----------------------+

| hello |

+-----------------------+

1 row in set (0.00 sec)

mysql> select right('hello world',5);

+------------------------+

| right('hello world',5) |

+------------------------+

| world |

+------------------------+

1 row in set (0.00 sec)

mysql>

字符串复制(repeat)：

Oracle

没有字符串复制函数

MySQL

mysql> select repeat('hello',2);

+-------------------+

| repeat('hello',2) |

+-------------------+

| hellohello |

+-------------------+

1 row in set (0.00 sec)

字符串替换(replace)：

Oracle

SQL> select replace('hello world','hello','kitty') from dual;

REPLACE('HE

-----------

kitty world

SQL> select translate('12345','135','abc') from dual;

TRANS

-----

a2b4c

SQL>

MySQL

mysql> select replace('hello world','hello','kitty');

+----------------------------------------+

| replace('hello world','hello','kitty') |

+----------------------------------------+

| kitty world |

+----------------------------------------+

1 row in set (0.00 sec)

mysql>

字符串长度计算(length)：

Oracle

SQL> select length('hello world') from dual;

LENGTH('HELLOWORLD')

--------------------

11

SQL>

MySQL

mysql> select length('hello world') ;

+-----------------------+

| length('hello world') |

+-----------------------+

| 11 |

+-----------------------+

1 row in set (0.00 sec)

mysql>

字符串查找(instr)：

Oracle

SQL> select instr('hello world','hello') from dual;

INSTR('HELLOWORLD','HELLO')

---------------------------

1

SQL>

MySQL

mysql> select instr('hello world','hello') ;

+------------------------------+

| instr('hello world','hello') |

+------------------------------+

| 1 |

+------------------------------+

1 row in set (0.00 sec)

mysql>

字符转ASCII码(ascii)：

Oracle

SQL> select ascii('a') from dual;

ASCII('A')

----------

97

MySQL

mysql> select ascii('a') ;

+------------+

| ascii('a') |

+------------+

| 97 |

+------------+

1 row in set (0.00 sec)

mysql>

ASCII码转字符(chr，char)：

Oracle

SQL> select chr(97) from dual;

C

-

a

SQL>

MySQL

mysql> select char(97);

+----------+

| char(97) |

+----------+

| a |

+----------+

1 row in set (0.00 sec)

去空格(trim，ltrim，rtrim)：

Oracle

SQL> select trim(' hello ') from dual;

TRIM(

-----

hello

SQL> select ltrim(' hello ') from dual;

LTRIM(

------

hello

SQL> select rtrim(' hello ') from dual;

RTRIM(

------

hello

SQL>

MySQL

mysql> select trim(' hello ');

+-----------------+

| trim(' hello ') |

+-----------------+

| hello |

+-----------------+

1 row in set (0.00 sec)

mysql> select ltrim(' hello ');

+------------------+

| ltrim(' hello ') |

+------------------+

| hello |

+------------------+

1 row in set (0.00 sec)

mysql> select rtrim(' hello ');

+------------------+

| rtrim(' hello ') |

+------------------+

| hello |

+------------------+

1 row in set (0.00 sec)

mysql>

字符串填充(lpad,rpad)：

Oracle

SQL> select lpad('hello',10,'*') from dual;

LPAD('HELL

----------

*****hello

SQL> select rpad('hello',10,'*') from dual;

RPAD('HELL

----------

hello*****

SQL>

MySQL

mysql> select lpad('hello',10,'*');

+----------------------+

| lpad('hello',10,'*') |

+----------------------+

| *****hello |

+----------------------+

1 row in set (0.00 sec)

mysql> select rpad('hello',10,'*');

+----------------------+

| rpad('hello',10,'*') |

+----------------------+

| hello***** |

+----------------------+

1 row in set (0.00 sec)

mysql>