Oracle迁移MySQL 8特殊SQL处理顶

算法之名

发布于 2020-05-18 16:41:24

1K0

发布于 2020-05-18 16:41:24

递归查询

在Oracle中建立一个表

create table nayi_180328_connect_test(
	dept_id varchar2(50), 
	parent_id varchar2(50), 
	dept_name varchar2(100), 
	dept_rank varchar2(2000), 
	val number);

插入语句

insert into nayi_180328_connect_test
	select 'root', '', '全国', '', 0 from dual
	union all
	select 'root_1', 'root', '北京市', '', 2000 from dual
	union all
	select 'ln_root', 'root', '辽宁省', '', 200 from dual
	union all
	select 'ln_ys', 'ln_root', '辽宁省沈阳市', '', 1000 from dual
	union all
	select 'ln_sy_hp', 'ln_ys', '辽宁省沈阳和平区', '', 500 from dual
	union all
	select 'ln_ys_dd', 'ln_ys', '辽宁省沈阳大东区', '', 600 from dual
	union all
	select 'jl_root', 'root', '吉林省', '', 0 from dual
	union all
	select 'jl_jl', 'jl_root', '吉林省吉林市', '', 200 from dual
	union all
	select 'jl_cc', 'jl_root', '吉林省长春市', '', 500 from dual
	;

Oracle的递归查询语句如下

	select t1.*,CONNECT_BY_ROOT(dept_name) root_name
	  from nayi_180328_connect_test t1
	 where 1=1
	 connect by prior t1.dept_id = t1.parent_id
	 start with t1.dept_id = 'root'
	;

结果如下

迁移MySQL 8,建表如下

create table nayi_180328_connect_test(
	dept_id varchar(50), 
	parent_id varchar(50), 
	dept_name varchar(100), 
	dept_rank varchar(2000), 
	val int,
	PRIMARY key	(dept_id)
	)

插入语句与Oracle相同

	insert into nayi_180328_connect_test
	select 'root', '', '全国', '', 0 from dual
	union all
	select 'root_1', 'root', '北京市', '', 2000 from dual
	union all
	select 'ln_root', 'root', '辽宁省', '', 200 from dual
	union all
	select 'ln_ys', 'ln_root', '辽宁省沈阳市', '', 1000 from dual
	union all
	select 'ln_sy_hp', 'ln_ys', '辽宁省沈阳和平区', '', 500 from dual
	union all
	select 'ln_ys_dd', 'ln_ys', '辽宁省沈阳大东区', '', 600 from dual
	union all
	select 'jl_root', 'root', '吉林省', '', 0 from dual
	union all
	select 'jl_jl', 'jl_root', '吉林省吉林市', '', 200 from dual
	union all
	select 'jl_cc', 'jl_root', '吉林省长春市', '', 500 from dual

MySQL 8递归查询语句如下

	with recursive t1(dept_id,parent_id,dept_name,dept_rank,val,root_name,rownum, order_str) as (
			select t0.*,t0.dept_name,@rownum := 1 rn, cast(@rownum as char) sdfsf from nayi_180328_connect_test t0 where t0.dept_id='root'
			union all
			select t2.*,t1.root_name,@rownum := @rownum + 1 rn, concat(t1.order_str, '-', @rownum) st  from nayi_180328_connect_test t2,t1
			where t2.parent_id = t1.dept_id
	)
	select * from t1 order by order_str

结果如下

日期转换字符串

Oracle中

select to_char(sysdate,'yyyy-mm-dd') from dual

结果

2020-05-07

MySQL 8中

	select date_format(now(),'%Y-%m-%d') from dual

结果

2020-05-07

字符串转换日期

Oracle中

select to_date('2020-01-01','yyyy-mm-dd') from dual

结果

2020-01-01 00:00:00

MySQL 8中

select str_to_date('2020-01-01','%Y-%m-%d %h:%i:%s') from dual

结果

2020-01-01 00:00:00

判断为空，用其他值代替

Oracle中

select nvl(parent_id,'boot') from nayi_180328_connect_test where dept_id='root'

结果

boot

MySQL 8中

select ifnull(parent_id,'boot') from nayi_180328_connect_test where dept_id='root'

结果

boot

条件判断取值

Oracle中

select decode(parent_id,null,'全国','root','省市','地区') from nayi_180328_connect_test

结果

MySQL 8中

select case when parent_id is null then '全国' when parent_id='root' then '省市' else '地区' end from nayi_180328_connect_test

结果

聚合字段拼接

在Oracle中

WITH TEMP AS(  
SELECT 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL  
SELECT 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL  
SELECT 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL  
SELECT 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL  
SELECT 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL  
SELECT 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL   
)  
SELECT 
NATION,LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY)  AS CITIES
FROM TEMP  
GROUP BY NATION

结果

在MySQL 8中

WITH TEMP AS(  
SELECT 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL  
SELECT 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL  
SELECT 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL  
SELECT 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL  
SELECT 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL  
SELECT 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL   
)  
SELECT 
NATION,GROUP_CONCAT(city order by city) cities
FROM TEMP  
GROUP BY NATION

结果

截取字符串

在Oracle中

select substr('HelloWorld',0,3) value from dual;
select substr('HelloWorld',1,3) value from dual;

以上执行结果相同

Hel

