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>
