oracle与mysql的区别

项目要求数据源从Oracle改成MySQL,遇到的需要改造的地方 

 

1、在Mysql中实现Oracle中的listagg()的功能

Oracle实现列转行并用逗号拼接用listagg()功能实现，如下

 listagg(b.SIGN, ',') within group (order by b.SIGN)

而在MySQL下可以使用group_concat()来实现：

group_concat(b.SIGN separator ',')

2、在Mysql中实现Oracle中的sysdate时间查询功能

（1）、在Oracle中获取当前时间用sysdate

select sysdate from dual;

（2）、MySQL提供sysdate()函数与now()函数；

a、如果函数sysdate()用于字符串上下文或YYYYMMDDHHMMSS格式，则SYSDATE()函数将返回当前日期时间，格式为“YYYY-MM-DD HH:MM:SS”的值，以防在函数用于数字上下文。

SYSDATE()函数接受一个可选参数fsp，它确定结果是否应该包含从0到6的小数秒精度。如下：

mysql> SELECT SYSDATE();
+---------------------+
| SYSDATE()           |
+---------------------+
| 2021-05-28 16:21:16 |
+---------------------+
1 row in set


mysql> SELECT SYSDATE(3);
+-------------------------+
| SYSDATE(3)              |
+-------------------------+
| 2021-05-28 16:21:16.985 |
+-------------------------+
1 row in set

b、sysdate()与 now()比较

sysdate()返回的是实际上返回执行时的时间，而now()返回一个常量时间，该语句开始执行。用sleep()暂停查询5秒可以看到区别

mysql>  SELECT SYSDATE(), SLEEP(5), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(5) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2021-05-28 16:21:16 |        0 | 2021-05-28 16:21:21 |
+---------------------+----------+---------------------+
1 row in set


mysql> SELECT NOW(), SLEEP(5), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(5) | NOW()               |
+---------------------+----------+---------------------+
| 2021-05-28 16:21:16 |        0 | 2021-05-28 16:21:16 |
+---------------------+----------+---------------------+
1 row in set

（3）、时间加减

当用到当前日期加一天或者减一天的场景时:

Oracle：直接加减数字，返回仍然是时间类型

select sysdate-1 from dual;

select sysdate+1 from dual;

MySQL:加法用DATE_ADD，减法用DATE_SUB。另：

year 年份 

month 月份 

day 天 

minute 分钟 

second 秒

SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);

SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);

SELECT DATE_SUB(NOW(),INTERVAL 1 YEAR);

SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);

（4）、时间转换格式

Oracle:日期转日期格式的字符串用to_char，日期格式的字符串转日期用to_date

select to_char(sysdate,'yyyy-MM-dd') time from dual;

select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') time from dual;

select to_date('2021-05-28','yyyy/MM/dd') time from dual;

MySQL:日期转日期格式的字符串用date_format，日期格式的字符串转日期用str_to_date

SELECT date_format(NOW(),'%Y-%m-%d') times;

SELECT date_format(NOW(),'%Y-%m-%d %k:%i:%s') times;

SELECT str_to_date('2021-05-28','%Y-%m-%d') times;

3、在Mysql中实现Oracle中的to_number

在MySQL中用cast语法

#整形
select cast('123' as unsigned int);

select cast('123' as signed integer);

#浮点
select cast('123' as decimal(10,2));

4、在Mysql中实现Oracle中的rownum

1、在oracle中，可使用rownum处理很多与行号相关的问题，比如分页等，但是mysql中没有类似的函数。如果想要在mysql中实现类似功能，可以参考如下来自定义显示行号：

2、分页MySQL使用limit来实现

#带rowno
SELECT @rowno:=@rowno+1 as rowno,r.* from dsm_alldata r ,(select @rowno:=0) t;

#分页
SELECT * FROM dsm_alldata limit 0,5;

3、MySQL从8.0开始支持窗口函数，row_number()可实现上述功能