在MySQL 8中

	select substr('HelloWorld',1,3) value from dual;

在MySQL中，substr()的首索引不能为0

结果

Hel

数字格式化字符串

在Oracle中

select to_char(12345678.657,'999,999,999,999.99')  from dual;

结果

12,345,678.66

在MySQL 8中

select format(12345678.657,2)  from dual;

结果

12,345,678.66

跨库查询

在Oracle中

select 字段名 from 表名@库名

在MySQL 8中

select 字段名 from 库名.表名

拼接字符串

在Oracle中

select 'ABC' || 'EFG' from dual

结果

ABCEFG

在MySQL 8中

select concat('ABC','EFG') from dual

结果

ABCEFG

表分区

在Oracle中

我们先创建两个表空间

create tablespace CUS_TS01 datafile '/home/oracle/app/oracle/oradata/helowin/cus01.dbf' size 20m;
create tablespace CUS_TS02 datafile '/home/oracle/app/oracle/oradata/helowin/cus02.dbf' size 20m;

此时查询表空间

select * from dba_tablespaces;

结果

我们可以看到最后两个表空间是我们刚生成的

CREATE TABLE CUSTOMER
(
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
    FIRST_NAME  VARCHAR2(30) NOT NULL,
    LAST_NAME   VARCHAR2(30) NOT NULL,
    PHONE        VARCHAR2(15) NOT NULL,
    EMAIL        VARCHAR2(80),
    STATUS       CHAR(1)
)
PARTITION BY RANGE (CUSTOMER_ID)
(
    PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,
    PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02
)

我们创建一个表，并生成两个表分区CUS_PART1,CUS_PART2.关于分区的分类可以参考https://www.cnblogs.com/wnlja/p/3979684.html

插入一条数据

insert into customer values (1,'Li','xiaoping','123456987','lixiaoping@123.com','1')

由于该分区是以数据的数目来进行分区的，前10W行在第一个分区，后10W行在第二个分区，所以我们按分区来进行查询时，只能在第一个分区查到该数据，而第二个分区是没有的

select * from customer partition(CUS_PART1)

select * from customer partition(CUS_PART2)

在MySQL 8中

创建同样的表，同样的分区，关于MySQL表分区的分类可以参考https://www.cnblogs.com/zhouguowei/p/9360136.html

CREATE TABLE CUSTOMER
(
    CUSTOMER_ID int NOT NULL,
    FIRST_NAME  VARCHAR(30) NOT NULL,
    LAST_NAME   VARCHAR(30) NOT NULL,
    PHONE        VARCHAR(15) NOT NULL,
    EMAIL        VARCHAR(80),
    STATUS       CHAR(1),
		primary key (CUSTOMER_ID)
)
PARTITION BY RANGE (CUSTOMER_ID)
(
    PARTITION CUS_PART1 VALUES LESS THAN (100000),
    PARTITION CUS_PART2 VALUES LESS THAN (200000)
)

插入同样的数据

insert into CUSTOMER values (1,'Li','xiaoping','123456987','lixiaoping@123.com','1')

进行分区查询

select * from CUSTOMER partition(CUS_PART1)

select * from CUSTOMER partition(CUS_PART2)

日期相减

在Oracle中

很多人喜欢用

trunc(日期1)-trunc(日期2)

因为trunc(日期)可以去掉时间部分，比如

select sysdate from dual

结果

2020-05-14 09:59:27

select trunc(sysdate) from dual

结果

2020-05-14 00:00:00

注:trunc(日期,'dd')与trunc(日期)意义相同

select trunc(sysdate ,'dd') from dual ;

结果

2020-05-15 00:00:00

在MySQL 8中

mysql中没有trunc函数，直接用

日期1-日期2

但如果日期带了时间部分，比如

select now() from dual

结果

2020-05-14 02:15:14

如果此时用带时间的日期相减会出错，可以用如下方式处理

select str_to_date(now(),'%Y-%m-%d')-str_to_date('2020-05-01','%Y-%m-%d') from dual

结果

为了保险起见，可以将Oracle中的trunc(日期)转换成str_to_date(日期,'%Y-%m-%d')

获取当前年份的第一天

在Oracle中

select TRUNC(SYSDATE, 'yyyy') from dual

结果

2020-01-01 00:00:00

在MySQL 8中

SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY) from dual

结果

2020-01-01

date_sub() 从日期减去指定的时间间隔。

格式：

DATE_SUB(date,INTERVAL expr type)

CURDATE() 函数返回当前的日期,不带时间

select curdate() from dual

结果

2020-05-14

DAYOFYEAR() 函数返回指定日期在一年中的位置

select dayofyear('2020-05-13') from dual

结果

134

DATE_SUB()函数的type为以下类型

Type值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

几个月之后(之前)的某天

在Oracle中

select ADD_MONTHS (to_date('2020-01-01','yyyy-mm-dd') , 3) from dual union all
select ADD_MONTHS (to_date('2020-01-01','yyyy-mm-dd') , -3) from dual

结果

2020-04-01 00:00:00

2019-10-01 00:00:00

在MySQL 8中

SELECT ADDDATE('2020-01-01', INTERVAL 3 MONTH) from dual union all
SELECT ADDDATE('2020-01-01', INTERVAL -3 MONTH) from dual

结果

2020-04-01

2019-10-01

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

oracle

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度