mydql--oracle项目迁移简说
前段时间，花费一个月在公司做了一个项目迁移，在这里把自己的经验留下来。
这是第一次，做数据库的迁移，所以，决定记录下来这样一个历程。
环境： eclipse 3.0
框架结构：WSI (webwork + spring + ibaits)
1 数据结构迁移。
在这里介绍一个工具，oracle官网可以下到，SQLDEVELOPER，现在的版本已经到了3.4或者4.0.，但是我推荐用的1.2，因为版本虽低，但是用于数据库迁移却很方便。
第一步，需要导入mysql的驱动
Tools-->preferences:
然后建立连接：
接下来就是进行迁移了，迁移有两种方式：
第一种: 普通迁移
然后选择表或者数据库
然后确认迁移，接下来，你可以自己确定转换的类型，且会生成相应的ddl文件。这种方法也会把相应的索引迁移过去。
如果，如果对于mysql和oracle类型结构不是很清楚的话，在Oracle官网的文档里面也会有比较，当然，在日志的最后我也会复制上去。
在我的项目中，只用到了下面8中类型：
|
变量 |
Mydql |
Oracle |
|
字符型 |
Varchar |
Varchar2 |
|
Char型 |
Char |
Varchar2 |
|
整型 |
Int |
Number[10,0] |
|
浮点型 |
Double |
Float[24,0] |
|
中等长度 |
Mediumblob |
Long raw |
|
文本 |
Text |
Clob |
|
长文本 |
MediunText |
Clob |
|
日期 |
Date |
Date |
第二种是快速迁移：
填写好所弹出来的窗口的STEP1-STEP4,点击确认即可。
2 数据迁移
2.1 表名去除单引号
很多时候，为了防止关键字的情况下，在往mysql插入数据时，往往会把表名放在单引号里，而在oracle里面是行不通的、所以，第一步，就是需要把所有的单引号去掉。
2.2 日期转换问题
在oracle里面，date型数据插入是一个很大的问题，日期格式默认情况下是‘04-9月 -01’，
而我们的数据却是’2011-11-11 ’格式的，怎么处理呢？
大多数人会想到to_date函数
to_date(‘2011-11-11’,’yyyy-MM-dd’);
当然，在数据量不大的情况下，这种方法是可行了，可是，如果你需要插入的是好几万，甚至是几十万书句的时候，这种方式就行不通了，在这里就说说第二种方法。
改变oracle日期的默认格式。
Alter session set nls_date_format = ‘yyyy-MM-dd hh24:mi:ss’
注意的是，这只是暂时保存在session作用域，当你一旦关闭的时候也就得重新执行一次。
2.3 &问题
在oracle里面，&是表示用户输入的标识，而在实际情况中，想text里面插入值得时候，很有可能是大片文章，那么，相对应的，也就可能出现了  "等符号的标识，我采取的方法是把所有这种标识去掉。
而如果不想去掉的话，可以采用[]把&包起来，也可达到相同的效果。
最后，关于数据迁移，我的建议是，用oracle自带的sqlplus，数据量过大时，第三方工具操作起来也相应的慢。
3 项目迁移
3.1 更改数据库连接配置
jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc\:oracle\:thin\:@localhost\:1521\:ID
3.2 SQL文的改变
总的概括：函数、limit分页、视图和索引、GROOUP BY
关于函数：
3.2.1 DATEDIFF
A 用trunc代替
B 自己写函数（示例）
- /*
- --功能:将字符串转为年月日格式,删除时分秒.
- --来源:http://jorkin.reallydo.com/article.asp?id=529
- --参数:
- Datechar Varchar2
- --Oracle9i测试通过
- */
- Create Or Replace Function CDate(Datechar In Varchar2) Return Date Is
- ReallyDo Date;
- Begin
- Select to_date(to_char(to_date(to_char(Datechar), 'YYYY-MM-DD HH24:MI:SS'),
- 'YYYY-MM-DD'),
- 'YYYY-MM-DD')
- Into ReallyDo
- From Dual;
- Return(ReallyDo);
- End CDate;
- /*
- --功能:将字符串转为年月日时分秒格式.
- --来源:http://jorkin.reallydo.com/article.asp?id=529
- --参数:
- Datechar Varchar2
- --Oracle9i测试通过
- */
- Create Or Replace Function CDateTime(Datechar In Varchar2) Return Date Is
- ReallyDo Date;
- Begin
- Select to_date(to_char(to_date(to_char(Datechar), 'YYYY-MM-DD HH24:MI:SS'),
- 'YYYY-MM-DD HH24:MI:SS'),
- 'YYYY-MM-DD HH24:MI:SS')
- Into ReallyDo
- From Dual;
- Return(ReallyDo);
- End CDateTime;
- /*
- --功能:类似MSSQL的日期比较函数
- --来源:http://jorkin.reallydo.com/article.asp?id=529
- --更新时间:20080721
- --参数:
- Datepart Varchar2 -- 比较年?月?日?
- StartDate Varchar2 -- 起始日期
- EndDate Varchar2 -- 结束日期
- --Oracle9i测试通过
- */
- Create Or Replace Function Datediff
- (
- Datepart In Varchar2,
- StartDate In Varchar2,
- EndDate In Varchar2
- ) Return Number Is
- ReallyDo Numeric;
- Begin
- Select Case Upper(Datepart)
- When 'YYYY' Then
- Trunc(Extract(Year From CDate(EndDate)) -
- Extract(Year From CDate(StartDate)))
- When 'M' Then
- Datediff('YYYY', StartDate, EndDate) * 12 +
- (Extract(Month From CDate(EndDate)) -
- Extract(Month From CDate(StartDate)))
- When 'D' Then
- Trunc(CDate(EndDate) - CDate(StartDate))
- When 'H' Then
- Datediff('D', StartDate, EndDate) * 24 +
- (to_Number(to_char(CDateTime(EndDate), 'HH24')) -
- to_Number(to_char(CDateTime(StartDate), 'HH24')))
- When 'N' Then
- Datediff('D', StartDate, EndDate) * 24 * 60 +
- (to_Number(to_char(CDateTime(EndDate), 'MI')) -
- to_Number(to_char(CDateTime(StartDate), 'MI')))
- When 'S' Then
- Datediff('D', StartDate, EndDate) * 24 * 60 * 60 +
- (to_Number(to_char(CDateTime(EndDate), 'SS')) -
- to_Number(to_char(CDateTime(StartDate), 'SS')))
- Else
- -29252888
- End
- Into ReallyDo
- From Dual;
- Return(ReallyDo);
- End Datediff;
/*
--功能:将字符串转为年月日格式,删除时分秒.
--来源:http://jorkin.reallydo.com/article.asp?id=529
--参数:
Datechar Varchar2
--Oracle9i测试通过
*/
Create Or Replace Function CDate(Datechar In Varchar2) Return Date Is
ReallyDo Date;
Begin
Select to_date(to_char(to_date(to_char(Datechar), 'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD'),
'YYYY-MM-DD')
Into ReallyDo
From Dual;
Return(ReallyDo);
End CDate;
/*
--功能:将字符串转为年月日时分秒格式.
--来源:http://jorkin.reallydo.com/article.asp?id=529
--参数:
Datechar Varchar2
--Oracle9i测试通过
*/
Create Or Replace Function CDateTime(Datechar In Varchar2) Return Date Is
ReallyDo Date;
Begin
Select to_date(to_char(to_date(to_char(Datechar), 'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD HH24:MI:SS')
Into ReallyDo
From Dual;
Return(ReallyDo);
End CDateTime;
/*
--功能:类似MSSQL的日期比较函数
--来源:http://jorkin.reallydo.com/article.asp?id=529
--更新时间:20080721
--参数:
Datepart Varchar2 -- 比较年?月?日?
StartDate Varchar2 -- 起始日期
EndDate Varchar2 -- 结束日期
--Oracle9i测试通过
*/
Create Or Replace Function Datediff
(
Datepart In Varchar2,
StartDate In Varchar2,
EndDate In Varchar2
) Return Number Is
ReallyDo Numeric;
Begin
Select Case Upper(Datepart)
When 'YYYY' Then
Trunc(Extract(Year From CDate(EndDate)) -
Extract(Year From CDate(StartDate)))
When 'M' Then
Datediff('YYYY', StartDate, EndDate) * 12 +
(Extract(Month From CDate(EndDate)) -
Extract(Month From CDate(StartDate)))
When 'D' Then
Trunc(CDate(EndDate) - CDate(StartDate))
When 'H' Then
Datediff('D', StartDate, EndDate) * 24 +
(to_Number(to_char(CDateTime(EndDate), 'HH24')) -
to_Number(to_char(CDateTime(StartDate), 'HH24')))
When 'N' Then
Datediff('D', StartDate, EndDate) * 24 * 60 +
(to_Number(to_char(CDateTime(EndDate), 'MI')) -
to_Number(to_char(CDateTime(StartDate), 'MI')))
When 'S' Then
Datediff('D', StartDate, EndDate) * 24 * 60 * 60 +
(to_Number(to_char(CDateTime(EndDate), 'SS')) -
to_Number(to_char(CDateTime(StartDate), 'SS')))
Else
-29252888
End
Into ReallyDo
From Dual;
Return(ReallyDo);
End Datediff;
此段代码来源:http://Jorkin.Reallydo.Com/default.asp?id=529
3.2.2 系统日期函数
Mysql : select sysdate()
Oracle : select sysdate from dual;
…..
在此省略一步分函数，因为mysql函数比较强大，所以具体的时候碰到了网上搜索对应的方案把。比如date_add等等。
关于 limit
Limit是mysql自带的分页机制，当迁移到oracle里面来，也有两种处理机制。
A 简单的可以用 rownum代替
示例： 查询emp表中的前三行
- select s.* from (select t.*,rownum rn from emp t) s
- where s.rn <= 3
select s.* from (select t.*,rownum rn from emp t) s
where s.rn <= 3
B 写一个oracle分页函数，这个大家肯定都用过吧。
关于索引和是视图
索引没什么变化，直接拿过来运行就OK了。
视图的语法不同，
在mysql中：
CREATE ALGORITHM=UNDEFINED DEFINER=`username`@`%` SQL SECURITY DEFINER VIEW viewname
Oracle： create or replace view viewname
关于group by
在oracle里面，有个规定，含有group by的查询语句，字段类不在聚合函数中，就必须在group by语句后，我的说法是，你把字段列加到group by 后面就可以了。
下面说个insert和update的问题：
向一个表中执行update和insert操作时，类型为CLOB列的就出现异常
异常为：
仅可以为插入 LONG 列的 LONG 值赋值 CLOB
............
当时上网查了，有很多人也碰到了相同的问题，总体而言有两种解决方式，
第一种是驱动版本过低。
第二种是修改hibernate的配置文件，当时，前提是你开发的持久层必须是hibernate。
我从oracle官方网站下了一个最新的JDBC驱动(通过附件上传了)，运行还是报哪个错误。没有效果。
而第二种方法，对于我而言，是肯定行不通的。
网上有部分人说，值长度在2000-4000之间会出现异常，
可是，我的不同，
我的是值的长度在1000-2000之间会出现异常，稍短或长都没问题。
在这里说明一下，肯定不会是因为长度限制的问题，因为CLOB在ORACLE里面，所输入的可达4G。
对于LONG，这个类型我没用过，在ORACLE里面也不建议用。是一个已经被废弃了的类型。
最后我的解决方法是，在视图层，对所输入的进行判断，把少了的用空格不上，在jsp中，四个空格等于一个字符的长度。
让其长度超过2000即可，但是，这种方法对于紧急情况可能还不错，而效率上就不说了。
还有，就是说，如果你的长度不会超过2000，就建议用varchar2，但是需要注意的是数据库字符集应该是UTF-8的，对于UTF-8或欧洲的某些字符集，oracle在存储时，对于一个字符需要2个或3个字节的存储空间，虽然表定义中varchar2(4000)，但是其实该字段的data_length为其2倍或3倍长。
在这里，也顺便把在网上看到的，关于hibernate修改配置的方法，粘贴出来.
Hibernate實體對象中的数据成员类型为String，映射的数据库字段类型为org.springframework.orm.hibernate.support.ClobStringType 。實例如下：
- @SuppressWarnings( " serial " )
- @Entity
- @Table(name = " GUIDE " )
- // @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
- public class Guide {
- @Type(type = " org.springframework.orm.hibernate3.support.ClobStringType " )
- private String content; // 内容
- // get & set
- }
@SuppressWarnings( " serial " )
@Entity
@Table(name = " GUIDE " )
// @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Guide {
@Type(type = " org.springframework.orm.hibernate3.support.ClobStringType " )
private String content; // 内容
// get & set
}
如果使用Spring的这个Clob类型就需要在applicationContext.xml中的sessionFactory bean里注入oracleLobHandler bean。下面給出Oracle數據庫的LobHandler配置
- < bean id ="oracleLobHandler"
- class ="org.springframework.jdbc.support.lob.OracleLobHandler"
- lazy-init ="true" >
- < property name ="nativeJdbcExtractor" ref ="nativeJdbcExtractor" />
- </ bean >
- < bean id ="nativeJdbcExtractor" class ="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
- lazy-init ="true" />
- <!-- Hibernate SessionFactory -->
- < bean id ="sessionFactory" class ="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" >
- < property name ="dataSource" ref ="dataSource" />
- < property name ="configLocation" value ="classpath:hibernate/hibernate.cfg.xml" />
- < property name ="hibernateProperties" >
- < value >
- </ value >
- </ property >
- < property name ="lobHandler" ref ="oracleLobHandler" />
- </ bean >
< bean id ="oracleLobHandler"
class ="org.springframework.jdbc.support.lob.OracleLobHandler"
lazy-init ="true" >
< property name ="nativeJdbcExtractor" ref ="nativeJdbcExtractor" />
</ bean >
< bean id ="nativeJdbcExtractor" class ="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
lazy-init ="true" />
<!-- Hibernate SessionFactory -->
< bean id ="sessionFactory" class ="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" >
< property name ="dataSource" ref ="dataSource" />
< property name ="configLocation" value ="classpath:hibernate/hibernate.cfg.xml" />
< property name ="hibernateProperties" >
< value >
</ value >
</ property >
< property name ="lobHandler" ref ="oracleLobHandler" />
</ bean >
大家可能已经注意到 nativeJdbcExtractor 和 oracleLobHandler Bean 都设置为 lazy-init="true"，这是因为 nativeJdbcExtractor 需要通过运行期的反射机制获取底层的JDBC 对象，所以需要避免在 Spring 容器启动时就实例化这两个Bean。
- < bean id ="defaultLobHandler"
- class ="org.springframework.jdbc.support.lob.DefaultLobHandler"
- lazy-init ="true" />
- < bean id ="testDao" class ="com.test.dao.jdbc.TestJdbcDao" >
- < property name ="lobHandler" ref =" defaultLobHandler" />
- < property name ="jdbcTemplate" ref ="jdbcTemplate" />
- </ bean >
< bean id ="defaultLobHandler"
class ="org.springframework.jdbc.support.lob.DefaultLobHandler"
lazy-init ="true" />
< bean id ="testDao" class ="com.test.dao.jdbc.TestJdbcDao" >
< property name ="lobHandler" ref =" defaultLobHandler" />
< property name ="jdbcTemplate" ref ="jdbcTemplate" />
</ bean >
DefaultLobHandler 只是简单地代理标准 JDBC 的 PreparedStatement 和 ResultSet 对象，由于并不需要访问数据库驱动本地的 JDBC 对象，所以它不需要 NativeJdbcExtractor 的帮助。
希望总有一张方法能帮你解决掉麻烦的ORA-01461。
如果，哪位有更好的解决方法，就麻烦分享一下了。
还有，就是说，如果你的长度不会超过2000，就建议用varchar2，但是需要注意的是数据库字符集应该是UTF-8的，对于UTF-8或欧洲的某些字符集，oracle在存储时，对于一个字符需要2个或3个字节的存储空间，虽然表定义中varchar2(4000)，但是其实该字段的data_length为其2倍或3倍长。
.............
4 旧数据导入问题
对于一个数据库项目迁移工作，数据迁移也是一个难点，
我就说说我碰到的问题。
在mysql中一个字段类型为text,里面的数据多的有几万个字符，而mysql所导出来的东西又仅仅是.sql文件，而在oracle里面，任何一个字符串通过insert语句插入值时，一旦超过4000个字符，就会报错。
这里不多说，相信碰到的人都知道，如果你是几条数据好几个方法都能解决，可是如果你碰到了好几万条数据的话，就会有点手足无措了，这里我最后是通过把原来的数据导出成xml文档，然后通过一个简单的java程序进行插入值。具体代码就不贴了，需要的博友可以通过留言，我给你。
总的来说，最主要的就是sql问题，认真仔细点就好。关于迁移，是一个工作量比较大，但是技术含量比较低的工作，所以，每一步走稳，每一步走好是最重要的。
posted on 2011-06-09 14:58 candiceChan 阅读(563) 评论(0) 编辑 收藏 举报