-- 数据结构
mysql> select * from order_tb;
+----------+---------+--------+---------------------+
| order_id | user_no | amount | create_date         |
+----------+---------+--------+---------------------+
|        1 | 0001    |    100 | 2020-10-01 00:00:00 |
|        2 | 0001    |    300 | 2020-10-02 00:00:00 |
|        3 | 0001    |    500 | 2020-10-03 00:00:00 |
|        4 | 0001    |    800 | 2020-10-04 00:00:00 |
|        5 | 0001    |    900 | 2020-10-05 00:00:00 |
|        6 | 0002    |    500 | 2020-10-06 00:00:00 |
|        7 | 0002    |    600 | 2020-10-07 00:00:00 |
|        8 | 0002    |    300 | 2020-10-08 00:00:00 |
|        9 | 0002    |    800 | 2020-10-09 00:00:00 |
|       10 | 0002    |    800 | 2020-10-10 00:00:00 |
+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)


mysql> select * from
    -> (
    ->     select row_number() over (partition by user_no order by amount desc) as row_num,
    ->     order_id, user_no, amount, create_date
    ->     from order_tb
    -> ) as t;
+---------+----------+---------+--------+---------------------+
| row_num | order_id | user_no | amount | create_date         |
+---------+----------+---------+--------+---------------------+
|       1 |        5 | 0001    |    900 | 2020-10-05 00:00:00 |
|       2 |        4 | 0001    |    800 | 2020-10-04 00:00:00 |
|       3 |        3 | 0001    |    500 | 2020-10-03 00:00:00 |
|       4 |        2 | 0001    |    300 | 2020-10-02 00:00:00 |
|       5 |        1 | 0001    |    100 | 2020-10-01 00:00:00 |
|       1 |        9 | 0002    |    800 | 2020-10-09 00:00:00 |
|       2 |       10 | 0002    |    800 | 2020-10-10 00:00:00 |
|       3 |        7 | 0002    |    600 | 2020-10-07 00:00:00 |
|       4 |        6 | 0002    |    500 | 2020-10-06 00:00:00 |
|       5 |        8 | 0002    |    300 | 2020-10-08 00:00:00 |
+---------+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)

5、在Mysql中实现Oracle中的nvl

在MySQL中用ifnull(expr1,expr2)，如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。

mysql> select IFNULL(1,0);

-> 1

mysql> select IFNULL(0,10);

-> 0

mysql> select IFNULL(1/0,10);

-> 10

mysql> select IFNULL(1/0,'yes');

-> 'yes'

6、Every derived table must have its own alias（sql语句错误解决方法）

在做多表查询，或者查询的时候产生新的表的时候会出现这个错误：Every derived table must have its own alias（每一个派生出来的表都必须有一个自己的别名）。

#oacle中不报错
select * from (select * from dsm_alldata);

#mysql中不起别名t则报错
select * from (select * from dsm_alldata) t;

7、mysql列出某一时间段的所有分钟

MySQL中如下sql，表dsm_mete_value为数据库中已存在的拥有比较多数据的表，若是选用的表只有5条数据则只能列出开始时间后五分钟的时间值。

SET @beginDate='2021-06-09 00:00:00';
SET @maxDate ='2021-06-09 23:59:59';
SELECT STR_TO_DATE(@tempMinute :=DATE_ADD(@tempMinute,INTERVAL 1 MINUTE),'%Y-%m-%d %H:%i:%s') as end_time
FROM dsm_mete_value t
LEFT JOIN (SELECT @tempMinute :=@beginDate) b  ON 1=1
WHERE @tempMinute < DATE_sub(@maxDate,INTERVAL 1 MINUTE)

在Oracle中可以使用level函数实现该功能;

SELECT
	to_date (
		'2021-05-09 00:00:00',
		'yyyy-mm-dd hh24:mi:ss'
	) + (LEVEL) / 24 / 60 AS end_time
FROM
	DUAL connect BY LEVEL <= floor(
		(
			to_date (
				'2021-05-09 23:59:59',
				'yyyy-mm-dd hh24:mi:ss'
			) - to_date (
				'2021-05-09 00:00:00',
				'yyyy-mm-dd hh24:mi:ss'
			)
		) * 24 * 60
	)