Oracle迁移MySQL 8特殊SQL处理
小编:啊南 196阅读 2020.12.04
- 递归查询
在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','[email protected]','1')
由于该分区是以数据的数目来进行分区的,前10W行在第一个分区,后10W行在第二个分区,所以我们按分区来进行查询时,只能在第一个分区查到该数据,而第二个分区是没有的
select * from customer partition(CUS_PART1)
select * from customer partition(CUS_PART2)
在MySQL 8中
创建同样的表,同样的分区
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','[email protected]','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
结果
13
为了保险起见,可以将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
相关推荐
- Oracle - 查询 1 查询语句查看账户下的所有表1 select * from tab;查看账户下的所有表的详细信息1 select * from user_tables;1.1 selectselect 用于从数据看查询数据。语法:1 select field1,filed2,.. .2 from tablename3 [where condition];利用 Oracle 数据库 Scott 账户…
- 3DMAX提示和技巧 本主题标识使用 Civil View 的一些重要提示和技巧。常规使用屏幕分辨率至少为 1280x1024 的 Civil View。低于此分辨率时,一些面板将占用过多屏幕空间。 将视口设置为线框显示以达到最佳性能。 要尽可能简化用户界面,请在单个视口中工作并关闭 3ds Max 命令面…