oracle迁移mysql 遇到的一些坑

背景

疫情期间，有一个老项目要从oracle迁移到MySQL， 其中碰到了一些坑，在此做个记录和大家分享一下

sequence 变 自增id

老项目中oracle主键id都是使用sequence，在mysql中自然得使用自增主键，这块主要朋友的问题是，在老项目中使用的dao层框架是springJdbc，再插入数据库中，有时需要返回插入的主键ID，oracle数据库一般都是，先查询nextval().再执行插入数据，在mysql中则不行，在springJdbc中使用KeyHolder，可以返回自增id。

oralce返回主键id代码

id = jdbcTemplate.queryForObject("SELECT SEQ_TABLE.NEXTVAL FROM DUAL",Long.class);

mysql返回主键ID代码
springjdbc通过keyHolder返回数据id

	KeyHolder keyHolder = new GeneratedKeyHolder();
			
		
				final String sql = "INSERT INTO TTABLEFO(PROVINCECODE,FILESEQ,VENDORCODE,FILEVERSION," +
						"FILELENGTH,FILETIME,KEYINDEX,EXTENSION1,EXTENSION2,CREATE_TIME) VALUES(?,?,?,?,?,?,?,?,?,now())";
				Object [] args = { file.getPROVINCECODE(), file.getFILESEQ(), file.getVENDORCODE(),file.getFILEVERSION(),
		flag = jdbcTemplate.update(new PreparedStatementCreator() {
					
					@Override
					public PreparedStatement createPreparedStatement(Connection conn)
							throws SQLException {
						PreparedStatement prepareStatement = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
						prepareStatement.setString(1, file.getPROVINCECODE());
						prepareStatement.setLong(2, file.getFILESEQ());
						prepareStatement.setString(3, file.getVENDORCODE());

					
						return prepareStatement;
					}
				},keyHolder);
	Long id = keyHolder.getKey().intValue();

涉及到时间函数的改造

oralce 涉及到时间一般都都使用sysdate，mysql中一般使用sysdate()/now()

oracle代码

insert into t_bis_agentit(staffid,verifytime,inserttime) values (?,?,sysdate)

mysql代码

now()

递归关键字connect by

oracle支持递归查询，而mysql根本没有类似的关键字，所以在查询中只能通过代码去实现递归查询

oracle代码

 select * from (select * from t_sys_menu_item order by menu_item_id asc ) m start with m.pid=0 connect by prior m.menu_item_id= m.pid

嵌套查询问题

oracle 嵌套查询中只需用用括号括起来不需要将子查询作别名，而在mysql中你可能会遇到这样的报错
You can’t specify target table ‘表名’ for update in FROM clause

oracle代码

UPDATE result 
SET StudentResult=StudentResult+5
WHERE StudentResult in( 
    (SELECT  res.StudentResult
                                                FROM student stu
                                                JOIN result res on res.StudentNo=res.StudentNo
                                                where res.StudentResult=53 and stu.GradeId=(SELECT GradeId
                                                                                        FROM grade
                                                                                        WHERE GradeName='大一'
                                                                                        )
    )

)

mysql代码

UPDATE result 
SET StudentResult=StudentResult+5
WHERE StudentResult in( 

    SELECT a.StudentResult from
    (SELECT  res.StudentResult
                                                FROM student stu
                                                JOIN result res on res.StudentNo=res.StudentNo
                                                where res.StudentResult=53 and stu.GradeId=(SELECT GradeId
                                                                                        FROM grade
                                                                                        WHERE GradeName='大一'
                                                                                        )
    ) AS a

)

也就是说：把结果集当作一个表，自我查询一遍

格式为：SELECT a.StudentResult FROM
(结果集)a

时间日期转换函数

oracle中一般使用to_char()/to_date()进行时间和字符串之间的转换，而在mysql中一般使用DateFormat（）

oracle代码

to_char(written_ack_time, "
							+ "'yyyy-mm-dd hh24:mi:ss')

mysql代码

DATE_FORMAT(written_ack_time,'%Y-%m-%d %H:%i:%s')

nvl函数和ifnull

nvl 函数和ifnull 函数都用来判断是否为空指针，其中nvl函数是oracle的函数，ifnull函数是mysql的函数

oracle函数

select CASE when (t.op_type = '2' and nvl(t.rps_role, '0') = '0') THEN ");

mysql函数

select CASE when (t.op_type = '2' and IFNULL(t.rps_role, '0') = '0')

删除语句的区别

oracle 删除语句没有没有关键字 from mysql删除语句关键字必须使用from

oracle语句

delete tableName where a = 2

mysql语句

delete from  tableName where a =