Oracle迁移MySQL 8特殊SQL处理

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.*
	  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,rownum, order_str) as (
			select t0.*,@rownum := 1 rn, cast(@rownum as char) sdfsf from nayi_180328_connect_test t0 where t0.dept_id="root"
			union all
			select t2.*,@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") from dual

结果

2020-01-01