Oracle到mysql转换地问题分析总结.doc
《Oracle到mysql转换地问题分析总结.doc》由会员分享,可在线阅读,更多相关《Oracle到mysql转换地问题分析总结.doc(44页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、-_Oracle 到到 mysql 转换的问题总结转换的问题总结常用字段类型区别常用字段类型区别oraclemysqlnumber(10,0)intnumber(10,2)decimal(10,2)varchar2varchardatedatetimeColbtext个别语句写法区别个别语句写法区别1.oracle 里只可以用单引号包起字符串,mysql 里可以用双引号和单引号。 2.mysql 在 select * from () .,from 后面是一个结果集时,括号后面必 须加上别名。 3.mysql 在 delete 数据时不能给表加别名,如:delete from table1 T
2、where.,会报错,但是可以这样写:delete T from table1 T where.。 4.Mysql 不支持在同一个表中先查这个表在更新这个表,举个例子说明一下,insert into table1table1 values(字段 1, (select 字段 2 from table1table1 where.) ) ,但是可以在后面那个 table1 加上别名就没有问题了。 insert into table1table1 values(字段 1, (select T.字段 2 from table1table1 T T where.) ) 5.MYSQL 有自动增长的数据类型
3、,插入记录时不用操作此字段,会自动获得数 据值。ORACLE 没有自动增长的数据类型,需要建立一个自动增长的序列号,插 入记录时要把序列号的下一个值赋于此字段。也可以自定义函数实现 oracle 的 nextval。6.翻页的 sql 语句处理,MYSQL 处理翻页的 SQL 语句比较简单,用 LIMIT 开始位置, 记录个数,例如:select * from table limit m,n,意思是从 m+1开始取 n 条。常见的函数替换常见的函数替换1.日期转换方面的函数 oraclemysql说明-_to_char(date,yyyy-MM-dd hh24:mi:ss)date_forma
4、t(date,%Y- %m-%d %H:%i:%s)注意时间格式的对应to_date(str,yyyy-MM-dd hh24:mi:ss)str_to_date(str,%Y-%m- %d %H:%i:%s)注意时间格式to_date(str,yyyy-MM-dd hh24:mi:ss)str_to_date(str,%Y-%m- %d%T)注意时间格式to_char()、to_number()convert(字段名,类型)类型转换 date + ndate_add(date,interval n day)日期增加 n 天 select date_add(sysdate(),INTER VAL
5、 2 DAY); add_months(date,n)date_add(date,interval n month)日期增加 n 个月 select date_add(sysdate(),INTER VAL 2 MONTH); date1 - date2datediff(date1,date2)日期相减获取天数 2.oracle 中 decode()函数,可以用 case when 进行替换 例子: Oracle:select decode(a,b,c,d) as col1 from table1; Mysql:select case when a=b then c when a!=b the
6、n d end as col1 from table1 3.oracle 的函数 ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2), 根据 col1 分组,在分组内根据 col2 排序,改函数计算的值就表示每组内部排序 后的顺序编号。 Mysql 没有这个函数,可以用 mysql 的用户变量来实现 例子: Oracle:select row_number() over(partition by col1 order by col2) as num from table1 Mysql:select num1 as num from (selec
7、t if(pdept=col1,rank:=rank+1,rank:=1) as num1,pdept:=col1 from table1 order by col2 ) H 这里用到 mysql 的用户变量。 4.oracle 的行号 ROWNUM,mysql 没有这个行号,也需要用用户变量来实现。 例子: Oracle:select * from table1 where rownum - col1=0 Mysql: select * from table1,(SELECT (rowNum := 0) HH where (rowNum := rowNum + 1) - col1=0 5.O
8、racle 中的 substr (hello,a,b) mysql 中是 substring(hello,a,b) oracle 的 a=0 和 a=1 是一样的,都是从第一 个开始。Mysql 是从 0 开始。-_6.Oracle 的 nvl()对应 mysql 的 ifnull()。 7.Oracle 中的 WMSYS.WM_CONCAT() ,列转行函数,以逗号隔开,mysql 可以用 GROUP_CONCAT()进行替换。 8.Oracle 的|可以用 mysql 的+替代,但是 mysql 在往某列加上字符时应该用 concat, 例如给表中 name 字段加上 x:update t
9、able1 set name=concat(x,name) 。1.1 移植过程中重点问题 1.1.1 数据类型差异ORACLE 数据库和 MYSQL 数据库在数据类型方面差异比较大,而且数据类型也是一个数据库存储数据的基础,所以找到数据类型之间的对应是整个系统进行移植的基础。以下给出了 ORACLE MYSQL 数据类型的对应关系。数值类型:NUMBER DECIMAL,精度刻度都不变注:如果是序列用 BIGINT字符串类型:VARCHAR2 VARCHAR 长度不变。LONG LONGTEXT这里有可能遇到的问题是超过主键 key 长度的问题,根据实际情况适当修改,如果是TEXT 类型也需要
10、指名长度,否则建立 key 会报错日期类型:-_DATE DATETIMETIMESTAMP(N) TIMESTAMP1.1.2 SQL 语法差异SEQUENCE:MYSQL 没有 ORACLE 中的 SEQUENCE 对象,我们在迁移的时候需要特别注意,一般 SEQUENCE 有两种用途:1、 作为表中自增字段的序列号。2、 程序中获得自动编号。MYSQL 数据类型中存在 AUTO_INCREMENT 为自增数据类型。我们可以利用该数据类型变通一下来满足我们现有系统中的 SEQUENCE 功能。1、 对于 ORACLE 中 SEQUENCE 作为表的自增列一般是通过与触发器绑定实现的,在 M
11、YSQL 中我们可以直接利用 MYSQL 的 AUTO_INCREMENT 类型来实现。2、 ORACLE 开发的应用程序中直接 SELECT SEQUENCT 来获得自动编号,对于这个功能我们也可以利用 MYSQL 的 AUTO_INCREMENT 类型来实现。首先介绍一个函数,我们可以利用如下函数查询最后一个序列号的值:mysql SELECT LAST_INSERT_ID();+-+| LAST_INSERT_ID() |+-+| 3 |+-+1 row in set (0.06 sec)-_我们可以创建一个含有自增列的表,对该表进行 INSERT 操作后,再利用LAST_INSERT_
12、ID()函数来获得刚刚 INSERT 的值,也就是相当于 ORACLE 中的SEQUENCE. NETVAL。也就是 INSERT 操作+SELECT 操作获得一个自动编号。mysql CREATE TABLE MOCHA_BE_SEQUENCE(ID BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT);Query OK, 0 rows affected (0.63 sec)mysql INSERT INTO MOCHA_BE_SEQUENCE VALUES(NULL);Query OK, 1 row affected (0.09 sec)mysql SEL
13、ECT LAST_INSERT_ID();+-+| LAST_INSERT_ID() |+-+| 1 |+-+1 row in set (0.05 sec)mysql INSERT INTO MOCHA_BE_SEQUENCE VALUES(NULL);Query OK, 1 row affected (0.06 sec)mysql SELECT LAST_INSERT_ID();+-+| LAST_INSERT_ID() |-_+-+| 2 |+-+1 row in set (0.00 sec)BLOG:ORACLE 和 MYSQL 都支持二进制大对象,数据类型的名称都是 BLOB,在存储方
14、面都是一样的,BLOB 列没有字符集,并且排序和比较基于列值字节位数;在开发应用程序时需要注意对两种数据库 BLOG 类型的操作的差异。视图Mysql 视图限制(1)SELECT 语句不能包含 FROM 子句中的子查询。(2)SELECT 语句不能引用系统或用户变量。(3)SELECT 语句不能引用预处理语句参数。(4)在存储子程序内,定义不能引用子程序参数或局部变量。(5)在定义中引用的表或视图必须存在。但是,创建了视图后,能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题,可使用 CHECK TABLE 语句。(6)在定义中不能引用 TEMPORARY 表,不能创建 TEMPOR
15、ARY 视图。(7)在视图定义中命名的表必须已存在。(8)不能将触发程序与视图关联在一起。我所要迁移的系统中的视图 90%用到了子查询,解决方案是首先重新写查询语句,尽量避免子查询,避免不了,就将子查询中的内容,单独 create 成一个新的视图,然后再建立所需要的视图。例子:-_Oracle 中带子查询的视图:CREATE OR REPLACE VIEW MOCHA_IM_ALL_ACCOUNT_VIEW ASSELECT USER_ID AS ID, USER_NAME AS NAME, CONCAT(CONCAT(CONCAT(NVL(ORG.ORG_CODE,00000001.1000
16、0000), .00.), PERSON_POSITION.PERSON_LEVEL), LPAD(PERSON_POSITION.ORG_PERSON_NO,9,0) AS CODE, NVL(ORG.ORG_LEVEL, 1) AS REC_LEVEL, PERSON AS REC_TYPEFROM (SELECT PERSON.USER_ID , PERSON.USER_NAME, NVL(POSITION.ORG_ID, -1) AS ORG_ID, NVL(POSITION.ORG_PERSON_NO, 0) AS ORG_PERSON_NO, NVL(POSITION.PERSON
17、_LEVEL, 64) AS PERSON_LEVEL FROM MOCHA_IM_PERSON_POSITION POSITION, MOCHA_IM_PERSON PERSON WHERE PERSON.USER_ID = POSITION.USER_ID (+) AND PERSON.ADMIN_FLAG = 0 And PERSON.STATUS=A)PERSON_POSITION, MOCHA_IM_ORG_VIEW ORGWHERE PERSON_POSITION.ORG_ID = ORG.ORG_ID (+)UNION ALLSELECT USER_ID AS ID, USER_
18、NAME AS NAME, 00000001.20000000.00.64000000000 AS CODE, 1 AS REC_LEVEL, PERSON AS REC_TYPEFROM MOCHA_IM_PERSON PERSON WHERE PERSON.ADMIN_FLAG = 0 AndPERSON.STATUS=IUNION ALLSELECT USER_ID AS ID, USER_NAME AS NAME, 00000001.30000000.00.64000000000 AS CODE,1 AS REC_LEVEL, PERSON AS REC_TYPEFROM MOCHA_
19、IM_PERSON WHERE ADMIN_FLAG = 1 AND STATUS=AUNION ALLSELECT LPAD(ORG_ID,20, ) AS ID, ORG_NAME AS NAME, ORG_CODE AS CODE,ORG_LEVEL AS REC_LEVEL, ORG AS REC_TYPE FROM MOCHA_IM_ORG_VIEW-_UNION ALLSELECT -1 AS ID, 未分派人员 AS NAME, 00000001.10000000 AS CODE, 1 AS REC_LEVEL, ORG AS REC_TYPE FROM DUALUNION AL
20、LSELECT -2 AS ID, 待删除人员 AS NAME, 00000001.20000000 AS CODE, 1 AS REC_LEVEL, ORG AS REC_TYPE FROM DUALUNION ALLSELECT -3 AS ID, 系统管理员 AS NAME, 00000001.30000000 AS CODE, 1 AS REC_LEVEL, ORG AS REC_TYPEFrom DUAL/转为 mysql:CREATE OR REPLACE VIEW PERSON_POSITION ASSELECT PERSON.USER_ID , PERSON.USER_NAME
21、, IFNULL(POSITION.ORG_ID, -1) AS ORG_ID, IFNULL(POSITION.ORG_PERSON_NO, 0) AS ORG_PERSON_NO, IFNULL(POSITION.PERSON_LEVEL, 64) AS PERSON_LEVEL FROM MOCHA_IM_PERSON_POSITION POSITION LEFT JOIN MOCHA_IM_PERSON PERSON ON PERSON.USER_ID = POSITION.USER_ID WHERE PERSON.ADMIN_FLAG = 0 And PERSON.STATUS=A;
22、/CREATE OR REPLACE VIEW MOCHA_IM_ALL_ACCOUNT_VIEW ASSELECT USER_ID AS ID, USER_NAME AS NAME,CONCAT(CONCAT(CONCAT(IFNULL(ORG.ORG_CODE,00000001.10000000), .00.),-_PERSON_POSITION.PERSON_LEVEL), LPAD(PERSON_POSITION.ORG_PERSON_NO,9,0) AS CODE, IFNULL(ORG.ORG_LEVEL, 1) AS REC_LEVEL, PERSON AS REC_TYPEFR
23、OM MOCHA_IM_ORG_VIEW ORG LEFT JOIN PERSON_POSITION ON PERSON_POSITION.ORG_ID = ORG.ORG_ID UNION ALLSELECT USER_ID AS ID, USER_NAME AS NAME, 00000001.20000000.00.64000000000 AS CODE, 1 AS REC_LEVEL, PERSON AS REC_TYPEFROM MOCHA_IM_PERSON PERSON WHERE PERSON.ADMIN_FLAG = 0 AndPERSON.STATUS=IUNION ALLS
24、ELECT USER_ID AS ID, USER_NAME AS NAME, 00000001.30000000.00.64000000000 AS CODE,1 AS REC_LEVEL, PERSON AS REC_TYPEFROM MOCHA_IM_PERSON WHERE ADMIN_FLAG = 1 AND STATUS=AUNION ALLSELECT LPAD(ORG_ID,20, ) AS ID, ORG_NAME AS NAME, ORG_CODE AS CODE,ORG_LEVEL AS REC_LEVEL, ORG AS REC_TYPE FROM MOCHA_IM_O
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle mysql 转换 问题 分析 总结
限制150内